mysql学习教程，从入门到精通，SQL窗口函数（38）

1、SQL窗口函数

SQL窗口函数（Window Functions）是一种强大的数据分析工具，它们允许你在结果集的行上执行计算，而不需要将这些行分组到单独的输出行中。窗口函数通常与OVER()子句一起使用，该子句定义了窗口或分区，以及窗口内的排序规则。以下是SQL窗口函数的一般语法：

<window_function>() OVER (
    [PARTITION BY <partition_expression>, ...]
    [ORDER BY <order_expression> [ASC|DESC], ...]
    [ROWS or RANGE <frame_clause>]
)

• <window_function>()：这是你要应用的窗口函数，如ROW_NUMBER()、RANK()、DENSE_RANK()、SUM()、AVG()、MIN()、MAX()等。注意，虽然SUM()、AVG()、MIN()和MAX()通常是聚合函数，但当它们与OVER()子句一起使用时，它们就变成了窗口函数。

• OVER()：这个子句定义了窗口函数的操作范围。

• PARTITION BY <partition_expression>：可选。这个子句将结果集划分为分区，窗口函数将在每个分区内独立计算。如果没有PARTITION BY，则整个结果集被视为一个单一的分区。

• ORDER BY <order_expression>：通常必需（但某些窗口函数可能不需要）。这个子句定义了窗口内行的排序顺序。这对于计算如排名或累计和等窗口函数是必需的。

• ROWS or RANGE <frame_clause>：可选。这个子句定义了窗口帧（frame），即窗口函数将考虑的行集。ROWS基于物理行数，而RANGE基于值的范围。常见的帧子句包括ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（从分区开始到当前行）和RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（基于值的范围，但具体行为可能因数据库而异）。

以下是一些常见的窗口函数及其用途：

• ROW_NUMBER()：为窗口内的每一行分配一个唯一的序号。
• RANK()：为窗口内的行分配排名，跳过相同的值并留下空位。
• DENSE_RANK()：为窗口内的行分配排名，不跳过相同的值。
• SUM()、AVG()、MIN()、MAX()：计算窗口内行的总和、平均值、最小值和最大值。

例如，以下查询使用ROW_NUMBER()窗口函数为按销售额排序的每个销售人员的销售记录分配一个序号：

SELECT
    salesperson_id,
    sale_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY salesperson_id ORDER BY sale_date) AS sale_rank
FROM
    sales;

在这个查询中，ROW_NUMBER()函数在salesperson_id分区内按sale_date排序为每行分配一个序号（sale_rank）。

窗口函数（Window Functions）是SQL中的一种强大工具，用于在查询结果集的行上执行计算，而不需要将结果集分组为单独的输出行。这些函数通常用于执行诸如排名、累计总和、移动平均等操作。

以下是一个示例，展示如何使用窗口函数来计算每个员工在部门内的工资排名和累计工资总和。

假设我们有一个名为 employees 的表，结构如下：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2)
);

并且表中有以下数据：

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES
(1, 'Alice', 1, 70000),
(2, 'Bob', 1, 50000),
(3, 'Charlie', 1, 60000),
(4, 'David', 2, 80000),
(5, 'Eve', 2, 90000),
(6, 'Frank', 2, 75000);

现在，我们希望计算每个员工在其部门内的工资排名（按降序排列）和累计工资总和。

可以使用以下SQL查询：

SELECT
    employee_id,
    employee_name,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank,
    SUM(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM
    employees;

解释：

1. RANK() OVER (PARTITION BY department_id ORDER BY salary DESC):

   • RANK() 是窗口函数之一，用于计算排名。
   • PARTITION BY department_id 表示将结果集按部门分区。
   • ORDER BY salary DESC 表示在每个分区内按工资降序排列。
   • 结果是每个员工在其部门内的工资排名。

2. SUM(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):

   • SUM() 是聚合函数之一，但在这里用作窗口函数。
   • PARTITION BY department_id 同样表示将结果集按部门分区。
   • ORDER BY salary DESC 表示在每个分区内按工资降序排列。
   • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示累计从分区开始到当前行的所有行的工资总和。
   • 结果是每个员工在其部门内的累计工资总和。

运行上述查询后，结果将如下所示：

employee_id | employee_name | department_id | salary | salary_rank | cumulative_salary
------------|---------------|---------------|--------|-------------|-------------------
4           | David         | 2             | 80000  | 1           | 80000
5           | Eve           | 2             | 90000  | 2           | 170000
6           | Frank         | 2             | 75000  | 3           | 245000
1           | Alice         | 1             | 70000  | 1           | 70000
3           | Charlie       | 1             | 60000  | 2           | 130000
2           | Bob           | 1             | 50000  | 3           | 180000

这样，我们就成功地使用了窗口函数来计算每个员工在其部门内的工资排名和累计工资总和。

当然可以。以下是一些使用SQL窗口函数的实际案例，这些案例展示了窗口函数在不同场景下的应用。

案例一：累计销售额计算

假设有一个销售数据表 sales，包含以下字段：date（日期）、amount（销售额）。我们需要计算到当前日期为止的累计销售额。

SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) AS cumulative_total
FROM
    sales;

在这个查询中，SUM(amount) OVER (ORDER BY date) 是一个窗口函数，它按照日期的顺序对销售额进行累计求和。结果集将包含每一天的销售额以及到该天为止的累计销售额。

案例二：部门内工资排名

考虑一个员工表 employees，包含以下字段：employee_id（员工ID）、name（姓名）、department_id（部门ID）、salary（工资）。我们希望计算每个员工在其部门内的工资排名。

SELECT
    employee_id,
    name,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM
    employees;

在这个查询中，RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) 是一个窗口函数，它首先按部门对员工进行分区，然后在每个分区内按工资降序排列，并计算排名。结果集将包含每个员工的ID、姓名、部门ID、工资以及在其部门内的工资排名。

案例三：累计计数

假设我们想要计算到当前日期为止的累计销售次数，可以使用 COUNT() 窗口函数。

SELECT
    date,
    COUNT(*) OVER (ORDER BY date) AS cumulative_count
FROM
    sales;

这个查询将返回每一天的日期以及到该天为止的累计销售次数。

案例四：获取前一行和后一行的数据

有时候，我们需要获取当前行前一行或后一行的数据。这可以使用 LAG() 和 LEAD() 窗口函数来实现。

SELECT
    employee_id,
    salary,
    LAG(salary, 1) OVER (ORDER BY salary) AS previous_salary,
    LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROM
    employees;

在这个查询中，LAG(salary, 1) OVER (ORDER BY salary) 返回当前行前一行的工资，而 LEAD(salary, 1) OVER (ORDER BY salary) 返回当前行后一行的工资。结果集将包含每个员工的ID、工资以及前一行和后一行的工资。

案例五：分组内的累计平均值

假设我们有一个表 student_scores，包含以下字段：student_id（学生ID）、course_id（课程ID）、score（成绩）。我们希望计算每个学生在每门课程内的累计平均成绩。

SELECT
    student_id,
    course_id,
    score,
    AVG(score) OVER (PARTITION BY student_id, course_id ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_average
FROM
    student_scores;

在这个查询中，AVG(score) OVER (PARTITION BY student_id, course_id ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 是一个窗口函数，它首先按学生和课程进行分区，然后在每个分区内按成绩顺序计算累计平均成绩。结果集将包含每个学生的ID、课程ID、成绩以及在该课程内的累计平均成绩。
这些案例展示了窗口函数在SQL中的广泛应用，它们可以极大地简化复杂的数据分析任务。