ROW_NUMBER()需按category分组、消费频次降序编号取前3,但关键在明确定义“高频”、处理并列及脏数据拦截,否则结果易错。

用窗口函数 ROW_NUMBER() 按分类分组排序取前3
直接在子查询或 CTE 中对每个 category 内按消费频次(如 count(*))降序排号,再外层筛选 rn 。这是最通用、可读性最强的做法,MySQL 8.0+、PostgreSQL、SQL Server、Oracle 都支持。
常见错误是用 GROUP BY 后直接加 LIMIT 3——这会全局只取3条,不是每类3条。
- 必须用
PARTITION BY category划分组,否则编号跨分类连续 - 排序字段要明确:如果是“高频消费用户”,通常指该用户在该分类下的订单数,即
COUNT(*);若已有用户-分类汇总表,直接按freq排 - 注意
ORDER BY COUNT(*) DESC要写在OVER里,不是外面的ORDER BY
RANK()
-
RANK():并列则跳号,如 [5,5,3,3,1] → [1,1,3,3,5] -
DENSE_RANK():并列不跳号,如 [5,5,3,3,1] → [1,1,2,2,3] - 只要把上例中的
ROW_NUMBER()换成对应函数即可,其余逻辑不变
MySQL 5.7 或更老版本不能用窗口函数?用关联子查询硬刚
没有 ROW_NUMBER() 就得靠自连接或相关子查询数“有多少人频次更高”。性能差、写法绕,但能跑通。
- 核心思路:对每个
(category, user_id),统计同分类下频次严格大于它的用户数,若小于3,就说明它在前3名内(允许并列) - 必须加
AND o2.user_id != o1.user_id,否则自己也算进去了 - 慎用,数据量稍大(单分类超几千用户)就会明显变慢
category
- 某个分类只有2个用户?那结果里就只出2条,不是补空行
- 用户ID是字符串但含前导空格?
GROUP BY会视为空格不同值,建议先TRIM(user_id) - 时间范围没限定?记得在原始
FROM orders加WHERE order_time >= '2024-01-01'等条件,否则统计的是全量历史
窗口函数本身不难,难的是想清楚“高频”到底指什么、并列怎么算、脏数据怎么拦——这些比语法细节更容易让结果出错。










