MySQL函数：`NTH_VALUE()`获取分组内的第 N 个值。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 函数：NTH_VALUE() 详解

大家好，今天我们来深入探讨 MySQL 中的一个窗口函数：NTH_VALUE()。这个函数允许我们在一个分区（分组）内，获取按照指定顺序排列的第 N 个值。相比于其他窗口函数，NTH_VALUE() 在特定场景下非常实用，尤其是在需要定位分组内的特定排位的数据时。

1. 窗口函数基础回顾

在深入 NTH_VALUE() 之前，我们先简单回顾一下窗口函数的基础概念。窗口函数（Window Function）是一种特殊的 SQL 函数，它对一组与当前行相关的行（称为窗口）执行计算。窗口函数与聚合函数类似，但不同之处在于，窗口函数不会将多行数据聚合成单行，而是为窗口中的每一行都返回一个值。

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

function_name(argument1, argument2, ...) OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column3, column4, ...]
    [frame_clause]
)

function_name: 要使用的窗口函数名称，例如 NTH_VALUE(), ROW_NUMBER(), RANK(), SUM(), AVG() 等。
argument1, argument2, ...: 传递给窗口函数的参数，具体参数取决于所使用的函数。
OVER(): OVER 子句定义了窗口函数的行为。
PARTITION BY: 将结果集划分为多个分区。窗口函数将在每个分区内独立计算。如果省略 PARTITION BY，则将整个结果集视为一个分区。
ORDER BY: 定义每个分区内行的顺序。窗口函数将按照指定的顺序进行计算。如果省略 ORDER BY，则窗口内的行的顺序是不确定的。
frame_clause: 定义窗口的大小（即窗口中包含的行的范围）。 frame_clause 允许更细粒度地控制窗口的范围。常见的 frame_clause 包括 ROWS BETWEEN 和 RANGE BETWEEN。

2. NTH_VALUE() 函数详解

NTH_VALUE() 函数的作用是返回窗口中第 N 个表达式的值。它的基本语法如下：

NTH_VALUE(expression, N) OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column3, column4, ...
    [frame_clause]
)

expression: 要从中获取值的表达式。这通常是一个列名。
N: 一个正整数，表示要获取的第 N 个值的索引。 N 必须是一个常量表达式，不能是列或变量。
OVER(): 与前面所述相同，用于定义窗口函数的行为。
PARTITION BY: 将结果集划分为多个分区。
ORDER BY: 定义每个分区内行的顺序。 NTH_VALUE() 函数会按照 ORDER BY 子句指定的顺序，找到第 N 个 expression 的值。
frame_clause: 定义窗口的大小。 NTH_VALUE() 函数会在指定的窗口范围内查找第 N 个值。如果省略 frame_clause，默认窗口是整个分区。

重要注意事项:

如果 N 大于窗口中的行数，则 NTH_VALUE() 函数返回 NULL。
NTH_VALUE() 函数需要 ORDER BY 子句来确定窗口中行的顺序，以便正确地找到第 N 个值。如果没有 ORDER BY 子句，结果将是不确定的。
N 的值必须是正整数，不能是 0 或负数。

3. NTH_VALUE() 函数的使用示例

为了更好地理解 NTH_VALUE() 函数，我们通过一些示例来演示它的使用方法。

示例 1：获取每个部门薪水第二高的员工的薪水

假设我们有一个 employees 表，包含以下列：

employee_id: 员工 ID
employee_name: 员工姓名
department_id: 部门 ID
salary: 薪水

我们想要获取每个部门薪水第二高的员工的薪水。可以使用 NTH_VALUE() 函数来实现：

SELECT
    department_id,
    NTH_VALUE(salary, 2) OVER (PARTITION BY department_id ORDER BY salary DESC) AS second_highest_salary
FROM
    employees;

在这个查询中：

