ORACLE lag和lead函数用法

一、定义

lag和lead函数是用来查询当前行的某一字段的前N行和后N行的数据作为独立的列。

二、用法

lag(field, num, default) field为要查询的字段，num表示查询前N行数据，default表示查询数据为空时的默认值。
lead(field, num, default) field为要查询的字段，num表示查询后N行数据，default表示查询数据为空时的默认值。

三、示例

首先我们创建一张日历表，导入某一月的数据

-- Create table
create table t_calendar
(
  phydate      VARCHAR2(8) not null,
  status     VARCHAR2(1) not null
);
-- Add comments to the table 
comment on table t_calendar
  is '日历表';
-- Add comments to the columns 
comment on column t_calendar.phydate
  is '日期';
comment on column t_calendar.status
  is '状态 0-非工作日 1-工作日';

1. 查询每个日期的前一天和后一天的日期

SELECT T.PHYDATE,
       T.STATUS,
       LAG(T.PHYDATE, 1, NULL) OVER(ORDER BY T.PHYDATE) PREDATE,
       LEAD(T.PHYDATE, 1, NULL) OVER(ORDER BY T.PHYDATE) NEXTDATE
  FROM T_CALENDAR T;

2. 查询某一工作日和上一个工作日间的日期
我们先构造一个临时表，查询出2019年7月29日的上一个工作日

WITH TEMP AS
 (SELECT *
    FROM (SELECT T.PHYDATE, LAG(T.PHYDATE, 1, NULL) OVER(ORDER BY T.PHYDATE) PREDATE
            FROM (SELECT T.PHYDATE, T.STATUS FROM T_CALENDAR T WHERE T.STATUS = '1') T)
   WHERE PHYDATE = '20190729')
SELECT * FROM TEMP;

关联 T_CALENDAR 表和 TEMP 表，查询出两个日期间的数据

WITH TEMP AS
 (SELECT *
    FROM (SELECT T.PHYDATE, LAG(T.PHYDATE, 1, NULL) OVER(ORDER BY T.PHYDATE) PREDATE
            FROM (SELECT T.PHYDATE, T.STATUS FROM T_CALENDAR T WHERE T.STATUS = '1') T)
   WHERE PHYDATE = '20190729')
SELECT A.*
  FROM T_CALENDAR A
  LEFT JOIN TEMP
    ON A.PHYDATE IS NOT NULL
WHERE A.PHYDATE BETWEEN TEMP.PREDATE AND TEMP.PHYDATE;

3.查询某一节假日和下一个节假日间的日期
与2同理，使用lead函数即可

WITH TEMP AS
 (SELECT *
    FROM (SELECT T.PHYDATE, LEAD(T.PHYDATE, 1, NULL) OVER(ORDER BY T.PHYDATE) NEXTDATE
            FROM (SELECT T.PHYDATE, T.STATUS FROM T_CALENDAR T WHERE T.STATUS = '0') T)
   WHERE PHYDATE = '20190714')
SELECT A.*
  FROM T_CALENDAR A
  LEFT JOIN TEMP
    ON A.PHYDATE IS NOT NULL
 WHERE A.PHYDATE BETWEEN TEMP.PHYDATE AND TEMP.NEXTDATE;