如何基于多周查询结果搭配CASE语句分配站点年度规模

核心逻辑 #

把你现有的周级查询作为内层结果集，按站点聚合后匹配自定义映射规则即可，具体修改如下：

最终SQL代码 #

WITH week_station_ref AS (
-- 内层保留你原有的周维度查询逻辑
SELECT 
    station,
    ISNULL(ar, '0') AS dsar,
    del_date,
    SUM(volume) / 7 AS volume_ref,
    DATE_PART("week", del_date) AS week_num,
    DATE_PART("year", del_date) AS year,
    CASE 
        WHEN volume_ref BETWEEN 0 AND 20000 AND dsar <> 'YES' 
            THEN 'ds x-small'
        WHEN volume_ref BETWEEN 20000 AND 36000 AND dsar <> 'YES' 
            THEN 'ds small'
        WHEN volume_ref BETWEEN 36000 AND 42000 AND dsar <> 'YES'    
            THEN 'ds standard'
        WHEN volume_ref BETWEEN 42000 AND 72000 AND dsar <> 'YES' 
            THEN 'ds large'
        WHEN volume_ref > 72000 
            THEN 'ds x-large'
        WHEN dsar = 'YES' 
            THEN 'ds x-large'
        ELSE 'ds small' 
    END AS station_ref
FROM 
    prophecy_na.na_topology_lrp
LEFT JOIN 
    wbr_global.raw_station_extended_attribute ON prophecy_na.na_topology_lrp.station = wbr_global.raw_station_extended_attribute.ds 
WHERE
    week_num IN (16, 20, 40, 48)
GROUP BY 
    na_topology_lrp.station, raw_station_extended_attribute.ar,
    na_topology_lrp.del_date
)
-- 外层按站点聚合，匹配年度规模映射规则
SELECT
    station,
    year,
    -- 这里写你300条自定义映射规则即可，用CASE匹配四周的station_ref组合
    CASE
        -- 示例规则，对应你举的例子
        WHEN ARRAY_AGG(station_ref ORDER BY week_num) = ARRAY['ds small','sa small','ds small','ds standard'] THEN 'ds small'
        -- 剩下的299条规则依次往下写即可
        -- 兜底规则
        ELSE 'ds small'
    END AS annual_station_ref
FROM week_station_ref
GROUP BY station, year;

说明 #

• 如果你用的数据库不支持ARRAY_AGG，可以替换成STRING_AGG，把四周结果拼成字符串再匹配，比如判断条件写为STRING_AGG(station_ref, ',' ORDER BY week_num) = 'ds small,sa small,ds small,ds standard'即可
• 映射规则只需要按照你实际的业务逻辑，把四周station_ref的组合和对应的年度规模一一对应写进CASE分支即可

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