oracle first_value，last_valus

標籤：

first_value和last_value 是用來去分析函數視窗中對應列的第一個值和最後一個值的函數。

文法如下：

first_value(col [ignore NULLS]) over([PARTITION BY col] [ORDER BY sal] [windows])last_value(col [ignore NULLS]) over([PARTITION BY col] [ORDER BY sal] [windows])--col          : 表示選取的列--ignore NULLS :表示忽略空值--PARTITION BY :表示分組--ORDER BY     :表示排序--windows      :表示視窗，預設值是（RANGE UNBOUNDED PRECEDING AND CURRENT ROW）從第一行到當前行--windows      :常用值（ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING）從第一行到最後一行               

樣本1：

SELECT first_value(comm ) over() firstval,       last_value(comm ) over() lastval,       e.*  FROM emp e

結果：

樣本2：

SELECT first_value(comm ) over(partition by deptno) firstval,       last_value(comm ) over(partition by deptno) lastval,       e.*  FROM emp e

結果：

樣本3：

SELECT first_value(comm ) over(partition by deptno order by sal) firstval,       last_value(comm ) over(partition by deptno order by sal) lastval,       e.*  FROM emp e

結果：

樣本4：

SELECT first_value(comm ignore nulls) over(partition by deptno order by sal) firstval,       last_value(comm ignore nulls) over(partition by deptno order by sal) lastval,       e.*  FROM emp e

結果：

樣本5：

SELECT first_value(sal ignore nulls) over(partition by deptno order by sal) firstval,       last_value(sal ignore nulls) over(partition by deptno order by sal) lastval,       e.*  FROM emp e;SELECT first_value(sal ignore nulls) over(partition by deptno order by sal) firstval,       last_value(sal ignore nulls) over(partition by deptno order by sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) lastval,       e.*  FROM emp e

結果：

第一個sql結果：

第二個sql結果：

oracle first_value，last_valus