FIRST_VALUE、LAST_VALUE分析函数可以按照特定分组和排序取出组内首尾值,语法
- FIRST_VALUE
- { (expr) [ {RESPECT | IGNORE} NULLS ]
- | (expr [ {RESPECT | IGNORE} NULLS ])
- }
- OVER (analytic_clause)
测试下
- 10:48:07 SCOTT@study> SELECT EMPNO,
- 10:48:15 2 DEPTNO,
- 10:48:15 3 SAL,
- 10:48:15 4 FIRST_VALUE(SAL) IGNORE NULLS OVER(PARTITION BY DEPTNO ORDER BY SAL) AS LOWEST_IN_DEPT,
- 10:48:15 5 FIRST_VALUE(SAL) IGNORE NULLS OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS 1 PRECEDING) AS PRECEDING_IN_DEPT,
- 10:48:15 6 LAST_VALUE(SAL) IGNORE NULLS OVER(PARTITION BY DEPTNO ORDER BY SAL) AS HIGHEST_IN_DEPT,
- 10:48:15 7 LAST_VALUE(SAL) IGNORE NULLS OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS HIGHEST_IN_DEPT
- 10:48:15 8 FROM EMP;
-
- EMPNO DEPTNO SAL LOWEST_IN_DEPT PRECEDING_IN_DEPT HIGHEST_IN_DEPT HIGHEST_IN_DEPT
- ---------- ---------- ---------- -------------- ----------------- --------------- ---------------
- 7934 10 1300 1300 1300 1300 5000
- 7782 10 2450 1300 1300 2450 5000
- 7839 10 5000 1300 2450 5000 5000
- 7369 20 800 800 800 800 3000
- 7876 20 1100 800 800 1100 3000
- 7566 20 2975 800 1100 2975 3000
- 7788 20 3000 800 2975 3000 3000
- 7902 20 3000 800 3000 3000 3000
- 7900 30 950 950 950 950 2850
- 7654 30 1250 950 950 1250 2850
- 7521 30 1250 950 1250 1250 2850
- 7844 30 1500 950 1250 1500 2850
- 7499 30 1600 950 1500 1600 2850
- 7698 30 2850 950 1600 2850 2850
-
- 14 rows selected.
-
- Elapsed: 00:00:00.00
- 10:48:17 SCOTT@study>
LAST_VALUE的默认写法结果不符合预期,是因为默认的开窗语句"RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW",所以需要显性写出正确的开窗语句