合并 SQL 行

Question

我有 SQL Compact 数据库，其中包含 IP 数据包标头表。该表如下所示：

Table: PacketHeaders    

ID  SrcAddress  SrcPort  DestAddress  DestPort  Bytes
1   10.0.25.1   255      10.0.25.50   500       64
2   10.0.25.50  500      10.0.25.1    255       80
3   10.0.25.50  500      10.0.25.1    255       16
4   75.48.0.25  387      74.26.9.40   198       72
5   74.26.9.40  198      75.48.0.25   387       64
6   10.0.25.1   255      10.0.25.50   500       48

我需要执行查询来显示本地网络上正在进行的“对话”。数据包从 A -> 出发B 是与来自 B -> B 的数据包相同会话的一部分。答：我需要执行查询来显示正在进行的对话。基本上我需要的是如下所示的内容：

Returned Query:

SrcAddress  SrcPort  DestAddress  DestPort  TotalBytes  BytesA->B  BytesB->A
10.0.25.1   255      10.0.25.50   500       208         112        96
75.48.0.25  387      74.26.9.40   198       136         72         64

如您所见，我需要查询（或一系列查询）来识别 A->B 与 B->A 相同，并相应地分解字节计数。无论如何，我不是 SQL 专家，但对此的任何帮助将不胜感激。

Answer 1

试试这个：

SELECT
    T1.SrcAddress,
    T1.SrcPort,
    T1.DestAddress,
    T1.DestPort,
    T1.Bytes + COALESCE(T2.Bytes, 0) AS TotalBytes,
    T1.Bytes AS A_to_B,
    COALESCE(T2.Bytes, 0) AS B_to_A
FROM (
    SELECT SrcAddress, SrcPort, DestAddress, DestPort, SUM(Bytes) AS Bytes
    FROM PacketHeaders
    GROUP BY SrcAddress, SrcPort, DestAddress, DestPort) AS T1
LEFT JOIN (
    SELECT SrcAddress, SrcPort, DestAddress, DestPort, SUM(Bytes) AS Bytes
    FROM PacketHeaders
    GROUP BY SrcAddress, SrcPort, DestAddress, DestPort) AS T2
ON T1.SrcAddress = T2.DestAddress
AND T1.SrcPort = T2.DestPort
AND T1.DestAddress = T2.SrcAddress
AND T1.DestPort = T2.SrcPort
WHERE T1.SrcAddress < T1.DestAddress OR
    (T1.SrcAddress = T1.DestAddress AND T1.SrcPort = T1.DestPort) OR
    T2.DestAddress IS NULL

在这个测试数据上：

CREATE TABLE PacketHeaders (ID INT, SrcAddress NVARCHAR(100), SrcPort INT, DestAddress NVARCHAR(100), DestPort INT, Bytes INT);
INSERT INTO PacketHeaders (ID, SrcAddress, SrcPort, DestAddress, DestPort, Bytes) VALUES
(1, '10.0.25.1', 255, '10.0.25.50', 500, 64),
(2, '10.0.25.50', 500, '10.0.25.1', 255, 80),
(3, '10.0.25.50', 500, '10.0.25.1', 255, 16),
(4, '75.48.0.25', 387, '74.26.9.40', 198, 72),
(5, '74.26.9.40', 198, '75.48.0.25', 387, 64),
(6, '10.0.25.1', 255, '10.0.25.50', 500, 48),
(7, '10.0.25.2', 255, '10.0.25.50', 500, 48),
(8, '10.0.25.52', 255, '10.0.25.50', 500, 48);

这给出了以下结果：

'10.0.25.1', 255, '10.0.25.50', 500, 208, 112, 96
'10.0.25.2', 255, '10.0.25.50', 500, 48, 48, 0
'10.0.25.52', 255, '10.0.25.50', 500, 48, 48, 0
'74.26.9.40', 198, '75.48.0.25', 387, 136, 64, 72

它的工作方式是首先对单向对话进行分组并计算字节总数。这确保了每个对话都将被精确地表示两次——每个方向一次。然后将此结果自连接以提供您需要的结果，通过强制 A 的（地址、端口）必须小于 B 来过滤重复项。左连接用于允许单向对话。

Answer 2

我可以看到两种基本方法......
1. 将其全部分组，忽略 a->b 和 b->a，然后对结果进行自连接。
2. 在“src”字段中使用“最低”IP 地址重新排列数据，同时创建一个“方向”字段。

选项 2 可能是我会选择的方式...

SELECT
    SrcAddress,
    SrcPort,
    DestAddress,
    DestPort,
    SUM(AtoB) + SUM(BtoA),
    SUM(AtoB),
    SUM(BtoA)
FROM
(
    SELECT
       CASE WHEN SrcAddress < DestAddress THEN SrcAddress  ELSE DestAddress END AS SrcAddress,
       CASE WHEN SrcAddress < DestAddress THEN SrcPort     ELSE DestPort    END AS SrcPort,
       CASE WHEN SrcAddress < DestAddress THEN DestAddress ELSE SrcAddress  END AS DestAddress,
       CASE WHEN SrcAddress < DestAddress THEN DestPort    ELSE ScrPort     END AS DestPort,
       CASE WHEN SrcAddress < DestAddress THEN Bytes       ELSE 0           END AS AtoB,
       CASE WHEN SrcAddress < DestAddress THEN 0           ELSE Bytes       END AS BtoA
    FROM
      PacketHeaders
)
    AS [data]
GROUP BY
    SrcAddress,
    SrcPort,
    DestAddress,
    DestPort

编辑

其他几个答案都有我所说的选项 1 的版本。我也会尝试一下，而不是在人们的帖子上发送垃圾评论答案 :(

SELECT
   ISNULL([AtoB].SrcAddress,  [BtoA].DestAddress)
   ISNULL([AtoB].SrcPort,     [BtoA].DestPort)
   ISNULL([AtoB].DestAddress, [BtoA].SrcAddress)
   ISNULL([AtoB].DestPort,    [BtoA].SrcPort)
   ISNULL([AtoB].Bytes,0) + ISNULL([BtoA].Bytes,0),
   ISNULL([AtoB].Bytes,0),
   ISNULL([BtoA].Bytes,0)
FROM
   (
      SELECT   SrcAddress, SrcPort, DestAddress, DestPort, SUM(Bytes) AS Bytes
      FROM     PacketHeaders
      WHERE    SrcAddress <= DestAddress
      GROUP BY SrcAddress, SrcPort, DestAddress, DestPort
   )
   AS [AtoB]
FULL OUTER JOIN
   (
      SELECT   SrcAddress, SrcPort, DestAddress, DestPort, SUM(Bytes) AS Bytes
      FROM     PacketHeaders
      WHERE    SrcAddress > DestAddress
      GROUP BY SrcAddress, SrcPort, DestAddress, DestPort
   )
   AS [BtoA]
      ON  [AtoB].SrcAddress  = [BtoA].DestPort
      AND [AtoB].SrcPort     = [BtoA].DestAddress
      AND [AtoB].DestAddress = [BtoA].SrcPort
      AND [AtoB].DestPort    = [BtoA].SrcAddress

但我确实说过我不会那样做......