學習筆記

[@more@]

1. lag和lead函式

select deptno,ename,sal,
lag(sal, 2, 0) over(partition by deptno order by sal) pre_2,
lag(sal, 1, 0) over(partition by deptno order by sal) pre_1,
lead(sal, 2, 0) over(partition by deptno order by sal) later_2,
lead(sal, 1, 0) over(partition by deptno order by sal) later_1
from hyf_t

DEPTNO	ENAME	SAL	PRE_2	PRE_1	LATER_2	LATER_1
0	I_CDEF1	50	0	0	200	100
0	AUDIT$	100	0	50	300	200
0	JOBSEQ	200	50	100	350	300
0	I_TYPE2	300	100	200	450	350
0	KOPM$	350	200	300	500	450
0	I_ACTOBJ	450	300	350	600	500
0	I2_JIREFRESHSQL$	500	350	450	650	600
0	FLOAT	600	450	500	0	650
0	DBMS_STANDARD	650	500	600	0	0

LAG和LEAD分別取出當前行的前N行,後N行的資料.

Oracle分析函式-3

相關文章