如何在COUNT() OVER(PARTITION)中结合WHERE子句实现条件统计

原查询的核心问题是：窗口函数后直接追加WHERE条件的语法不合法，窗口函数无法通过这种方式筛选数据，必须通过条件表达式实现带筛选的计数逻辑。以下是两种正确的实现方案：

方案1：保留原表全量记录（使用窗口函数） #

如果需要保留Table1中的每一条记录，同时在每条记录后附加对应NPI的统计数据，使用带条件的窗口函数：

create or replace table Table2 as
select 
    NPI, 
    LAST_NAME, 
    FIRST_NAME, 
    ADDRESS, 
    City, 
    State, 
    Zip, 
    SPECIALTY_DESCRIPTION,
    COUNT(DISTINCT CLAIM_ID) OVER (PARTITION BY NPI) AS TOTAL_CLAIMS,
    COUNT(DISTINCT CASE WHEN APPROVAL_FLAG = 'APPROVED' THEN CLAIM_ID END) OVER (PARTITION BY NPI) AS APPROVED_CLAIMS,
    COUNT(DISTINCT CASE WHEN APPROVAL_FLAG <> 'APPROVED' THEN CLAIM_ID END) OVER (PARTITION BY NPI) AS REJECTED_CLAIMS
from Table1;

方案2：按NPI分组聚合（更高效的统计方式） #

如果每个NPI对应的个人信息（姓名、地址等）是唯一的，推荐用分组聚合直接生成每个NPI一行的统计结果，性能更优：

create or replace table Table2 as
select 
    NPI, 
    LAST_NAME, 
    FIRST_NAME, 
    ADDRESS, 
    City, 
    State, 
    Zip, 
    SPECIALTY_DESCRIPTION,
    COUNT(DISTINCT CLAIM_ID) AS TOTAL_CLAIMS,
    COUNT(DISTINCT CASE WHEN APPROVAL_FLAG = 'APPROVED' THEN CLAIM_ID END) AS APPROVED_CLAIMS,
    COUNT(DISTINCT CASE WHEN APPROVAL_FLAG <> 'APPROVED' THEN CLAIM_ID END) AS REJECTED_CLAIMS
from Table1
group by NPI, LAST_NAME, FIRST_NAME, ADDRESS, City, State, Zip, SPECIALTY_DESCRIPTION;

关键说明 #

• CASE WHEN条件不满足时会返回NULL，而COUNT函数会自动忽略NULL值，因此能精准统计符合筛选规则的去重索赔ID数量。
• 如果业务中需要排除APPROVAL_FLAG为NULL的情况，可将拒赔的条件调整为CASE WHEN APPROVAL_FLAG IS NOT NULL AND APPROVAL_FLAG <> 'APPROVED' THEN CLAIM_ID END。

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