发布于 由admin

MySQL高级函数之：`FIRST_VALUE()` 和 `LAST_VALUE()`：其在窗口帧内的首尾值获取。

MySQL高级函数之：FIRST_VALUE() 和 LAST_VALUE()：窗口帧内的首尾值获取

大家好，今天我们来深入探讨MySQL中两个非常有用的窗口函数：FIRST_VALUE() 和 LAST_VALUE()。这两个函数允许我们在窗口帧内获取第一个值和最后一个值，这在很多场景下都非常有用，例如计算累计占比、识别首尾事件等等。

1. 窗口函数基础回顾

在深入了解 FIRST_VALUE() 和 LAST_VALUE() 之前，我们先简单回顾一下窗口函数的基本概念。窗口函数是一种特殊的函数，它在与当前行相关的行集合（称为窗口）上执行计算。与聚合函数不同的是，窗口函数不会将多行数据聚合为一行，而是为结果集中的每一行返回一个值。

窗口函数的基本语法如下：

function_name() OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column3, column4, ...]
    [frame_clause]
)
  • function_name()：窗口函数的名称，例如 FIRST_VALUE(), LAST_VALUE(), ROW_NUMBER(), RANK(), SUM(), AVG() 等等。
  • PARTITION BY 子句：将结果集划分为多个分区。窗口函数将分别应用于每个分区。类似于 GROUP BY 子句，但不会将行聚合。
  • ORDER BY 子句：定义每个分区内行的顺序。这对于某些窗口函数（例如 ROW_NUMBER() 和 RANK()）非常重要，也影响 FIRST_VALUE() 和 LAST_VALUE() 的结果，尤其是在没有指定 frame_clause 的情况下。
  • frame_clause 子句：定义窗口帧。窗口帧是当前行相关的行集合。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: 要获取第一个值的表达式，可以是列名、常量或表达式。

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

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

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    department VARCHAR(255) NOT NULL,
    hire_date DATE NOT NULL
);

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

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

SELECT
    name,
    department,
    hire_date,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY hire_date ASC) AS first_employee_name
FROM
    employees;

这个查询将返回每个员工的姓名、部门、入职日期以及该部门最早入职员工的姓名。PARTITION BY department 子句将结果集划分为多个分区，每个分区对应一个部门。ORDER BY hire_date ASC 子句定义了每个分区内行的顺序，按照入职日期升序排列。 FIRST_VALUE(name) 函数返回每个分区中第一个员工的姓名，也就是最早入职员工的姓名。

2.2 示例：计算每个产品销售额占总销售额的比例，并展示第一个产品的销售额

假设我们有一个 sales 表，包含产品的名称和销售额：

CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL,
    sales_amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO sales (product_name, sales_amount) VALUES
('Product A', 100.00),
('Product B', 150.00),
('Product C', 200.00),
('Product D', 120.00);

要计算每个产品销售额占总销售额的比例，并展示第一个产品的销售额，可以使用以下查询：

SELECT
    product_name,
    sales_amount,
    sales_amount / SUM(sales_amount) OVER () AS sales_percentage,
    FIRST_VALUE(sales_amount) OVER (ORDER BY sales_amount DESC) AS first_product_sales
FROM
    sales;

这个查询将返回每个产品的名称、销售额、销售额占总销售额的比例以及销售额最高的产品的销售额。 SUM(sales_amount) OVER () 计算总销售额，FIRST_VALUE(sales_amount) OVER (ORDER BY sales_amount DESC) 获取销售额最高的产品的销售额。注意这里没有 PARTITION BY 子句，因此整个结果集被视为一个分区。

2.3 frame_clause 对 FIRST_VALUE() 的影响

frame_clause 可以控制 FIRST_VALUE() 函数的窗口帧范围。如果没有指定 frame_clause，并且存在 ORDER BY 子句，则默认的窗口帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这意味着窗口帧包括从分区的第一行到当前行的所有行。这会导致 FIRST_VALUE() 返回分区的第一行的值。

例如，考虑以下查询：

SELECT
    name,
    department,
    hire_date,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY hire_date ASC) AS first_employee_name_default_frame
FROM
    employees;

这个查询与之前的例子相同，但没有指定 frame_clause。由于存在 ORDER BY hire_date ASC 子句，默认的窗口帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。因此，对于每个部门，FIRST_VALUE(name) 返回该部门入职日期最早的员工的姓名。

