Spark SQL 窗口函数详解：从入门到精通

窗口函数（Window Functions，又称开窗函数或分析函数）是 SQL 进阶的核心技能，也是大厂面试中最高频的考点之一。它能在不折叠数据行的前提下，对一组相关行进行累计、排名、环比等计算。

🚀 不想啃文档？ 试试全网独一无二的 窗口函数可视化播放器，逐帧动画演示底层执行原理。

一、窗口函数语法

func() OVER (PARTITION BY ... ORDER BY ... 窗口帧) 是窗口函数的标准写法。与普通聚合函数（如 SUM、AVG）不同，窗口函数不会将多行合并为一行，而是为每一行返回一个计算结果，同时保留原始行的详细信息。

window_function 
[ nulls_option ]
OVER
( [  { PARTITION | DISTRIBUTE } BY partition_col_name = partition_col_val ( [ , ... ] ) ]
  { ORDER | SORT } BY expression [ ASC | DESC ] 
 [ NULLS { FIRST | LAST } ] 
 [ , ... ]
 [ window_frame ] )

上面函数对应下面几个部分

• 函数：指具体使用什么函数，支持那些函数见【函数列表】
• 空值选项（可选）
• over：代表开窗，固定格式；
• 分组方式（可选）
• 排序方式（可选）（上面语法来源于spark官方文档，语法表述为必选项，实际应用为可选）
• 空值选项（可选）
• 窗口帧（可选）：指明窗口的范围，从什么地方开始到什么地方结束

二、函数分类与速查

窗口函数分为三大类：排序函数、位移与分析函数、聚合窗口函数。点击函数名跳转到详细教程。

排序函数（Ranking）

函数	一句话说明	详解教程
ROW_NUMBER	严格编号，不并列，从1开始	→ 详解
RANK	并列跳号，如 1,1,3	→ 详解
DENSE_RANK	并列不跳号，如 1,1,2	→ 详解
NTILE	均匀分成 N 个桶	→ 详解

位移与分析函数

函数	一句话说明	详解教程
LAG	往前 N 行取值	→ 详解
LEAD	往后 N 行取值	→ 详解
FIRST_VALUE	窗口内第一行的值	→ 详解
LAST_VALUE	窗口内最后一行的值	→ 详解
NTH_VALUE	窗口内第 N 行的值	→ 详解
PERCENT_RANK	百分比排名 (0~1)	→ 详解
CUME_DIST	累积分布值	→ 详解

聚合窗口函数

函数	一句话说明	详解教程
SUM OVER	累计求和、移动求和	→ 详解
AVG OVER	移动平均、滑动窗口均值	→ 详解
COUNT OVER	累计计数、分组统计	→ 详解
MAX / MIN OVER	窗口内极值、至今为止的峰值	→ 详解

更多支持 OVER() 的聚合函数（如 stddev、variance、percentile 等）请参考 Spark 函数速查手册。

三、空值选项

[ nulls_option ] 指定在评估窗口函数时是否跳过空值。

• RESPECT NULLS 表示不跳过空值
• IGNORE NULLS 表示跳过空值。 如果未指定，默认值为 RESPECT NULLS。 仅 LAG | LEAD | NTH_VALUE | FIRST_VALUE | LAST_VALUE函数可以使用IGNORE NULLS

四、排序方式

• 排序函数（Ranking Functions）、分析函数（Analytic Functions）开窗时必须要进行排序；
• 聚合函数（Aggregate Functions）根据需要进行排序。

排序对聚合窗口函数的影响

聚合函数开窗可以排序也可以不排序。

• 不排序则窗口帧范围，计算范围为整个分区；
• 排序不指定窗口帧范围，计算范围为分区开始行到当前行；
• 排序制定窗口帧范围，按照制定范围聚合

举例： 样例数据

--建表语句
CREATE TABLE t_employees
(
    name   STRING,
    dept   STRING,
    salary BIGINT,
    age    BIGINT
);
--插入数据
INSERT INTO t_employees
VALUES ("Lisa", "Sales", 10000, 35),
       ("Evan", "Sales", 32000, 38),
       ("Fred", "Engineering", 21000, 28),
       ("Alex", "Sales", 30000, 33),
       ("Tom", "Engineering", 23000, 33),
       ("Jane", "Marketing", 29000, 28),
       ("Helen", "Marketing", 29000, 40),
       ("Jeff", "Marketing", 35000, 38),
       ("Paul", "Engineering", 29000, 23),
       ("Chloe", "Engineering", 23000, 25);

