如何在BigQuery中查询用户维度的负余额账龄报表

核心逻辑 #

处理这类连续状态统计的核心是先归并连续同状态的时间段再计算时长，首先要解决同日多笔流水的冲突问题：

• 先聚合每个用户每日的最终余额，同一天有多条流水时，取当日最后一笔交易后的余额作为当日有效余额，避免同日同时出现正负余额的统计错误
• 通过窗口函数给连续的负余额记录分配相同的分组ID，非负余额会打断连续分组
• 针对统计当日的记录，若当日余额非负则持续天数为0；若为负则计算当日到分组内最早日期的自然日差，即为连续负余额的天数

可直接运行的SQL代码 #

假设你的源表名为user_balance_records，包含三个字段：record_date（DATE类型，记账日期）、username（STRING类型，用户名）、balance（NUMERIC类型，账户余额），统计日期通过参数@stat_date传入（示例中为'2022-07-06'）：

WITH daily_balance AS (
  -- 步骤1：计算每个用户每日最终余额，同日多笔流水取最后一笔的余额
  -- 如果你有精确到时分秒的交易时间字段，把ORDER BY后面的字段换成交易时间字段即可
  SELECT
    record_date,
    username,
    ARRAY_AGG(balance ORDER BY record_date DESC LIMIT 1)[OFFSET(0)] AS end_of_day_balance
  FROM `user_balance_records`
  WHERE record_date <= @stat_date -- 只扫描统计日及之前的数据，降低计算量
  GROUP BY record_date, username
),
status_segment AS (
  -- 步骤2：给连续同状态的记录打同组标记
  SELECT
    record_date,
    username,
    end_of_day_balance,
    -- 累计到当前行的非负余额出现次数，次数相同即为同一段连续状态
    COUNTIF(end_of_day_balance >= 0) OVER (
      PARTITION BY username
      ORDER BY record_date ASC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS segment_id
  FROM daily_balance
)
-- 步骤3：计算统计当日的负余额持续天数
SELECT
  username,
  @stat_date AS stat_date,
  CASE
    WHEN MAX(IF(record_date = @stat_date, end_of_day_balance, NULL)) >= 0 THEN 0
    ELSE DATE_DIFF(@stat_date, MIN(record_date), DAY) + 1 -- +1是为了包含负余额开始的第一天
  END AS negative_balance_days
FROM status_segment
WHERE segment_id = (
  SELECT segment_id
  FROM status_segment s
  WHERE s.username = status_segment.username AND s.record_date = @stat_date
)
GROUP BY username

示例数据验证 #

代入你提供的样例数据，统计日设为2022-07-06时，计算过程如下：

1. 日终余额聚合结果：2022-06-30余额为538（同日两笔流水取最后入账的正值）、2022-07-01余额为20（正值）、2022-07-02至2022-07-06余额均为-100（负值）
2. 连续状态分组：2022-07-02至2022-07-06属于同一个连续负余额分组，分组内最早日期为2022-07-02
3. 天数计算：DATE_DIFF('2022-07-06', '2022-07-02', DAY) + 1 = 4 +1 =5，和预期结果完全匹配。

补充说明：如果需要统计用户历史上每一次负余额的持续时长，只需要去掉最后一步筛选统计当日的逻辑，按username+segment_id分组，取分组内最小、最大日期计算差值即可。

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