一、定义
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;