SQL获取无clinic_1=clinic_2的individual-clinic-month数据方法

问题背景 #

现有如下数据集：

individual  |  clinic_1   | clinic_2    | month      | address_recorded | address_code
1           |  A          | B           | 01-01-2016 | 01-02-1999       | C01
1           |  A          | A           | 01-01-2016 | 01-02-2003       | C02
1           |  A          | A           | 01-01-2016 | 01-02-2001       | C06
1           |  A          | X           | 01-01-2016 | 01-02-2000       | C03
2           |  C          | B           | 01-04-2016 | 01-02-1999       | D04
2           |  C          | A           | 01-04-2016 | 01-02-2001       | D05
2           |  C          | X           | 01-04-2016 | 01-02-2000       | D06

期望得到如下查询结果：

individual  |  clinic_1   | month      | address_code
1           |  A          | 01-01-2016 | C02
2           |  C          | 01-04-2016 | D05

筛选规则 #

• 对于存在clinic_1 = clinic_2记录的唯一individual-clinic_1-month组合，选取clinic_1匹配范围内address_recorded（地址登记日期）最新的记录对应的address_code
• 对于不存在任何clinic_1 = clinic_2记录的唯一individual-clinic_1-month组合，选取全诊所范围内address_recorded最新的记录对应的address_code

现有实现问题 #

当前编写的SQL仅能覆盖存在clinic_1=clinic_2的组合，逻辑如下：

with cte_1
as
(
select * from table
where clinic_1 = clinic_2
)
,cte_2
as
(
select row_number () over (Partition by clinic_1, individual, month order by clinic_1, individual, month, address_recorded desc) as number, *
from cte_1
)
select individual, clinic_1, month, address_code from cte_2 where number = 1

需要补充逻辑，覆盖不存在clinic_1=clinic_2实例的分组场景。

实现方案 #

核心思路是先标记每个分组是否存在clinic_1 = clinic_2的记录，再根据标记选择对应排序范围取最新记录：

WITH group_flag AS (
    -- 标记每个唯一分组是否存在clinic_1=clinic_2的记录
    SELECT 
        *,
        MAX(CASE WHEN clinic_1 = clinic_2 THEN 1 ELSE 0 END) 
            OVER (PARTITION BY individual, clinic_1, month) AS has_self_clinic_match
    FROM your_table
),
ranked_records AS (
    SELECT 
        individual,
        clinic_1,
        month,
        address_code,
        ROW_NUMBER() OVER (
            PARTITION BY individual, clinic_1, month 
            ORDER BY 
                -- 存在匹配时优先排clinic_1=clinic_2的记录，不存在时全量记录参与排序
                CASE WHEN has_self_clinic_match = 1 AND clinic_1 != clinic_2 THEN 1 ELSE 0 END ASC,
                address_recorded DESC
        ) AS rn
    FROM group_flag
)
SELECT individual, clinic_1, month, address_code
FROM ranked_records
WHERE rn = 1

逻辑说明：

• 第一个CTE给每个分组打标记，判断组内是否有同一诊所的就诊记录
• 第二个CTE排序时，对有匹配标记的分组，先把clinic_1 != clinic_2的记录排到后面，只在同诊所记录里按登记日期倒序取最新；对无匹配标记的分组，所有记录权重一致，直接取全组最新日期的记录
• 最终取每个分组排序后序号为1的记录即可得到符合规则的结果

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