BigQuery中基于日期拆分发票表行以计算月度收入的SQL方案

要实现跨月发票的拆分，核心思路是生成发票覆盖的所有月份列表，再将总金额按月份数均分后分配到每个月。以下是具体的SQL实现方案：

假设原表结构 #

假设你的发票表（命名为invoices）包含以下核心字段：

• invoice_id：发票唯一ID
• total_amount：发票总金额
• start_date：发票覆盖的起始日期（如2022-01-01）
• end_date：发票覆盖的结束日期（如2022-10-01）

拆分SQL代码 #

WITH monthly_invoices AS (
  SELECT
    invoice_id,
    total_amount,
    start_date,
    end_date,
    -- 计算发票覆盖的总月份数
    DATE_DIFF(end_date, start_date, MONTH) + 1 AS total_months,
    -- 生成覆盖期间每个月的起始日期数组
    GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 MONTH) AS month_dates
  FROM invoices
)
SELECT
  invoice_id,
  -- 统一格式为年月（月度统计标识）
  DATE_TRUNC(month_date, MONTH) AS month,
  -- 计算月度分摊金额（保留两位小数）
  ROUND(total_amount / total_months, 2) AS monthly_amount
FROM monthly_invoices,
UNNEST(month_dates) AS month_date
ORDER BY invoice_id, month;

代码说明 #

1. CTE预处理：先计算每张发票的总覆盖月份数，同时生成覆盖期内每个月的起始日期数组

   • DATE_DIFF(end_date, start_date, MONTH) + 1：DATE_DIFF返回的是两个日期的月份差（比如2022-01-01到2022-10-01返回9），加1后得到实际覆盖的10个月
   • GENERATE_DATE_ARRAY：生成从起始日到结束日的每月起始日期数组，对应示例会生成['2022-01-01', '2022-02-01', ..., '2022-10-01']

2. 主查询拆分：用UNNEST将日期数组拆分为多行，同时计算每个月的分摊金额，并用DATE_TRUNC统一月度格式

示例验证 #

对应你提到的示例记录：

• 原数据：invoice_id=1, total_amount=5000, start_date='2022-01-01', end_date='2022-10-01'
• 执行SQL后会生成10行记录，每行的month从2022-01-01到2022-10-01，monthly_amount均为500.00

注意事项 #

• 若发票起止日期不是当月第一天，DATE_TRUNC会自动归到所属月份，保证月度统计的一致性
• 若总金额无法被月份数整除，可根据业务需求调整ROUND精度，或把尾差统一分配到最后一个月

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