如何在BigQuery中计算各时间点的用户未偿贷款金额？

嘿，这个需求用BigQuery SQL就能完美解决，我给你梳理一套实操方案，咱们一步步来：

1. 先明确原始数据结构 #

假设你的贷款表（比如叫user_loans）包含以下关键字段：

• user_id: 用户唯一标识
• loan_amount: 贷款本金金额
• start_month: 贷款起始月份（建议用DATE类型，比如2024-01-01代表2024年1月）
• term_months: 贷款期限（就是你说的3、4、5个月）

如果你的起始月份是字符串或整数，先转换成DATE类型会更方便计算，比如：

ALTER TABLE user_loans
ALTER COLUMN start_month SET DATA TYPE DATE;
-- 或者如果是YYYY-MM字符串，用PARSE_DATE('%Y-%m', start_month_str)转换

2. 核心解决方案：生成时间序列 + 关联判断 #

步骤A：生成所有需要统计的时间点 #

我们需要覆盖从最早的贷款起始月到最晚的贷款到期月之间的每一个月，用GENERATE_DATE_ARRAY生成连续的每月第一天：

WITH all_months AS (
  SELECT 
    month_start
  FROM UNNEST(
    GENERATE_DATE_ARRAY(
      (SELECT MIN(start_month) FROM user_loans),
      (SELECT MAX(DATE_ADD(start_month, INTERVAL term_months MONTH)) FROM user_loans),
      INTERVAL 1 MONTH
    )
  ) AS month_start
),

步骤B：关联贷款数据，判断未偿状态 #

把每个时间点和用户的所有贷款关联，筛选出当前时间点仍在还款期内的贷款：

loan_status AS (
  SELECT
    ul.user_id,
    am.month_start AS report_month,
    ul.loan_amount
  FROM all_months am
  CROSS JOIN user_loans ul
  WHERE 
    am.month_start >= ul.start_month
    AND am.month_start < DATE_ADD(ul.start_month, INTERVAL ul.term_months MONTH)
)

步骤C：按用户和时间点聚合未偿总额 #

最后按用户和统计月份分组，加总未偿金额：

SELECT
  user_id,
  FORMAT_DATE('%Y-%m', report_month) AS report_month, -- 转换成YYYY-MM格式输出
  SUM(loan_amount) AS outstanding_amount
FROM loan_status
GROUP BY user_id, report_month
ORDER BY user_id, report_month;

3. 示例测试（可选） #

如果你想先验证逻辑，可以创建一个示例表测试：

CREATE TEMP TABLE user_loans AS
SELECT
  123 AS user_id,
  5000 AS loan_amount,
  DATE('2024-01-01') AS start_month,
  3 AS term_months
UNION ALL
SELECT
  123 AS user_id,
  6000 AS loan_amount,
  DATE('2024-02-01') AS start_month,
  4 AS term_months
UNION ALL
SELECT
  123 AS user_id,
  4000 AS loan_amount,
  DATE('2024-03-01') AS start_month,
  5 AS term_months;

运行上面的完整查询后，你会得到类似这样的输出：

4. 注意事项 #

• 如果你的贷款有分期还款（不是到期一次性还清），那需要调整逻辑，比如按每月还款额递减未偿金额——不过你没提这个，默认是到期前全额未偿哈。
• 如果要统计的是具体日期（不是每月），只需要把GENERATE_DATE_ARRAY的间隔改成INTERVAL 1 DAY就行。

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