NTH_VALUE 返回 analytic_clause 定义的窗口中第 n 行的 measure_expr 值。返回的值具有 measure_expr 的数据类型。

语法

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

作为分析函数使用,您需要使用窗口函数的完整语法,它对一组行的集合进行计算并返回多个值,具体信息请查阅文档 OVER (analytic_clause)

参数

参数说明
OVER使用 OVER 子句定义窗口进行计算。
measure_expr字段名
nn 为正数,确定要为其返回测量值的第 n 行,如果 n 是 NULL ,函数将返回错误,如果 n 大于窗口内所有的行数,函数返回 NULL。
FROM { FIRST | LAST }确定计算方向是从窗口的第一行还是最后一行开始,默认值为 FROM FIRST
{RESPECT | IGNORE} NULLS表示是否忽略 NULL 值。默认值为 RESPECT NULLS,考虑 NULL 值。

返回类型

返回值的数据类型不限。

示例

创建表 emp_msg,并向里面插入数据。执行以下语句:

  1. CREATE TABLE emp_msg(deptno INT, ename VARCHAR(30),sal INT, MGR VARCHAR(30), hiredate VARCHAR(50));
  2. INSERT INTO emp_msg VALUES(10,'CLARK', 2750, 7839, '2018-05-01');
  3. INSERT INTO emp_msg VALUES(10,'KING', 5300, NULL, '2018-05-10');
  4. INSERT INTO emp_msg VALUES(10,'MILLER', 1600, 7782, '2018-06-01');
  5. INSERT INTO emp_msg VALUES(20,'ADAMS', 1400, 7788, '2018-05-21');
  6. INSERT INTO emp_msg VALUES(20,'FORD', 3300, 7566, '2018-06-01');
  7. INSERT INTO emp_msg VALUES(20,'JONES', 3275, 7839, '2018-06-20');
  8. INSERT INTO emp_msg VALUES(20,'SCOTT', 3300, 7566, '2018-07-01');
  9. INSERT INTO emp_msg VALUES(20,'SMITH', 1100, 7902, '2018-07-10');
  10. INSERT INTO emp_msg VALUES(30,'ALLEN', 1900, 7698, '2018-08-05');
  11. INSERT INTO emp_msg VALUES(30,'BLAKE', 3150, 7839, '2018-06-10');
  12. INSERT INTO emp_msg VALUES(30,'JAMES', 1250, 7698, '2018-09-05');
  13. INSERT INTO emp_msg VALUES(30,'MARTIN', 1550, 7698, '2018-10-01');
  14. INSERT INTO emp_msg VALUES(30,'TURNER', 1800, 7698, '2019-05-01');
  15. INSERT INTO emp_msg VALUES(30,'WARD', 1550, 7698, '2019-05-10');

按部门 deptno 分组,查询每个部门的人员的薪资和该部门内排名第三的薪资金额的对比。执行以下语句:

  1. SELECT deptno, ename, sal, nth_value(sal, 3) OVER (PARTITION BY deptno ORDER BY sal DESC
  2. rows BETWEEN unbounded preceding AND unbounded following) AS third_most_sal
  3. FROM emp_msg ORDER BY deptno,sal DESC;

查询结果如下:

  1. +--------+--------+------+----------------+
  2. | DEPTNO | ENAME | SAL | THIRD_MOST_SAL |
  3. +--------+--------+------+----------------+
  4. | 10 | KING | 5300 | 1600 |
  5. | 10 | CLARK | 2750 | 1600 |
  6. | 10 | MILLER | 1600 | 1600 |
  7. | 20 | FORD | 3300 | 3275 |
  8. | 20 | SCOTT | 3300 | 3275 |
  9. | 20 | JONES | 3275 | 3275 |
  10. | 20 | ADAMS | 1400 | 3275 |
  11. | 20 | SMITH | 1100 | 3275 |
  12. | 30 | BLAKE | 3150 | 1800 |
  13. | 30 | ALLEN | 1900 | 1800 |
  14. | 30 | TURNER | 1800 | 1800 |
  15. | 30 | MARTIN | 1550 | 1800 |
  16. | 30 | WARD | 1550 | 1800 |
  17. | 30 | JAMES | 1250 | 1800 |
  18. +--------+--------+------+----------------+