求BigQuery中免子查询解析日期的AOV计算及群组分析方案

优化后的AOV计算SQL（无日期解析子查询） #

直接在主查询中完成日期解析，无需嵌套子查询，同时修正原SQL中日期筛选的逻辑问题：

SELECT
  DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), WEEK) AS week_start,
  SUM(purchase_revenue_in_usd) / COUNT(DISTINCT user_pseudo_id) AS AOV
FROM `turing_data_analytics.raw_events`
WHERE PARSE_DATE('%Y%m%d', event_date) BETWEEN DATE('2020-11-01') AND DATE('2021-01-24')
GROUP BY week_start
ORDER BY week_start

关键优化点：

• 移除了用于日期解析的子查询，直接在SELECT和WHERE中调用PARSE_DATE转换日期格式
• 将GROUP BY改为使用明确的字段别名week_start，比原代码的位置序号更易维护
• 修正原SQL的筛选逻辑：原代码用字符串格式的event_date与带横杠的日期字符串比较会导致筛选错误，现在统一转为DATE类型后再做范围判断

扩展：群组分析实现 #

如果需要按用户首次购买周进行群组分析，以下代码仅在必要时使用子查询（用于获取用户首次购买时间，而非日期解析）：

SELECT
  DATE_TRUNC(user_first_purchase_week, WEEK) AS cohort_week,
  DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), WEEK) AS order_week,
  SUM(purchase_revenue_in_usd) / COUNT(DISTINCT user_pseudo_id) AS cohort_AOV,
  DATE_DIFF(order_week, cohort_week, WEEK) AS weeks_since_cohort
FROM (
  SELECT
    *,
    -- 用窗口函数获取每个用户的首次购买日期
    MIN(PARSE_DATE('%Y%m%d', event_date)) OVER (PARTITION BY user_pseudo_id) AS user_first_purchase_week
  FROM `turing_data_analytics.raw_events`
  WHERE PARSE_DATE('%Y%m%d', event_date) BETWEEN DATE('2020-11-01') AND DATE('2021-01-24')
    AND purchase_revenue_in_usd IS NOT NULL -- 仅筛选有购买记录的事件
)
GROUP BY cohort_week, order_week, weeks_since_cohort
ORDER BY cohort_week, weeks_since_cohort

群组分析说明：

• 通过窗口函数MIN(...) OVER (PARTITION BY user_pseudo_id)标记每个用户的首次购买周，作为群组划分依据
• 按群组周、订单周分组，计算各群组在不同时间节点的AOV，以及与首次购买周的间隔周数，便于分析群组的长期价值表现

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