sql - 计算每个最小网络 block 的记录(间隔)

Question

在 SQL (postgresql 8.4.x) 中，我如何有效地COUNT IP 记录的数量落入可能的最小 netblock包括网 block ？例如，我不想在 10/8 和 0/0 下计算 10.0.0.1。

更具体地说，给定:

-- CREATE TABLE iplog (ip INET NOT NULL, ...)
--
      ip      | ...
==============+=====
192.168.1.100 | ...
192.168.1.101 | ...
192.168.55.5  | ...
10.1.2.3      | ...

-- CREATE TABLE netblocks (nb CIDR UNIQUE NOT NULL, ...)
--
       nb      | ...
===============+======
192.168.1.0/24 | ...
192.168.0.0/16 | ...
10.0.0.0/8     | ...
0.0.0.0/0      | ...

如何高效地生成结果集:

       nb      | ips_logged
===============+============
192.168.1.0/24 | 2
192.168.0.0/16 | 1
10.0.0.0/8     | 1

Answer 1

这对我适用于 8.3 - 在 8.4 上也应该没问题。我们需要自定义聚合，因为 max(cidr) 不是内置的(尽管 > 是内置的)

create or replace function greatest_pair(cidr, cidr) 
                  returns cidr
                  language 'sql' immutable as 
$$select greatest($1, $2);$$;

create aggregate max( basetype = cidr, 
                      sfunc = greatest_pair, 
                      stype = cidr );

select max_nb, count(*)
from ( select ip, max(nb) as max_nb 
       from netblocks n join iplog i on(i.ip << n.nb)
       group by ip ) z
group by max_nb;

     max_nb     | count
----------------+-------
 192.168.1.0/24 |     2
 10.0.0.0/8     |     1
 192.168.0.0/16 |     1

如果你不想要自定义聚合，你可以这样做:

create or replace view v as
select ip, nb from netblocks n join iplog i on(i.ip << n.nb);

select nb, count(*)
from ( select * 
       from v o 
       where not exists ( select * 
                          from v i 
                          where i.ip=o.ip and i.nb>o.nb ) ) z
group by nb;

或类似使用 with 子句并且在 8.4 上没有 View ，但问题是有效地 :-)

使用这些 View 进行测试:

create or replace view iplog as
select '192.168.1.100'::inet as ip union all
select '192.168.1.101'::inet union all
select '192.168.55.5'::inet union all
select '10.1.2.3'::inet;

create or replace view netblocks as
select '192.168.1.0/24'::cidr as nb union all
select '192.168.0.0/16'::cidr union all
select '10.0.0.0/8'::cidr union all
select '0.0.0.0/0'::cidr;