一篇文章看懂SQL中的开窗函数

SQL开窗函数是用于处理分组内计算的特殊函数，能在不破坏原有数据结构的情况下进行复杂分析。本文助你快速理解并掌握开窗函数的使用。

深入浅出，一文掌握SQL中的开窗函数

在SQL查询中，我们经常需要对数据进行分组、排序以及计算各种聚合值，而开窗函数（Window Function）为我们提供了一种强大的方法来处理这类需求，开窗函数可以在不破坏原有数据行结构的情况下，对数据进行分组、排序、计算聚合值等操作，本文将详细介绍开窗函数的概念、用法以及在实际场景中的应用。

开窗函数的基本概念

1、什么是开窗函数？

开窗函数是一种特殊的函数，它可以在一组行（称为“窗口”）上执行计算，同时保留原始行的结构，这意味着开窗函数不会像普通聚合函数（如SUM、AVG等）那样将多行数据聚合成一行，而是在每行数据上单独进行计算。

2、开窗函数的组成

开窗函数通常由两部分组成：函数部分和开窗部分。

（1）函数部分：可以是SQL内置的聚合函数（如SUM、AVG、MAX等），也可以是自定义的标量函数。

（2）开窗部分：用于定义窗口的范围和排序规则，主要包括以下三个要素：

- 分区（PARTITION BY）：将数据行按照某个或多个列进行分组。

- 排序（ORDER BY）：指定窗口内数据的排序规则。

- 窗口帧（FRAME）：定义窗口的边界，即计算的范围。

开窗函数的分类与用法

1、聚合开窗函数

聚合开窗函数主要包括SUM、AVG、MAX、MIN等，它们可以在窗口内对数据进行聚合计算。

示例：计算每个部门内员工的平均薪资。

SELECT
  department_id,
  employee_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM
  employees;

2、排序开窗函数

排序开窗函数主要包括ROW_NUMBER、RANK、DENSE_RANK等，它们可以在窗口内对数据进行排序。

（1）ROW_NUMBER：为窗口内的每一行分配一个唯一的序号。

示例：为每个部门内的员工按薪资升序分配序号。

SELECT
  department_id,
  employee_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary ASC) AS rn
FROM
  employees;

（2）RANK：为窗口内的每一行分配一个序号，但存在并列情况时，序号会跳过。

示例：为每个部门内的员工按薪资升序分配序号，并列情况跳过。

SELECT
  department_id,
  employee_id,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary ASC) AS rk
FROM
  employees;

（3）DENSE_RANK：为窗口内的每一行分配一个序号，但存在并列情况时，序号不会跳过。

示例：为每个部门内的员工按薪资升序分配序号，并列情况不跳过。

SELECT
  department_id,
  employee_id,
  salary,
  DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary ASC) AS dr
FROM
  employees;

3、偏移开窗函数

偏移开窗函数主要包括LAG、LEAD等，它们可以访问窗口内当前行之前或之后的行。

（1）LAG：获取窗口内当前行之前指定偏移量的行的数据。

示例：获取每个部门内员工的上一条记录的薪资。

SELECT
  department_id,
  employee_id,
  salary,
  LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY salary ASC) AS prev_salary
FROM
  employees;

（2）LEAD：获取窗口内当前行之后指定偏移量的行的数据。

示例：获取每个部门内员工的下一条记录的薪资。

SELECT
  department_id,
  employee_id,
  salary,
  LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY salary ASC) AS next_salary
FROM
  employees;

开窗函数在实际场景中的应用

1、首尾记录查询

使用开窗函数可以轻松查询每个分组的首条或最后一条记录。

示例：查询每个部门薪资最高和最低的员工。

SELECT
  department_id,
  employee_id,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS max_salary_rank,
  RANK() OVER (PARTITION BY department_id ORDER BY salary ASC) AS min_salary_rank
FROM
  employees
WHERE
  max_salary_rank = 1 OR min_salary_rank = 1;

2、动态分组

开窗函数可以用于动态创建分组，以便于进行进一步的分析。

示例：根据员工薪资水平动态创建分组。

WITH salary_groups AS (
  SELECT
    employee_id,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_group
  FROM
    employees
)
SELECT
  salary_group,
  AVG(salary) AS avg_salary
FROM
  salary_groups
GROUP BY
  salary_group;

3、连续登录天数

使用开窗函数可以计算用户连续登录的天数。

示例：计算每个用户连续登录的天数。

SELECT
  user_id,
  login_date,
  DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date ASC) DAY) AS start_date,
  ROW_NUMBER() OVER (PARTITION BY user_id, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date ASC) DAY) ORDER BY login_date ASC) AS consecutive_days
FROM
  user_logins;

开窗函数是SQL查询中一种非常强大的功能，它可以在不破坏原始数据行结构的情况下，对数据进行分组、排序、计算聚合值等操作，通过掌握开窗函数的用法，我们可以轻松应对各种复杂的查询需求，在实际工作中，灵活运用开窗函数可以提高查询效率，帮助我们更好地分析数据。