MySQL高级函数之：`NTH_VALUE()`：其在窗口帧内的指定位置值获取。

MySQL 高级函数 NTH_VALUE() 详解：窗口帧内的指定位置值获取

大家好，今天我们来深入探讨 MySQL 中的一个高级窗口函数：NTH_VALUE()。这个函数允许我们在一个窗口帧内，获取指定位置的值，这在很多复杂的查询场景中非常有用。我们将通过详细的示例和逐步的讲解，帮助大家理解和掌握它的用法。

1. 什么是窗口函数？

在理解 NTH_VALUE() 之前，我们需要先了解什么是窗口函数。简单来说，窗口函数是一种特殊的函数，它可以对一组与当前行相关的行（即窗口）进行计算，并为每一行返回一个值。与聚合函数（如 SUM(), AVG()）不同，窗口函数不会将多个行合并成一个单一行，而是保留每一行的独立性。

窗口函数的一般语法如下：

function_name(arguments) OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column1, column2, ...]
    [frame_clause]
)

• function_name(arguments)：要执行的窗口函数，例如 NTH_VALUE()。
• OVER(...)：定义窗口的范围和计算方式。
• PARTITION BY column1, column2, ...：将结果集分成多个分区，每个分区独立计算。类似于 GROUP BY，但不会合并行。
• ORDER BY column1, column2, ...：在每个分区内，对行进行排序。
• frame_clause：定义窗口帧，即当前行的计算范围。

2. NTH_VALUE() 函数的定义和语法

NTH_VALUE() 函数的作用是返回窗口帧内的第 N 个值。它的语法如下：

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

• expression：要返回的值的表达式，通常是一个列名。
• N：一个整数，表示要返回的值的位置。从 1 开始计数。

重要提示： 如果在窗口帧内，行的数量小于 N，则 NTH_VALUE() 函数返回 NULL。

3. 创建示例数据表

为了更好地演示 NTH_VALUE() 的用法，我们首先创建一个名为 employees 的表，并插入一些数据：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (id, department, salary) VALUES
(1, 'Sales', 60000.00),
(2, 'Sales', 75000.00),
(3, 'Sales', 80000.00),
(4, 'Marketing', 70000.00),
(5, 'Marketing', 85000.00),
(6, 'Marketing', 90000.00),
(7, 'IT', 95000.00),
(8, 'IT', 100000.00),
(9, 'IT', 105000.00),
(10, 'HR', 65000.00),
(11, 'HR', 70000.00),
(12, 'HR', 75000.00);

现在我们有了一个包含员工 ID、部门和薪资的表，可以用来进行各种窗口函数的实验。

4. NTH_VALUE() 的基本用法：获取部门内第二高薪资

让我们从一个简单的例子开始，使用 NTH_VALUE() 获取每个部门内第二高的薪资：

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

这个查询做了以下几件事：

1. PARTITION BY department：将员工按部门分成不同的分区。
2. ORDER BY salary DESC：在每个部门内，按薪资降序排序。
3. NTH_VALUE(salary, 2)：获取每个部门内排序后的第二高的薪资。

查询结果如下：

id	department	salary	second_highest_salary
10	HR	65000.00	70000.00
11	HR	70000.00	70000.00
12	HR	75000.00	70000.00
7	IT	95000.00	100000.00
8	IT	100000.00	100000.00
9	IT	105000.00	100000.00
5	Marketing	85000.00	85000.00
4	Marketing	70000.00	85000.00
6	Marketing	90000.00	85000.00
1	Sales	60000.00	75000.00
2	Sales	75000.00	75000.00
3	Sales	80000.00	75000.00

可以看到，second_highest_salary 列显示了每个部门的第二高薪资。对于少于两名员工的部门，NTH_VALUE() 返回 NULL。 在这个查询中，每个部门都有超过两名员工，所以都返回了结果。

5. 使用 frame_clause 控制窗口帧

frame_clause 用于定义窗口帧，即当前行计算的范围。它可以是以下两种形式：

• ROWS BETWEEN start AND end：基于行数定义窗口帧。
• RANGE BETWEEN start AND end：基于排序后的值定义窗口帧。

常用的 start 和 end 值包括：

• UNBOUNDED PRECEDING：窗口帧从分区的第一行开始。
• UNBOUNDED FOLLOWING：窗口帧到分区的最后一行结束。
• CURRENT ROW：窗口帧只包含当前行。
• N PRECEDING：窗口帧从当前行之前的第 N 行开始。
• N FOLLOWING：窗口帧到当前行之后的第 N 行结束。

