SQL - 跨列的分组依据值

假设最小的 IP 是源 IP，最大的 IP 是目标 IP。

您可以使用 和 函数来确保对于最小和最大 IP 地址的每个组合，将选择一个条目：LEASTGREATEST

with cte as (
  select least(src_ip, dst_ip) as smallestIP, greatest(src_ip, dst_ip) as largestIP
  from mytable src
  group by least(src_ip, dst_ip), greatest(src_ip, dst_ip)
),
routes as (
  select distinct src_ip, src_port, dst_ip, dst_port 
  from (
    select src_ip, src_port, dst_ip, dst_port 
    from mytable t
    inner join cte c on t.src_ip = c.smallestIP
    union all
    select dst_ip as src_ip, dst_port as src_port, src_ip as dst_ip, src_port as dst_port
    from mytable t
    inner join cte c on t.dst_ip = c.smallestIP
  ) as s
)
select r.src_ip, r.src_port, r.dst_ip, r.dst_port,
       sum(case when r.src_ip = t.src_ip and r.src_port = t.src_port
                     and r.dst_ip = t.dst_ip and r.dst_port = t.dst_port
                then bytes_sent else 0 end ) as bytes_sent,
       sum(case when r.src_ip = t.dst_ip and r.src_port = t.dst_port
                     and r.dst_ip = t.src_ip and r.dst_port = t.src_port
                then bytes_sent else 0 end ) as bytes_recv
from routes r
inner join mytable t on (
                        r.src_ip = t.src_ip and r.src_port = t.src_port
                        and r.dst_ip = t.dst_ip and r.dst_port = t.dst_port)
                      or (
                        r.src_ip = t.dst_ip and r.src_port = t.dst_port
                        and r.dst_ip = t.src_ip and r.dst_port = t.src_port
                      )
group by r.src_ip, r.src_port, r.dst_ip, r.dst_port

在这里演示

您可以使用 self-：join

with cte as (
   select row_number() over (order by greatest(n.src_ip, n.dst_ip)) r, n.src_ip, n.src_port, n.dst_ip, n.dst_port, min(n.bytes_sent) bytes_sent from netflow n
   group by n.src_ip, n.src_port, n.dst_ip, n.dst_port
)
select n.src_ip, n.src_port, n.dst_ip, n.dst_port, n.bytes_sent, 
   coalesce(n1.bytes_sent, 0) bytes_recieved
from cte n left join cte n1 on n1.src_port = n.dst_port
where not exists (select 1 from cte n2 where n2.r < n.r and n2.dst_port = n.src_port)

看小提琴

为了确定哪个 IP、端口和方向，您必须在汇总结果中制定一个规则，将谁视为发送方和接收方。让我们将较小的 IP 作为源，将较大的 IP 作为目标。然后，一次又一次地使用几乎相同的表达式来决定将哪个原始列放入哪个结果列中。完成此操作后，汇总数据。CASE

with 
  data as
  (
    select
      case when src_ip < dst_ip then  src_ip      else  dst_ip      end as source_ip,
      case when src_ip < dst_ip then  dst_ip      else  src_ip      end as dest_ip,
      case when src_ip < dst_ip then  src_port    else  dst_port    end as source_port,
      case when src_ip < dst_ip then  dst_port    else  src_port    end as dest_port,
      case when src_ip < dst_ip then  bytes_sent  else  0           end as sent,
      case when src_ip < dst_ip then  0           else  bytes_sent  end as received
    from mytable
  )
select
  source_ip, source_port, dest_ip, dest_port,
  sum(sent) as bytes_sent,
  sum(received) as bytes_received
from data
group by source_ip, source_port, dest_ip, dest_port
order by source_ip, source_port, dest_ip, dest_port;

您可以通过使用以下语句的组合来实现所需的输出：GROUP BY、CASE 和 SUM 函数来聚合函数

可以按如下方式完成以下查询：

SELECT
    CASE WHEN src_ip < dst_ip THEN src_ip ELSE dst_ip END AS src_ip,
    CASE WHEN src_ip < dst_ip THEN src_port ELSE dst_port END AS src_port,
    CASE WHEN src_ip < dst_ip THEN dst_ip ELSE src_ip END AS dst_ip,
    CASE WHEN src_ip < dst_ip THEN dst_port ELSE src_port END AS dst_port,
    SUM(CASE WHEN src_ip < dst_ip THEN bytes_sent ELSE 0 END) AS bytes_sent,
    SUM(CASE WHEN src_ip < dst_ip THEN 0 ELSE bytes_sent END) AS bytes_recv
FROM your_table
GROUP BY
    CASE WHEN src_ip < dst_ip THEN src_ip ELSE dst_ip END,
    CASE WHEN src_ip < dst_ip THEN src_port ELSE dst_port END,
    CASE WHEN src_ip < dst_ip THEN dst_ip ELSE src_ip END,
    CASE WHEN src_ip < dst_ip THEN dst_port ELSE src_port END;

上面的 CASE 语句将根据 src 和 dst 值的词法顺序确定它们的顺序，以确保两个方向的分组一致。而 SUM 和 CASE 语句用于分别聚合不同方向（正向和反向）的bytes_sent值。

您可以将其简化为 least（）、greatest（） 和聚合过滤器。Demo@db<>小提琴：

select least(   src_ip,   dst_ip  ) AS src_ip,
       least(   src_port, dst_port) AS src_port,
       greatest(src_ip,   dst_ip  ) AS dst_ip,
       greatest(src_port, dst_port) AS dst_port,
       coalesce( sum(bytes_sent)filter(where src_ip=least(src_ip,dst_ip))
                ,0) AS bytes_sent,
       coalesce( sum(bytes_sent)filter(where dst_ip=least(src_ip,dst_ip))
                ,0) AS bytes_recv
from netflow group by 1,2,3,4;

如果你喜欢任何没有流量的地方，你可以放弃将其换成 .null0