通过日期对比查找用户资格缺失时间段的SQL解决方案

需要通过日期对比确定用户失去资格的时间段。以示例用户为例，其在2023年10月1日至10月19日无资格，该区间为上一条记录的MEM_EXP_DATE（2023/9/30）与下一条记录的MEM_EFF_DATE（2023/10/20）之间的空档期。需要将此资格缺失区间与用户的AUTH_EFF_DATE、AUTH_EXP_DATE（授权时间段）做对比，找出覆盖了资格缺失区间的授权记录。考虑过用LEAD或LAG函数，但不确定具体用法。

CREATE TABLE ADMITS
(
ID_NUM INT
,AUTH_EFF_DATE  date null
,AUTH_EXP_DATE  date null
,MEM_EFF_DATE   date null
,MEM_EXP_DATE date null
)

INSERT INTO ADMITS (ID_NUM,AUTH_EFF_DATE,AUTH_EXP_DATE,MEM_EFF_DATE,MEM_EXP_DATE)
VALUES
 (118206307, '1/2/2023', '6/7/2023', '4/22/2022', '9/30/2023')
,(118206307, '8/30/2023', '2/17/2024', '4/22/2022', '9/30/2023')
,(118206307, '1/2/2023', '6/7/2023', '10/20/2023', '12/31/9999')
,(118206307, '8/30/2023', '2/17/2024', '10/20/2023', '12/31/9999');

步骤1：提取用户的会员资格空档期 #

先对用户的会员区间去重，再按会员生效日期排序，用LEAD函数获取下一条记录的会员生效日期，从而计算出资格缺失的区间：

WITH member_gaps AS (
    SELECT 
        ID_NUM,
        MEM_EXP_DATE + INTERVAL '1 DAY' AS gap_start,
        LEAD(MEM_EFF_DATE) OVER (PARTITION BY ID_NUM ORDER BY MEM_EFF_DATE) - INTERVAL '1 DAY' AS gap_end
    FROM (
        SELECT DISTINCT ID_NUM, MEM_EFF_DATE, MEM_EXP_DATE
        FROM ADMITS
    ) AS distinct_members
)
SELECT ID_NUM, gap_start, gap_end
FROM member_gaps
WHERE gap_start <= gap_end;

执行后会得到有效资格缺失区间，示例中结果为2023-10-01至2023-10-19。

步骤2：关联授权记录，筛选覆盖空档期的授权 #

将空档期与原表的授权时间段做重叠判断，找出符合条件的授权记录：

WITH member_gaps AS (
    SELECT 
        ID_NUM,
        MEM_EXP_DATE + INTERVAL '1 DAY' AS gap_start,
        LEAD(MEM_EFF_DATE) OVER (PARTITION BY ID_NUM ORDER BY MEM_EFF_DATE) - INTERVAL '1 DAY' AS gap_end
    FROM (
        SELECT DISTINCT ID_NUM, MEM_EFF_DATE, MEM_EXP_DATE
        FROM ADMITS
    ) AS distinct_members
), valid_gaps AS (
    SELECT ID_NUM, gap_start, gap_end
    FROM member_gaps
    WHERE gap_start <= gap_end
)
SELECT 
    a.ID_NUM,
    a.AUTH_EFF_DATE,
    a.AUTH_EXP_DATE,
    v.gap_start AS 资格缺失开始日期,
    v.gap_end AS 资格缺失结束日期
FROM ADMITS a
JOIN valid_gaps v ON a.ID_NUM = v.ID_NUM
WHERE (a.AUTH_EFF_DATE <= v.gap_end) AND (a.AUTH_EXP_DATE >= v.gap_start);

关键说明 #

• LEAD(MEM_EFF_DATE) OVER (PARTITION BY ID_NUM ORDER BY MEM_EFF_DATE)：按用户分组、会员生效日期排序，获取当前行下一行的会员生效日期，用于计算空档期结束时间。
• 日期加减语法可根据数据库调整：比如MySQL用DATE_ADD(MEM_EXP_DATE, INTERVAL 1 DAY)，SQL Server用DATEADD(day,1,MEM_EXP_DATE)，上述示例为标准SQL语法。

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