例如，我们可以使用 frame_clause 来计算每个员工及其前后一名员工的平均薪资：

SELECT
    id,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg_salary
FROM
    employees;

在这个查询中，ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 定义了窗口帧为当前行及其前后各一行。对于每个员工，AVG() 函数计算了其自身薪资以及相邻员工的薪资的平均值。

6. NTH_VALUE() 与 frame_clause 结合使用

NTH_VALUE() 可以与 frame_clause 结合使用，以获取窗口帧内指定位置的值。例如，我们可以获取每个部门内，当前行及其之前所有行的第二高薪资：

SELECT
    id,
    department,
    salary,
    NTH_VALUE(salary, 2) OVER (PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS second_highest_salary
FROM
    employees;

在这个查询中，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义了窗口帧为当前行及其之前的所有行。NTH_VALUE(salary, 2) 函数获取了该窗口帧内第二高的薪资。

查询结果如下：

id	department	salary	second_highest_salary
10	HR	65000.00	NULL
11	HR	70000.00	65000.00
12	HR	75000.00	70000.00
7	IT	95000.00	NULL
8	IT	100000.00	95000.00
9	IT	105000.00	100000.00
5	Marketing	85000.00	NULL
4	Marketing	70000.00	70000.00
6	Marketing	90000.00	85000.00
1	Sales	60000.00	NULL
2	Sales	75000.00	60000.00
3	Sales	80000.00	75000.00

可以看到，对于每个部门的第一行，由于窗口帧内只有一行，因此 NTH_VALUE() 返回 NULL。

7. 案例：获取每个部门内薪资排名前三的员工

现在我们来看一个更复杂的例子，使用 NTH_VALUE() 来获取每个部门内薪资排名前三的员工。为了实现这个目标，我们可以使用子查询和 NTH_VALUE() 函数：

SELECT
    id,
    department,
    salary
FROM (
    SELECT
        id,
        department,
        salary,
        NTH_VALUE(salary, 3) OVER (PARTITION BY department ORDER BY salary DESC) AS third_highest_salary
    FROM
        employees
) AS subquery
WHERE
    salary >= third_highest_salary OR third_highest_salary IS NULL;

这个查询的逻辑如下：

1. 子查询:
   • 使用 NTH_VALUE(salary, 3) 获取每个部门内第三高的薪资。
   • 将结果作为子查询 subquery。
2. 外部查询:
   • 从 subquery 中选择员工的 ID、部门和薪资。
   • 使用 WHERE 子句过滤结果，只保留薪资大于等于第三高薪资的员工，或者第三高薪资为 NULL 的员工（即部门内员工人数少于 3 人的情况）。

这个查询有效地筛选出了每个部门内薪资排名前三的员工。

8. FIRST_VALUE() 和 LAST_VALUE() 的对比

与 NTH_VALUE() 类似，FIRST_VALUE() 和 LAST_VALUE() 也是常用的窗口函数。

• FIRST_VALUE(expression) OVER (...)：返回窗口帧内的第一个值。
• LAST_VALUE(expression) OVER (...)：返回窗口帧内的最后一个值。

实际上，NTH_VALUE(expression, 1) 等价于 FIRST_VALUE(expression)。

LAST_VALUE() 的使用需要特别注意，因为它默认的 frame_clause 是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，这意味着它只会返回当前行及其之前行的最后一个值。如果想要获取整个窗口的最后一个值，需要显式地指定 frame_clause 为 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 或 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

例如，以下查询展示了 LAST_VALUE() 的正确使用方法：

SELECT
    id,
    department,
    salary,
    LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary
FROM
    employees;

这个查询会返回每个部门内薪资最高的员工的薪资。

9. 总结与注意事项

NTH_VALUE() 函数是一个强大的窗口函数，可以帮助我们获取窗口帧内指定位置的值。在使用它时，需要注意以下几点：

• N 的值必须是正整数，且从 1 开始计数。
• 如果窗口帧内的行数小于 N，NTH_VALUE() 函数返回 NULL。
• frame_clause 的选择会影响窗口帧的范围，从而影响 NTH_VALUE() 的结果。
• NTH_VALUE() 可以与 PARTITION BY 和 ORDER BY 子句结合使用，以实现更复杂的查询。
• 注意和FIRST_VALUE()和LAST_VALUE()函数的区别和联系。

掌握了 NTH_VALUE() 函数，可以让我们在处理复杂的数据分析任务时更加得心应手。希望今天的讲解能够帮助大家更好地理解和使用这个函数。