发布于 由admin

MySQL函数：`FIRST_VALUE()`与`LAST_VALUE()`获取分组内的第一个和最后一个值。

MySQL窗口函数：FIRST_VALUE()与LAST_VALUE()深度剖析

大家好，今天我们来深入探讨MySQL中两个强大的窗口函数：FIRST_VALUE()和LAST_VALUE()。这两个函数主要用于获取窗口（分组）内的第一个和最后一个值，在数据分析、报表生成等场景下非常有用。我们将从基本语法、应用场景、注意事项以及性能优化等方面进行详细讲解，并通过大量的代码示例帮助大家理解和掌握。

1. 窗口函数的基本概念

在讨论FIRST_VALUE()和LAST_VALUE()之前，我们先简单回顾一下窗口函数的概念。窗口函数允许我们对查询结果集中的行进行分组（partitioning），然后在每个分组内进行计算。与GROUP BY不同的是，窗口函数不会改变原始查询结果的行数，而是为每一行添加一个基于窗口的计算结果。

窗口函数的通用语法结构如下：

function_name(argument1, argument2, ...) OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column3, column4, ...]
    [frame_clause]
)
  • function_name(): 要执行的窗口函数，例如FIRST_VALUE()、LAST_VALUE()、ROW_NUMBER()等。
  • argument1, argument2, ...: 传递给窗口函数的参数。
  • OVER(): 定义窗口的规范。
    • PARTITION BY: 将结果集划分为多个分区，窗口函数将分别应用于每个分区。
    • ORDER BY: 定义分区内行的顺序，影响某些窗口函数的计算结果，如FIRST_VALUE()、LAST_VALUE()和累积计算函数。
    • frame_clause: 定义窗口帧，即当前行可以访问的行范围。如果没有指定frame_clause，默认的帧定义取决于ORDER BY子句是否存在。

2. FIRST_VALUE()函数

FIRST_VALUE()函数返回窗口中第一行的表达式的值。其基本语法如下：

FIRST_VALUE(expression) OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column3, column4, ...
    [frame_clause]
)
  • expression: 需要返回值的表达式，通常是列名。
  • PARTITION BY: 定义分区列。
  • ORDER BY: 定义分区内的排序规则，决定哪个值是“第一个”。
  • frame_clause: 定义窗口帧。

示例1：获取每个部门最早入职的员工的姓名

假设我们有一个employees表，包含员工姓名、部门和入职日期：

CREATE TABLE employees (
    employee_name VARCHAR(255),
    department VARCHAR(255),
    hire_date DATE
);

INSERT INTO employees (employee_name, department, hire_date) VALUES
('Alice', 'Sales', '2022-01-15'),
('Bob', 'Sales', '2022-03-10'),
('Charlie', 'Marketing', '2022-02-01'),
('David', 'Marketing', '2022-04-20'),
('Eve', 'IT', '2022-01-20'),
('Frank', 'IT', '2022-03-01');

要获取每个部门最早入职的员工姓名，可以使用以下SQL查询：

SELECT
    employee_name,
    department,
    hire_date,
    FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date) AS first_hired_employee
FROM
    employees;

查询结果如下：

employee_name department hire_date first_hired_employee
Charlie Marketing 2022-02-01 Charlie
David Marketing 2022-04-20 Charlie
Eve IT 2022-01-20 Eve
Frank IT 2022-03-01 Eve
Alice Sales 2022-01-15 Alice
Bob Sales 2022-03-10 Alice

可以看到，first_hired_employee列显示了每个部门最早入职的员工姓名，每行都重复显示该部门最早入职的员工姓名。

示例2：结合ROW_NUMBER()获取最早入职员工的完整信息

如果我们只想获取每个部门最早入职员工的完整信息，可以结合ROW_NUMBER()函数：

WITH RankedEmployees AS (
    SELECT
        employee_name,
        department,
        hire_date,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY hire_date) AS rn
    FROM
        employees
)
SELECT
    employee_name,
    department,
    hire_date
FROM
    RankedEmployees
WHERE
    rn = 1;

这个查询首先使用ROW_NUMBER()为每个部门的员工按照入职日期排序，然后选择排名为1的员工。

3. LAST_VALUE()函数

