BigQuery中GA日期提取月份：Legacy转Standard SQL遇解析错误

嘿，我看到你在把Legacy SQL的GA查询转成Standard SQL时遇到了日期解析的问题，还有几个潜在的小坑需要修正，我来帮你理清楚：

首先说你碰到的Failed to parse input string "20170304"错误——这是因为你用了小写的%y格式符，它只能匹配两位年份（比如"17"），但GA的date字段是YYYYMMDD格式的四位年份字符串（比如"20170304"），所以得换成大写的%Y来匹配四位年份。

接下来我们一步步修正你的代码，同时解决其他容易踩的坑：

1. 修复日期解析与月份提取 #

你原来的Legacy代码是用MONTH(date)提取月份，但你现在的Standard代码里错误地提取了年份。正确的写法应该是用EXTRACT(MONTH FROM PARSE_DATE('%Y%m%d', date))来获取月份，和Legacy的MONTH()效果一致。

2. 修正WHERE条件的逻辑优先级 #

你的WHERE子句里AND和OR混用但没加括号，这会导致逻辑错误！原来的Legacy逻辑是：事件类别是Ecommerce，且事件动作是两个Add to Basket中的一个，所以必须把OR的部分用括号括起来，否则BigQuery会先执行AND再执行OR，完全偏离你的需求。

3. 优化表后缀的日期筛选 #

用_table_suffix筛选日期时，直接把日期格式化成YYYYMMDD字符串来匹配表后缀，比解析字符串更高效，避免额外的计算开销。

修正后的完整代码 #

SELECT 
  EXTRACT(MONTH FROM PARSE_DATE('%Y%m%d', date)) AS Mon,
  hits.eventInfo.eventAction,
  ROUND(AVG(hits_product.productPrice / 1000000), 2) AS avg_price
FROM `xxx.ga_sessions_20*` AS t
CROSS JOIN UNNEST(hits) AS hits
CROSS JOIN UNNEST(hits.product) AS hits_product
WHERE 
  _table_suffix BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) 
                     AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  AND hits.eventInfo.eventCategory = "Ecommerce"
  AND (hits.eventInfo.eventAction = "Add to Basket PDP" OR hits.eventInfo.eventAction = "Add to Basket PLP")
GROUP BY Mon, hits.eventInfo.eventAction

额外小提示 #

• 如果你需要的是「年份+月份」的组合（比如201703），可以用FORMAT_DATE('%Y%m', PARSE_DATE('%Y%m%d', date)) AS year_month来替代单独提取月份，这样分组会更精准（避免不同年份的同月份被合并）。
• 确认hits.product字段确实存在且有数据，否则UNNEST可能会过滤掉没有产品的会话，这和Legacy SQL的行为可能略有不同，如果需要保留无产品的会话，可以用LEFT JOIN UNNEST()替代CROSS JOIN UNNEST()。

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