BigQuery窗口函数使用别名报错，如何解决？

在BigQuery中，这个报错的核心原因是SQL逻辑执行顺序的限制：窗口函数的执行时机早于SELECT子句中别名的解析，因此在同一个SELECT子句里，窗口函数无法直接引用SELECT中定义的别名（尽管GROUP BY支持引用SELECT别名是BigQuery的语法糖，但窗口函数不适用这个规则）。

针对你的查询，有两种简洁的解决方式：

方法1：重复转换表达式 #

直接在窗口函数的PARTITION BY中使用CAST(sent_at AS DATE)代替别名date，绕开别名解析的限制：

SELECT 
  anonymous_id, 
  SUM(CAST(plan_cost as decimal)) as plan_cost, 
  CAST(sent_at AS DATE) as date,
  COUNT(*) OVER (PARTITION BY anonymous_id, CAST(sent_at AS DATE)) as occurrences
FROM `****-348311.****_prod.purchase`
GROUP BY anonymous_id, date
ORDER BY date asc;

方法2：用CTE/子查询预处理日期列 #

先通过CTE或子查询将sent_at转换为date列，在外层查询中就可以直接使用别名进行窗口函数分组，避免重复写转换逻辑：

CTE版本 #

WITH purchase_with_date AS (
  SELECT 
    anonymous_id,
    plan_cost,
    CAST(sent_at AS DATE) as date
  FROM `****-348311.****_prod.purchase`
)
SELECT 
  anonymous_id,
  SUM(CAST(plan_cost as decimal)) as plan_cost,
  date,
  COUNT(*) OVER (PARTITION BY anonymous_id, date) as occurrences
FROM purchase_with_date
GROUP BY anonymous_id, date
ORDER BY date asc;

子查询版本 #

SELECT 
  anonymous_id,
  SUM(CAST(plan_cost as decimal)) as plan_cost,
  date,
  COUNT(*) OVER (PARTITION BY anonymous_id, date) as occurrences
FROM (
  SELECT 
    anonymous_id,
    plan_cost,
    CAST(sent_at AS DATE) as date
  FROM `****-348311.****_prod.purchase`
)
GROUP BY anonymous_id, date
ORDER BY date asc;

两种方法各有优势：方法1适合简单查询，写法直接；方法2更适合复杂场景，能提升代码的可读性和维护性。

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