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

sql - 如何在PostgreSQL中找到IP对的最佳用法?,第1张

我有一个包含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)

您可以做出任意决定来代表"较小的" IP地址优先,"更大"第二。从那以后,它就是一个简单的group bysum

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)

使用派生表来排序"排序" IP位址。它的GROUP BYSUM结果。

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
本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复