--样例数据
+--------+--------------+---------+------+
|  name  |     dept     | salary  | age  |
+--------+--------------+---------+------+
| Lisa   | Sales        | 10000   | 35   |
| Evan   | Sales        | 32000   | 38   |
| Fred   | Engineering  | 21000   | 28   |
| Alex   | Sales        | 30000   | 33   |
| Tom    | Engineering  | 23000   | 33   |
| Jane   | Marketing    | 29000   | 28   |
| Helen  | Marketing    | 29000   | 40   |
| Jeff   | Marketing    | 35000   | 38   |
| Paul   | Engineering  | 29000   | 23   |
| Chloe  | Engineering  | 23000   | 25   |
+--------+--------------+---------+------+

sum()函数举例： 1.计算每个员工所在部门的部门总薪水

select
    name,
    dept,
    salary,
    age,
    sum(salary)over(partition by dept) as dept_total_salary
from t_employees;
--执行结果
+--------+--------------+---------+------+--------------------+
|  name  |     dept     | salary  | age  | dept_total_salary  |
+--------+--------------+---------+------+--------------------+
| Fred   | Engineering  | 21000   | 28   | 96000              |
| Tom    | Engineering  | 23000   | 33   | 96000              |
| Paul   | Engineering  | 29000   | 23   | 96000              |
| Chloe  | Engineering  | 23000   | 25   | 96000              |
| Jane   | Marketing    | 29000   | 28   | 93000              |
| Helen  | Marketing    | 29000   | 40   | 93000              |
| Jeff   | Marketing    | 35000   | 38   | 93000              |
| Lisa   | Sales        | 10000   | 35   | 72000              |
| Evan   | Sales        | 32000   | 38   | 72000              |
| Alex   | Sales        | 30000   | 33   | 72000              |
+--------+--------------+---------+------+--------------------+

注意： 这里dept_total_salayr列得出的结果接统计了部门全部员工的总薪资。

2.按照员工薪资排序，从低到高，每个部门截止到该员工的累积薪水是多少；(累积求和)

--不限定窗口帧
select
    name,
    dept,
    salary,
    age,
    sum(salary)over(partition by dept order by salary asc) as dept_total_salary
from t_employees;
--查询结果
+--------+--------------+---------+------+--------------------+
|  name  |     dept     | salary  | age  | dept_total_salary  |
+--------+--------------+---------+------+--------------------+
| Fred   | Engineering  | 21000   | 28   | 21000              |
| Tom    | Engineering  | 23000   | 33   | 67000              |
| Chloe  | Engineering  | 23000   | 25   | 67000              |
| Paul   | Engineering  | 29000   | 23   | 96000              |
| Jane   | Marketing    | 29000   | 28   | 58000              |
| Helen  | Marketing    | 29000   | 40   | 58000              |
| Jeff   | Marketing    | 35000   | 38   | 93000              |
| Lisa   | Sales        | 10000   | 35   | 10000              |
| Alex   | Sales        | 30000   | 33   | 40000              |
| Evan   | Sales        | 32000   | 38   | 72000              |
+--------+--------------+---------+------+--------------------+

