BigQuery中SELECT定义的transaction_date为何在WHERE子句中无法识别？

错误原因 #

SQL的执行逻辑是先处理FROM和WHERE子句，再执行SELECT子句。你在SELECT里定义的transaction_date别名，在WHERE子句执行时还未被创建，所以BigQuery无法识别这个名称。另外注意：BigQuery中没有today()函数，需用CURRENT_DATE()替代；同时BETWEEN的日期范围要遵循「小值在前、大值在后」的规则。

正确查询写法 #

有两种常用的修正方式：

方式1：在WHERE子句中重复字段转换逻辑 #

直接在WHERE里使用原字段的CAST转换逻辑，替代别名：

SELECT
  CAST(creation_date_transactions_orders AS DATE) as transaction_date,
  email_contact_transactions_orders,
  title_transactions_orders,
  total_paid_transactions_orders,
  status_transactions_orders
FROM
  `nla-analytics.NLA_Keap_Keap_Keap.transactions_orders` 
WHERE total_paid_transactions_orders IS NOT NULL 
AND status_transactions_orders LIKE "PAID"
AND CAST(creation_date_transactions_orders AS DATE) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE()

方式2：使用CTE（公共表表达式） #

如果后续要做新客户、流失客户这类复杂统计，用CTE会让逻辑更清晰，便于后续扩展：

WITH recent_orders AS (
  SELECT
    CAST(creation_date_transactions_orders AS DATE) as transaction_date,
    email_contact_transactions_orders,
    title_transactions_orders,
    total_paid_transactions_orders,
    status_transactions_orders
  FROM
    `nla-analytics.NLA_Keap_Keap_Keap.transactions_orders` 
  WHERE total_paid_transactions_orders IS NOT NULL 
  AND status_transactions_orders LIKE "PAID"
)
SELECT *
FROM recent_orders
WHERE transaction_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE()

后续业务统计扩展提示 #

针对你要统计新订单/客户、订单/客户流失、一次性销售的需求，可以基于上述基础查询继续扩展：

• 区分一次性/订阅产品：可通过title_transactions_orders字段的关键词（如含「一次性」「订阅」）过滤，若有专门的产品类型字段则更准确。
• 统计新客户：用窗口函数ROW_NUMBER()按邮箱分组，筛选首次下单日期落在过去30天内的记录。
• 统计流失客户：需对比历史订单数据，找出「之前有下单记录，但过去30天无订单」的客户。

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