BigQuery SQL：按月度统计账户连续三月正余额分布（GROUP BY）

需求说明 #

现有账户月度余额数据（每个账户每月一条记录），需统计每个月内，账户在**当前月及前后各1个月（共3个月）**的正余额次数（RU_IND）分别为0、1、2、3的账户数量，要求一步完成无需创建中间表。

实现SQL #

WITH monthly_data AS (
  SELECT
    account_id,
    tdate,
    -- 生成正余额标记：余额>0为1，否则0
    CASE WHEN balance > 0 THEN 1 ELSE 0 END AS od_user
  FROM
    `your-project.your-dataset.target-table` -- 替换为你的实际表路径
)
SELECT
  tdate,
  ru_ind,
  COUNT(DISTINCT account_id) AS account_count
FROM (
  SELECT
    account_id,
    tdate,
    -- 按账户分区，计算当前月前后1个月内的正余额次数
    SUM(od_user) OVER (
      PARTITION BY account_id
      ORDER BY tdate
      RANGE BETWEEN INTERVAL 1 MONTH PRECEDING AND INTERVAL 1 MONTH FOLLOWING
    ) AS ru_ind
  FROM monthly_data
)
-- 仅统计需求中的0-3次范围
WHERE ru_ind BETWEEN 0 AND 3
GROUP BY tdate, ru_ind
ORDER BY tdate, ru_ind;

关键说明 #

• 正余额标记生成：通过子查询monthly_data完成od_user字段的计算，替代BigQuery不支持的CALCULATED命令
• 窗口函数区分账户：使用PARTITION BY account_id确保每个账户的统计独立进行，避免跨账户干扰
• 时间窗口范围：用RANGE BETWEEN INTERVAL 1 MONTH PRECEDING AND INTERVAL 1 MONTH FOLLOWING精准划定当前月及前后各1个月的统计区间
• 分组汇总：外层查询按tdate（月份）和ru_ind分组，直接统计对应账户数量，一步输出最终统计结果

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