Teradata按会员重复诊断标记汇总赔付金额问题求助

问题核心 #

需要实现：会员每拥有一项诊断，其对应赔付金额就重复计算一次（如同时患3种病，40000赔付额计为120000）。原SQL使用Coalesce+CASE仅返回会员的首个匹配诊断，导致每条理赔只关联一个诊断，无法达到重复计算的要求。

修正思路 #

将会员的多诊断列拆分为多行记录：对每个诊断字段单独生成一行，而非仅取第一个匹配项。这样每条理赔记录会根据会员的诊断数量生成对应行数，汇总时自然实现赔付金额按诊断重复计算。

修正后的SQL #

SELECT 
    DX_FLAG
    ,SUM(AMT_PAID) AS PHARM_PAID_AMT
    ,COUNT(DISTINCT MEMBER_AMISYS_NBR) AS MEMBER_COUNT 
FROM (
    -- 拆分哮喘诊断记录
    SELECT 
        st.MEMBER_AMISYS_NBR 
        ,ph.PHARMACY_CLAIM_CK 
        ,ph.AMT_PAID 
        ,FILL.DATE_DATE AS Fill_Date 
        ,'Asthma' AS DX_FLAG
    FROM STATE_OVERALL_MBRS st
    JOIN FT_PHARMACY_CLAIM ph 
        ON st.MEMBER_CURR_CK = ph.PRESCRIBER_MEMBER_CURR_CK 
        AND ph.DELETED_IND = 'N'                        
    JOIN DIM_DATE FILL 
        ON ph.FILL_DATE_DIM_CK = FILL.DATE_DIM_CK
    WHERE FILL.DATE_DATE BETWEEN '2021-10-01' AND '2022-09-30'  
        AND ph.PLAN_DIM_CK = 10  
        AND ph.REVERSAL_IND = 'N' 
        AND ph.AMT_PAID > 0
        AND st.DX_ASTHMA = 'ASTHMA'
    
    UNION ALL
    
    -- 拆分COPD诊断记录
    SELECT 
        st.MEMBER_AMISYS_NBR 
        ,ph.PHARMACY_CLAIM_CK 
        ,ph.AMT_PAID 
        ,FILL.DATE_DATE AS Fill_Date 
        ,'COPD' AS DX_FLAG
    FROM STATE_OVERALL_MBRS st
    JOIN FT_PHARMACY_CLAIM ph 
        ON st.MEMBER_CURR_CK = ph.PRESCRIBER_MEMBER_CURR_CK 
        AND ph.DELETED_IND = 'N'                        
    JOIN DIM_DATE FILL 
        ON ph.FILL_DATE_DIM_CK = FILL.DATE_DIM_CK
    WHERE FILL.DATE_DATE BETWEEN '2021-10-01' AND '2022-09-30'  
        AND ph.PLAN_DIM_CK = 10  
        AND ph.REVERSAL_IND = 'N' 
        AND ph.AMT_PAID > 0
        AND st.DX_COPD = 'COPD'
    
    UNION ALL
    
    -- 拆分糖尿病诊断记录
    SELECT 
        st.MEMBER_AMISYS_NBR 
        ,ph.PHARMACY_CLAIM_CK 
        ,ph.AMT_PAID 
        ,FILL.DATE_DATE AS Fill_Date 
        ,'DIABETES' AS DX_FLAG
    FROM STATE_OVERALL_MBRS st
    JOIN FT_PHARMACY_CLAIM ph 
        ON st.MEMBER_CURR_CK = ph.PRESCRIBER_MEMBER_CURR_CK 
        AND ph.DELETED_IND = 'N'                        
    JOIN DIM_DATE FILL 
        ON ph.FILL_DATE_DIM_CK = FILL.DATE_DIM_CK
    WHERE FILL.DATE_DATE BETWEEN '2021-10-01' AND '2022-09-30'  
        AND ph.PLAN_DIM_CK = 10  
        AND ph.REVERSAL_IND = 'N' 
        AND ph.AMT_PAID > 0
        AND st.DX_DIABETES = 'DIABETES'
    
    UNION ALL
    
    -- 拆分心力衰竭诊断记录
    SELECT 
        st.MEMBER_AMISYS_NBR 
        ,ph.PHARMACY_CLAIM_CK 
        ,ph.AMT_PAID 
        ,FILL.DATE_DATE AS Fill_Date 
        ,'HEART_FAILURE' AS DX_FLAG
    FROM STATE_OVERALL_MBRS st
    JOIN FT_PHARMACY_CLAIM ph 
        ON st.MEMBER_CURR_CK = ph.PRESCRIBER_MEMBER_CURR_CK 
        AND ph.DELETED_IND = 'N'                        
    JOIN DIM_DATE FILL 
        ON ph.FILL_DATE_DIM_CK = FILL.DATE_DIM_CK
    WHERE FILL.DATE_DATE BETWEEN '2021-10-01' AND '2022-09-30'  
        AND ph.PLAN_DIM_CK = 10  
        AND ph.REVERSAL_IND = 'N' 
        AND ph.AMT_PAID > 0
        AND st.DX_HEART_FAILURE = 'HEART FAILURE'
    
    UNION ALL
    
    -- 拆分高血压诊断记录
    SELECT 
        st.MEMBER_AMISYS_NBR 
        ,ph.PHARMACY_CLAIM_CK 
        ,ph.AMT_PAID 
        ,FILL.DATE_DATE AS Fill_Date 
        ,'HYPERTENSION' AS DX_FLAG
    FROM STATE_OVERALL_MBRS st
    JOIN FT_PHARMACY_CLAIM ph 
        ON st.MEMBER_CURR_CK = ph.PRESCRIBER_MEMBER_CURR_CK 
        AND ph.DELETED_IND = 'N'                        
    JOIN DIM_DATE FILL 
        ON ph.FILL_DATE_DIM_CK = FILL.DATE_DIM_CK
    WHERE FILL.DATE_DATE BETWEEN '2021-10-01' AND '2022-09-30'  
        AND ph.PLAN_DIM_CK = 10  
        AND ph.REVERSAL_IND = 'N' 
        AND ph.AMT_PAID > 0
        AND st.DX_HYPERTENSION = 'HYPERTENSION'
) rx
GROUP BY DX_FLAG;

