如何在Snowflake中使用SQL基于邮编起止范围生成行数据

需求说明 #

仅使用SQL，无需存储过程，将包含卡号、起始邮编、结束邮编的数据集，展开为每个卡号对应起止范围内的所有邮编行。

样本数据 #

解决方案SQL #

利用Snowflake内置的GENERATOR()函数生成序列行，结合LATERAL JOIN为每个邮编区间生成对应行数的邮编值：

SELECT 
    t.card_number,
    t.start_zip + seq4() AS 邮编
FROM 
    your_table_name t,
    LATERAL (
        SELECT seq4() 
        FROM TABLE(GENERATOR(ROWCOUNT => 10000)) -- 调整ROWCOUNT值以覆盖最大邮编区间长度
        WHERE seq4() <= (t.end_zip - t.start_zip)
    ) s
ORDER BY 
    t.card_number, 邮编;

代码说明 #

• GENERATOR(ROWCOUNT => 10000)：生成指定数量的空行，这里设置10000足以覆盖绝大多数邮编区间（若有更大区间可按需调整）
• seq4()：生成从0开始的连续整数序列，与起始邮编相加后得到区间内的每个邮编
• WHERE seq4() <= (t.end_zip - t.start_zip)：限制序列行数不超过邮编区间的长度，避免生成多余行
• LATERAL JOIN：让主表的每一行都关联对应数量的序列行，实现区间展开

测试示例（含临时表创建） #

如果需要验证效果，可创建临时表并插入样本数据：

-- 创建临时样本表
CREATE OR REPLACE TEMP TABLE zip_range_sample (
    card_number VARCHAR(20),
    start_zip INT,
    end_zip INT
);

-- 插入样本数据
INSERT INTO zip_range_sample VALUES
('786****', 97000, 97003),
('787****', 98000, 98003);

-- 执行展开查询
SELECT 
    t.card_number,
    t.start_zip + seq4() AS 邮编
FROM 
    zip_range_sample t,
    LATERAL (
        SELECT seq4() 
        FROM TABLE(GENERATOR(ROWCOUNT => 10000))
        WHERE seq4() <= (t.end_zip - t.start_zip)
    ) s
ORDER BY 
    t.card_number, 邮编;

预期输出 #

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