解释窗口函数的“分组但不聚合 collapsing”语义,给出 ROW_NUMBER/RANK/DENSE_RANK 与 SUM() OVER 示例。
GROUP BY 不同,后者会折叠行数)。语法:函数() OVER (PARTITION BY 分组列 ORDER BY 排序列 [窗口帧])。 - ROW_NUMBER():组内连续序号(1,2,3…),并列也唯一。
- RANK():并列同号、跳过后续号(1,1,3)。
- DENSE_RANK():并列同号、不跳号(1,1,2)。
- 聚合类 SUM/AVG/COUNT(...) OVER (...):计算累计/移动值。
SELECT dept_id, emp_name, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
FROM emp;
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
题目与答案就在本页;收藏、批注和错题重练在应用内