SQL窗口函数实战：避开排名逻辑中的那些“坑”
先说一个核心判断：rank()和dense_rank()在多条件排序时，行为其实是一致的。它们真正的区别只在于名次是否“跳号”（比如出现1,1,3还是1,1,2）。真正决定数据能否并列的，其实是ORDER BY子句里是否包含了完整的排序字段，而不是你选了哪个窗口函数。
rank() 和 dense_rank() 在多条件排序时的行为差异
我们来看一个典型场景：要求“先按销售额降序排，销售额相同的再按注册时间升序排，如果都相同才允许并列”。这时候，rank()和dense_rank()本身并不决定并列逻辑，真正起作用的，是ORDER BY子句里那些完整的排序表达式。
一个常见的错误是，只写了ORDER BY sales DESC，结果发现注册时间相同的用户被错误地并列了——原因很简单，因为你没把次级排序字段放进ORDER BY里。
正确的写法必须把所有排序优先级都显式写出来：
SELECT user_id, sales, register_time,
rank() OVER (ORDER BY sales DESC, register_time ASC) AS rnk
FROM users;
这里需要特别注意：当排序键完全相同时，rank()和dense_rank()的行为是一致的（都会产生并列）。它们的差异只体现在后续名次是否跳号上。选择用哪个，得看业务需求，这可不是用来“修复”并列逻辑问题的。
用子查询预处理解决动态分组内排名
真实业务里，需求往往更复杂。比如，需要“在每个城市内部按销量排名”，但“城市”这个分组依据本身就很混乱：可能需要先过滤掉无效的城市编码、合并不同的别名、或者排除测试数据。这时候，直接上PARTITION BY city就行不通了。
千万别硬套窗口函数。更安全的做法是，先在子查询里把分组依据算得清清楚楚、明明白白。
典型的错误是试图在OVER子句里写一个CASE表达式来做动态分区，比如PARTITION BY (CASE ...)。但多数数据库（像PostgreSQL、SQL Server）并不支持在PARTITION BY里放这么复杂的逻辑，要么直接报错，要么执行结果和你想的不一样。
稳妥的解决方案是把分组字段提前计算好：
SELECT user_id, city_clean, sales,
dense_rank() OVER (PARTITION BY city_clean ORDER BY sales DESC) AS city_rank
FROM (
SELECT user_id,
CASE
WHEN city IN ('BJ', 'BEIJING') THEN 'Beijing'
WHEN city LIKE '%SH%' THEN 'Shanghai'
ELSE city
END AS city_clean,
sales
FROM raw_users
WHERE city IS NOT NULL AND user_id NOT LIKE 'TEST%'
) t;
这里的要点在于：子查询输出的city_clean字段，必须是一个确定的、非空的、已经归一化处理好的值。只有这样，它才能被稳定地用于PARTITION BY进行分组。
WHERE 不能直接过滤窗口函数结果，必须用 CTE 或嵌套
如果想查询“每个城市销量排名前3的用户”，直接写WHERE rank() OVER (...) <= 3是行不通的。你会立刻收到一个错误：window functions are not allowed in WHERE。这是由SQL的执行顺序决定的——WHERE子句在窗口计算之前就已经执行了。
所以，必须先把窗口计算的结果变成一个普通的列，然后才能在外层进行筛选。这里有几种方法：
- 使用CTE（公共表表达式），代码最清晰：
WITH ranked AS ( SELECT *, rank() OVER (PARTITION BY city ORDER BY sales DESC) AS rnk FROM users ) SELECT * FROM ranked WHERE rnk <= 3;
- 用嵌套子查询也可以，但可读性会差一些。另外要注意，某些旧版本的MySQL（8.0以前）不支持在子查询里使用窗口函数。
- 记住，别想用
HA VING来代替——HA VING是针对GROUP BY聚合结果的，跟窗口函数半毛钱关系都没有，强行使用只会导致语法错误。
性能陷阱：ORDER BY 表达式含函数时索引失效
这才是最容易被忽略的性能杀手。当你的排名依据是一个计算字段时，比如ORDER BY ABS(sales - target)（按与目标值的绝对差排序）或者ORDER BY UPPER(name)（按大写后的名字排序），问题就来了。
即使sales或name字段上建有索引，数据库在大多数情况下也无法利用这个索引来加速排序。结果就是，每次查询都会触发一次全表扫描加上文件排序（filesort），数据量一大，查询延迟就会飙升。
优化方向其实很实际：
- 如果计算逻辑是固定的（比如永远用
UPPER(name)排序），可以考虑建立函数索引：CREATE INDEX idx_name_upper ON users (UPPER(name));
（PostgreSQL、Oracle等数据库支持；MySQL 8.0+版本通常需要通过创建存储生成列来间接实现。） - 更通用的做法是冗余一个已计算好的列，比如新增一个
name_upper字段。通过触发器或应用层逻辑来保证它的实时同步，然后直接ORDER BY name_upper，这样就能愉快地使用普通索引了。 - 务必避免在
ORDER BY里调用用户自定义函数（UDF），它的执行开销往往不可控。
说到底，设计复杂排名规则时，最容易被忽略的恰恰是排序字段的“物理可索引性”。窗口函数写得再精巧，如果卡在排序这一步需要全表计算，那整个查询的性能就会退化到线性扫描，这才是关键所在。
