Snowflake医疗数据中基于日期关联的就诊分组问题求助

分组规则 #

需按以下规则对目标医疗服务的就诊记录（table1）分组：

• 规则1：两条就诊记录的日期存在重叠
• 规则2：两条就诊记录日期无重叠，但存在一条全量理赔记录（table2中ProcedureCode非目标代码）的日期范围同时覆盖这两条记录的日期

错误现象：现有逻辑错误将MemberID=2的两条无重叠且无覆盖记录归为一组，正确预期应为：Member1、2的目标就诊无需分组（FLAG=0），Member3的两条记录需分组（FLAG=1）

测试数据集 #

目标医疗服务理赔记录表（table1） #

CREATE OR REPLACE TEMP TABLE table1 (
ProcedureCode varchar,
MemberID NUMERIC,
StartDate date,
EndDate date,
StartYearMonth varchar,
EndYearMonth varchar);

INSERT INTO table1 (ProcedureCode, MemberID, StartDate, EndDate, StartYearMonth, EndYearMonth)
VALUES 
('12345', 1, '7/6/2021', '7/6/2021', 202107, 202107),
('12345', 1, '8/17/2021', '8/17/2021', 202108, 202108),
('12345', 2, '8/3/2021', '8/3/2021', 202108, 202108),
('12345', 2, '8/9/2021', '8/9/2021', 202108, 202108),
('12345', 3, '11/5/2021', '11/5/2021', 202111, 202111),
('12345', 3, '11/11/2021', '11/11/2021', 202111, 202111);

全量理赔记录表（table2） #

CREATE OR REPLACE TEMP TABLE table2 (
ProcedureCode varchar,
MemberID NUMERIC,
StartDate date,
EndDate date,
StartYearMonth varchar,
EndYearMonth varchar);

INSERT INTO table2 (ProcedureCode, MemberID, StartDate, EndDate, StartYearMonth, EndYearMonth)
VALUES 
('12345',   1,  '7/6/2021', '7/6/2021', 202107, 202107),
('12345',   1,  '8/17/2021', '8/17/2021', 202108, 202108),
('98765',   2,  '8/1/2021', '8/2/2021', 202108, 202108),
('12345',   2,  '8/3/2021', '8/3/2021', 202108, 202108),
('12345',   2,  '8/9/2021', '8/9/2021', 202108, 202108),
('98765',   2,  '8/11/2021', '8/15/2021', 202108, 202108),
('98765',   3,  '11/1/2021', '11/15/2021', 202111, 202111),
('12345',   3,  '11/5/2021', '11/5/2021',   202111, 202111),
('12345',   3,  '11/11/2021', '11/11/2021', 202111, 202111);

现有错误代码 #

分组尝试代码（table3） #

CREATE OR REPLACE TEMP TABLE table3 AS
SELECT 
a.MemberID,
a.StartDate,
a.EndDate,
LEAD(a.StartDate, 1) OVER (PARTITION BY a.MemberID ORDER BY a.StartDate) AS next_row_start_date,
LEAD(a.EndDate, 1) OVER (PARTITION BY a.MemberID ORDER BY a.EndDate) AS next_row_end_date,
MIN(b.StartDate) AS MinStartDate,
MAX(b.EndDate) AS MaxEndDate,
ROW_NUMBER() OVER (PARTITION BY a.MemberID ORDER BY a.StartDate) AS row_num
FROM table1 A
LEFT JOIN table2 B
ON A.MemberID = B.MemberID
AND A.StartYearMonth = B.StartYearMonth
AND A.EndYearMonth = B.EndYearMonth 
GROUP BY 
A.MemberID,
a.StartDate,
a.EndDate;

标记分组代码（逻辑错误，table4） #

CREATE OR REPLACE TEMP TABLE table4 AS
SELECT 
A.*,
CASE WHEN 
(
((a.StartDate BETWEEN a.MinStartDate AND a.MaxEndDate) AND (a.next_row_start_date BETWEEN a.MinStartDate AND a.MaxEndDate))
OR 
((a.EndDate BETWEEN a.MinStartDate AND a.MaxEndDate) AND (a.next_row_end_date BETWEEN a.MinStartDate AND a.MaxEndDate))
)
THEN 1 
ELSE 0
END AS flag
FROM table3 A;

错误原因：现有逻辑用同月份全量记录的最小/最大日期范围判断，忽略了规则2中“单条无关记录覆盖两条目标记录”的核心要求，导致MemberID=2的两条无关联记录被错误归为一组。

修正方案 #

思路 #

1. 先提取全量数据中的非目标理赔记录，单独存储其日期范围
2. 对目标就诊记录进行两两配对，分别验证是否满足规则1或规则2
3. 基于配对结果，为每条目标记录标记是否属于同一分组

修正代码 #

-- 步骤1：提取非目标医疗服务的理赔记录日期范围
CREATE OR REPLACE TEMP TABLE non_target_claims AS
SELECT 
    MemberID,
    StartDate AS non_target_start,
    EndDate AS non_target_end
FROM table2
WHERE ProcedureCode != '12345';

-- 步骤2：两两配对目标记录，验证分组规则
CREATE OR REPLACE TEMP TABLE paired_claims AS
SELECT 
    t1.MemberID,
    t1.StartDate AS claim1_start,
    t1.EndDate AS claim1_end,
    t2.StartDate AS claim2_start,
    t2.EndDate AS claim2_end,
    -- 验证规则1：日期重叠
    CASE WHEN t1.EndDate >= t2.StartDate AND t2.EndDate >= t1.StartDate THEN 1 ELSE 0 END AS overlap_flag,
    -- 验证规则2：存在非目标记录覆盖两条目标记录
    CASE WHEN EXISTS (
        SELECT 1 
        FROM non_target_claims nt
        WHERE nt.MemberID = t1.MemberID
        AND nt.non_target_start <= LEAST(t1.StartDate, t2.StartDate)
        AND nt.non_target_end >= GREATEST(t1.EndDate, t2.EndDate)
    ) THEN 1 ELSE 0 END AS covered_flag
FROM table1 t1
JOIN table1 t2 
    ON t1.MemberID = t2.MemberID
    AND t1.StartDate < t2.StartDate; -- 避免重复配对

-- 步骤3：生成最终分组标记
CREATE OR REPLACE TEMP TABLE final_grouped_claims AS
SELECT 
    t.*,
    CASE WHEN EXISTS (
        SELECT 1 
        FROM paired_claims p
        WHERE p.MemberID = t.MemberID
        AND (p.claim1_start = t.StartDate AND p.claim1_end = t.EndDate OR p.claim2_start = t.StartDate AND p.claim2_end = t.EndDate)
        AND (p.overlap_flag = 1 OR p.covered_flag = 1)
    ) THEN 1 ELSE 0 END AS group_flag
FROM table1 t;

-- 查询结果
SELECT * FROM final_grouped_claims;

结果验证 #

运行后结果符合预期：

• MemberID=1的两条记录：无重叠且无覆盖，group_flag=0
• MemberID=2的两条记录：无重叠且无覆盖，group_flag=0
• MemberID=3的两条记录：被非目标记录（11/1-11/15）覆盖，group_flag=1

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