窗口函数

本文章基于MySQL数据库进行测试, 其他数据库可能存在差异。

1 什么是窗口函数

1.1介绍

窗口函数（Window Function）是一种在数据库中执行实时分析处理的函数。它可以用于排序并生成序列号，对一组相关的数据行（称为窗口）进行计算，并生成每个窗口内的聚合值。窗口函数允许用户对数据进行更灵活的分析和处理，例如计算排名、分组和计算移动平均等。

窗口函数的使用需要使用OVER子句来定义窗口的规则，并可以结合聚合函数（如sum、avg、count、max、min等）对数据进行计算。常见的窗口函数包括ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()、LAG()和LEAD()等。

1.2 语法

窗口函数的语法通常由以下几部分组成：

1. 函数名：指定要使用的窗口函数的名称。
2. OVER 子句：定义窗口的规则，包括分区（PARTITION BY）和排序规则（ORDER BY）。
3. 参数列表：指定窗口函数的参数，例如聚合函数中的参数。

下面是一个示例窗口函数的语法：

<窗口函数名称> OVER ([PARTITION BY <用于分组的列名>] [ORDER BY <用于排序的列名>])

其中，<窗口函数名称>可以是任何有效的窗口函数名称，例如ROW_NUMBER()、RANK()、DENSE_RANK()等。PARTITION BY子句用于指定分组规则，将数据分为多个窗口，而ORDER BY子句则用于指定每个窗口内的排序规则。

[]中的部分是可以省略的。在有的窗口函数中，PARTITION BY子句和ORDER BY子句都是可选的。

• ROW_NUMBER()函数在省略PARTITION BY和ORDER BY子句时，会为结果集中的每一行分配一个唯一的序号。而如果在PARTITION BY子句中指定了分组列，则会在每个分组内为每一行分配一个序号。

• 省略PARTITION BY子句将导致在整个结果集中进行计算，而省略ORDER BY子句将导致按照默认顺序（通常是按照行的物理顺序）进行计算。

2 案例数据

-- 员工信息表
CREATE TABLE employees (  
  id INT PRIMARY KEY,  
  name VARCHAR(50),  
  salary DECIMAL(10, 2),  
  department VARCHAR(50)  
);  
  
INSERT INTO employees (id, name, salary, department)  
VALUES  
  (1, 'John Doe', 5000, 'Sales'),  
  (2, 'Jane Smith', 5500, 'Marketing'),  
  (3, 'Bob Johnson', 4500, 'HR'),  
  (4, 'Alice Brown', 5400, 'Sales'),  
  (5, 'Mike Davis', 4800, 'Marketing'),  
  (6, 'Sarah Harris', 5400, 'HR'),  
  (7, 'Chris Thompson', 5300, 'Sales'),  
  (8, 'Emily Davis', 5400, 'Marketing'),  
  (9, 'Daniel Wilson', 4900, 'HR'),  
  (10, 'Olivia Martinez', 5400, 'Sales');
  
-- 每月实际工资表  
CREATE TABLE salary (  
  id INT PRIMARY KEY COMMENT 'ID',  
  actual_salary DECIMAL(10, 2) COMMENT '实际工资',  
  month INT COMMENT '月份',  
  employee_id INT COMMENT '员工ID'  
);

INSERT INTO Salary (id, actual_salary, month, employee_id)  
VALUES  
(1, 5000, 5, 1),  
(2, 5500, 5, 2), 
(3, 6000, 5, 3),  
(4, 6000, 6, 1),
(5, 6500, 6, 2),
(6, 7000, 6, 3),                
(7, 7000, 7, 1),  
(8, 7500, 7, 2),  
(9, 8000, 7, 3);

3 案例

3.1 ROW_NUMBER

ROW_NUMBER用于为结果集中的每一行分配一个唯一的序号。它通常与OVER子句一起使用，以指定排序顺序和分区方式。

3.1.1 每个部门的员工根据薪资排序

SELECT
    id,
    NAME,
    salary,
    department,
    ROW_NUMBER() OVER ( PARTITION BY department ORDER BY salary DESC ) AS row_num 
FROM
    employees;

3.1.2 对所有员工的按照薪资进行排序

如果去掉PARTITION BY department，查询将不再按照部门进行分区，而是对整个结果集进行排序，变成为对所有员工根据薪资进行排序。

SELECT
    id,
    NAME,
    salary,
    department,
    ROW_NUMBER() OVER ( ORDER BY salary DESC ) AS row_num 
FROM
    employees;

3.1.2 去除ORDER BY salary DESC

虽然不知道这个查询是否意义，但是还是展示一下查询结果

SELECT
    id,
    NAME,
    salary,
    department,
    ROW_NUMBER() OVER ( PARTITION BY department ) AS row_num 
FROM
    employees;

3.2 RANK和DENSE_RANK

3.2.1 RANK()函数：

RANK()函数返回结果集中每一行的排名，按照指定的排序顺序进行排名。默认情况下，RANK()函数使用升序排序，并返回每个值的位置。如果存在相同的值，则并列排名，下一个排名会跳过并列的数量。

SELECT
    id,
    NAME,
    salary,
    department,
    RANK() OVER ( ORDER BY salary DESC ) AS rn 
FROM
    employees;

3.2.2 DENSE_RANK()函数：

DENSE_RANK()函数与RANK()函数类似，都用于对结果集进行排名。但是，DENSE_RANK()函数不会跳过并列排名的数量。即使存在相同的值，下一个排名仍然是连续的。

SELECT
    id,
    NAME,
    salary,
    department,
    DENSE_RANK() OVER ( ORDER BY salary DESC ) AS rn 
FROM
    employees;

3.3 NTILE

