网站导航
> 技术文档 > SQL：窗口函数（Window Functions）_sql窗口函数

SQL：窗口函数（Window Functions）_sql窗口函数

网友: 技术文档 2025-07-30 13:01:48

目录

什么是窗口函数？

基本语法结构 

常用组合与含义说明

为什么要用窗口函数？

常见的窗口函数分类

1️⃣ 排名类函数

2️⃣ 聚合类函数（不影响原始行）

3️⃣ 值访问函数

窗口范围说明（ROWS / RANGE）

什么是窗口函数？

窗口函数是一类 SQL 函数，在不分组的情况下，可以对查询结果中的某一“窗口”范围内的数据进行计算。

窗口函数 = 能在每一行数据上“看见”其他相关行的函数。

它允许你在不聚合（不合并行）的前提下，对一组相关行进行计算，并把结果加回到原来的每一行上。

不同于聚合函数（如 SUM, AVG），窗口函数不会压缩行，而是为每一行返回一个计算值。

基本语法结构 

([参数]) OVER ( [PARTITION BY 子句] [ORDER BY 子句] [ROWS 或 RANGE 子句])函数名(...) OVER ( PARTITION BY ... -- 按谁分组（可选） ORDER BY ... -- 按什么顺序（常用） ROWS BETWEEN ... -- 控制范围（高级用法）)

👉 OVER (...) 是入口

  • OVER 是告诉 SQL 引擎：“这个函数不是普通聚合，而是要基于某个窗口来作用于每行。”

  • 它后面带括号表示你要指定这个“窗口”的细节。

👉PARTITION BY 列名1, 列名2, ...：分组但不折叠

  • 类似于 GROUP BY，但只是划分“作用范围”，不减少数据行。

  • 举例：你要在每个部门里对员工按薪资排名 → 这就是按部门 PARTITION。

这保证了同组内计算、组间隔离。

👉 ORDER BY 时间列 ASC|DESC：排序定义窗口内的顺序

  • 很多计算（如排名、累计值）都依赖顺序。

  • 例如要知道“过去 7 天销售额”，就必须知道哪个日期在前。

这部分是为窗口中的“相对位置”操作服务的，比如：

