聚合结果不能直接套窗口函数，必须用子查询或CTE先完成聚合再排名；窗口函数在SELECT阶段执行，不能在同级WHERE中引用其生成的列。
聚合结果不能直接套窗口函数
直接在 GROUP BY 查询里写 DENSE_RANK() 或 ROW_NUMBER() 会报错，典型错误是 column "xxx" must appear in the GROUP BY clause。因为窗口函数运行在原始行级别，而 GROUP BY 后只剩聚合行，两者粒度冲突。
常见错误写法：SELECT dept_id, AVG(salary), DENSE_RANK() OVER (ORDER BY AVG(salary)) FROM emp GROUP BY dept_id;
这会失败——AVG(salary) 是聚合结果，但 OVER 子句无法在分组后引用它作为排序依据。
- 必须先用子查询或 CTE 把聚合结果算出来，再对这个“中间结果集”应用窗口函数
- 聚合字段要起别名（如
avg_salary），否则外层OVER (ORDER BY ...)无法引用 - 如果需要分组内排名（比如每部门按平均薪资排），外层的
PARTITION BY没意义——聚合后已无“组内多行”可分
用 CTE 实现聚合后全局排名
这是最清晰、最不易出错的方式。CTE 先完成聚合，主查询再排名。
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_salary
FROM emp
GROUP BY dept_id
)
SELECT *,
ROW_NUMBER() OVER (ORDER BY avg_salary DESC) AS rank_no,
DENSE_RANK() OVER (ORDER BY avg_salary DESC) AS dense_rank_no
FROM dept_avg;注意：
- ORDER BY avg_salary DESC 必须写全，漏掉会导致排序随机，尤其在并行执行引擎（如 Redshift、Doris）中更明显
- 如果多个部门平均薪资相同，ROW_NUMBER() 会强制给不同序号，DENSE_RANK() 则并列且不跳号
- 想让并列时有确定顺序（比如同薪按部门 ID 小的优先），加兜底排序：
ORDER BY avg_salary DESC, dept_id ASC - 别名
avg_salary在OVER中必须可用，不能写成AVG(salary)
动态 TOP N 要靠外层 WHERE 筛选
窗口函数生成的排名列（如 rn）不能在同一个查询的 WHERE 中使用，否则报错 column "rn" does not exist。因为 SQL 执行顺序是：WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT，而窗口函数在 SELECT 阶段才计算。
正确做法只能是嵌套一层：
WITH ranked AS (
SELECT dept_id, COUNT(*) AS emp_cnt,
ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
FROM emp
GROUP BY dept_id
)
SELECT dept_id, emp_cnt
FROM ranked
WHERE rn <= 3;- 别试图在视图或 BI 工具里对含排名的字段做“动态筛选”，Power BI 或 Tableau 的视觉级过滤会重算上下文，导致
rn值失效 - 如果前端要支持“用户选前5/前20”，参数
N必须传给外层WHERE rn <= ?，不能塞进OVER里 - 性能上，
ROW_NUMBER()比RANK()或DENSE_RANK()略快，因无需比较值是否重复
分组字段来自前端时怎么安全处理
不能把前端传来的字段名（如 'region' 或 'role'）直接拼进 GROUP BY 或 ORDER BY，否则会 SQL 注入或语法错误。
安全做法是用 CASE WHEN 映射到预设字段：
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY CASE :group_param
WHEN 'dept' THEN dept_id
WHEN 'region' THEN region
ELSE 'all'
END
ORDER BY salary DESC
) AS rank_in_group
FROM emp;:group_param 是占位符，需由服务端严格校验白名单（只允许 'dept'、'region'、'role'），再通过 PreparedStatement 绑定。
-
ORDER BY里的字段也不能动态拼接，必须用同样方式映射 - 若业务要求“并列不跳号”，此处改用
DENSE_RANK()；但要注意 BI 工具对它的下钻支持常不稳定 - 一旦用了
PARTITION BY，ORDER BY就必须存在，漏写会导致组内排序不可控
实际最难的部分不是写法，而是搞清“排名基准”到底是什么——是全量数据？还是当前查询已过滤后的结果？很多线上问题都源于窗口函数的输入集被隐式缩小了，而开发没意识到。
