NTH_VALUE

声明

NTH_VALUE (measure_expr, n) [ FROM { FIRST | LAST } ] [ { RESPECT | IGNORE } NULLS ] OVER (analytic_clause)

说明

返回表达式 expr 的第 n 个值，方向由 [ FROM { FIRST | LAST } ] 确定，默认为 FROM FIRST，含有是否忽略 NULL 值的标志。其窗口为统一的 analytic_clause。

其中，n 必须为正数，如果 n 是 NULL，函数将返回错误；如果 n 大于窗口内所有的行数，此函数将返回 NULL。

示例

CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected (0.036 sec)

INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected (00.001 sec)

SELECT LAST_NAME, FIRST_VALUE(SALARY) OVER(PARTITION BY JOB_ID) FIRST_S, LAST_VALUE(SALARY) OVER(PARTITION BY JOB_ID) LAST_S, NTH_VALUE(SALARY,2) OVER(PARTITION BY JOB_ID) 2ND_S FROM EXPLOYEES;
+-----------+---------+--------+-------+
| LAST_NAME | FIRST_S | LAST_S | 2ND_S |
+-----------+---------+--------+-------+
| JIM       |    2000 |   2000 |  NULL |
| MIKE      |   12000 |  11000 | 13000 |
| LILY      |   12000 |  11000 | 13000 |
| TOM       |   12000 |  11000 | 13000 |
+-----------+---------+--------+-------+
4 rows in set (0.003 sec)