求有效SQL查询：识别满足特定用药理赔条件的ID（Databricks）

假设你的理赔记录表名为claims，数据如下：

以下两种查询分别满足仅输出ID、输出所有符合条件的理赔记录的需求：

仅输出符合条件的ID #

WITH valid_drug_per_id AS (
    SELECT 
        ID,
        药物名称
    FROM claims
    GROUP BY ID, 药物名称
    HAVING COUNT(DISTINCT 服务日期) >= 2
)
SELECT ID
FROM valid_drug_per_id
GROUP BY ID
HAVING COUNT(DISTINCT 药物名称) >= 2;

输出符合条件的所有理赔记录（排除ccc这类不符合的药物行） #

WITH valid_drug_per_id AS (
    SELECT 
        ID,
        药物名称
    FROM claims
    GROUP BY ID, 药物名称
    HAVING COUNT(DISTINCT 服务日期) >= 2
),
valid_ids AS (
    SELECT ID
    FROM valid_drug_per_id
    GROUP BY ID
    HAVING COUNT(DISTINCT 药物名称) >= 2
)
SELECT c.*
FROM claims c
JOIN valid_drug_per_id vd ON c.ID = vd.ID AND c.药物名称 = vd.药物名称
JOIN valid_ids vi ON c.ID = vi.ID;

1. valid_drug_per_id 临时表：按ID和药物分组，过滤掉服务日期不足2个的药物（比如ID111的ccc、ID222的eee）。
2. 后续分组统计：对筛选后的有效药物按ID分组，只保留拥有≥2种有效药物的ID。
3. 若需输出具体理赔记录，通过关联原表和筛选结果，确保仅保留符合条件的ID与药物对应的行。

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