关于在GCP BigQuery中编写QoQ growth SQL的技术咨询

嗨，我来帮你搞定这个BigQuery里的季度环比计算需求～ 首先明确你的核心要求：基于月度粒度的表，计算当前季度最后一个月与上一个季度最后一个月的差值作为环比增长，对吧？我给你拆解下实现步骤，再附上可直接复用的SQL示例。

核心思路拆解 #

要实现这个逻辑，我们分两步走：

1. 从月度数据里筛选出每个季度的最后一个月的记录
2. 对这些季度末的数据，关联上一个季度的数值，计算增长差值

具体SQL示例 #

假设你的源表结构包含date_month（月度日期，格式比如2023-01-01）和metric_value（需要计算增长的指标，比如营收、用户数），SQL代码如下：

WITH quarterly_end_data AS (
  SELECT
    DATE_TRUNC(date_month, QUARTER) AS quarter_start, -- 标记每条记录所属的季度
    date_month,
    metric_value,
    -- 找出当前季度的最后一个月
    MAX(date_month) OVER(PARTITION BY DATE_TRUNC(date_month, QUARTER)) AS quarter_end_month
  FROM
    `your-project.your-dataset.your-source-table` -- 替换成你的源表路径
  -- 仅保留季度最后一个月的数据
  QUALIFY date_month = quarter_end_month
),
qoq_growth_calculation AS (
  SELECT
    quarter_start,
    date_month AS current_quarter_end_month,
    metric_value AS current_quarter_value,
    -- 取上一个季度的指标值
    LAG(metric_value) OVER(ORDER BY quarter_start) AS previous_quarter_value,
    -- 计算环比增长值（当前季度值 - 上季度值）
    metric_value - LAG(metric_value) OVER(ORDER BY quarter_start) AS qoq_growth_value,
    -- 可选：计算环比增长率（百分比，保留2位小数）
    ROUND((metric_value - LAG(metric_value) OVER(ORDER BY quarter_start)) / LAG(metric_value) OVER(ORDER BY quarter_start) * 100, 2) AS qoq_growth_percent
  FROM
    quarterly_end_data
)
-- 输出最终结果，按季度排序
SELECT * FROM qoq_growth_calculation ORDER BY quarter_start;

关键细节说明 #

• DATE_TRUNC函数：用来把月度日期截断到季度起始（比如2023-02-01会变成2023-01-01，代表Q1），方便我们按季度分组。
• QUALIFY子句：BigQuery专属的实用语法，直接基于窗口函数的结果筛选数据，不用额外嵌套子查询就能过滤出每个季度的最后一个月。
• LAG窗口函数：按季度的时间顺序，取上一个季度的指标值，这样就能直接计算两个季度末的差值。

额外适配建议 #

如果你的表有多维度拆分（比如按地区、产品分类），只需要在PARTITION BY里加上这些维度即可，比如：

MAX(date_month) OVER(PARTITION BY region, product, DATE_TRUNC(date_month, QUARTER)) AS quarter_end_month

这样就能确保每个维度单独计算环比增长，不会互相干扰。

备注：内容来源于stack exchange，提问作者Prithwiraj Samanta