如何用SQL在BigQuery中实现start_date到end_date区间费用分摊分析

你可以直接使用BigQuery内置的GENERATE_DATE_ARRAY+UNNEST功能实现日期拆分和费用分摊，参考代码如下：

WITH expense_converted AS (
  -- 转换字符串日期为DATE类型，若原始字段已是DATE类型可省略该CTE
  SELECT
    PARSE_DATE('%d/%m/%Y', 开始日期) AS start_date,
    PARSE_DATE('%d/%m/%Y', 结束日期) AS end_date,
    开始日期 AS original_start_date,
    结束日期 AS original_end_date,
    费用编码,
    REPLACE(总金额, ',', '') AS total_amount, -- 若总金额已是数值类型可省略该转换
    Ref_code,
    DATE_DIFF(PARSE_DATE('%d/%m/%Y', 结束日期), PARSE_DATE('%d/%m/%Y', 开始日期), DAY) + 1 AS total_days
  FROM `你的项目.你的数据集.你的原始表名` -- 替换为实际表路径
)
SELECT
  original_start_date AS 开始日期,
  original_end_date AS 结束日期,
  费用编码,
  FORMAT("%.2f", total_amount) AS 总金额, -- 按需调整金额格式
  Ref_code,
  FORMAT_DATE('%d/%m/%Y', daily_date) AS Daily_date,
  ROUND(SAFE_DIVIDE(SAFE_CAST(total_amount AS FLOAT64), total_days), 6) AS Daily_expense
FROM expense_converted,
UNNEST(GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 DAY)) AS daily_date

注意事项：

• 如果你的原始表中开始日期、结束日期字段已经是DATE类型、总金额已经是数值类型，可以删除对应的格式转换逻辑，直接使用原生字段计算即可
• 计算总天数时加1是因为要同时包含首尾日期，和你示例的计算逻辑一致：11月1日到13日共13天，70000/13≈5384.615385；11月1日到30日共30天，3000/30=100
• SAFE_DIVIDE用于规避总天数为0（开始日期=结束日期）时的除零错误，无特殊业务场景可直接用普通除法
• 保留小数位数可通过调整ROUND函数的第二个参数自定义

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