详解开窗函数

1.什么是窗口函数

窗口函数（Window functions）又称分析函数或开窗函数，它允许你在不改变原始行的情况下，对一组相关的行（称为“窗口”）进行计算和分析。与普通的聚合函数（如SUM、AVG等）不同，窗口函数不会将多行合并为一行，而是为每一行返回一个计算结果，同时保留原始行的详细信息。通常写法为func()over()，详细语法如下：

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官方文档，语法表述为必选项，实际应用为可选）
• 空值选项（可选）
• 窗口框架（可选）：指明窗口的范围，从什么地方开始到什么地方结束

2.函数列表

支持开窗的函数列表，支持开窗函数分为：排序函数（Ranking Functions）、分析函数（Analytic Functions）、聚合函数（Aggregate Functions）

2.1 排序函数

2.2 分析函数

2.3 聚合函数

3.空值选项

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

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

4.排序方式

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

4.1聚合函数开窗的排序

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

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

举例： 样例数据

--建表语句
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的薪水；

4.2 排序不唯一带来的问题

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

分析： 为了保证每个部门仅取出一行记录，我们使用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的薪资也是一样的。虽然当前满足了需求内容，但在实际生产中，发生流程重跑，则数据内容可能发生变化，数据校验出现前后不一致，较难排查。还会影响下游使用，例如使用结果数据计算最低薪水员工的平均年龄，数据重跑之后平均年龄发生变化。所以保证排序唯一十分重要；

4.3 排序中的空值

可以在排序时指定空值是排在最前面还是最后面,测试数据中没有空值，仅写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;

5.窗口框架

窗口框架指定窗口从哪一行开始以及在哪里结束 语法 { 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