如何在Snowflake中将混合格式的DOW_NUM列转为展开的数字列表

思路拆解 #

• 先给原始字符串“卸妆”：砍掉首尾多余的逗号，再拆成一个个独立的星期数/星期范围片段
• 逐个处理片段：带-的范围直接展开成连续数字，单个数字就直接留用
• 最后把所有数字去重、排好队，拼成目标格式的字符串

具体SQL代码 #

WITH cleaned_data AS (
    -- 清洗原始数据：去掉首尾逗号，拆分出各个片段
    SELECT 
        t.*,
        SPLIT(TRIM(t.DOW_NUM, ','), ',') AS dow_fragments
    FROM your_raw_schema.your_table t
),
expanded_fragments AS (
    -- 展开每个片段：范围转连续数字，单个数字直接保留
    SELECT 
        cd.*,
        CASE 
            WHEN frag.value LIKE '%-%' THEN 
                FLATTEN(INPUT => SEQUENCE(SPLIT_PART(frag.value, '-', 1)::INT, SPLIT_PART(frag.value, '-', 2)::INT))
            ELSE 
                FLATTEN(INPUT => ARRAY_CONSTRUCT(frag.value::INT))
        END AS dow_item
    FROM cleaned_data cd,
         LATERAL FLATTEN(INPUT => cd.dow_fragments) frag
)
-- 聚合生成目标列：去重+按顺序拼接
SELECT 
    *,
    LISTAGG(DISTINCT dow_item.value::VARCHAR, ',') WITHIN GROUP (ORDER BY dow_item.value::INT) AS NEW_DOW_NUM
FROM expanded_fragments
GROUP BY ALL;

这个解法的优势 #

• 纯原生SQL实现，不用写自定义函数，适配Snowflake分布式计算的性能特点
• 自动处理各种边界输入：比如首尾全是逗号、单个数字混在范围里、重复的数字
• 逻辑直白易维护，要调整排序或格式直接修改最后一步即可

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