LAG():求前N的值
--语法结构:
LAG(目标字段,N[,默认值])OVER([PARTITION BY] 分组字段 ORDER BY 排序字段 ASC/DESC):求目标字段的前N行的该字段的值,跟该字段放同一行上显示
--需要注意的是:
1、第三个参数(默认值)意思是,当前N个超出了窗口时给的一个值,如果不写,就会给一个NULL值
2、ORDER BY 排序字段必须要有,意思就是需要按照某一个字段作为依据
3、默认值的类型需要跟目标字段的类型一致
4、LAG()是对结果集来求前N的对应的字段的值。意思就是:如果结果集发生了变化(过滤,分组啊等),那么它的值也会跟着变
5、可以直接用于算术运算(*)
--比如:查询emp表中前一个入职的员工的工资
SELECT E.*,
LAG(E.SAL,1,0)OVER(ORDER BY E.HIREDATE) LAG_SAL
FROM EMP E;
--比如:查询emp表中前一个入职的员工的工资,并且该员工比前一个员工的工资增加了多少
SELECT E.*,
LAG(E.SAL,1,0)OVER(ORDER BY E.HIREDATE) LAG_SAL,
E.SAL - LAG(E.SAL,1,0)OVER(ORDER BY E.HIREDATE) "差值"
FROM EMP E;
LEAD():求后N的值
--语法结构:
LEAD(目标字段,N[,默认值])OVER([PARTITION BY] 分组字段 ORDER BY 排序字段 ASC/DESC):求目标字段的后N行的该字段的值,跟该字段放同一行上显示
--比如:查询emp表中后一个入职的员工的工资
SELECT E.*,
LEAD(E.SAL,1,0)OVER(ORDER BY E.HIREDATE) LEAD_SAL
FROM EMP E;
---小练习一把:查询emp表中各个部门的员工的前一个入职的员工的工资,后一个入职的员工的工资
SELECT E.*,
LAG(E.SAL,1,0)OVER(PARTITION BY E.DEPTNO ORDER BY E.HIREDATE ASC) LAG_SAL,
LEAD(E.SAL,1,0)OVER(PARTITION BY E.DEPTNO ORDER BY E.HIREDATE ASC) LEAD_SAL
FROM EMP E;