Oracle如何用递归CTE、lead分析函数查询N>1的连续缺勤记录

我尝试用递归公用表表达式（Recursive Common Table Expressions）和LEAD分析函数查询连续缺勤天数大于1的缺勤日期记录，实现过程中遇到了问题。
注意：为简化测试用例，暂时没有在查询中关联employees表获取first_name和last_name字段。

测试用例 #

期望输出 #

EMPLOYEE_ID ABSENT_DATE
1            14-JUL-21 Jane Doe
1            15-JUL-21 Jane Doe
1            30-JUL-21 Jane Doe
1            31-JUL-21 Jane Doe
4            22-JUL-21 Mike Jones 
4            23-JUL-21 Mike Jones 

表结构与测试数据 #

首先建员工表employees：

Create table employees(
 employee_id NUMBER(6), 
 first_name VARCHAR2(20),
 last_name VARCHAR2(20),
 card_num VARCHAR2(10),
 work_days VARCHAR2(7)
);

ALTER TABLE employees
ADD ( CONSTRAINT employees_pk
PRIMARY KEY (employee_id));

-- 插入员工测试数据
INSERT INTO employees                   
(
EMPLOYEE_ID,
first_name, 
last_name,
card_num,
work_days
)
WITH names AS ( 
SELECT 1, 'Jane',     'Doe','F123456', 'NYYYYYN'FROM dual UNION ALL 
SELECT 2, 'Madison', 'Smith','R33432','NYYYYYN'
FROM dual UNION ALL 
SELECT 3, 'Justin',   'Case','C765341','NYYYYYN'
FROM dual UNION ALL 
SELECT 4, 'Mike',     'Jones','D564311','NYYYYYN' FROM dual  ) 
SELECT * FROM names;

再建缺勤记录表absences：

create table absences(
 seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
 employee_id NUMBER(6),
 absent_date DATE,
 constraint absence_chk check (absent_date=trunc(absent_date, 'dd')),
 constraint absence_pk primary key (employee_id, absent_date)
);

-- 插入缺勤测试数据
begin  
  insert into absences values (1,1, date'2021-07-21');  
  insert into absences values (2,4, date'2021-07-22');  
  insert into absences values (3,4, date'2021-07-23');  
  insert into absences values (4,4, date'2021-07-26');  
  insert into absences values (5,1, date'2021-07-30');  
  insert into absences values (6,1, date'2021-07-31');  
  insert into absences values (7,4, date'2021-07-13');  
  insert into absences values (8,1, date'2021-07-14');  
  insert into absences values (9,1, date'2021-07-15');  
  commit;  
end;
/

多种解决方案 #

方案1：Tabibitosan法统计连续缺勤区间 #

返回每个连续缺勤的起止日期和缺勤天数：

WITH multi_day as (
    -- 用Tabibitosan法给连续日期分组：日期减去行号，连续日期的计算结果相同
    select employee_id
          ,absent_date
          ,absent_date-row_number() over (partition by employee_id order by absent_date) as grp
    from   absences
    order by  employee_id, absent_date
)
select  m.employee_id
       ,e.first_name
       ,e.last_name
       ,min(m.absent_date) as start_of_absence
       ,max(m.absent_date) as end_of_absence
       ,count(*) as days_absent
from   multi_day m
join employees e on e.employee_id = m.employee_id
group by m.employee_id, m.grp, e.first_name, e.last_name
having count(*) > 1
order by 1,2;

方案2：Tabibitosan法返回所有连续缺勤的单条记录 #

和方案1逻辑类似，直接返回符合条件的每条缺勤记录，和期望输出格式一致：

WITH tab as (
    -- 用Tabibitosan法给连续日期分组
    select employee_id
          ,absent_date
          ,absent_date-row_number() over (partition by employee_id order by absent_date) as grp
    from   absences
),
multi_day as (
    -- 统计每个分组的天数
    select employee_id
          ,absent_date
          ,count(*) over (partition by employee_id, grp) as grp_cnt
    from   tab
)
select m.employee_id
      ,e.first_name
      ,e.last_name
      ,m.absent_date
from   multi_day m
join employees e on e.employee_id = m.employee_id
where grp_cnt > 1
order by 1,2;

方案3：LEAD/LAG窗口函数法 #

通过相邻日期的差值判断是否属于连续区间：

WITH consecutive_absences AS 
(
    SELECT  a.absent_date,
            a.employee_id, 
            e.first_name, 
            e.last_name,
            -- 取同员工下一条缺勤日期
            LEAD(a.absent_date) OVER (PARTITION BY a.employee_id ORDER BY a.absent_date) AS next_date,
            -- 取同员工上一条缺勤日期
            LAG(a.absent_date) OVER (PARTITION BY a.employee_id ORDER BY a.absent_date) AS prev_date
    FROM absences a
    join employees e on e.employee_id = a.employee_id
) 
SELECT employee_id, 
       first_name,
       last_name,
       absent_date 
FROM consecutive_absences
-- 只要和前后任意一天差1天，就属于连续区间
WHERE (next_date = absent_date + 1 OR prev_date = absent_date -1)
ORDER BY employee_id, absent_date;

方案4：MATCH_RECOGNIZE模式匹配法（Oracle 12c+支持） #

直接用模式匹配识别连续日期序列：

SELECT a.employee_id, a.absent_date, e.first_name, e.last_name
FROM absences
MATCH_RECOGNIZE
(
    PARTITION BY employee_id
    ORDER BY absent_date
    ALL ROWS PER MATCH
    -- 匹配至少2天的连续日期序列
    PATTERN (frst nxt+)
    DEFINE nxt AS absent_date <= PREV(absent_date) + 1
) a
JOIN employees e ON e.employee_id = a.employee_id
ORDER BY employee_id, absent_date;

内容的提问来源于stack exchange，提问作者Beefstu