每周观测计数SQL脚本问题：无法同步产品与年龄信息

已构建MMWR系列表框架用于按周统计观测值并追踪变化，创建了MMWR、MMWR_CATEGORY、MMWR_Primary表，定义了Current_table输入表及预期结果。但当前SQL脚本仅返回计数，无法同步产品与年龄信息，需修正脚本以匹配预期输出。

相关表创建语句 #

Create table MMWR as
SELECT '202140' as MMWR_WEEK,'10/09/2021' as END_DATE FROM DUAL
UNION ALL (SELECT '202141','10/16/2021' FROM DUAL)
UNION ALL (SELECT '202142','10/23/2021' FROM DUAL)
;

Create table MMWR_CATEGORY as (
SELECT 'Product 1' as Prod,'0-50' as AGE FROM DUAL
UNION ALL SELECT 'Product 1', '51-100' as AGE FROM DUAL
UNION ALL SELECT 'Product 2', '0-50' FROM DUAL
UNION ALL SELECT 'Product 2','51-100' FROM DUAL
UNION ALL SELECT 'Product 3', '0-50' FROM DUAL
UNION ALL SELECT 'Product 3', '51-100' FROM DUAL
);

CREATE TABLE MMWR_Primary as (select * from MMWR, MMWR_Category);

输入表Current_table #

SELECT '0001' as Patient_UUID,'08-OCT-2021' as pr_dt_1,'22-OCT-2021' as pr_dt_2,to_date(NULL) as pr_dt_3,'Product1' as pr_1,'Product2' as pr_2,to_char(NULL) as pr_3,'0-50' as pr_age_1,'0-50' as pr_age_2,to_char(Null) as pr_age_3 FROM DUAL
 UNION ALL SELECT  '0002', '15-OCT-2021','22-OCT-2021', to_date(NULL),'Product1','Product2',to_char(NULL),  '51-100', '51-100', to_char(Null) FROM DUAL
 UNION ALL SELECT  '0003', '15-OCT-2021','22-OCT-2021', to_date(NULL),'Product2','Product3',to_char(NULL),  '0-50', '51-100', to_char(Null) FROM DUAL

注：修正了原输入中的拼写错误：Procuct2改为Product2，0-51改为0-50以匹配预期结果的年龄组定义

预期结果 #

MMWR Week | End Date | Most Recent Product | Age_Group | Use of Product
202140     10/09/2021   Product 1              0-50          1
202140     10/09/2021   Product 1              51-100        0
202140     10/09/2021   Product 2              0-50          0
202140     10/09/2021   Product 2              51-100        0
202140     10/09/2021   Product 3              0-50          0
202140     10/09/2021   Product 3              51-100        0
202141     10/16/2021   Product 1              0-50          1
202141     10/16/2021   Product 1              51-100        1
202141     10/16/2021   Product 2              0-50          1
202141     10/16/2021   Product 2              51-100        0
202141     10/16/2021   Product 3              0-50          0
202141     10/16/2021   Product 3              51-100        0
202142     10/23/2021   Product 1              0-50          0
202142     10/23/2021   Product 1              51-100        0
202142     10/23/2021   Product 2              0-50          0
202142     10/23/2021   Product 2              51-100        1
202142     10/23/2021   Product 3              0-50          1
202142     10/23/2021   Product 3              51-100        1

当前存在问题的脚本 #

Select count(*) 
from Current_table pi 
where (MMWR_Primary.AGE = pi.pr_age_1 and pr_dt_1 <= MMWR_Primary.END_DATE) 
OR (MMWR_Primary.AGE = pi.pr_age_2 and pr_dt_2 <= MMWR_Primary.END_DATE)
OR (MMWR_Primary.AGE = pi.pr_age_3 and pr_dt_3 <= MMWR_Primary.END_DATE) as 'Use of Product'

问题：仅返回单一计数，未关联MMWR周、产品和年龄组信息，无法生成分组统计结果

修正后的SQL脚本 #

SELECT
    mp.MMWR_WEEK AS "MMWR Week",
    mp.END_DATE AS "End Date",
    mp.Prod AS "Most Recent Product",
    mp.AGE AS "Age_Group",
    SUM(CASE 
        WHEN up.pr_dt <= TO_DATE(mp.END_DATE, 'MM/DD/YYYY') THEN 1 
        ELSE 0 
    END) AS "Use of Product"
FROM MMWR_Primary mp
LEFT JOIN (
    -- 将Current_table的多列产品数据逆透视为单行单产品结构
    SELECT 
        Patient_UUID,
        REPLACE(pr_col, 'Product', 'Product ') AS pr_name, -- 统一产品名称格式
        pr_dt,
        pr_age
    FROM Current_table
    UNPIVOT (
        (pr_dt, pr_age) FOR pr_col IN (
            (pr_dt_1, pr_age_1) AS 'Product1',
            (pr_dt_2, pr_age_2) AS 'Product2',
            (pr_dt_3, pr_age_3) AS 'Product3'
        )
    )
    WHERE pr_dt IS NOT NULL -- 过滤空记录
) up ON mp.Prod = up.pr_name AND mp.AGE = up.pr_age
GROUP BY mp.MMWR_WEEK, mp.END_DATE, mp.Prod, mp.AGE
ORDER BY mp.MMWR_WEEK, mp.Prod, mp.AGE;

思路说明 #

1. 逆透视转换：将Current_table中多列的产品、日期、年龄数据转换为单行单产品的结构，简化后续关联逻辑；
2. 左关联匹配：以MMWR_Primary为基础表做左关联，确保所有MMWR周+产品+年龄组的组合都能被统计（包括使用次数为0的情况）；
3. 分组统计：按MMWR周、结束日期、产品、年龄组分组，用CASE语句判断日期是否在当前MMWR周内，统计符合条件的使用次数。

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