如果我们使用 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 作为 frame_clause，结果会保持不变，因为窗口帧包括分区的所有行：

SELECT
    name,
    department,
    hire_date,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY hire_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_employee_name_full_frame
FROM
    employees;

但是，如果我们使用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 作为 frame_clause，结果仍然是该部门入职日期最早的员工的姓名，因为这是默认行为：

SELECT
    name,
    department,
    hire_date,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY hire_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_employee_name_current_row_frame
FROM
    employees;

3. LAST_VALUE() 函数

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

LAST_VALUE(expression) OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column3, column4, ...]
    [frame_clause]
)
  • expression: 要获取最后一个值的表达式，可以是列名、常量或表达式。

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

使用之前的 employees 表，要获取每个部门最新入职员工的姓名，可以使用以下查询：

SELECT
    name,
    department,
    hire_date,
    LAST_VALUE(name) OVER (PARTITION BY department ORDER BY hire_date ASC) AS last_employee_name
FROM
    employees;

这个查询的结果可能不是我们期望的。由于默认的窗口帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，LAST_VALUE(name) 返回的是当前行的姓名。要获取每个部门最新入职员工的姓名，我们需要显式地指定 frame_clause 为 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：

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

现在，这个查询将返回每个员工的姓名、部门、入职日期以及该部门最新入职员工的姓名。

3.2 示例：获取每个月最后一次交易的金额

假设我们有一个 transactions 表，包含交易日期和交易金额：

CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO transactions (transaction_date, amount) VALUES
('2023-01-10', 100.00),
('2023-01-20', 150.00),
('2023-02-05', 200.00),
('2023-02-15', 120.00),
('2023-02-28', 180.00),
('2023-03-10', 250.00);

要获取每个月最后一次交易的金额，可以使用以下查询：

SELECT
    transaction_date,
    amount,
    LAST_VALUE(amount) OVER (PARTITION BY DATE_FORMAT(transaction_date, '%Y-%m') ORDER BY transaction_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_transaction_amount
FROM
    transactions;

这个查询将返回每个交易的日期、金额以及该月最后一次交易的金额。 PARTITION BY DATE_FORMAT(transaction_date, '%Y-%m') 子句将结果集划分为多个分区，每个分区对应一个月。 ORDER BY transaction_date ASC 子句定义了每个分区内行的顺序，按照交易日期升序排列。 LAST_VALUE(amount) 函数返回每个分区中最后一个交易的金额，也就是该月最后一次交易的金额。同样，ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 确保了窗口帧包含该月的所有交易。

4. FIRST_VALUE() 和 LAST_VALUE() 与 IGNORE NULLS 子句

MySQL 8.0 及更高版本支持 IGNORE NULLS 子句，可以与 FIRST_VALUE() 和 LAST_VALUE() 一起使用，以忽略窗口帧中的 NULL 值。

例如：

SELECT
    name,
    department,
    hire_date,
    FIRST_VALUE(name) IGNORE NULLS OVER (PARTITION BY department ORDER BY hire_date ASC) AS first_employee_name
FROM
    employees;

SELECT
    name,
    department,
    hire_date,
    LAST_VALUE(name) IGNORE NULLS OVER (PARTITION BY department ORDER BY hire_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_employee_name
FROM
    employees;

如果 name 列包含 NULL 值，IGNORE NULLS 子句将忽略这些 NULL 值，并返回窗口帧中第一个非 NULL 值或最后一个非 NULL 值。

5. 总结

FIRST_VALUE() 和 LAST_VALUE() 是强大的窗口函数，可以帮助我们在窗口帧内获取第一个值和最后一个值。理解 PARTITION BY、ORDER BY 和 frame_clause 的作用至关重要，才能正确地使用这两个函数。 IGNORE NULLS 子句可以帮助我们处理 NULL 值的情况。掌握这两个函数，可以使你的SQL查询更加灵活和高效。

6. 重点回顾：关键概念和用法

  • FIRST_VALUE() 获取窗口帧的第一个值，LAST_VALUE() 获取窗口帧的最后一个值。
  • frame_clause 定义窗口帧范围，影响 LAST_VALUE() 的结果。
  • IGNORE NULLS 可以忽略 NULL 值。

发表回复

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