LAST_VALUE()函数返回窗口中最后一行的表达式的值。其基本语法如下：

LAST_VALUE(expression) OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column3, column4, ...
    [frame_clause]
)
  • expression: 需要返回值的表达式，通常是列名。
  • PARTITION BY: 定义分区列。
  • ORDER BY: 定义分区内的排序规则，决定哪个值是“最后一个”。
  • frame_clause: 定义窗口帧。

默认的窗口帧问题

需要特别注意的是，如果没有指定frame_clause，并且指定了ORDER BY子句，LAST_VALUE()函数会使用默认的窗口帧：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这意味着它只考虑从分区的第一行到当前行的范围。

示例3：未指定frame_clause时LAST_VALUE()的表现

沿用上面的employees表，我们执行以下查询：

SELECT
    employee_name,
    department,
    hire_date,
    LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date) AS last_hired_employee
FROM
    employees;

查询结果如下：

employee_name department hire_date last_hired_employee
Alice Sales 2022-01-15 Alice
Bob Sales 2022-03-10 Bob
Charlie Marketing 2022-02-01 Charlie
David Marketing 2022-04-20 David
Eve IT 2022-01-20 Eve
Frank IT 2022-03-01 Frank

可以看到，last_hired_employee列并不是每个部门最后入职的员工，而是截止到当前行的最后入职的员工。例如，对于Sales部门，第一行显示Alice，第二行显示Bob，因为Bob是截止到第二行的最后入职的员工。

示例4：正确使用LAST_VALUE()获取每个部门最后入职的员工姓名

要正确获取每个部门最后入职的员工姓名，需要显式指定frame_clause为 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，或者 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING:

SELECT
    employee_name,
    department,
    hire_date,
    LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hired_employee
FROM
    employees;

或者

SELECT
    employee_name,
    department,
    hire_date,
    LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hired_employee
FROM
    employees;

查询结果如下：

employee_name department hire_date last_hired_employee
Alice Sales 2022-01-15 Bob
Bob Sales 2022-03-10 Bob
Charlie Marketing 2022-02-01 David
David Marketing 2022-04-20 David
Eve IT 2022-01-20 Frank
Frank IT 2022-03-01 Frank

现在，last_hired_employee列正确显示了每个部门最后入职的员工姓名。

4. frame_clause详解

frame_clause用于定义窗口帧，即当前行可以访问的行范围。它可以包含以下子句：

  • ROWS or RANGE: 指定帧的单位是行还是值范围。
  • BETWEEN: 指定帧的起始和结束位置。
  • UNBOUNDED PRECEDING: 表示帧的起始位置是分区的第一行。
  • UNBOUNDED FOLLOWING: 表示帧的结束位置是分区的最后一行。
  • CURRENT ROW: 表示帧的起始或结束位置是当前行。
  • n PRECEDING: 表示帧的起始位置是当前行之前的n行。
  • n FOLLOWING: 表示帧的结束位置是当前行之后的n行。

ROWS vs RANGE

  • ROWS: 帧的单位是行，根据行的物理位置进行计算。
  • RANGE: 帧的单位是值范围，根据ORDER BY子句指定的列的值进行计算。

示例5：使用ROWS定义窗口帧

SELECT
    employee_name,
    department,
    hire_date,
    LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS last_hired_employee
FROM
    employees;

这个查询的窗口帧定义为当前行之前的一行和之后的一行。

示例6：使用RANGE定义窗口帧

SELECT
    employee_name,
    department,
    hire_date,
    LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date RANGE BETWEEN INTERVAL '1' MONTH PRECEDING AND INTERVAL '1' MONTH FOLLOWING) AS last_hired_employee
FROM
    employees;

这个查询的窗口帧定义为入职日期在当前行入职日期之前一个月和之后一个月范围内的行。

5. 应用场景

FIRST_VALUE()和LAST_VALUE()在各种数据分析和报表生成场景中都有广泛的应用：

  • 获取每个分组的第一个/最后一个值：如示例所示，可以获取每个部门最早/最后入职的员工。
  • 计算累积值：结合窗口帧，可以计算累积总和、累积平均值等。
  • 报表生成：在报表中显示每个分组的起始值和结束值。
  • 金融数据分析：计算股票的最高价、最低价等。

