NTH_VALUE 函数

简介

NTH_VALUE 函数用于从指定窗口内返回第 offset 行的值。该函数在处理数据时非常有用，特别是当需要获取特定位置的数据时。

语法

nth_value(expr, offset[,ignoreNull]) over ([partition_clause] [orderby_clause] [frame_clause])

参数

• expr

  expr
  : 任意类型的表达式。

• offset

  offset
  : 大整数类型（BIGINT），必须是正整数常量。当 offset 为 1 时，函数行为与 FIRST_VALUE 函数相同。

• ignoreNull

  ignoreNull
  : 可选参数，布尔类型常量，默认为 false。当设置为 true 时，函数将返回窗口内第 offset 个非 null 的值。

返回结果

返回值类型与 expr 类型相同。

使用示例

1. 基本使用（获取分区内第 3 个值）：

SELECT dep_no, salary, nth_value(salary, 3) OVER (PARTITION BY dep_no) FROM VALUES ('Eric', 1, null), ('Alex', 1, 32000), ('Felix', 2, 21000), ('Frank', 1, 30000), ('Tom', 2, 23000), ('Jane', 3, 29000), ('Jeff', 3, 35000), ('Paul', 2, 29000), ('Charles', 2, 23000) AS tab(name, dep_no, salary);

结果：

+--------+--------+---------------------------------------------------+ | dep_no | salary | `nth_value`(salary, 3) OVER (PARTITION BY dep_no) | +--------+--------+---------------------------------------------------+ | 3 | 29000 | null | | 3 | 35000 | null | | 1 | null | 30000 | | 1 | 32000 | 30000 | | 1 | 30000 | 30000 | | 2 | 21000 | 29000 | | 2 | 23000 | 29000 | | 2 | 29000 | 29000 | | 2 | 23000 | 29000 | +--------+--------+---------------------------------------------------+

2. 忽略 NULL 值（获取分区内第 2 个非 NULL 值）：

SELECT dep_no, salary, nth_value(salary, 2, true) OVER (PARTITION BY dep_no) FROM VALUES ('Eric', 1, null), ('Alex', 1, 32000), ('Felix', 2, 21000), ('Frank', 1, 30000), ('Tom', 2, 23000), ('Jane', 3, 29000), ('Jeff', 3, 35000), ('Paul', 2, 29000), ('Charles', 2, 23000) AS tab(name, dep_no, salary);

结果：

+--------+--------+--------------------------------------------------------+ | dep_no | salary | `nth_value`(salary, 2, true) OVER (PARTITION BY dep_no) | +--------+--------+--------------------------------------------------------+ | 1 | null | 30000 | | 1 | 32000 | 30000 | | 1 | 30000 | 30000 | | 2 | 21000 | 23000 | | 2 | 23000 | 23000 | | 2 | 29000 | 23000 | | 2 | 23000 | 23000 | | 3 | 29000 | 35000 | | 3 | 35000 | 35000 | +--------+--------+--------------------------------------------------------+

dep_no=1 分区中第 1 个非 NULL 值是 32000，第 2 个是 30000；dep_no=3 分区只有两行，第 2 个非 NULL 值是 35000。