SUM(金额) OVER ( PARTITION BY 店铺 ORDER BY 日期 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

👉 ROWS vs RANGE：定义窗口范围的方式

  • ROWS 是“行数”：例如“当前行往前数 3 行”。

  • RANGE 是“值的范围”：例如“当前行往前 7 天”。

-- ROWS: 只往前看 2 行，无论值是多少SUM(金额) OVER (ORDER BY 日期 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)-- RANGE: 往前看7天内所有行（不论有多少行）SUM(金额) OVER (ORDER BY 日期 RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW)

 RANGE 更常用于时间、数值等连续维度的聚合。

👉BETWEEN ... PRECEDING AND ... FOLLOWING：滑动窗口的界定

  • 让你定义每一行所对应的“窗口范围”。

  • 举例：

    • 1 PRECEDING AND 1 FOLLOWING → 以当前行为中心，向前后各看一行。

    • UNBOUNDED PRECEDING AND CURRENT ROW → 从分组的第一行累加到当前行。

常见边界选项 

关键字 含义 示例 UNBOUNDED PRECEDING 从最开头行开始 整组累加直到当前行 n PRECEDING 当前行向前数第 n 行 过去 n 行 CURRENT ROW 当前行本身 仅此行 or 截止此行 n FOLLOWING 当前行之后第 n 行 未来 n 行 UNBOUNDED FOLLOWING 一直到最后一行 当前行到结尾 
  当前行----------------↓----------------←←← N PRECEDING N FOLLOWING →→→

 你定义的是这条中间线（当前行）左边和右边要纳入多少行（ROWS）或多少值范围（RANGE）。

常用组合与含义说明

🔹 BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

  •  作用：从组开头到当前行

  •  用于累计和、累计平均等累计计算

  • 示例：

SUM(销售额) OVER ( PARTITION BY 店铺 ORDER BY 日期 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

🔹 BETWEEN N PRECEDING AND CURRENT ROW

  •  作用：一个固定长度的滑动窗口

  •  常用于移动平均、滑动总和

  •  示例（过去 6 天 + 今天共 7 天）：

AVG(销售额) OVER ( ORDER BY 日期 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

🔹 BETWEEN CURRENT ROW AND N FOLLOWING

  •  作用：当前行及之后 N 行

  •  可用于“未来几天预测累计值”

  •  示例：

SUM(销售额) OVER ( ORDER BY 日期 ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING)

🔹 BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

  •  作用：整个分组范围

  •  用于对整组做标量对比（如组内最大值）

  •  示例：

MAX(销售额) OVER ( PARTITION BY 店铺 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

🔹 BETWEEN N PRECEDING AND N FOLLOWING

  •  作用：中心滑动窗口

  •  用于中心对称统计（如中心平均、滑动中位数）

  •  示例：

AVG(销售额) OVER ( ORDER BY 日期 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)

🔒 注意事项：

  1. 使用 RANGE 时，ORDER BY 的列必须是数值或时间类型，且不可重复。否则你需要指定精确间隔（如 INTERVAL \'7 days\'）。

  2. 在大多数数据库（如 PostgreSQL、SQL Server）中，RANGE 的性能可能低于 ROWS，尤其是当排序字段重复时。

  3. 并不是所有窗口函数都支持窗口范围（如 RANK() 就不支持 ROWS BETWEEN 语法）。

示例：

SELECT 员工, 部门, 销售额, SUM(销售额) OVER ( PARTITION BY 部门 ORDER BY 日期 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW ) AS 移动销售总额FROM 销售表;

这句语法的逻辑就是：

  1. 每个部门分成一个组；

  2. 在每个部门内部，按日期排序；

  3. 计算当前行及前 3 行的销售额之和。

为什么要用窗口函数？

传统的 SQL 聚合函数（如 SUM()、AVG()、COUNT()）会把多行压缩成一行结果。
但有些计算 —— 例如“每行对应的前 7 天销售总和”或“当前行在每个部门内的排名” —— 需要在保留原始行的同时，引入“行之间的上下文”。

窗口函数就满足这个需求。它的设计让你在保持每一行数据不变的情况下，执行类似“跨行计算”的操作。

在传统的 SQL 聚合函数（如 SUM, AVG, COUNT）中，如果你写：

SELECT person_id, SUM(weight)FROM QueueGROUP BY person_id;

你得到的是每个 person_id 的总重，但你没法看到其他人的情况 —— 一条记录一条记录独立计算。

而窗口函数就像给每一行配了一个“望远镜”，能看到前面的、后面的或整组内的数据，然后基于这些数据算出“每一行自己的视角”。

举个通俗例子（排队上车）：

有一张表：

turn person_name weight 1 Alice 250 2 Bob 300 3 Charlie 200 4 David 400

你想知道：每个人上车时，前面所有人（包括自己）累计多重了？

👉 用窗口函数就可以这样写：

SELECT person_name, weight, SUM(weight) OVER (ORDER BY turn) AS cumulative_weightFROM Queue;

输出结果：

person_name weight cumulative_weight Alice 250 250 Bob 300 550 (250+300) Charlie 200 750 (250+300+200) David 400 1150

🚀 神奇的是，你没有把这些行合并，而是在每行里加上了“前面和自己的累积情况”。

常见的窗口函数分类

1️⃣ 排名类函数

函数 描述 ROW_NUMBER() 每一组数据中按顺序分配唯一行号 RANK() 同分并列，跳跃排名（如：1,1,3） DENSE_RANK() 同分不跳（如：1,1,2） NTILE(n) 将结果分为 n 个桶，每行给出所属桶编号

1.ROW_NUMBER()

意义：按顺序为每行分配一个“唯一编号”。
名称：ROW_NUMBER = 行号。

语法示例：

ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num

用途：

  • 对每个部门中员工薪资进行唯一编号（常用于分页、去重等）

2.RANK()

意义：返回排名，相同值并列排名，后续名次跳跃。
名称：RANK = 排名。

语法示例：

RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
salary RANK 1000 1 1000 1 900 3

3.DENSE_RANK()

意义：与 RANK() 类似，但排名连续不跳跃。
名称：DENSE_RANK = 密集排名。

语法示例：

DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
salary DENSE_RANK 1000 1 1000 1 900 2

4. NTILE(n)

意义：将数据平均分成 n 个桶，每行返回桶编号。
名称：NTILE = \"N Tile\"，即“分桶”。

语法示例：

NTILE(4) OVER (ORDER BY salary DESC) AS quartile

用途：

  • 按工资水平将员工划分为四个档次（四分位分析）

2️⃣ 聚合类函数（不影响原始行）

函数 描述 SUM(), AVG(), MAX(), MIN() 聚合函数 + 窗口：在窗口范围内计算 COUNT() 窗口内的行数统计

5. SUM(expr)、AVG(expr)、MAX(expr)、MIN(expr)

意义：在窗口内执行聚合计算，但不影响原始行展示。
名称：

  • SUM = 总和

  • AVG = 平均

  • MAX = 最大值

  • MIN = 最小值

语法示例：

SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS running_total

用途：

  • 滚动汇总、组内对比等。

3️⃣ 值访问函数

函数 描述 LAG(expr, n, default) 返回当前行前第 n 行的值 LEAD(expr, n, default) 返回当前行后第 n 行的值 FIRST_VALUE(expr) 窗口中的第一个值 LAST_VALUE(expr) 窗口中的最后一个值

6. LAG(expr, offset, default)

意义：返回当前行的前 N 行的值。
名称：LAG = 滞后。

语法示例：

LAG(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY hire_date) AS prev_salary

用途：

  • 分析趋势、比较环比。

7. LEAD(expr, offset, default)

意义：返回当前行的后 N 行的值。
名称：LEAD = 领先。

语法示例：

LEAD(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY hire_date) AS next_salary

用途：

  • 预测未来、构建时间序列对比。

 8. FIRST_VALUE(expr)

意义：返回窗口中按排序后第一行的值。
名称：FIRST_VALUE = 第一个值。

语法示例：

FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS first_salary

9. LAST_VALUE(expr)

意义：返回窗口中最后一行的值。
名称：LAST_VALUE = 最后一个值。

注意： LAST_VALUE 需要配合 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 才能获取整个分区最后一行值。

LAST_VALUE(salary) OVER ( PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary

窗口范围说明（ROWS / RANGE）

ROWS 是基于物理行号

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

 RANGE 是基于值范围

RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW

常见用途总结

场景 推荐函数 排名 ROW_NUMBER, RANK, DENSE_RANK 对比趋势 LAG, LEAD, FIRST_VALUE, LAST_VALUE 滚动汇总 SUM, AVG, COUNT + ROWS BETWEEN 分段统计 NTILE 时间窗口 RANGE BETWEEN
网络标签：

相关问题