如何在Snowflake中实现按月回溯的循环查询？

在Snowflake中，更高效的处理方式是通过生成月份维度序列批量完成逐月统计，而非传统循环语句，以下是具体实现方案：

1. 生成目标月份的动态日期范围 #

先构造2022年1月至10月的月份序列，同时自动计算每个月份对应的：

• aa.datum的完整当月区间（自动适配28/29/30/31天）
• bb.datum_objednani的起始日期（当月往前推3个月的月初）

WITH month_ranges AS (
    SELECT
        DATE_TRUNC('MONTH', month_date) AS aa_month_start,
        LAST_DAY(month_date) AS aa_month_end,
        DATEADD('MONTH', -3, DATE_TRUNC('MONTH', month_date)) AS bb_start_date,
        TO_CHAR(month_date, 'YYYY-MM') AS stats_month
    FROM (
        -- 生成2022-01到2022-10的10个月份，ROWCOUNT对应月份数量
        SELECT DATEADD('MONTH', seq4(), '2022-01-01') AS month_date
        FROM TABLE(GENERATOR(ROWCOUNT => 10))
        -- 按倒序排列，实现从10月回溯到1月的顺序
        ORDER BY month_date DESC
    )
)

2. 关联原统计逻辑完成逐月计算 #

将生成的月份范围与你的业务表关联，替换固定日期条件为动态区间：

WITH month_ranges AS (
    SELECT
        DATE_TRUNC('MONTH', month_date) AS aa_month_start,
        LAST_DAY(month_date) AS aa_month_end,
        DATEADD('MONTH', -3, DATE_TRUNC('MONTH', month_date)) AS bb_start_date,
        TO_CHAR(month_date, 'YYYY-MM') AS stats_month
    FROM (
        SELECT DATEADD('MONTH', seq4(), '2022-01-01') AS month_date
        FROM TABLE(GENERATOR(ROWCOUNT => 10))
        ORDER BY month_date DESC
    )
)
SELECT
    mr.stats_month,
    -- 替换为你原查询的统计字段，比如COUNT、SUM等
    COUNT(DISTINCT aa.id) AS aa_record_count,
    SUM(bb.amount) AS bb_total_amount
FROM month_ranges mr
-- 关联表aa，过滤当月数据
LEFT JOIN your_table_aa aa 
    ON aa.datum BETWEEN mr.aa_month_start AND mr.aa_month_end
-- 关联表bb，过滤从提前3个月开始到当月结束的数据
LEFT JOIN your_table_bb bb 
    ON bb.datum_objednani BETWEEN mr.bb_start_date AND mr.aa_month_end
    -- 补充aa与bb的其他关联条件（如果有）
    AND aa.relation_id = bb.relation_id
-- 按月份分组统计
GROUP BY mr.stats_month
ORDER BY mr.stats_month DESC;

关键细节说明 #

• 调整GENERATOR(ROWCOUNT => 10)的数值可快速修改统计的月份数量，修改起始日期可调整统计区间
• LAST_DAY函数自动适配不同月份的天数，无需手动判断月末日期
• 若bb.datum_objednani的结束日期不是当月月末，可直接修改bb的过滤条件（比如改为bb.datum_objednani < mr.aa_month_start）

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