rank()和dense_rank()在多条件排序时行为一致，差异仅在跳名（如1,1,3 vs 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() 在排序键完全相同时行为一致（都并列），差异只体现在跳名上（如 1,1,3 vs 1,1,2）。是否跳名由业务决定，不是“修复”问题的手段。
用子查询预处理解决动态分组内排名
真实场景中，常需“每个城市内按销量排名”，但城市分组逻辑复杂（比如要先过滤掉无效城市码、合并别名、排除测试数据），无法直接用 PARTITION BY city。这时不能硬套窗口函数，得先在子查询里把分组依据算干净。
典型错误是试图在 OVER 子句里写 CASE 表达式做动态分区，但多数数据库（PostgreSQL、SQL Server）不支持 PARTITION BY (CASE ...) 中含复杂逻辑，会报错或语义不符。
安全做法是把分组字段提前算好：
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 (...) 会报错：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
- 用嵌套子查询也行，但可读性差，且某些旧版 MySQL 不支持子查询里用窗口函数（需 8.0+）
- 别用
HAVING代替 —— HAVING 针对 GROUP BY，和窗口无关，强行用会语法报错
性能陷阱：ORDER BY 表达式含函数时索引失效
当排名依据是计算字段（如 ORDER BY ABS(sales - target) 或 ORDER BY UPPER(name)），即使 sales 或 name 有索引，数据库通常也无法利用它加速排序。结果是每次查询都触发全表扫描 + 文件排序，大数据量下延迟飙升。
优化方向很实际：
- 如果计算逻辑固定（比如总是用
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（用户自定义函数），执行开销不可控
复杂排名规则最易被忽略的，其实是排序字段的物理可索引性——再精巧的窗口嵌套，卡在排序这一步，整个查询就慢成线性扫描。