PARTITION BY department_id 将 employees 表划分为多个分区，每个分区代表一个部门。
ORDER BY salary DESC 按照薪水降序排列每个分区内的员工。
NTH_VALUE(salary, 2) 在每个分区内，找到薪水第二高的员工的薪水。
second_highest_salary 是结果集中新列的别名，用于存储每个部门薪水第二高的薪水。

示例 2：获取每个类别中销量排名第三的产品名称

假设我们有一个 products 表，包含以下列：

product_id: 产品 ID
product_name: 产品名称
category_id: 类别 ID
sales: 销量

我们想要获取每个类别中销量排名第三的产品名称。可以使用 NTH_VALUE() 函数来实现：

SELECT
    category_id,
    NTH_VALUE(product_name, 3) OVER (PARTITION BY category_id ORDER BY sales DESC) AS third_highest_selling_product
FROM
    products;

在这个查询中：

PARTITION BY category_id 将 products 表划分为多个分区，每个分区代表一个类别。
ORDER BY sales DESC 按照销量降序排列每个分区内的产品。
NTH_VALUE(product_name, 3) 在每个分区内，找到销量排名第三的产品的名称。
third_highest_selling_product 是结果集中新列的别名，用于存储每个类别销量排名第三的产品的名称。

示例 3：结合 frame_clause 使用

假设我们有一个 orders 表，包含以下列：

order_id: 订单 ID
order_date: 订单日期
customer_id: 客户 ID
order_amount: 订单金额

我们想要获取每个客户最近 3 个订单中的第二个订单的金额。可以结合 frame_clause 和 NTH_VALUE() 函数来实现：

