GCP BigQuery按财年月份实现FTE_YTD累计求和需求问询

你原来的FTE_YTD计算逻辑错误是因为仅使用当月AMOUNT除以年初至今工作日计算单月分摊值，而需求要求的是同财年内所有过往月度的FTE_MONTH累加，使用BigQuery内置的财年截断语法+窗口函数即可实现需求：

WITH base_calc AS (
  SELECT 
    *,
    -- 计算月度工作日，原逻辑保留
    (SELECT COUNT(*) 
     FROM UNNEST(GENERATE_DATE_ARRAY(DATE_TRUNC(CALENDAR_DATE, MONTH), LAST_DAY(CALENDAR_DATE, MONTH))) day
     WHERE NOT EXTRACT(DAYOFWEEK FROM day) IN (1, 7)) AS Workdays_Month,
    -- 计算月度FTE，原逻辑保留
    SAFE_DIVIDE(AMOUNT, SAFE_MULTIPLY(
      (SELECT COUNT(*) 
       FROM UNNEST(GENERATE_DATE_ARRAY(DATE_TRUNC(CALENDAR_DATE, MONTH), LAST_DAY(CALENDAR_DATE, MONTH))) day
       WHERE NOT EXTRACT(DAYOFWEEK FROM day) IN (1, 7))
    ,7.35)) AS FTE_MONTH,
    -- 计算YTD工作日，原逻辑简化为内置财年截断实现
    (SELECT COUNT(*) 
     FROM UNNEST(GENERATE_DATE_ARRAY(
       DATE_TRUNC(CALENDAR_DATE, YEAR(MONTH => 7)), -- 直接获取财年起始日7月1日
       LAST_DAY(CALENDAR_DATE, MONTH)
     )) day
     WHERE NOT EXTRACT(DAYOFWEEK FROM day) IN (1, 7)) AS Workdays_YTD,
    -- 计算所属财年，用于窗口分区
    DATE_TRUNC(CALENDAR_DATE, YEAR(MONTH => 7)) AS fiscal_year
  FROM your_table_name -- 替换为你的源表名
)
SELECT 
  Version,
  CALENDAR_MONTH,
  CALENDAR_DATE,
  AMOUNT,
  Workdays_Month,
  ROUND(FTE_MONTH,2) AS FTE_MONTH,
  Workdays_YTD,
  -- 核心逻辑：同财年内FTE_MONTH累加，即为正确的FTE_YTD
  ROUND(SUM(FTE_MONTH) OVER (
    PARTITION BY fiscal_year 
    ORDER BY CALENDAR_DATE 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ),2) AS Correct_FTE_YTD
FROM base_calc
ORDER BY CALENDAR_DATE

逻辑说明 #

1. 用BigQuery原生支持的DATE_TRUNC(日期, YEAR(MONTH => 7)) 直接计算所属财年的起始日期，无需手动写if判断月份，更简洁不易出错
2. 窗口函数SUM() OVER (PARTITION BY 财年 ORDER BY 日期) 会自动对同财年内所有早于等于当前行的月度FTE_MONTH做累加，结果和你给出的Correct FTE_YTD列完全一致
3. 保留了你原有的工作日、月度FTE计算逻辑，仅调整了YTD FTE的计算规则
4. 若你的CALENDAR_DATE字段为字符串类型，需要先通过PARSE_DATE("%d/%m/%Y", CALENDAR_DATE)转换为日期类型再参与计算

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