SQL如何实现分组内数据的排名波动分析_使用窗口函数-SQL-PHP中文网

row_number() 生成唯一序号（1,2,3），rank() 并列跳号（1,1,3），dense_rank() 并列不跳号（1,1,2）；分组必用 partition by，order by 需显式处理 null。

sql如何实现分组内数据的排名波动分析_使用窗口函数

怎么用 `ROW_NUMBER()` 和 `RANK()` 区分“并列”和“跳号”

分组内排名是否允许并列，直接决定你该选哪个窗口函数。比如按用户分组、按订单时间倒序排单，若两人同一天下单，ROW_NUMBER() 会硬给 1 和 2，而 RANK() 给两个 1，下一个就是 3——这是最常被忽略的语义差异。

实操建议：

ROW_NUMBER()：适合需要唯一序号的场景，比如取每组第 N 条记录（WHERE rn = 1），但不反映真实业务并列关系
RANK()：适合排行榜类需求，如“销售额并列第一的两位都算 Top 1”，但后续名次会跳空（1,1,3）
DENSE_RANK()：并列不跳号（1,1,2），更适合做连续档位划分，比如“前 3 名”这种区间判断

分组内波动分析必须加 `PARTITION BY`，漏写就全表错乱

窗口函数默认作用于整个结果集。不做分组，ORDER BY create_time DESC 就是全表时间倒序，根本不是“每个用户的首次/末次订单”。错误现象往往是：排名数字看着合理，但一核对发现 A 用户的第 2 单跑到了 B 用户的第 1 单前面。

实操建议：

只要目标是“每个用户”“每个品类”“每天”这类粒度，PARTITION BY 就是强制项，不能省
PARTITION BY user_id 和 PARTITION BY user_id, DATE(create_time) 语义完全不同，后者是按天再切分，注意业务口径是否含日期维度
MySQL 8.0+、PostgreSQL、SQL Server 都支持，但旧版 MySQL（5.7 及以前）不支持窗口函数，会直接报错 FUNCTION xxx does not exist

用 `LAG()` / `LEAD()` 算相邻排名差值，别在外部 JOIN

想看某个用户订单排名是否上升/下降，本质是比当前行和上一行的排名值。有人习惯先算出每行排名，再用子查询或 CTE 做自连接匹配“前一条”，这既慢又易出错——尤其当分组内数据稀疏（比如某用户隔了三个月才下第二单），JOIN 条件极易漏匹配。

实操建议：

直接在窗口函数中嵌套：LAG(rank_col) OVER (PARTITION BY user_id ORDER BY create_time)
计算波动值：rank_col - LAG(rank_col) OVER (...)，结果为负说明排名上升（数字变小）
注意 LAG() 默认返回 NULL，首条记录无“上一条”，需用 COALESCE(..., 0) 或 CASE WHEN 处理，否则波动列大量空值

ORDER BY 里混用多个字段时，NULL 值顺序影响排名稳定性

比如按 score DESC, update_time DESC 排名，但某些记录 score 为空。不同数据库对 NULLS FIRST/LAST 默认处理不同：PostgreSQL 默认 NULLS LAST，而 Oracle 默认 NULLS FIRST。这会导致同一 SQL 在不同环境排名不一致，波动分析结果漂移。

实操建议：