Standard SQL如何实现月度数据按每3个月动态分组统计？

你可以基于现有逻辑扩展，先补全缺失月份再按3个月分组聚合即可，完整修改后的SQL如下：

WITH utils AS (
    SELECT
        today,
        month_lag,
        GENERATE_DATE_ARRAY(DATE_SUB(DATE_TRUNC(DATE_SUB(today, INTERVAL month_lag MONTH), MONTH), INTERVAL 11 MONTH), DATE_TRUNC(DATE_SUB(today, INTERVAL month_lag MONTH), MONTH), INTERVAL 1 MONTH) AS months
    FROM (
        SELECT
            CURRENT_DATE() AS today,
            IF(EXTRACT(DAY FROM CURRENT_DATE()) < 7, 2, 1) AS month_lag
    )
),
-- 原有按月聚合结果
monthly_stats AS (
    SELECT
        date,
        MAX(ndays) AS ndays,
        COUNT(*) AS count
    FROM (
        SELECT
            DATE_TRUNC(date, MONTH) AS date,
            IFNULL(DATE_DIFF(date, prev_date, DAY), 186) AS ndays
        FROM (
            SELECT
                date,
                LAG(date, 1) OVER (ORDER BY date) AS prev_date
            FROM `myproject.mydataset.mytable`, utils
            WHERE
                DATE_TRUNC(date, MONTH) >= DATE_TRUNC(DATE_SUB(today, INTERVAL 17+month_lag MONTH), MONTH)
                AND type = 'Departamento'
        ), utils
        WHERE DATE_TRUNC(date, MONTH) BETWEEN DATE_TRUNC(DATE_SUB(today, INTERVAL 11+month_lag MONTH), MONTH) AND DATE_TRUNC(DATE_SUB(today, INTERVAL month_lag MONTH), MONTH)
    )
    GROUP BY date
),
-- 补全所有月份，关联已有统计数据并生成分组编号
full_months AS (
    SELECT
        m.month AS date,
        IFNULL(s.ndays, 0) AS ndays,
        IFNULL(s.count, 0) AS count,
        -- 自动计算分组号：每3个连续月份为一组
        DIV(DATE_DIFF(m.month, MIN(m.month) OVER(), MONTH), 3) AS group_id
    FROM utils, UNNEST(utils.months) AS m
    LEFT JOIN monthly_stats s ON m.month = s.date
)
-- 最终按分组聚合得到目标结果
SELECT
    CONCAT(FORMAT_DATE("%Y %B", MIN(date)), " - ", FORMAT_DATE("%Y %B", MAX(date))) AS group_name,
    MAX(ndays) AS ndays,
    SUM(count) AS count,
    SUM(32 - EXTRACT(DAY FROM DATETIME_ADD(DATETIME_TRUNC(DATETIME_SUB(date, INTERVAL 1 MONTH), MONTH), INTERVAL 31 DAY))) AS group_ndays
FROM full_months
GROUP BY group_id
ORDER BY group_id

关键逻辑说明 #

• 通过UNNEST(utils.months)展开预先生成的完整月份数组，左关联原有按月统计结果，补全缺失月份的统计值为0，避免缺失月份导致分组偏移
• 使用DIV(DATE_DIFF(m.month, MIN(m.month) OVER(), MONTH), 3)自动分配分组编号，无需硬编码分组范围，可适配任意统计区间的自动分组
• 聚合时通过FORMAT_DATE格式化月份名称拼接分组名，同时按提供的公式累加计算分组总天数

注：你给出的期望输出示例中的分组年份存在笔误，实际执行后第二组会显示为2021 January - 2021 March，符合真实时间范围。

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