多时段指标对比SQL查询优化及BigQuery适配咨询

需求说明 #

• 基于2021年1月起的业务数据，提取当月、上月、上年同月的purchase、purchaserate、fulfilledrate指标
• 处理边界场景：2021年1月无上月数据、2021全年无上年数据，对应字段返回NULL
• 将SQL Server中的CROSS APPLY语法转换为BigQuery支持的写法

示例输入数据 #

预期输出 #

基础指标输出 #

同比环比差值输出 #

原尝试的SQL Server代码 #

SELECT 
        c.archive, 
        c.state, 
        c.purchase AS curr_month_purchase,
        c1.purchase as prev_month_purchase,
        c12.purchase as same_month_of_prev_year_purchase,
        c.purchaserate AS curr_month_purchaserate,
        c1.purchaserate as prev_month_purchaserate,
        c12.purchaserate as same_month_of_prev_year_purchaserate,
        c.fulfilledrate AS curr_month_fulfilledrate,
        c1.fulfilledrate as prev_month_fulfilledrate,
        c12.fulfilledrate as same_month_of_prev_year_fulfilledrate
    FROM CTE_Sum AS c
    CROSS APPLY (
        SELECT * FROM CTE_Sum WHERE archive = DATEADD(MONTH, -1, c.archive) AND state = c.state
    ) AS c1
    CROSS APPLY (
        SELECT * FROM CTE_Sum WHERE archive = DATEADD(MONTH, -12, c.archive) AND state = c.state
    ) AS c12

BigQuery适配解决方案 #

1. CROSS APPLY的替代方案 #

BigQuery不支持CROSS APPLY，可通过窗口函数LAG或LEFT JOIN实现关联上月、上年同月数据：

方案一：窗口函数LAG（简洁高效）

按State分组、Archive排序，直接提取前1个月和前12个月的数据，自动处理无数据场景（返回NULL）：

WITH monthly_data AS (
    SELECT 
        archive,
        state,
        purchase,
        purchaserate,
        fulfilledrate,
        -- 提取上月数据
        LAG(purchase) OVER (PARTITION BY state ORDER BY archive) AS purchase_previous_month,
        LAG(purchaserate) OVER (PARTITION BY state ORDER BY archive) AS purchaserate_previous_month,
        LAG(fulfilledrate) OVER (PARTITION BY state ORDER BY archive) AS fulfilledrate_previous_month,
        -- 提取上年同月数据
        LAG(purchase, 12) OVER (PARTITION BY state ORDER BY archive) AS purchase_prev_year_same_month,
        LAG(purchaserate, 12) OVER (PARTITION BY state ORDER BY archive) AS purchaserate_prev_year_same_month,
        LAG(fulfilledrate, 12) OVER (PARTITION BY state ORDER BY archive) AS fulfilledrate_prev_year_same_month
    FROM `your-project.your-dataset.input_table`
    WHERE DATE_TRUNC(archive, MONTH) >= '2021-01-01' -- 过滤2021年1月及以后数据
)
SELECT 
    archive,
    state,
    purchase AS purchase_current_month,
    purchase_previous_month,
    purchase_prev_year_same_month,
    purchaserate AS purchaserate_current_month,
    purchaserate_previous_month,
    purchaserate_prev_year_same_month,
    fulfilledrate AS fulfilledrate_current_month,
    fulfilledrate_previous_month,
    fulfilledrate_prev_year_same_month,
    -- 计算同比环比（百分比），处理除数为0或无数据的情况
    ROUND(IFNULL((purchase - purchase_previous_month)/NULLIF(purchase_previous_month, 0)*100, NULL), 1) AS purchase_MOM_change,
    ROUND(IFNULL((purchase - purchase_prev_year_same_month)/NULLIF(purchase_prev_year_same_month, 0)*100, NULL), 1) AS purchase_YOM_change,
    ROUND(IFNULL((purchaserate - purchaserate_previous_month), NULL), 1) AS purchaserate_MOM_change,
    ROUND(IFNULL((purchaserate - purchaserate_prev_year_same_month), NULL), 1) AS purchaserate_YOM_change,
    ROUND(IFNULL((fulfilledrate - fulfilledrate_previous_month), NULL), 1) AS fulfilledrate_MOM_change,
    ROUND(IFNULL((fulfilledrate - fulfilledrate_prev_year_same_month), NULL), 1) AS fulfilledrate_YOM_change
FROM monthly_data
ORDER BY archive DESC, state;

方案二：LEFT JOIN（逻辑直观）

通过日期函数计算上月、上年同月的日期，再关联原表，确保无数据时返回NULL：

WITH monthly_data AS (
    SELECT 
        DATE_TRUNC(archive, MONTH) AS month_date, -- 统一到月初日期，避免日期间差异
        state,
        purchase,
        purchaserate,
        fulfilledrate
    FROM `your-project.your-dataset.input_table`
    WHERE DATE_TRUNC(archive, MONTH) >= '2021-01-01'
)
SELECT 
    curr.month_date AS archive,
    curr.state,
    curr.purchase AS purchase_current_month,
    prev.purchase AS purchase_previous_month,
    prev_year.purchase AS purchase_prev_year_same_month,
    curr.purchaserate AS purchaserate_current_month,
    prev.purchaserate AS purchaserate_previous_month,
    prev_year.purchaserate AS purchaserate_prev_year_same_month,
    curr.fulfilledrate AS fulfilledrate_current_month,
    prev.fulfilledrate AS fulfilledrate_previous_month,
    prev_year.fulfilledrate AS fulfilledrate_prev_year_same_month,
    -- 计算同比环比
    ROUND(IFNULL((curr.purchase - prev.purchase)/NULLIF(prev.purchase, 0)*100, NULL), 1) AS purchase_MOM_change,
    ROUND(IFNULL((curr.purchase - prev_year.purchase)/NULLIF(prev_year.purchase, 0)*100, NULL), 1) AS purchase_YOM_change,
    ROUND(IFNULL((curr.purchaserate - prev.purchaserate), NULL), 1) AS purchaserate_MOM_change,
    ROUND(IFNULL((curr.purchaserate - prev_year.purchaserate), NULL), 1) AS purchaserate_YOM_change,
    ROUND(IFNULL((curr.fulfilledrate - prev.fulfilledrate), NULL), 1) AS fulfilledrate_MOM_change,
    ROUND(IFNULL((curr.fulfilledrate - prev_year.fulfilledrate), NULL), 1) AS fulfilledrate_YOM_change
FROM monthly_data curr
LEFT JOIN monthly_data prev 
    ON curr.state = prev.state 
    AND curr.month_date = DATE_ADD(prev.month_date, INTERVAL 1 MONTH)
LEFT JOIN monthly_data prev_year 
    ON curr.state = prev_year.state 
    AND curr.month_date = DATE_ADD(prev_year.month_date, INTERVAL 12 MONTH)
ORDER BY curr.month_date DESC, curr.state;

2. 边界场景处理说明 #

• 2021年1月数据：上月（2020年12月）无数据，*_previous_month字段返回NULL
• 2021年任意月份：上年同月（2020年对应月份）无数据，*_prev_year_same_month字段返回NULL
• 用NULLIF避免除以0的错误，IFNULL确保无数据时返回NULL而非报错

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