BigQuery使用Workday计算字段生成FTE字段报错的正确写法咨询

报错核心原因 #

• 标准SQL（含BigQuery SQL）不支持同一SELECT层级直接引用刚定义的字段别名，同一级SELECT里定义的Workday不能直接在后续同层级的字段计算逻辑里调用，和SQL的执行顺序规则有关。
• 原代码中函数使用错误：MAX是列聚合函数，不能用来取两个标量值的较大值，该场景应该用GREATEST函数；SAFE_MULTIPLY需要传入两个运算参数，原写法的参数结构不符合要求。

正确写法 #

推荐方案（可读性高，易维护） #

通过CTE提前计算好Workday，再在后续查询中引用该字段：

WITH base_calc AS (
  SELECT
    -- 此处保留你原查询的其他业务字段，例如AMOUNT
    AMOUNT,
    -- 计算当月工作日数量
    (
      SELECT COUNT(*) 
      FROM UNNEST(GENERATE_DATE_ARRAY(DATE_TRUNC('2021-05-25', MONTH), LAST_DAY('2021-05-25', MONTH))) day 
      WHERE NOT EXTRACT(DAYOFWEEK FROM day) IN (1, 7)
    ) AS Workday
  FROM 你的业务表名 -- 替换为你实际存储AMOUNT字段的表
)
SELECT
  AMOUNT,
  Workday,
  -- 计算FTE，需取Workday和7.35的较大值可替换为GREATEST(Workday, 7.35)
  SAFE_DIVIDE(AMOUNT, SAFE_MULTIPLY(Workday, 7.35)) AS FTE
FROM base_calc

简化方案（无需CTE） #

直接把Workday的计算逻辑嵌套进FTE的计算逻辑中，适合逻辑简单的场景：

SELECT
  (
    SELECT COUNT(*) 
    FROM UNNEST(GENERATE_DATE_ARRAY(DATE_TRUNC('2021-05-25', MONTH), LAST_DAY('2021-05-25', MONTH))) day 
    WHERE NOT EXTRACT(DAYOFWEEK FROM day) IN (1, 7)
  ) AS Workday,
  SAFE_DIVIDE(
    AMOUNT,
    SAFE_MULTIPLY(
      (
        SELECT COUNT(*) 
        FROM UNNEST(GENERATE_DATE_ARRAY(DATE_TRUNC('2021-05-25', MONTH), LAST_DAY('2021-05-25', MONTH))) day 
        WHERE NOT EXTRACT(DAYOFWEEK FROM day) IN (1, 7)
      ),
      7.35
    )
  ) AS FTE
FROM 你的业务表名

补充说明 #

如果你的业务逻辑需要先取Workday和7.35的较大值再做乘法，把上述代码里的SAFE_MULTIPLY(Workday, 7.35)替换为SAFE_MULTIPLY(GREATEST(Workday, 7.35), 你需要的其他系数)即可。

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