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
WITH ranked AS (
SELECT
category,
user_id,
COUNT(*) AS freq,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY COUNT(*) DESC, user_id ASC
) AS rn
FROM orders
GROUP BY category, user_id
)
SELECT category, user_id, freq
FROM ranked
WHERE rn <= 3;
遇到并列情况时改用 RANK() 或 DENSE_RANK()
如果同一分类下多个用户消费频次相同(比如都是5次),ROW_NUMBER() 会强行给不同名次(如 1,2,3),而实际业务可能希望并列第1后直接第4(RANK)或第2(DENSE_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,否则自己也算进去了 - 慎用,数据量稍大(单分类超几千用户)就会明显变慢
SELECT o1.category, o1.user_id, o1.freq
FROM (
SELECT category, user_id, COUNT(*) AS freq
FROM orders
GROUP BY category, user_id
) o1
WHERE (
SELECT COUNT(*)
FROM (
SELECT category, user_id, COUNT(*) AS freq
FROM orders
GROUP BY category, user_id
) o2
WHERE o2.category = o1.category
AND o2.freq > o1.freq
) < 3;
别漏掉空值和边缘场景
真实数据里 category 或 user_id 为 NULL 很常见,而 PARTITION BY 默认把 NULL 当作一个独立分组——如果你不想让 NULL 分类参与排名,得提前 WHERE category IS NOT NULL 过滤。
- 某个分类只有2个用户?那结果里就只出2条,不是补空行
- 用户ID是字符串但含前导空格?
GROUP BY会视为空格不同值,建议先TRIM(user_id) - 时间范围没限定?记得在原始
FROM orders加WHERE order_time >= '2024-01-01'等条件,否则统计的是全量历史
窗口函数本身不难,难的是想清楚“高频”到底指什么、并列怎么算、脏数据怎么拦——这些比语法细节更容易让结果出错。

















