BigQuery月度同比问题：实现截至当日的MTD同期销售对比

需求说明 #

需实现月度内任意时间点的同期同比对比：统计当前年月1日至当日的累计销量，匹配上一年同月同期（即上一年同月1日到对应日）的累计销量，而非上一年全月总销量。数据包含sale_date每日销售总计，部分产品存在无销售日期，最终输出字段为Product_ID、Year、Month、PY_Sales、CY_Sales。

现有代码问题 #

当前代码直接按年月聚合全月销量，导致去年同期销量取的是全月值，例如product_id=2的2022年8月PY_Sales错误显示为7，正确值应为1（需排除2021年8月31日未到同期的6个销量）。现有代码如下：

WITH cte AS 
(
SELECT 
     PRODUCT_ID,
     EXTRACT(YEAR FROM SALE_DATE) AS Year,
     EXTRACT(MONTH FROM SALE_DATE) AS Month,
     CONCAT(EXTRACT(YEAR FROM SALE_DATE), '-',EXTRACT(MONTH FROM SALE_DATE)) AS Year_Month,
     SUM(Units) AS Units
FROM data
WHERE Product_ID = 1
AND DATE(SALE_DATE) >= '2019-01-01'
GROUP BY 1, 2, 3
),

    diff AS 
( 
SELECT 
     COALESCE(c.PRODUCT_ID, p.PRODUCT_ID) AS Product_ID,
     COALESCE(c.Year, p.Year + 1) AS Year,
     COALESCE(c.Month, p.Month) AS Month,
     IFNULL(c.Units, 0) AS Current_Units,
     IFNULL(p.Units, 0) AS Previous_Units,
     NULLIF(((IFNULL(c.Units, 0) - IFNULL(p.Units,0)) / p.Units),0) * 100 AS Percent_Change
FROM CTE c
FULL OUTER JOIN CTE p ON c.PRODUCT_ID = p.PRODUCT_ID AND c.Year = p.Year + 1 AND c.Month = p.Month
WHERE c.Year <= EXTRACT(YEAR FROM CURRENT_DATE())
ORDER BY 2, c.Year, c.Month
)
SELECT *
FROM diff
--This is to avoid dividing by 0
WHERE diff.Previous_Units > 0
--AND Percent_Change <= -.5

修改后的代码 #

WITH date_range AS (
    -- 生成所有需要统计的日期范围
    SELECT DATE(date) AS sale_date
    FROM UNNEST(GENERATE_DATE_ARRAY('2019-01-01', CURRENT_DATE())) AS date
),
product_dates AS (
    -- 为每个产品补全无销售日期，确保每日数据存在
    SELECT 
        p.PRODUCT_ID,
        dr.sale_date,
        COALESCE(d.Units, 0) AS Units
    FROM (SELECT DISTINCT PRODUCT_ID FROM data) p
    CROSS JOIN date_range dr
    LEFT JOIN data d 
        ON p.PRODUCT_ID = d.PRODUCT_ID 
        AND DATE(d.SALE_DATE) = dr.sale_date
    WHERE dr.sale_date >= '2019-01-01'
),
mtd_sales AS (
    -- 计算每日的MTD累计销量
    SELECT 
        PRODUCT_ID,
        EXTRACT(YEAR FROM sale_date) AS Year,
        EXTRACT(MONTH FROM sale_date) AS Month,
        EXTRACT(DAY FROM sale_date) AS Day,
        SUM(Units) OVER (
            PARTITION BY PRODUCT_ID, Year, Month 
            ORDER BY sale_date
        ) AS MTD_Units
    FROM product_dates
),
current_vs_prev AS (
    -- 关联当前年与上一年同期的MTD数据
    SELECT 
        curr.PRODUCT_ID,
        curr.Year,
        curr.Month,
        curr.MTD_Units AS CY_Sales,
        prev.MTD_Units AS PY_Sales
    FROM mtd_sales curr
    LEFT JOIN mtd_sales prev 
        ON curr.PRODUCT_ID = prev.PRODUCT_ID
        AND curr.Year = prev.Year + 1
        AND curr.Month = prev.Month
        AND curr.Day = prev.Day
    -- 过滤到当前年月的最新日期数据，或所有日期的同期对比（根据需求调整）
    WHERE curr.sale_date = LAST_DAY(CURRENT_DATE()) 
        OR (curr.Year = EXTRACT(YEAR FROM CURRENT_DATE()) 
            AND curr.Month = EXTRACT(MONTH FROM CURRENT_DATE()) 
            AND curr.sale_date = CURRENT_DATE())
)
-- 按产品、年、月聚合，取最终的同期销量
SELECT 
    PRODUCT_ID,
    Year,
    Month,
    MAX(PY_Sales) AS PY_Sales,
    MAX(CY_Sales) AS CY_Sales
FROM current_vs_prev
GROUP BY PRODUCT_ID, Year, Month
HAVING PY_Sales > 0 -- 避免除以0的情况（如果需要计算同比率可添加）
ORDER BY Year, Month, PRODUCT_ID;

关键改动说明 #

1. 补全无销售日期：通过GENERATE_DATE_ARRAY和CROSS JOIN为每个产品生成完整的日期序列，确保无销售日期的销量为0，避免窗口函数计算MTD时遗漏日期。
2. 计算MTD累计销量：使用窗口函数SUM(...) OVER (PARTITION BY ... ORDER BY sale_date)，按产品、年、月分组，逐日累计销量，得到每日的MTD值。
3. 精准匹配同期日期：通过Year = prev.Year + 1、Month = prev.Month、Day = prev.Day关联当前日期与上一年同期日期，确保取到的是同期累计销量。
4. 过滤最新数据：通过curr.sale_date = CURRENT_DATE()获取当前年月截至当日的最新MTD数据，若需要查看历史所有月份的同期对比，可去掉该条件并按日期聚合。

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