按客户分区批量分配状态：检测监管期重叠与缺口的SQL实现

需求说明 #

需为客户监管记录生成异常报告，检测监管期的重叠或缺口情况，并为每个客户的所有记录统一分配状态：

• 若客户的所有监管期既无重叠也无缺口，所有行状态为Valid
• 若客户存在任一监管期重叠或缺口，所有行状态为Issue Found

示例情况：

• Client A：无重叠/缺口，状态全为Valid
• Client B、C：存在监管期重叠，状态全为Issue Found
• Client D：存在监管期缺口，状态全为Issue Found

数据集表结构与数据 #

CREATE TABLE Report
(Id INT, ClientId INT, ClientName VARCHAR(30), SupervisorId INT, SupervisorName 
VARCHAR(30), SupervisionStartDate DATE, SupervisionEndDate DATE);

INSERT INTO Report
VALUES
(1, 22, 'Client A', 33, 'Supervisor A', '2022-01-01', '2022-04-30'),
(2, 22, 'Client A', 44, 'Supervisor B', '2022-05-01', '2022-08-23'),
(3, 22, 'Client A', 55, 'Supervisor C', '2022-08-24', NULL),
(4, 23, 'Client B', 33, 'Supervisor A', '2022-01-01', '2022-04-30'),
(5, 23, 'Client B', 44, 'Supervisor B', '2022-04-30', '2022-08-23'),
(6, 24, 'Client C', 33, 'Supervisor A', '2022-01-01', '2022-04-30'),
(7, 24, 'Client C', 44, 'Supervisor B', '2022-05-01', '2022-08-23'),
(8, 24, 'Client C', 55, 'Supervisor C', '2022-07-22', '2022-10-25'),
(9, 25, 'Client D', 33, 'Supervisor A', '2022-01-01', '2022-04-30'),
(10, 25, 'Client D', 44, 'Supervisor B', '2022-07-23', NULL)

完整SQL实现方案 #

通过窗口函数标记客户的异常状态，再将状态关联回原表，实现统一分配状态的需求：

WITH ClientSupervisionCheck AS (
    SELECT 
        Report.*,
        -- 标记单条记录是否存在缺口或重叠异常
        CASE 
            -- 缺口：当前记录开始日期晚于上一条结束日期1天以上
            WHEN LAG(SupervisionEndDate) OVER (PARTITION BY ClientId ORDER BY SupervisionStartDate) IS NOT NULL 
                 AND SupervisionStartDate > DATEADD(DAY, 1, LAG(SupervisionEndDate) OVER (PARTITION BY ClientId ORDER BY SupervisionStartDate)) THEN 1
            -- 重叠：当前记录开始日期早于上一条结束日期
            WHEN LAG(SupervisionEndDate) OVER (PARTITION BY ClientId ORDER BY SupervisionStartDate) IS NOT NULL 
                 AND SupervisionStartDate < LAG(SupervisionEndDate) OVER (PARTITION BY ClientId ORDER BY SupervisionStartDate) THEN 1
            -- 重叠：当前记录结束日期（空值取当前日期）晚于下一条开始日期
            WHEN LEAD(SupervisionStartDate) OVER (PARTITION BY ClientId ORDER BY SupervisionStartDate) IS NOT NULL 
                 AND COALESCE(SupervisionEndDate, GETDATE()) > LEAD(SupervisionStartDate) OVER (PARTITION BY ClientId ORDER BY SupervisionStartDate) THEN 1
            ELSE 0
        END AS HasIssue
    FROM Report
),
ClientStatus AS (
    SELECT 
        ClientId,
        -- 客户只要有一条异常记录，整体状态标记为Issue Found
        CASE WHEN MAX(HasIssue) = 1 THEN 'Issue Found' ELSE 'Valid' END AS ClientStatus
    FROM ClientSupervisionCheck
    GROUP BY ClientId
)
SELECT 
    r.*,
    cs.ClientStatus
FROM Report r
JOIN ClientStatus cs ON r.ClientId = cs.ClientId
ORDER BY r.ClientId, r.SupervisionStartDate;

逻辑说明 #

1. ClientSupervisionCheck CTE：

   • 用LAG函数对比当前与上一条记录的日期，识别缺口和前向重叠
   • 用LEAD函数对比当前与下一条记录的日期，识别后向重叠
   • 用HasIssue字段标记单条记录是否存在异常

2. ClientStatus CTE：

   • 按客户分组，通过MAX(HasIssue)判断客户是否存在异常，统一生成客户级状态

3. 最终查询：将客户状态关联回原表，输出每条记录对应的客户整体状态

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