如何通过SQL按IP聚合，生成含Web规则计数列表的单条记录？

现有数据集：

IP,web,rule
12.54.5435,web1,rule1
12.54.5435,web1,rule1
12.54.5435,web2,rule1
12.54.5435,web1,rule2
13.54.5435,web1,rule1
13.54.5435,web1,rule1
13.54.5435,web1,rule1
13.54.5435,web1,rule2

需要为每个IP生成单条记录，格式如下：

total_count,ip, webrulecountlist
4,12.54.5435, ['web1,rule1, 2', 'web2,rule1,1', 'web1,rule2,1']
4,13.54.5435, ['web1, rule1, 3', 'web1, rule2, 1']

已写出内层分组查询：

select count(ip) as c, ip, web as webacl, rule from t1 group by ip, web, rule

该查询输出结果（注：原结果中rulw为笔误，应为rule）：

2,12.54.5435,web1,rule1
1,12.54.5435,web1,rule2
1,12.54.5435,web2,rule1
3,13.54.5435,web1,rule1
1,13.54.5435,web1,rule2

现需按IP进一步分组，将相关列值合并为指定格式的列表，该如何操作？

不同数据库的字符串聚合函数存在差异，以下针对主流数据库给出具体实现方案：

MySQL #

利用GROUP_CONCAT函数完成字符串拼接，外层按IP分组并计算总记录数：

SELECT 
    SUM(c) AS total_count,
    ip,
    CONCAT('[', GROUP_CONCAT(DISTINCT CONCAT('''', webacl, ',', rule, ', ', c, '''') SEPARATOR ', '), ']') AS webrulecountlist
FROM (
    SELECT count(ip) AS c, ip, web AS webacl, rule 
    FROM t1 
    GROUP BY ip, web, rule
) AS sub
GROUP BY ip;

• CONCAT('''', webacl, ',', rule, ', ', c, '''')：将每个子分组的web、rule、计数拼接为带单引号的格式，例如'web1,rule1, 2'
• GROUP_CONCAT(...) SEPARATOR ', '：将所有拼接后的字符串用逗号分隔
• CONCAT('[', ..., ']')：为整体添加方括号，形成目标列表格式
• SUM(c)：统计每个IP对应的总记录数

PostgreSQL #

使用STRING_AGG函数实现字符串聚合，若需要匹配目标格式可直接拼接字符串：

SELECT 
    SUM(c) AS total_count,
    ip,
    CONCAT('[', STRING_AGG(DISTINCT CONCAT('''', webacl, ',', rule, ', ', c, ''''), ', '), ']') AS webrulecountlist
FROM (
    SELECT count(ip) AS c, ip, web AS webacl, rule 
    FROM t1 
    GROUP BY ip, web, rule
) AS sub
GROUP BY ip;

如果需要生成真正的数组类型而非字符串格式，可改用：

SELECT 
    SUM(c) AS total_count,
    ip,
    ARRAY_AGG(DISTINCT CONCAT(webacl, ',', rule, ', ', c)) AS webrulecountlist
FROM (
    SELECT count(ip) AS c, ip, web AS webacl, rule 
    FROM t1 
    GROUP BY ip, web, rule
) AS sub
GROUP BY ip;

SQL Server #

SQL Server 2017及以上版本支持STRING_AGG，用法如下：

SELECT 
    SUM(c) AS total_count,
    ip,
    CONCAT('[', STRING_AGG(DISTINCT CONCAT('''', webacl, ',', rule, ', ', c, ''''), ', '), ']') AS webrulecountlist
FROM (
    SELECT COUNT(ip) AS c, ip, web AS webacl, rule 
    FROM t1 
    GROUP BY ip, web, rule
) AS sub
GROUP BY ip;

若使用SQL Server 2016及以下版本，需通过FOR XML PATH模拟字符串聚合：

SELECT 
    SUM(c) AS total_count,
    ip,
    CONCAT('[', STUFF((
        SELECT DISTINCT ', ''' + webacl + ',' + rule + ', ' + CAST(c AS VARCHAR) + ''''
        FROM (
            SELECT COUNT(ip) AS c, ip, web AS webacl, rule 
            FROM t1 
            GROUP BY ip, web, rule
        ) AS sub2
        WHERE sub2.ip = sub.ip
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, ''), ']') AS webrulecountlist
FROM (
    SELECT COUNT(ip) AS c, ip, web AS webacl, rule 
    FROM t1 
    GROUP BY ip, web, rule
) AS sub
GROUP BY ip;

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