Lag、Lead

Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。

LAG语法

​​​https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/LAG.html#GUID-68081CD0-72BE-4C0A-AA6B-AD39FFA7BCF2​​​

LEAD语法

​​​https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/LEAD.html#GUID-0A0481F1-E98F-4535-A739-FCCA8D1B5B77​​​​

lag/lead(value_expr,offset,def_value) over()
value_expr---列名
offset---偏移量
def_value---默认值，没有值默认为NULL

示例

查询job_id = 'PU_CLERK'的用户信息

SELECT hire_date, last_name, salary,
       LAG(salary) OVER (ORDER BY hire_date) AS prev_sal
  FROM hr.employees
  WHERE job_id = 'PU_CLERK'
  ORDER BY hire_date;

HIRE_DATE ​	​ LAST_NAME ​	​ SALARY ​	​ PREV_SAL ​
18-MAY-03	Khoo	3100	​ -<<<为空​
24-JUL-05	Tobias	2800	3100
24-DEC-05	Baida	2900	2800
15-NOV-06	Himuro	2600	2900
10-AUG-07	Colmenares	2500	2600 ​

查询job_id = 'PU_CLERK'的用户信息，指定默认值为0

SELECT hire_date, last_name, salary,
       LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
  FROM hr.employees
  WHERE job_id = 'PU_CLERK'
  ORDER BY hire_date;

HIRE_DATE	LAST_NAME	SALARY	​ PREV_SAL ​​
18-MAY-03	Khoo	3100	​ 0 ​
24-JUL-05	Tobias	2800	3100
24-DEC-05	Baida	2900	2800
15-NOV-06	Himuro	2600	2900
10-AUG-07	Colmenares	2500	2600 ​

偏移量设置为2

SELECT hire_date, last_name, salary,
       LAG(salary, 2, 0) OVER (ORDER BY hire_date) AS prev_sal
  FROM hr.employees
  WHERE job_id = 'PU_CLERK'
  ORDER BY hire_date;

HIRE_DATE	LAST_NAME	SALARY	​ PREV_SAL ​​
18-MAY-03	Khoo	3100	0
24-JUL-05	Tobias	2800	​​​ 0 ​​​
24-DEC-05	Baida	2900	3100
15-NOV-06	Himuro	2600	2800
10-AUG-07	Colmenares	2500	2900 ​

查询job_id = 'PU_CLERK'用户工资差

select employee_id, ename, job_id, hire_date, salary,sal_last,salary-sal_last diff_sal from (select employee_id,first_name||' '|| last_name ename, job_id, hire_date, salary , 
      lag(salary,1,0) over (order by hire_date)  sal_last
  FROM hr.employees
  WHERE job_id = 'PU_CLERK'
  ORDER BY hire_date);

EMPLOYEE_ID	ENAME	JOB_ID	HIRE_DATE	SALARY	SAL_LAST	​ DIFF_SAL ​​
115	Alexander Khoo	PU_CLERK	18-MAY-03	3100	0	​​​ 3100 ​​​
117	Sigal Tobias	PU_CLERK	24-JUL-05	2800	3100	​​​ -300 ​​​
116	Shelli Baida	PU_CLERK	24-DEC-05	2900	2800	​​​ 100 ​​​
118	Guy Himuro	PU_CLERK	15-NOV-06	2600	2900	​​​ -300 ​​​
119	Karen Colmenares	PU_CLERK	10-AUG-07	2500	2600	​​​ -100 ​​​

LEAD与LAG语法相同，只是LEAD从后向前查询