如何用单条SQL查询将关联IP与域名归组并按对应域名统计数量

实现方案 #

核心逻辑是先通过规则将原始url映射为归属的域名别名，再按别名分组统计，可通过以下两种方式实现：

方式1：映射规则固定时使用CASE表达式（单条SQL无需额外表）

SELECT 
    CASE 
        WHEN url IN ('10.100.15', '11.22.33', 'domain2.com') THEN 'domain2'
        WHEN url IN ('172.100.15', 'domain3.com') THEN 'domain3'
        WHEN url = 'domain.com' THEN 'domain'
    END AS url,
    COUNT(*) AS count
FROM table1
GROUP BY 1
-- 若不需要统计未匹配映射的记录，可加下行过滤
-- HAVING url IS NOT NULL

注：如果所用数据库不支持GROUP BY引用SELECT别名，把GROUP BY 1替换为整个CASE表达式即可。

方式2：映射规则频繁变动时使用映射表（扩展性更强）

先单独新建映射表url_domain_map存储原始url和归属域名的对应关系：

再通过JOIN关联查询：

SELECT m.belong_domain AS url, COUNT(*) AS count
FROM table1 t
INNER JOIN url_domain_map m ON t.url = m.origin_url
GROUP BY m.belong_domain

后续新增映射关系只需在映射表中插入数据，无需修改SQL逻辑。

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