BigQuery实现：计算订阅月到期时历史逾期未付月数

原表数据 #

需求说明 #

需要计算每个订阅月到期当日（to_be_paid_date），该客户已有多少个历史订阅月（sub_month_number小于当前值）仍未付款。例如第4个订阅月到期日2020-06-01时，第2、3个月的实际付款日期均晚于该日期，因此num_past_overdue为2。

预期结果 #

尝试的代码（无法满足需求） #

WITH payments as (
SELECT *
,LEAD(to_be_paid_date) OVER (PARTITION BY id ORDER BY id,  to_be_paid_date) AS next_due_date  
FROM table)

SELECT *
, CASE WHEN DATE_DIFF(actual_payment_date, next_due_date, DAY)> 0 THEN True
          ELSE False END AS overdue_when_next_was_due
FROM payments

该代码仅能判断当前订阅月是否在下次到期时仍逾期，无法统计所有历史未付款的订阅月总数。

正确的BigQuery SQL解决方案 #

SELECT
  id,
  sub_month_number,
  to_be_paid_date,
  actual_payment_date,
  CASE 
    WHEN sub_month_number = 1 THEN '-'
    ELSE COUNTIF(actual_payment_date > current_to_be_paid) OVER (
      PARTITION BY id 
      ORDER BY to_be_paid_date 
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    )
  END AS num_past_overdue
FROM (
  SELECT
    *,
    to_be_paid_date AS current_to_be_paid
  FROM `your-project.your-dataset.your-table` -- 替换为你的实际表路径
)
ORDER BY id, sub_month_number;

逻辑说明 #

1. 内层查询：为每条记录添加current_to_be_paid字段，即当前订阅月的到期日，用于后续判断历史订阅的实际付款时间是否晚于该日期。
2. 外层查询：
   • 按客户分组（PARTITION BY id），并按到期日排序（ORDER BY to_be_paid_date）。
   • 使用COUNTIF结合窗口范围ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING，统计当前记录之前的所有历史订阅中，实际付款日期晚于当前到期日的数量。
   • 第一个订阅月无历史记录，返回'-'。

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