SELECT
    customer_id,
    order_date,
    order_amount,
    NTH_VALUE(order_amount, 2) OVER (PARTITION BY customer_id ORDER BY order_date DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS second_most_recent_order_amount
FROM
    orders;

在这个查询中：

PARTITION BY customer_id 将 orders 表划分为多个分区，每个分区代表一个客户。
ORDER BY order_date DESC 按照订单日期降序排列每个分区内的订单。
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 定义了一个窗口，包括当前行以及当前行之前的两行。也就是说，对于每一行，窗口包含该客户最近的 3 个订单。
NTH_VALUE(order_amount, 2) 在每个窗口内，找到第二个订单的金额。
second_most_recent_order_amount 是结果集中新列的别名，用于存储每个客户最近 3 个订单中的第二个订单的金额。

更详尽的示例以及数据准备:

为了更清楚地理解NTH_VALUE()，我们创建一个具体的employees表，并插入一些数据，然后运行查询。

-- 创建 employees 表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    department_id INT,
    salary DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES
(1, 'Alice', 101, 60000.00),
(2, 'Bob', 101, 75000.00),
(3, 'Charlie', 101, 70000.00),
(4, 'David', 102, 55000.00),
(5, 'Eve', 102, 65000.00),
(6, 'Frank', 102, 60000.00),
(7, 'Grace', 103, 80000.00),
(8, 'Henry', 103, 90000.00),
(9, 'Ivy', 103, 85000.00);

现在，让我们重新运行示例 1 的查询，并查看结果：

SELECT
    employee_name,
    department_id,
    salary,
    NTH_VALUE(salary, 2) OVER (PARTITION BY department_id ORDER BY salary DESC) AS second_highest_salary
FROM
    employees;

运行以上查询后，我们会得到类似以下的结果：

employee_name	department_id	salary	second_highest_salary
Bob	101	75000.00	70000.00
Charlie	101	70000.00	70000.00
Alice	101	60000.00	70000.00
Eve	102	65000.00	60000.00
Frank	102	60000.00	60000.00
David	102	55000.00	60000.00
Henry	103	90000.00	85000.00
Ivy	103	85000.00	85000.00
Grace	103	80000.00	85000.00

可以看到，second_highest_salary 列显示了每个部门薪水第二高的值。

处理 N 大于分区行数的情况：

如果 N 大于分区内的行数，NTH_VALUE() 将返回 NULL。我们创建一个部门，只有一名员工，看看会发生什么。

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES
(10, 'Jack', 104, 50000.00);

SELECT
    employee_name,
    department_id,
    salary,
    NTH_VALUE(salary, 2) OVER (PARTITION BY department_id ORDER BY salary DESC) AS second_highest_salary
FROM
    employees;

结果如下：

employee_name	department_id	salary	second_highest_salary
Bob	101	75000.00	70000.00
Charlie	101	70000.00	70000.00
Alice	101	60000.00	70000.00
Eve	102	65000.00	60000.00
Frank	102	60000.00	60000.00
David	102	55000.00	60000.00
Henry	103	90000.00	85000.00
Ivy	103	85000.00	85000.00
Grace	103	80000.00	85000.00
Jack	104	50000.00	NULL

对于部门 104，由于只有一名员工，因此 NTH_VALUE(salary, 2) 返回 NULL。

4. NTH_VALUE() vs. 其他窗口函数

NTH_VALUE() 函数与其他一些窗口函数，如 RANK(), DENSE_RANK(), ROW_NUMBER() 存在一些相似之处，但也存在明显的差异。

RANK() 和 DENSE_RANK(): 这些函数用于计算每一行在分区内的排名。它们返回的是排名值，而不是具体的某个值。如果需要获取排名第 N 的行的值，需要结合子查询或 CTE。
ROW_NUMBER(): 这个函数为分区内的每一行分配一个唯一的序号，从 1 开始。与 RANK() 和 DENSE_RANK() 类似，需要结合子查询或 CTE 来获取特定序号的行的值。
NTH_VALUE(): NTH_VALUE() 函数直接返回分区内第 N 个表达式的值，无需额外的子查询或 CTE。这使得它在某些场景下更加简洁和高效。

例如，如果使用 RANK() 函数获取每个部门薪水第二高的员工的薪水，需要使用如下的 SQL：

WITH RankedSalaries AS (
    SELECT
        employee_name,
        department_id,
        salary,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
    FROM
        employees
)
SELECT
    employee_name,
    department_id,
    salary
FROM
    RankedSalaries
WHERE
    salary_rank = 2;

而使用 NTH_VALUE() 函数，则可以直接通过一个简单的查询实现。

5. 性能考虑

虽然 NTH_VALUE() 函数在某些场景下非常方便，但在处理大数据集时，也需要考虑其性能影响。

ORDER BY 子句: ORDER BY 子句是窗口函数性能的关键。确保 ORDER BY 子句中使用的列已经建立了索引，可以显著提高查询效率。
PARTITION BY 子句: PARTITION BY 子句也会影响性能。合理地选择分区列，避免创建过多的分区，可以提高查询效率。
frame_clause: frame_clause 会增加窗口函数的计算复杂度。尽量避免使用过于复杂的 frame_clause，或者考虑使用其他替代方案。

一般来说，窗口函数的性能不如简单的聚合函数，但在需要进行分组和排序的情况下，窗口函数是不可避免的选择。在实际应用中，需要根据具体的数据量和查询需求，选择合适的窗口函数，并进行性能优化。

6. 总结与应用场景建议

NTH_VALUE() 函数是一个强大的窗口函数，它可以让我们轻松地获取分区内第 N 个表达式的值。掌握 NTH_VALUE() 函数，可以帮助我们编写更简洁、更高效的 SQL 查询。在以下场景中，NTH_VALUE() 函数尤其有用：

获取分组内的最大值、最小值、中位数等。
获取分组内排名第 N 的记录。
计算移动平均值、移动总和等。
比较当前行与分组内的其他行。

希望今天的讲解能够帮助大家更好地理解和使用 NTH_VALUE() 函数。谢谢大家！

MySQL 函数：NTH_VALUE() 详解

1. 窗口函数基础回顾

2. NTH_VALUE() 函数详解

3. NTH_VALUE() 函数的使用示例

4. NTH_VALUE() vs. 其他窗口函数

5. 性能考虑

6. 总结与应用场景建议

发表回复 取消回复

发表回复取消回复