按最大連續行分組

我有如下數據：

id  | src_ip         | dst_ip       | port |
----|----------------|--------------|------|
256 | 192.168.61.200 | 10.75.64.222 | 80   |
257 | 192.168.61.200 | 10.75.64.222 | 81   |
258 | 10.65.72.207   | 10.75.64.223 | 80   |
259 | 10.75.254.27   | 10.75.64.223 | 82   |
260 | 10.75.254.27   | 10.75.64.224 | 80   |
261 | 10.75.254.27   | 10.75.64.230 | 80   |
262 | 10.75.254.27   | 10.75.64.230 | 81   |
263 | 10.75.254.27   | 10.75.64.230 | 82   |
264 | 10.75.254.27   | 10.75.64.230 | 83   |
265 | 10.75.254.27   | 10.75.64.230 | 84   |

我在這裡建構了架構並載入了相同的數據：http: //sqlfiddle.com/#!9/ 1185c

我打算對行進行分組，以便為我的防火牆重現一些規則。鑑於防火牆支持埠或 IP 的範圍，我想查詢此表並儘可能多地對行進行分組以生成有效規則。

有幾種不同的方法可以對行進行分組，但我想dst_ip在同一個埠上按連續分組。

鑑於上面提供的表格，我想得到這樣的東西：

dst_ip       | max_consecutive | port |
--------------|-----------------|------|
10.75.64.222 | 10.75.64.224    | 80   |
10.75.64.222 | 10.75.64.222    | 81   |
10.75.64.223 | 10.75.64.223    | 82   |
10.75.64.230 | 10.75.64.230    | 80   |
10.75.64.230 | 10.75.64.230    | 81   |
10.75.64.230 | 10.75.64.230    | 82   |
10.75.64.230 | 10.75.64.230    | 83   |
10.75.64.230 | 10.75.64.230    | 84   |

注意：max_consecutive也可以是連續行數，不一定是上位IP。

實際上，如果我可以更深入，我也想分組port。這意味著結果的最後 5 行將匯總為類似

10.75.64.230 | 10.75.64.230    | 80   | 84

我目前正在使用 MySQL，但如果有必要，我可以遷移到 PostgreSQL。

在Postgres（使用 v9.3 測試）中，您可以使用專用**inet**數據類型來儲存僅 7 個字節的 IPv4 地址（或具有 19 個字節的 IPv6），並具有自動完整性檢查和專用功能和類型轉換等。

架構

翻譯後的架構可能如下所示：

CREATE TABLE log (
 id serial PRIMARY KEY
, dst_port int
, src_ip inet
, dst_ip inet
);
CREATE INDEX ON log (dst_port);
CREATE INDEX ON log (src_ip);

我移至dst_port int第二個位置以優化對齊/填充：

• 為讀取性能配置 PostgreSQL

現在我們可以使用標準的視窗函式（在 MySQL 中是不可能的）。

第 1 步：將連續的組折疊dst_ip成相同的 ( dst_port)

一個特殊的困難：聚合函式min()/max()尚未inet在 Postgres 9.4 中實現。兩者都在即將發布的 Postgres 9.5 中！

DISTINCT ON所以我在第一步中替換為：

• 在每個 GROUP BY 組中選擇第一行？

SELECT DISTINCT ON (dst_port, ip_grp)
      dst_ip, count(*) OVER (PARTITION BY dst_port, ip_grp) AS ip_ct, dst_port
FROM  (
  SELECT dst_ip, dst_port, dst_ip - row_number() OVER (PARTITION BY dst_port
                                                       ORDER BY dst_ip) AS ip_grp
  FROM   log
  ORDER  BY dst_port, dst_ip
  ) sub
ORDER  BY dst_port, ip_grp, dst_ip;

所需的結果 - 行數（也可以是上層 IP）。

integer您可以從類型中減去/添加inet。通過減去row_number()所有連續的行得到相同的grp- 的值grp是無關緊要的，只是每個分區相同的快（dst_port）。

然後我們可以GROUP BY ...——或者在這種特殊情況下DISTINCT ON dst_port, ip_grp。ip_ct我使用另一個視窗函式在同一步驟中獲取計數： count(*) OVER (PARTITION BY dst_port, ip_grp) AS ip_ct.

請注意，連續的 IP 可以跨越字節邊界（請參閱我對問題的評論）。

該技術的詳細解釋：

• 選擇最長的連續序列

dst_port第2步：將連續的組折疊起來(dst_ip, ip_ct)

SELECT dst_ip, ip_ct, min(dst_port) AS dst_port, count(*) AS port_ct
FROM  (
  SELECT *, dst_port - row_number() OVER (PARTITION BY dst_ip, ip_ct
                                          ORDER BY dst_port) AS port_grp
  FROM  (
     SELECT DISTINCT ON (dst_port, ip_grp)
            dst_ip, count(*) OVER (PARTITION BY dst_port, ip_grp) AS ip_ct, dst_port
     FROM  (
        SELECT dst_ip, dst_port, dst_ip - row_number() OVER (PARTITION BY dst_port
                                                             ORDER BY dst_ip) AS ip_grp
        FROM   log
        ORDER  BY dst_port, dst_ip
        ) sub1
     ORDER  BY dst_port, ip_grp, dst_ip
     ) sub2
  ) sub3
GROUP  BY 1, 2, port_grp
ORDER  BY 1, 3, 2;

基本上，重複與第一步相同的邏輯，應用於第一步的結果。

但是現在您必須ip_ct另外分組。而這一次，您可以使用更簡單的min(dst_port)，因為埠號是一個普通的integer.

SQL Fiddle展示了所有內容。

引用自：https://dba.stackexchange.com/questions/100475