SQL/Snowflake无需硬编码实现非运算统计关联表行数

• 关联DRUG、REACTION两张表，关联字段为PRIMARYID，需实现统计逻辑：指定PT值（示例为Nausea）时，返回每个药品名对应的「匹配目标PT、且药品名不等于当前统计药品的记录数」，例如结果PREDNISONE | 20代表共有20条Nausea相关关联记录的药品不是PREDNISONE。
• 原有Snowflake代码执行后返回0行，根因是where条件中DRUGNAME != DRUGNAME为逐行同字段自比较，逻辑恒为假，未实现按当前分组药品排除的效果；且表中药品超4000种，无法硬编码枚举所有药品取值。

原有错误代码如下：

select distinct DRUGNAME, count(PT) as c from DRUG join REACTION
on"DRUG".PRIMARYID = "REACTION".PRIMARYID
where PT ='Nausea' and DRUGNAME != DRUGNAME
group by DRUGNAME

核心思路为先计算目标PT下的全量统计值，再按药品维度分组计数，用全量值减去单药品计数得到排除当前药品后的结果，全程不需要枚举药品名。

场景1：按关联记录维度统计（匹配示例需求） #

即统计关联后满足PT条件、行中药品名不等于当前统计药品的总行数，和示例描述的返回逻辑一致：

WITH base_data AS (
    -- 先筛选出PT匹配目标值的所有关联记录
    SELECT
        d.DRUGNAME
    FROM DRUG d
    INNER JOIN REACTION r
        ON d.PRIMARYID = r.PRIMARYID
    WHERE r.PT = 'Nausea' -- 可替换为其他目标PT值
),
total_cnt AS (
    -- 计算目标PT下的总关联记录数
    SELECT COUNT(*) AS total_count FROM base_data
),
drug_cnt AS (
    -- 计算每个药品在目标PT记录中的行数
    SELECT DRUGNAME, COUNT(*) AS drug_count
    FROM base_data
    GROUP BY DRUGNAME
)
-- 总记录数减去当前药品的记录数，即为排除当前药品后的结果
SELECT
    dc.DRUGNAME,
    tc.total_count - dc.drug_count AS exclude_current_drug_count
FROM drug_cnt dc
CROSS JOIN total_cnt tc
ORDER BY exclude_current_drug_count DESC;

场景2：按原始病例维度统计 #

如果需要统计的是不包含当前药品、且存在目标PT的去重原始病例报告数（即同一个PRIMARYID算1个病例，只要病例中用过当前统计药品，就计入排除范围），可以用以下代码：

WITH base_case AS (
    -- 去重拿到目标PT下所有病例-药品对应关系
    SELECT DISTINCT
        d.DRUGNAME,
        r.PRIMARYID
    FROM DRUG d
    INNER JOIN REACTION r
        ON d.PRIMARYID = r.PRIMARYID
    WHERE r.PT = 'Nausea' -- 可替换为其他目标PT值
),
total_case AS (
    -- 计算目标PT下的总去重病例数
    SELECT COUNT(DISTINCT PRIMARYID) AS total_case_count FROM base_case
),
drug_case AS (
    -- 计算每个药品涉及的目标PT病例数
    SELECT DRUGNAME, COUNT(DISTINCT PRIMARYID) AS drug_case_count
    FROM base_case
    GROUP BY DRUGNAME
)
SELECT
    dc.DRUGNAME,
    tc.total_case_count - dc.drug_case_count AS exclude_current_drug_case_count
FROM drug_case dc
CROSS JOIN total_case tc
ORDER BY exclude_current_drug_case_count DESC;

• 方案通过CTE预先拆分基础数据、全量统计值、单药品统计值三层逻辑，避免了低效自连接，在Snowflake中执行性能更优。
• 不需要手动枚举任何药品名称，表中药品增减时统计结果会自动适配。

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