Databricks SQL筛选zip5：每个facility_type仅出现1次及以下的实现

需要调整Databricks SQL代码，使输出仅保留每个zip5下各facility_type（Hospital、ASC、Other、null）出现次数≤1的记录。例如zip5 10003、10025、10029需保留，10016、10021需排除。之前尝试的HAVING语句存在漏洞（会让10016这类不符合要求的记录混入），求正确实现方式，是否必须使用HAVING子句？

原SQL代码 #

原查询语句：

SELECT a.zip5, a.org_id, ok.facility_type 
FROM sales_table a 
LEFT JOIN (SELECT ok.org_id, 
                  CASE WHEN cot.COT_DESC IN ('Outpatient') THEN 'ASC'
                       WHEN cot.cot_desc IN ('Hospital') THEN cot.cot_desc 
                       ELSE 'Other' 
                       END AS facility_type
           FROM ref_table1 ok
           LEFT JOIN ref_table2 cot ON ok.ID = cot.ID) ok ON a.org_id = ok.org_id 
GROUP BY a.zip5, a.org_id, ok.facility_type 

尝试过的错误HAVING条件：

HAVING (COUNT(DISTINCT ok.facility_type) <= 1 and count(distinct a.org_id) <=1)

原始输出表格 #

你的核心需求是确保单个zip5下，同一种facility_type的出现次数不超过1次。原HAVING语句失效的原因是：原查询的分组粒度是zip5+org_id+facility_type，这个维度下的统计无法反映整个zip5范围内各类型的出现次数。

正确的做法是先统计每个zip5下各类型的出现次数，筛选出符合条件的zip5，再关联回原始数据。HAVING子句会在筛选zip5的步骤中用到：

WITH facility_type_counts AS (
    -- 统计每个zip5下每种facility_type的出现次数
    SELECT
        a.zip5,
        ok.facility_type,
        COUNT(*) AS occurrence
    FROM sales_table a
    LEFT JOIN (
        SELECT
            ok.org_id,
            CASE
                WHEN cot.COT_DESC IN ('Outpatient') THEN 'ASC'
                WHEN cot.cot_desc IN ('Hospital') THEN cot.cot_desc
                ELSE 'Other'
            END AS facility_type
        FROM ref_table1 ok
        LEFT JOIN ref_table2 cot ON ok.ID = cot.ID
    ) ok ON a.org_id = ok.org_id
    GROUP BY a.zip5, ok.facility_type
),
valid_zip_codes AS (
    -- 筛选所有类型出现次数都≤1的zip5
    SELECT zip5
    FROM facility_type_counts
    GROUP BY zip5
    HAVING MAX(occurrence) <= 1
)
-- 关联回原始数据，只保留符合条件的zip5记录
SELECT
    a.zip5,
    a.org_id,
    ok.facility_type
FROM sales_table a
LEFT JOIN (
    SELECT
        ok.org_id,
        CASE
            WHEN cot.COT_DESC IN ('Outpatient') THEN 'ASC'
            WHEN cot.cot_desc IN ('Hospital') THEN cot.cot_desc
            ELSE 'Other'
        END AS facility_type
    FROM ref_table1 ok
    LEFT JOIN ref_table2 cot ON ok.ID = cot.ID
) ok ON a.org_id = ok.org_id
WHERE a.zip5 IN (SELECT zip5 FROM valid_zip_codes)

代码解释 #

1. facility_type_counts：按zip5和facility_type分组，统计每个zip5下每种类型的出现次数；
2. valid_zip_codes：对每个zip5，检查其所有类型的最大出现次数是否≤1，满足条件的zip5即为有效；
3. 最终查询：用有效zip5列表过滤原始关联数据，得到符合要求的记录。

这种方式能精准过滤掉像10016、10021这类存在重复类型的zip5，完全符合需求。

内容的提问来源于stack exchange，提问作者Dr.Data