NTILE()用于将数据集分为n个相等的百分位数，每个百分位包含相同数量的数据, 计算方法是将数据集中的数据按照指定的百分位数进行分组，每个组包含相同数量的数据。每个组对应一个百分位数，因此总共有n个百分位数，每个百分位数包含的数据数量相同。

SELECT
    id,
    NAME,
    salary,
    department,
    NTILE( 4 ) OVER ( ORDER BY salary ) AS quartile 
FROM
    Employees;

在这个查询中，我们使用了NTILE函数将员工按照工资水平分为四个组。每个组对应一个百分位数，即前25%、25%-50%、50%-75%和75%-100%。每个员工所属的组由NTILE函数计算得到，并使用quartile列表示。

3.4 LAG和LEAD

LAG和LEAD是窗口函数，用于获取当前行之前或之后的行的值，常用于时间序列数据或需要计算移动平均线等场合。

lead(EXPR,<OFFSET>,<DEFAULT>)

lag(EXPR,<OFFSET>,<DEFAULT>)

1. EXPR：一般是列名，指定要获取之前或之后值的列；也可以是从其他行返回的表达式。
2. OFFSET：这是一个整数，指定了要偏移的行数。它指定了要获取之前或之后多少行的值。在函数中，OFFSET通常为1，表示获取当前行之前的1行的值。
3. DEFAULT：这是一个可选参数，指定当没有更早的行可用时返回的默认值。如果没有更早的行可用，将返回默认值。

3.4.1 LAG函数

LAG函数用于获取当前行之前的指定列的值, 第一行显示null。它需要指定要获取值的列和偏移量。偏移量指定了要获取之前多少行的值。

3.4.1.1 查询员工当月以及上月实际工资是多少

SELECT
    employee_id,
    MONTH,
    actual_salary,
    LAG( actual_salary ) OVER ( PARTITION BY employee_id ORDER BY MONTH ) AS previous_month_salary 
FROM
    salary;

3.4.1.2 查询员工当月以及两个月前的实际工资是多少

SELECT
    employee_id,
    MONTH,
    actual_salary,
    LAG( actual_salary, 2 ) OVER ( PARTITION BY employee_id ORDER BY MONTH ) AS previous_month_salary 
FROM
    salary;

3.4.1.3 查询员工当月以及上月实际工资是多少,第一个月返回0

SELECT
    employee_id,
    MONTH,
    actual_salary,
    LAG( actual_salary, 1, 0 ) OVER ( PARTITION BY employee_id ORDER BY MONTH ) AS previous_month_salary 
FROM
    salary;

3.4.2 LEAD函数

LEAD函数用于获取当前行之后的指定列的值，最后一行显示null。它同样需要指定要获取值的列和偏移量。偏移量指定了要获取之后多少行的值。

比如，查询员工当月以及下个月实际工资是多少

SELECT
    employee_id,
    MONTH,
    actual_salary,
    LEAD( actual_salary ) OVER ( PARTITION BY employee_id ORDER BY MONTH ) AS previous_month_salary 
FROM
    salary;

关于LEAD()其他参数不再举例，详情请见LAG()函数

3.5 FIRST_VALUE与LAST_VALUE

FIRST_VALUE和LAST_VALUE用于获取查询结果集中每行的第一个值或最后一个值。它们通常在排序的窗口中使用，以便在每组中进行聚合操作。

FIRST_VALUE (EXPR)

LAST_VALUE (EXPR)

EXPR：一般是列名，指定要获取之前或之后值的列；也可以是从其他行返回的表达式。

3.5.1 FIRST_VALUE

FIRST_VALUE函数返回指定列的第一个值。

比如，获取每个员工每月的工资以及首月工资

SELECT
    employee_id,
    MONTH,
    actual_salary,
    FIRST_VALUE( actual_salary ) OVER ( PARTITION BY employee_id ORDER BY MONTH ) AS first_salary 
FROM
    salary;

3.5.2 LAST_VALUE

LAST_VALUE函数返回指定列的最后一个值,。

比如，获取每个员工每月的工资以及最后一月工资

SELECT
    employee_id,
    MONTH,
    actual_salary,
    LAST_VALUE( actual_salary ) OVER ( PARTITION BY employee_id ORDER BY MONTH ) AS last_salary 
FROM
    salary;

看到这里，你可能会好奇，没啥没达到理想的效果，请看下面这句sql

SELECT 
    employee_id, 
    month, 
    actual_salary, 
    LAST_VALUE(actual_salary) OVER (PARTITION BY employee_id ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary 
FROM salary;

为啥加上ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, 这就涉及到一个叫窗口帧（window frame）的东西；

这里就不展开叙述，请看Window Function Frame Specification。

3.6 统计函数(MAX、MIN、AVG、SUM、COUNT)

这里简单举几个例子；

3.6.1 MAX

SELECT 
  employee_id,
  month,
  actual_salary,
  MAX(actual_salary) OVER (PARTITION BY employee_id) AS max_salary
FROM 
  salary;

3.6.2 MIN

SELECT 
  employee_id,
  month,
  actual_salary,
  MIN(actual_salary) OVER (PARTITION BY employee_id) AS min_salary
FROM 
  salary;

3.6.3 AVG

SELECT 
  employee_id,
  month,
  actual_salary,
  AVG(actual_salary) OVER (PARTITION BY employee_id) AS avg_salary
FROM 
  salary;

3.6.4 SUM

SELECT 
  employee_id,
  month,
  actual_salary,
  SUM(actual_salary) OVER (PARTITION BY employee_id) AS sum_salary
FROM 
  salary;

3.6.5 COUNT

SELECT 
  employee_id,
  month,
  actual_salary,
  COUNT(actual_salary) OVER (PARTITION BY employee_id) AS count_salary
FROM 
  salary;