SQL窗口函数如何使用_OVER分区排序完整示例【指导】_技术教程

SQL窗口函数的核心是OVER子句，由PARTITION BY（分组）、ORDER BY（排序）和可选窗口帧构成；PARTITION BY逻辑分组不聚合，ORDER BY决定行序，对排名和累计函数至关重要。

SQL窗口函数的核心在于 OVER 子句，它定义了数据“窗口”的范围、分组和排序方式。掌握 OVER 的写法，就掌握了窗口函数的使用关键。

一、OVER 基本结构：PARTITION BY + ORDER BY

OVER 子句通常包含三部分：可选的 PARTITION BY（分组）、可选的 ORDER BY（组内排序），以及可选的 窗口帧子句（如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）。最常用的是前两者。

PARTITION BY 类似 GROUP BY，但不聚合行，而是把数据逻辑切分成多个独立计算区域；
ORDER BY 决定窗口内行的顺序，对 ROW_NUMBER()、RANK()、累计类函数（如 SUM() OVER(...)）至关重要；
如果只写 ORDER BY 不写 PARTITION BY，整个结果集视为一个大分区；
如果只写 PARTITION BY 不写 ORDER BY，多数排名类函数会报错（如 ROW_NUMBER 要求显式排序），但聚合类函数（如 AVG）仍可运行（但结果无序，意义有限）。

二、完整示例：销售数据中的典型应用

假设有销售表 sales：

id | emp_name | dept | amount | sale_date
1 | 张三 | 技术部 | 8000 | 2025-01-10
2 | 李四 | 销售部 | 12000 | 2025-01-12
3 | 王五 | 技术部 | 9500 | 2025-01-15
4 | 赵六 | 销售部 | 7800 | 2025-01-16
5 | 孙七 | 技术部 | 11000 | 2025-01-18

需求：按部门分组，查出每位员工销售额、部门总销售额、部门内按金额从高到低的排名、以及部门内累计销售额（按日期先后）。

对应 SQL：

SELECT
  emp_name,
  dept,
  amount,
  SUM(amount) OVER (PARTITION BY dept) AS dept_total,
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY amount DESC) AS rn_by_amt,
  SUM(amount) OVER (PARTITION BY dept ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amt_by_date
FROM sales;

结果示意（简化）：

emp_name | dept | amount | dept_total | rn_by_amt | cum_amt_by_date
孙七 | 技术部 | 11000 | 28500 | 1 | 11000
张三 | 技术部 | 8000 | 28500 | 3 | 19000
王五 | 技术部 | 9500 | 28500 | 2 | 28500
李四 | 销售部 | 12000 | 19800 | 1 | 12000
赵六 | 销售部 | 7800 | 19800 | 2 | 19800

三、ORDER BY 在不同函数中的影响差异

不是所有窗口函数都依赖 ORDER BY，但行为差异明显：

ROW_NUMBER(), RANK(), DENSE_RANK()：必须有 ORDER BY，否则语法错误；
LAG()/LEAD()：强烈建议加 ORDER BY，否则“上一行/下一行”无意义（默认物理顺序不可靠）；
COUNT(*) OVER(PARTITION BY ...)：可不写 ORDER BY，仅统计每组行数；
SUM()/AVG()/MAX() OVER(...)：不写 ORDER BY → 整个分区值相同（等价于该组聚合结果广播到每行）；写了 ORDER BY → 变成“滚动计算”，如累计和、移动平均等。

四、常见易错点提醒

PARTITION BY 后不能跟别名：写 PARTITION BY dept ✅，不能写 PARTITION BY d（即使 SELECT 中写了 dept AS d）；
ORDER BY 的字段必须在 SELECT 或原表中存在，不能是表达式别名（除非用子查询或 CTE 提前定义）；
在 MySQL 8.0+、PostgreSQL、SQL Server、Oracle 中支持标准窗口函数；旧版 MySQL（
窗口函数不能出现在 WHERE 或 GROUP BY 中（执行顺序：WHERE → GROUP BY → HAVING → SELECT → ORDER BY），只能用于 SELECT 和 ORDER BY 子句。