如何在BigQuery中按客户计算月度成本增减差异

你可以通过窗口函数LAG()结合PARTITION BY子句实现按客户分组对比上月成本的需求，同时用CASE语句判断增减情况。以下是完整的查询代码：

WITH monthly_costs AS (
    SELECT 
        customer_id,
        format_date('%Y-%m-01', event_date) AS event_date, -- 统一为每月1日的日期格式，与示例匹配
        SUM(cost) AS amount
    FROM myTable 
    GROUP BY customer_id, format_date('%Y-%m-01', event_date)
)
SELECT 
    customer_id,
    event_date,
    amount,
    CASE
        WHEN LAG(amount) OVER (PARTITION BY customer_id ORDER BY event_date) IS NULL THEN 'N/A'
        WHEN amount > LAG(amount) OVER (PARTITION BY customer_id ORDER BY event_date) THEN 'Increase'
        WHEN amount < LAG(amount) OVER (PARTITION BY customer_id ORDER BY event_date) THEN 'Decrease'
        ELSE 'No Change' -- 处理成本与上月持平的情况
    END AS change_from_prior_month
FROM monthly_costs
ORDER BY customer_id, event_date;

核心逻辑说明： #

• PARTITION BY customer_id：这是避免跨客户对比的关键，让LAG()函数仅在同一客户的记录范围内查找上月数据。
• ORDER BY event_date：在每个客户的分组内按日期排序，确保LAG()取到的是对应客户的上一个月记录。
• CASE分支判断：根据当前成本与上月成本的对比结果，返回对应的状态描述，同时处理无上月数据的初始记录场景。

补充说明： #

• 原查询的event_date格式为%Y-%m，这里调整为%Y-%m-01是为了和你给出的示例格式一致，若需保留%Y-%m格式，只需修改format_date的参数即可，不影响窗口函数的逻辑。
• 若你的业务中存在客户某月无记录的情况，可能需要额外处理日期连续性问题，比如生成连续月份的维度表进行关联（可根据实际需求调整）。

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