Snowflake中按8周周期分组聚合52周日期的SQL求助

针对你按8周聚合周日起始周数据的需求，可以通过以下步骤实现：

核心思路 #

先将每个日期映射到其所在周的周日起始日，再基于这些起始日计算分组编号，最后按分组聚合数据。

具体SQL示例 #

假设你的表名为weekly_data，包含日期列date_col和需要聚合的数值列（比如sales）：

WITH weekly_start AS (
    -- 第一步：获取每个日期对应的周日起始周，同时找到整个数据集的最早周日起始日
    SELECT
        date_col,
        DATE_TRUNC('week', date_col, 'sunday') AS week_start, -- 得到该日期所在周的周日
        MIN(DATE_TRUNC('week', date_col, 'sunday')) OVER () AS min_week_start -- 数据集最早的周日起始日
    FROM weekly_data
),
grouped_weeks AS (
    -- 第二步：计算每个周属于哪个8周组
    SELECT
        date_col,
        week_start,
        FLOOR(DATEDIFF('week', min_week_start, week_start) / 8) AS eight_week_group_id,
        -- 可选：生成分组的起始和结束日期，方便查看
        DATEADD('week', eight_week_group_id * 8, min_week_start) AS group_start_date,
        DATEADD('week', (eight_week_group_id + 1) * 8 - 1, min_week_start) AS group_end_date
    FROM weekly_start
)
-- 第三步：按8周组聚合数据
SELECT
    eight_week_group_id,
    group_start_date,
    group_end_date,
    SUM(sales) AS total_sales -- 替换成你需要的聚合函数
FROM grouped_weeks
GROUP BY eight_week_group_id, group_start_date, group_end_date
ORDER BY eight_week_group_id;

关键函数说明 #

• DATE_TRUNC('week', date_col, 'sunday')：Snowflake支持指定周起始日为周日，这个函数会把任意日期转成它所在周的周日。
• DATEDIFF('week', min_week_start, week_start)：计算当前周起始日和最早周起始日之间的周数差。
• FLOOR(差 / 8)：将周数差除以8后取整，得到唯一的8周组编号，确保连续8周归为同一组。

自定义调整 #

如果不想从数据集最早周开始分组，而是固定从某个特定周日（比如'2024-01-07'）开始，可以把MIN(...) OVER ()替换成固定日期：

-- 替换这一行
DATE '2024-01-07' AS min_week_start

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