MySQL 5.7分组后如何选取出现频率最高的字段值

需求概述 #

将用户按其关联表中出现次数最多的Emotion值分类，同时提取用户记录的年份，相关数据示例如下：

原表数据 #

期望结果 #

限制条件 #

使用MySQL 5.7，无法使用窗口函数（如ROW_NUMBER()）

问题分析 #

原查询仅能统计每个用户每种情绪的出现次数及对应年份，但无法筛选出每个用户情绪次数最高的记录：

select
    user_id,
    YEAR(MAX(registered_on)) as year,
    emotion,
    COUNT(user_id) as how_many_times
from users_emotions
group by user_id, emotion
order by user_id, how_many_times desc

完整解决方案 #

结合GROUP_CONCAT+SUBSTRING_INDEX的方法，同时整合年份提取逻辑，最终查询语句如下：

SELECT
    base.user_id,
    YEAR(base.max_registered) AS year,
    SUBSTRING_INDEX(GROUP_CONCAT(base.emotion ORDER BY base.how_many_times DESC), ',', 1) AS emotion
FROM (
    SELECT
        user_id,
        emotion,
        COUNT(*) AS how_many_times,
        MAX(registered_on) AS max_registered
    FROM users_emotions
    GROUP BY user_id, emotion
) AS base
GROUP BY base.user_id, YEAR(base.max_registered)

逻辑说明 #

1. 内层子查询base：按user_id和emotion分组，统计每种情绪的出现次数，同时获取该用户对应情绪记录中的最晚注册日期（用于提取年份）
2. 外层查询：对每个用户，用GROUP_CONCAT按情绪出现次数降序拼接情绪值，再用SUBSTRING_INDEX截取第一个值（即出现次数最多的情绪）；同时通过YEAR(base.max_registered)提取年份

平局处理说明 #

当同一用户有多种情绪出现次数相同时，该方法会随机选取其中一个情绪值，符合需求要求。

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