MariaDB分组统计中实现组内多列聚合计数的方法咨询

现有数据表数据如下：

first   second  third
1       1       1
1       1       1
1       2       2
1       1       2
2       3       1

执行基础分组统计查询：

SELECT first, count(*) AS cnt
FROM table
GROUP BY first

得到结果：

first   cnt
1       4
2       1

需要在此基础上，统计first分组内second和third列各值的出现次数，期望得到如下结果：

first   cnt aggregated_count_by_second  aggregated_count_by_third
1       4   {1: 3, 2: 1}                {1: 2, 2: 2}
2       1   {3: 1}                      {1: 1}

使用数据库版本：10.3.39-MariaDB-0+deb10u1

针对该MariaDB版本，可通过GROUP_CONCAT结合字符串拼接生成所需的聚合统计结果，具体SQL语句如下：

SELECT 
    first,
    COUNT(*) AS cnt,
    CONCAT('{', GROUP_CONCAT(DISTINCT CONCAT(second, ':', second_cnt)), '}') AS aggregated_count_by_second,
    CONCAT('{', GROUP_CONCAT(DISTINCT CONCAT(third, ':', third_cnt)), '}') AS aggregated_count_by_third
FROM (
    SELECT 
        first,
        second,
        third,
        COUNT(*) OVER (PARTITION BY first, second) AS second_cnt,
        COUNT(*) OVER (PARTITION BY first, third) AS third_cnt
    FROM `table`
) AS sub
GROUP BY first;

若需要严格符合JSON格式（键为字符串类型），可调整拼接逻辑，给键添加双引号：

SELECT 
    first,
    COUNT(*) AS cnt,
    CONCAT('{"', GROUP_CONCAT(DISTINCT CONCAT('"', second, '":', second_cnt)), '}') AS aggregated_count_by_second,
    CONCAT('{"', GROUP_CONCAT(DISTINCT CONCAT('"', third, '":', third_cnt)), '}') AS aggregated_count_by_third
FROM (
    SELECT 
        first,
        second,
        third,
        COUNT(*) OVER (PARTITION BY first, second) AS second_cnt,
        COUNT(*) OVER (PARTITION BY first, third) AS third_cnt
    FROM `table`
) AS sub
GROUP BY first;

逻辑说明 #

• 子查询中使用窗口函数COUNT(*) OVER (PARTITION BY first, second)，计算每个first分组内不同second值的出现次数，同理处理third列。
• 外层查询通过GROUP_CONCAT将同一first分组内的键值对拼接成字符串，再用CONCAT包裹成类JSON结构。
• 添加DISTINCT避免同一键值对重复输出。

如果后续升级到MariaDB 10.5及以上版本，可直接使用JSON_OBJECTAGG函数简化写法：

SELECT 
    first,
    COUNT(*) AS cnt,
    JSON_OBJECTAGG(second, second_cnt) AS aggregated_count_by_second,
    JSON_OBJECTAGG(third, third_cnt) AS aggregated_count_by_third
FROM (
    SELECT 
        first,
        second,
        third,
        COUNT(*) OVER (PARTITION BY first, second) AS second_cnt,
        COUNT(*) OVER (PARTITION BY first, third) AS third_cnt
    FROM `table`
) AS sub
GROUP BY first;

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