oracle 分组first,Oracle分析函数FIRST_VALUE、LAST_VALUE

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"，所以需要显性写出正确的开窗语句