多列匹配关联两表拉取站点适配规模的SQL查询实现问题

• 现有SQL可基于单周吞吐量计算站点临时规模，全年适配的站点规模需参考16、26、39、43共4个指定周的吞吐量组合判定
• 所有吞吐量组合对应的站点规模规则存储在独立的station_sizes表中，4个指定周的吞吐量标识全部匹配即可得到唯一station_size
• 强制规则：AR字段为“Yes”的站点规模最低为ds standard，仅可向上适配更大规模，不能小于该等级

1. 先修正原查询CASE语句的判断顺序，将AR字段的判断优先级前置，避免低吞吐量场景下AR规则失效
2. 按站点维度聚合4个指定周的station_ref标识，行转列为4个独立字段用于关联规则表
3. 关联station_sizes表匹配得到对应站点规模
4. 最后做AR规则兜底校验，确保AR为“Yes”的站点规模不会低于ds standard

WITH weekly_station_ref AS (
    -- 第一步：计算每个站点四个指定周的临时规模，同时保留站点AR标记
    SELECT 
        station,   
        DATE_PART("week",del_date) as week_num,
        DATE_PART("year",del_date) as year,
        sum(volume)/7 as volume_ref,
        MAX(ar) as ar_flag, -- 提取站点AR标记
        CASE 
            -- 优先级1：先判断AR规则，满足则最低为ds standard，吞吐量更高则按实际匹配
            WHEN MAX(ar) = 'YES' AND sum(volume)/7 < 36000 THEN 'ds standard'
            WHEN sum(volume)/7 between 0 and 20000 THEN 'ds x-small' 
            WHEN sum(volume)/7 between 20000 and 36000 THEN 'ds small' 
            WHEN sum(volume)/7 between 36000 and 42000 THEN 'ds standard' 
            WHEN sum(volume)/7 between 42000 and 72000 THEN 'ds large' 
            WHEN sum(volume)/7 > 72000 THEN 'ds x-large' 
            ELSE 'ds small' 
        END as station_ref
    FROM prophecy_na.na_topology_lrp 
    RIGHT JOIN wbr_global.raw_station_extended_attribute 
        ON prophecy_na.na_topology_lrp.station = wbr_global.raw_station_extended_attribute.ds 
    WHERE DATE_PART("week",del_date) in (16,26,39,43)
    GROUP BY station, del_date, year
),
station_4weeks_agg AS (
    -- 第二步：按站点+年度聚合，把四个周的规模标识行转列，用于关联规则表
    SELECT 
        station,
        year,
        MAX(ar_flag) as ar_flag,
        MAX(CASE WHEN week_num = 16 THEN station_ref END) as week16_ref,
        MAX(CASE WHEN week_num = 26 THEN station_ref END) as week26_ref,
        MAX(CASE WHEN week_num = 39 THEN station_ref END) as week39_ref,
        MAX(CASE WHEN week_num = 43 THEN station_ref END) as week43_ref
    FROM weekly_station_ref
    GROUP BY station, year
),
matched_station_size AS (
    -- 第三步：关联站点规模规则表，得到匹配的全年规模
    SELECT 
        a.*,
        b.station_size as raw_station_size
    FROM station_4weeks_agg a
    -- 此处关联条件可根据station_sizes表实际字段调整，示例默认规则表四个匹配字段为w16_ref、w26_ref、w39_ref、w43_ref
    LEFT JOIN station_sizes b
        ON a.week16_ref = b.w16_ref
        AND a.week26_ref = b.w26_ref
        AND a.week39_ref = b.w39_ref
        AND a.week43_ref = b.w43_ref
)
-- 第四步：AR规则兜底校验，输出最终结果
SELECT 
    station,
    year,
    week16_ref,
    week26_ref,
    week39_ref,
    week43_ref,
    -- AR为Yes时如果匹配到的规模小于ds standard则替换，否则保留匹配结果
    CASE 
        WHEN ar_flag = 'YES' AND raw_station_size IN ('ds x-small','ds small') THEN 'ds standard'
        ELSE raw_station_size 
    END AS final_station_size
FROM matched_station_size;

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