PostgreSQL中按子网列表分组IP整数值的最简单查询方法

需求说明 #

现有结构如下的表（假设表名为ip_table）：

ip         | value
2379284488 | 2
2379284482 | 4
3226028805 | 3

给定整数列表[3226028800, 2379284480]，需要生成PostgreSQL查询，得到如下格式的输出：

net        | ip         | value
2379284480 | 2379284488 | 2
2379284480 | 2379284482 | 4
3226028805 | 3226028805 | 3

需满足条件：net & ip = net。

最简查询语句 #

可以用UNNEST把列表转成临时net集合，再与原表关联处理，同时兼容无匹配net的ip记录：

SELECT
    COALESCE(n.net, t.ip) AS net,
    t.ip,
    t.value
FROM
    ip_table t
LEFT JOIN
    UNNEST(ARRAY[3226028800, 2379284480]) AS n(net)
ON
    t.ip & n.net = n.net
ORDER BY
    net, ip;

逻辑说明 #

• UNNEST(ARRAY[...])将给定整数列表转换为包含net列的临时数据集。
• LEFT JOIN确保原表所有记录都被保留，即便没有匹配列表中的net值。
• COALESCE(n.net, t.ip)：匹配到对应net时使用该net值；无匹配时（如示例中的3226028805），直接用ip自身作为net值，贴合输出要求。
• 条件t.ip & n.net = n.net保证net是ip的前缀掩码（按位与运算结果等于net本身）。

内容的提问来源于stack exchange，提问作者markzz