关键说明 #

1. 拆分诊断为多行：通过UNION ALL对每个诊断单独查询，仅保留对应诊断为真的会员记录，这样一个有3种诊断的会员，其每条理赔会生成3行不同诊断的记录，汇总时赔付金额会被计算3次。
2. 会员数统计：COUNT(DISTINCT MEMBER_AMISYS_NBR)依然适用，它统计的是每个诊断分组下的独特会员数量，不会因同一会员多次出现而重复计数，符合业务统计需求。
3. 性能优化提示：如果会员表和理赔表数据量较大，可以先筛选出符合时间、计划维度的核心记录，再进行UNION ALL拆分，减少中间数据量。

原问题背景 #

需求说明

需在Teradata中按会员的重复诊断（DX）标记汇总赔付金额，即会员每拥有一项诊断，其赔付金额就对应重复计算一次（例如会员A同时患有COPD、ASTHMA、DIABETES，赔付金额40000需计为120000）。

原SQL代码

SELECT 
    DX_FLAG
    ,Sum( AMT_PAID) AS PHARM_PAID_AMT
    ,Count(DISTINCT(MEMBER_AMISYS_NBR)) AS MEMBER_COUNT 

FROM 
(SELECT 
        st.MEMBER_AMISYS_NBR 
        ,ph.PHARMACY_CLAIM_CK 
        ,ph.AMT_PAID 
            ,FILL.DATE_DATE AS Fill_Date 
        ,Coalesce(CASE WHEN DX_ASTHMA = 'ASTHMA' THEN 'Asthma' END,
        CASE WHEN DX_COPD = 'COPD' THEN 'COPD' END,
        CASE WHEN DX_DIABETES = 'DIABETES' THEN 'DIABETES' END,
        CASE WHEN DX_HEART_FAILURE = 'HEART FAILURE' THEN 'HEART_FAILURE' END,
        CASE WHEN DX_HYPERTENSION = 'HYPERTENSION' THEN 'HYPERTENSION' END)
        AS DX_FLAG
 FROM
  STATE_OVERALL_MBRS st
           JOIN FT_PHARMACY_CLAIM ph ON st.MEMBER_CURR_CK = ph.PRESCRIBER_MEMBER_CURR_CK AND ph.DELETED_IND = 'N'                        
           JOIN DIM_DATE FILL ON ph.FILL_DATE_DIM_CK = FILL.DATE_DIM_CK
 WHERE FILL.DATE_DATE BETWEEN '2021-10-01' AND '2022-09-30'  
          AND ph.PLAN_DIM_CK =10  
      AND  ph.REVERSAL_IND = 'N' 
      AND ph.AMT_PAID > 0
) rx  
GROUP BY DX_FLAG; -- 补充原SQL缺失的GROUP BY语句

当前输出结果

会员表（STATE_OVERALL_MBRS）结构

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