sql - 如何在PostgreSQL中找到IP对的最佳用法？

我有一个包含source_ip和destination_ip以及用法的表。现在我想找到ip对的最佳用法。 我的桌子是这样的：

source_ip     | destination_ip | usage
192.168.1.1   | 192.168.1.2    | 20
192.168.1.2   | 192.168.1.1    | 30
192.168.1.3   | 192.168.1.2    | 20
192.168.1.2   | 192.168.1.3    | 20

例如，这两个记录是一对。

source_ip   | destination_ip | usage
192.168.1.1 | 192.168.1.2    | 20
192.168.1.2 | 192.168.1.1    | 30

最后我想得到这个

192.168.1.1 and 192.168.1.2 used 50 b
192.168.1.2 and 192.168.1.3 used 40 b

这是我的查询，

with T1 as(
    SELECT  source_ip, distination_ip, sum(usage)
    FROM receiver
    GROUP BY source_ip, distination_ip
)
SELECT DISTINCT * FROM T1 JOIN T1 T2
ON T1.source_ip = T2.distination_ip AND T1.distination_ip = T2.source_ip

我的查询返回：

source_ip     | destination_ip | usage | source_ip     | destination_ip | usage
192.168.1.1   | 192.168.1.2    | 20    | 192.168.1.2   | 192.168.1.1    | 30
192.168.1.2   | 192.168.1.1    | 30    | 192.168.1.1   | 192.168.1.2    | 20
192.168.1.3   | 192.168.1.2    | 20    | 192.168.1.2   | 192.168.1.3    | 20
192.168.1.2   | 192.168.1.3    | 20    | 192.168.1.3   | 192.168.1.2    | 20

最佳答案：

2 个答案:

答案 0 :(得分：3)

您可以做出任意决定来代表＆＃34;较小的＆＃34; IP地址优先，＆＃34;更大＆＃34;第二。从那以后，它就是一个简单的group by和sum：

WITH t1 AS (
    SELECT  LEAST(source_ip, distination_ip) AS ip1,
            GREATEST(source_ip, distination_ip) AS ip2 , 
            usage
    FROM    receiver
)
SELECT   ip1, ip2, SUM(usage)
FROM     t1
GROUP BY ip1, ip2

或者，如果您想在查询本身中进行结果格式化：

WITH t1 AS (
    SELECT  LEAST(source_ip, distination_ip) AS ip1,
            GREATEST(source_ip, distination_ip) AS ip2 , 
            usage
    FROM    receiver
)
SELECT   ip1 || ' and ' || ip2 || ' used ' || SUM(usage) || ' b'
FROM     t1
GROUP BY ip1, ip2

答案 1 :(得分：2)

使用派生表来排序＆＃34;排序＆＃34; IP位址。它的GROUP BY和SUM结果。

select ip1, ip2, sum(usage)
from
(
select case when source_ip < destination_ip then source_ip else destination_ip end ip1,
       case when source_ip > destination_ip then source_ip else destination_ip end ip2,
       usage
from receiver
)
group by ip1, ip2