PostgreSQL如何从表条目查询去重的IPv4子网与超网

你当前使用DISTINCT去重是直接针对主机IP做去重，自然只能返回单个主机地址，PostgreSQL 对inet数据类型提供了原生的网络位操作函数，直接对IP做掩码截断后再去重，就能拿到你需要的聚合子网结果。

固定掩码长度子网提取（满足你当前/24子网需求） #

使用set_masklen函数直接将IP地址的主机位清零，指定掩码长度为24即可得到每个IP所属的/24网段，再做去重排序：

SELECT DISTINCT set_masklen(dst_ip, 24) AS dst_subnet
FROM copy_tcp_3
WHERE dst_ip << '10.40.0.0/11' AND vlanid = 11
ORDER BY dst_subnet;

针对你给出的示例数据，上述查询会返回如下结果，完全匹配你要的输出形式：

dst_subnet
-------------
 10.40.3.0/24
 10.41.3.0/24
 10.42.3.0/24
 10.42.20.0/24
 10.42.25.0/24
 10.43.3.0/24

函数说明 #

set_masklen(inet类型字段, 掩码长度)会直接保留IP对应掩码长度的网络位，将后续主机位全部置0，不需要做字符串切割处理，性能远高于字符串操作，且能完美兼容inet类型的索引。如果后续你需要提取其他掩码长度的网段，比如/16、/22，直接修改第二个参数的掩码数值即可。

使用时注意两个要点：

• 不要先将inet类型转成字符串再做切割取网段，字符串操作不仅性能差，还容易因为IP格式、补零问题出现计算错误，优先使用inet原生函数。
• 如果你的查询频次很高，可以给set_masklen(dst_ip, 24)建表达式索引，查询速度会提升数倍。

动态聚合最大超网（可选进阶方案） #

如果你不需要固定掩码，而是希望将连续存在的IP自动聚合成尽可能大的超网（比如某/23网段下所有地址都有记录时，自动返回/23网段而不是两个/24网段），在PostgreSQL 14及以上版本可以使用原生的网络聚合函数实现：

SELECT network_agg(dst_ip) AS dst_supernet
FROM copy_tcp_3
WHERE dst_ip << '10.40.0.0/11' AND vlanid = 11;

如果你的PostgreSQL版本低于14，需要通过递归CTE逐段合并相邻网段实现动态聚合，逻辑复杂度较高，日常固定掩码的场景用第一种方案足够。

内容的提问来源于stack exchange，提问作者Farzad Heidari Azad