--使用rows限定窗口帧
select name,
       dept,
       salary,
       age,
       sum(salary)
           over (partition by dept order by salary asc rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as dept_total_salary
from t_employees;
--查询结果
+--------+--------------+---------+------+--------------------+
|  name  |     dept     | salary  | age  | dept_total_salary  |
+--------+--------------+---------+------+--------------------+
| Fred   | Engineering  | 21000   | 28   | 21000              |
| Tom    | Engineering  | 23000   | 33   | 44000              |
| Chloe  | Engineering  | 23000   | 25   | 67000              |
| Paul   | Engineering  | 29000   | 23   | 96000              |
| Jane   | Marketing    | 29000   | 28   | 29000              |
| Helen  | Marketing    | 29000   | 40   | 58000              |
| Jeff   | Marketing    | 35000   | 38   | 93000              |
| Lisa   | Sales        | 10000   | 35   | 10000              |
| Alex   | Sales        | 30000   | 33   | 40000              |
| Evan   | Sales        | 32000   | 38   | 72000              |
+--------+--------------+---------+------+--------------------+

--使用range限定窗口帧
select name,
       dept,
       salary,
       age,
       sum(salary)
           over (partition by dept order by salary asc range BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as dept_total_salary
from t_employees;

--查询结果
+--------+--------------+---------+------+--------------------+
|  name  |     dept     | salary  | age  | dept_total_salary  |
+--------+--------------+---------+------+--------------------+
| Fred   | Engineering  | 21000   | 28   | 21000              |
| Tom    | Engineering  | 23000   | 33   | 67000              |
| Chloe  | Engineering  | 23000   | 25   | 67000              |
| Paul   | Engineering  | 29000   | 23   | 96000              |
| Jane   | Marketing    | 29000   | 28   | 58000              |
| Helen  | Marketing    | 29000   | 40   | 58000              |
| Jeff   | Marketing    | 35000   | 38   | 93000              |
| Lisa   | Sales        | 10000   | 35   | 10000              |
| Alex   | Sales        | 30000   | 33   | 40000              |
| Evan   | Sales        | 32000   | 38   | 72000              |
+--------+--------------+---------+------+--------------------+

注意：

1. 使用sum开窗后增加order by子句，sum的结果不在是整个部门，而是截止到当前值的结果。
2. 如果省略了窗口帧内容，则相当于使用了range，限定截止到当前行的值，关注Tom所在行的结果，聚合了包含Chloe的薪水；
3. 使用rows的窗口帧，则仅包含到Tom的薪水，不含Chloe的薪水；

排序不唯一带来的问题

要求： 取出每个部门薪水最低的员工记录，要求每个部门仅取出一行记录

分析： 为了保证每个部门仅取出一行记录，我们使用row_number函数来进行处理，具体语句和执行结果如下：

--执行SQL
select name,
       dept,
       salary,
       age
from (select name,
             dept,
             salary,
             age,
             row_number() over (partition by dept order by salary asc) as rn
      from t_employees) t
where rn = 1

--执行结果
+-------+--------------+---------+------+
| name  |     dept     | salary  | age  |
+-------+--------------+---------+------+
| Fred  | Engineering  | 21000   | 28   |
| Jane  | Marketing    | 29000   | 28   |
| Lisa  | Sales        | 10000   | 35   |
+-------+--------------+---------+------+

注意： 关注Marketing部门的记录，取出的是Jane的记录。Jane确实是最低的，但是同时Hellen的薪资也是一样的。虽然当前满足了需求内容，但在实际生产中，发生流程重跑，则数据内容可能发生变化，数据校验出现前后不一致，较难排查。还会影响下游使用，例如使用结果数据计算最低薪水员工的平均年龄，数据重跑之后平均年龄发生变化。所以保证排序唯一十分重要；

排序中 NULL 值的处理

可以在排序时指定空值是排在最前面还是最后面,测试数据中没有空值，仅写SQL了

--样例SQL
select name,
       dept,
       salary,
       age,
       sum(salary)
           over (partition by dept order by salary asc nulls first range BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as dept_total_salary
from t_employees;

五、窗口帧（Window Frame）

窗口帧指定窗口从哪一行开始以及在哪里结束 语法

{ RANGE | ROWS } { frame_start | BETWEEN frame_start AND frame_end }

框架指定方式分为range方式和rows方式，如果不指定默认为range方式，

frame_start和frame_end可以为以下内容

UNBOUNDED PRECEDING： 从分区的第一行开始

offset PRECEDING：从当前行之前的第 offset 行开始

CURRENT ROW：当前行

offset FOLLOWING：到当前行之后的第 offset 行结束

UNBOUNDED FOLLOWING：到分区的最后一行结束

如果未指定 frame_end，则默认值为 CURRENT ROW

---

延伸阅读

• 📖 窗口函数专题首页 — 全部 16 篇函数详解
• 🎬 窗口函数可视化播放器 — 逐帧动画演示执行原理
• 📝 窗口函数面试真题 — 字节/阿里/腾讯高频考题
• 🔧 Spark SQL 函数速查 — 417 个内置函数手册

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题