MySQL技术问询：如何基于用户维度统计不同story与novel的最高出现次数？

首先先明确你的数据和需求：

原始数据表 #

你的需求 #

你需要给每个用户找出出现次数最多的story和novel，还要满足：

• 统计的是每个值在用户记录中的总出现次数（不是去重后的次数，比如用户1的story a出现3次，所以计数是3）
• 如果用户没有有效story/novel（比如用户4的NULL），对应计数设为0
• 最终要得到你给出的那组输出结果

你当前SQL的问题 #

你写的这条SQL：

SELECT userId, story, COUNT(story) as story_count, novel, COUNT(novel) as novel_count FROM logs WHERE user = (SELECT DISTINCT(user)) GROUP BY story, novel;

存在几个关键问题：

1. WHERE user = (SELECT DISTINCT(user))逻辑完全错误，子查询会返回多个用户ID，直接用=会报错，而且根本没法实现按用户分组统计的目的
2. 同时按story和novel分组，会把两者的组合作为分组单元，这样你得到的是每个（story, novel）组合的计数，而不是分别统计每个用户下story和novel各自的最高频次
3. 没有处理NULL值的情况，也没法保证所有用户都出现在结果里（比如用户4只有NULL值，你的SQL可能会漏掉它）

解决方案 #

要实现这个需求，我们需要分开统计story和novel的频次，再给每个用户挑出频次最高的项，最后合并结果。这里分两种情况给出方案：

方案1：用窗口函数（推荐，适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

窗口函数可以很方便地给每个用户的story/novel按频次排序，直接取第一名即可。完整SQL如下：

WITH story_stats AS (
    SELECT 
        userId,
        story,
        COUNT(*) AS story_count,
        -- 按频次降序排，频次相同则按story值排序（可选，避免随机）
        ROW_NUMBER() OVER (PARTITION BY userId ORDER BY COUNT(*) DESC, story) AS rn_story
    FROM logs
    GROUP BY userId, story
),
novel_stats AS (
    SELECT 
        userId,
        novel,
        COUNT(*) AS novel_count,
        ROW_NUMBER() OVER (PARTITION BY userId ORDER BY COUNT(*) DESC, novel) AS rn_novel
    FROM logs
    GROUP BY userId, novel
),
all_users AS (
    -- 先获取所有用户ID，确保不遗漏任何用户
    SELECT DISTINCT userId FROM logs
)
SELECT 
    au.userId,
    -- 没有有效story时返回NULL，计数转0
    COALESCE(ss.story, NULL) AS story,
    COALESCE(ss.story_count, 0) AS story_count,
    COALESCE(ns.novel, NULL) AS novel,
    COALESCE(ns.novel_count, 0) AS novel_count
FROM all_users au
-- 左关联取每个用户频次最高的story
LEFT JOIN story_stats ss ON au.userId = ss.userId AND ss.rn_story = 1
-- 左关联取每个用户频次最高的novel
LEFT JOIN novel_stats ns ON au.userId = ns.userId AND ns.rn_novel = 1
ORDER BY au.userId;

逻辑解释：

1. story_stats和novel_stats两个CTE分别统计每个用户下每个story/novel的出现次数，并用ROW_NUMBER()给每个用户的项按频次排号，排号1的就是该用户出现次数最多的项
2. all_users CTE确保所有用户都被包含，哪怕只有NULL值的用户（比如用户4）
3. 最后通过左关联把用户表和两个统计结果合并，用COALESCE()把NULL对应的计数转为0，完美匹配你的期望输出

方案2：兼容旧版数据库（无窗口函数，比如MySQL 5.x） #

如果你的数据库不支持窗口函数，可以用自关联的方式找出每个用户下频次最高的项：

SELECT 
    au.userId,
    COALESCE(ss.story, NULL) AS story,
    COALESCE(ss.story_count, 0) AS story_count,
    COALESCE(ns.novel, NULL) AS novel,
    COALESCE(ns.novel_count, 0) AS novel_count
FROM (SELECT DISTINCT userId FROM logs) au
LEFT JOIN (
    -- 找出每个用户频次最高的story
    SELECT 
        s1.userId,
        s1.story,
        s1.story_count
    FROM (
        SELECT userId, story, COUNT(*) AS story_count
        FROM logs
        GROUP BY userId, story
    ) s1
    LEFT JOIN (
        SELECT userId, story, COUNT(*) AS story_count
        FROM logs
        GROUP BY userId, story
    ) s2 ON s1.userId = s2.userId AND s1.story_count < s2.story_count
    -- 当s2.userId为NULL时，说明s1的频次是该用户最高的
    WHERE s2.userId IS NULL
) ss ON au.userId = ss.userId
LEFT JOIN (
    -- 找出每个用户频次最高的novel，逻辑和story一致
    SELECT 
        n1.userId,
        n1.novel,
        n1.novel_count
    FROM (
        SELECT userId, novel, COUNT(*) AS novel_count
        FROM logs
        GROUP BY userId, novel
    ) n1
    LEFT JOIN (
        SELECT userId, novel, COUNT(*) AS novel_count
        FROM logs
        GROUP BY userId, novel
    ) n2 ON n1.userId = n2.userId AND n1.novel_count < n2.novel_count
    WHERE n2.userId IS NULL
) ns ON au.userId = ns.userId
ORDER BY au.userId;

这个方案的核心逻辑是：如果一个项的频次是用户范围内最高的，那么不存在同用户下另一个项的频次比它高，所以左关联后对应的s2.userId/n2.userId会是NULL，以此筛选出最高频次的项。

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