优化BigQuery SQL查询：移除CTE并简化时区转换逻辑

你可以直接将CTE中的时区转换逻辑内联到主查询中，无需额外的公共表表达式，以下是修改后的完整SQL：

SELECT
  DATE(DATETIME(usage_start_time, "America/Los_Angeles")) AS date,
  IF(project.name IS NULL, "support", project.name) AS project_name,
  SUM(cost) AS cost_before_credits,
  SUM(cost) + SUM(discounts) + SUM(promotions) AS cost_after_credit,
  SUM(cost) + SUM(discounts) AS cost_after_discount
FROM
  `ama.details_cost_credit`
WHERE
  DATE(DATETIME(usage_start_time, "America/Los_Angeles")) = DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
  AND (
    project.name IS NULL
    OR project.name IN ("project1","project2","project3")
  )
GROUP BY
  date, project.name

关键说明： #

• 时区转换逻辑复用：原CTE中对usage_start_time的时区转换（转成America/Los_Angeles时区的日期），直接在SELECT和WHERE子句中重复使用，确保过滤和聚合的日期维度完全匹配原逻辑
• 简化GROUP BY：BigQuery允许在GROUP BY中引用SELECT子句定义的别名（如date），避免重复书写冗长的转换表达式，保持代码简洁
• 保留原业务逻辑：所有聚合计算、项目名称判断、过滤条件均与原查询完全一致，仅移除了冗余的CTE层

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