SQL实现分组查询时获取组内出现频次最高的字段值

需要基于数据库记录的出现频次，查询对应用户的偏好分类。最初参考其他示例编写的SQL如下，返回结果不符合预期：

SELECT thread_id AS tid,
    (SELECT user_id FROM thread_posts 
        WHERE thread_id = tid 
        GROUP BY user_id
        ORDER BY COUNT(*) DESC
        LIMIT 0,1) AS topUser
FROM thread_posts
GROUP BY thread_id

业务表通过User Section和User Sub Section两个字段唯一标识单个用户，表结构与测试数据如下：

User Section | User Sub Section | Category
------------------------------------------
1            | A                | Foo
1            | A                | Bar
1            | A                | Foo
1            | B                | 123
2            | A                | Bar
2            | A                | Bar
2            | A                | Bar
2            | A                | Foo
3            | A                | 123
3            | A                | 123
3            | B                | Bar
4            | A                | Foo

预期返回每个(User Section, User Sub Section)分组下出现次数最多的Category值，预期结果如下：

User Section | User Sub Section | Category
------------------------------------------
1            | A                | Foo
1            | B                | 123
2            | A                | Bar
3            | A                | 123
3            | B                | Bar
4            | A                | Foo

原SQL的逻辑只适配了单字段thread_id分组取分组内top1的场景，既没有匹配双字段的用户唯一标识关联条件，也没有针对Category维度做频次统计，需要调整逻辑。

兼容低版本数据库（无窗口函数支持） #

所有主流关系型数据库都可以运行该写法，逻辑是先统计每个用户分组下每个Category的出现次数，再关联取出每个分组下频次最高的记录：

SELECT 
    t.`User Section`,
    t.`User Sub Section`,
    t.Category
FROM (
    SELECT 
        `User Section`,
        `User Sub Section`,
        Category,
        COUNT(*) AS cnt
    FROM thread_posts -- 替换为实际业务表名
    GROUP BY `User Section`, `User Sub Section`, Category
) t
JOIN (
    SELECT 
        `User Section`,
        `User Sub Section`,
        MAX(cnt) AS max_cnt
    FROM (
        SELECT 
            `User Section`,
            `User Sub Section`,
            Category,
            COUNT(*) AS cnt
        FROM thread_posts -- 替换为实际业务表名
        GROUP BY `User Section`, `User Sub Section`, Category
    ) tmp
    GROUP BY `User Section`, `User Sub Section`
) t2
ON t.`User Section` = t2.`User Section` 
AND t.`User Sub Section` = t2.`User Sub Section`
AND t.cnt = t2.max_cnt;

注意：如果同一个用户分组下有多个Category出现频次并列最高，该写法会返回所有并列的结果。

支持窗口函数的数据库（MySQL8.0+、PostgreSQL、SQL Server等） #

用窗口函数写法更简洁，执行效率也更高：

WITH category_rank AS (
    SELECT
        `User Section`,
        `User Sub Section`,
        Category,
        ROW_NUMBER() OVER (
            PARTITION BY `User Section`, `User Sub Section`
            ORDER BY COUNT(*) DESC
        ) AS rk
    FROM thread_posts -- 替换为实际业务表名
    GROUP BY `User Section`, `User Sub Section`, Category
)
SELECT `User Section`, `User Sub Section`, Category
FROM category_rank
WHERE rk = 1;

注意：该写法用ROW_NUMBER()时，并列最高频的场景只会返回其中一条Category；如果需要返回所有并列最高频结果，可以把ROW_NUMBER()替换为RANK()。另外带空格的字段名需要根据你使用的数据库选择对应的标识符包裹：MySQL用反引号、SQL Server用方括号、PostgreSQL用双引号。

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