示例7：计算每个部门的累积薪资

假设我们有一个employee_salaries表，包含员工姓名、部门和薪资：

CREATE TABLE employee_salaries (
    employee_name VARCHAR(255),
    department VARCHAR(255),
    salary DECIMAL(10, 2)
);

INSERT INTO employee_salaries (employee_name, department, salary) VALUES
('Alice', 'Sales', 50000.00),
('Bob', 'Sales', 60000.00),
('Charlie', 'Marketing', 55000.00),
('David', 'Marketing', 70000.00),
('Eve', 'IT', 75000.00),
('Frank', 'IT', 80000.00);

可以使用以下SQL查询计算每个部门的累积薪资：

SELECT
    employee_name,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM
    employee_salaries;

6. 注意事项

  • ORDER BY 子句的重要性: FIRST_VALUE() 和 LAST_VALUE() 的结果强烈依赖于 ORDER BY 子句。确保你定义的排序规则能正确地决定 "第一个" 和 "最后一个" 的概念。
  • frame_clause 的默认行为: 务必理解默认的 frame_clause 行为，并根据需要显式指定 frame_clause，尤其是在使用 LAST_VALUE() 的时候。
  • NULL 值的处理: 如果窗口中的第一个或最后一个值是 NULL，FIRST_VALUE() 和 LAST_VALUE() 将返回 NULL。 可以使用 COALESCE 函数处理 NULL 值。 例如 FIRST_VALUE(COALESCE(column_name, 'default_value')) OVER (...)。
  • 性能: 对于大型数据集，窗口函数可能会影响性能。 考虑对相关列建立索引，并优化查询语句。
  • 与 GROUP BY 的区别: 记住窗口函数不会像 GROUP BY 那样减少行数，而是为每一行添加额外的信息。

7. 性能优化建议

  • 索引: 在PARTITION BY和ORDER BY子句中使用的列上建立索引可以显著提高查询性能。
  • 避免不必要的排序: 如果不需要排序，可以省略ORDER BY子句。
  • 简化窗口帧: 选择最简单的窗口帧定义，避免复杂的计算。
  • 使用物化视图: 对于频繁使用的窗口函数查询，可以考虑创建物化视图来预先计算结果。
  • 查询优化器: MySQL的查询优化器会自动优化窗口函数查询，但了解优化器的行为可以帮助你编写更高效的SQL。

8. 代码示例：使用COALESCE处理NULL值

假设employees表中的title列允许NULL值，我们想获取每个部门第一个员工的头衔，如果头衔为NULL，则显示"Unknown"：

CREATE TABLE employees_with_null_title (
    employee_name VARCHAR(255),
    department VARCHAR(255),
    hire_date DATE,
    title VARCHAR(255)
);

INSERT INTO employees_with_null_title (employee_name, department, hire_date, title) VALUES
('Alice', 'Sales', '2022-01-15', 'Sales Manager'),
('Bob', 'Sales', '2022-03-10', NULL),
('Charlie', 'Marketing', '2022-02-01', 'Marketing Specialist'),
('David', 'Marketing', '2022-04-20', 'Senior Marketing Manager'),
('Eve', 'IT', '2022-01-20', NULL),
('Frank', 'IT', '2022-03-01', 'Software Engineer');

SELECT
    employee_name,
    department,
    hire_date,
    COALESCE(FIRST_VALUE(title) OVER (PARTITION BY department ORDER BY hire_date), 'Unknown') AS first_hired_title
FROM
    employees_with_null_title;

查询结果将显示每个部门第一个员工的头衔，如果头衔为NULL，则显示"Unknown"。

9. 总结

FIRST_VALUE() 和 LAST_VALUE() 是强大的窗口函数，能够方便地获取分组内的第一个和最后一个值。理解它们的语法、默认行为以及 frame_clause 的作用，是有效使用这两个函数的关键。 结合其他窗口函数和适当的性能优化措施，可以充分发挥它们在数据分析和报表生成中的作用。

希望今天的讲解能够帮助大家更好地理解和使用FIRST_VALUE()和LAST_VALUE()函数。感谢大家的聆听。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注