Объединение строк SQL

У меня есть SQL Compact Database, которая содержит таблицу заголовков 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 → A. Мне нужно выполнить запрос, чтобы показать текущие разговоры. В основном мне нужно что-то похожее на это:

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 любыми способами, но любая помощь по этому поводу будет очень признательна.

Ответ 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. Левое соединение используется для односторонних цепочек.

Ответ 2

Я вижу два основных способа сделать это...
1. Группируйте все это, игнорируя a- > b и b- > a, а затем присоединяйтесь к результатам.
2. Переупорядочите ваши данные с помощью "самого низкого" IP-адреса в поле "src", но также создайте поле "направление".

Вариант 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

Но я сказал, что не буду так делать...