如何泛化MySQL查询以获取每个user_id的Top5应用？

别担心，完全不需要针对每个用户单独执行查询——用一条SQL语句就能实现所有用户对应的Top5应用统计。下面根据你的MySQL版本，给你两种可行方案：

方法1：窗口函数（MySQL 8.0及以上版本推荐） #

这是最简洁高效的方案，利用MySQL 8.0引入的窗口函数ROW_NUMBER()来为每个用户的应用按时长排序，筛选出前5条记录。

示例SQL #

SELECT sum_duration, app_name, app_id, user_id
FROM (
    SELECT 
        SUM(duration) AS sum_duration,
        app_name,
        app_id,
        user_id,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY SUM(duration) DESC) AS rn
    FROM timesheet
    GROUP BY user_id, app_id, app_name
) AS ranked
WHERE rn <= 5;

说明 #

• PARTITION BY user_id：按用户分组，每个用户单独计算排序逻辑
• ORDER BY SUM(duration) DESC：每个用户组内按应用总时长倒序排列
• ROW_NUMBER()：给每个用户组内的记录分配唯一行号，行号≤5的就是该用户的Top5应用
• 如果业务允许并列排名（比如两个应用总时长相同，都算入Top5），可以替换为：
  • RANK()：相同时长会获得相同行号，后续行号会跳跃（例如1,1,3）
  • DENSE_RANK()：相同时长获得相同行号，后续行号连续（例如1,1,2）

方法2：自定义变量（兼容MySQL 5.x版本） #

如果你的MySQL版本低于8.0，不支持窗口函数，可以用用户自定义变量模拟分组排序逻辑：

示例SQL #

SELECT sum_duration, app_name, app_id, user_id
FROM (
    SELECT 
        SUM(duration) AS sum_duration,
        app_name,
        app_id,
        user_id,
        @rn := IF(@current_user = user_id, @rn + 1, 1) AS rn,
        @current_user := user_id
    FROM timesheet, (SELECT @current_user := -1, @rn := 0) AS init
    GROUP BY user_id, app_id, app_name
    ORDER BY user_id, sum_duration DESC
) AS ranked
WHERE rn <= 5;

说明 #

• 初始化变量@current_user（跟踪当前用户）和@rn（当前用户组内的行号）
• 先按user_id和sum_duration DESC排序，确保同一用户的应用按时长倒序排列
• 通过IF判断当前记录的用户是否和上一条相同，相同则行号+1，否则重置为1
• 最后筛选行号≤5的记录，得到每个用户的Top5应用

效果验证 #

用你给出的user_id=164的测试数据验证，上述查询会返回该用户的5条Top记录（和你原查询结果一致），同时还会返回其他所有用户的Top5应用数据。

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