如何在AWS Athena中生成含缺失Purchase事件的临时表？

我在AWS Athena中有一张名为events的表，包含user_id、event_name、session_id、epoch_utc、event_date、order_id、email等字段，存储在线购买用户的事件，典型事件流为PageView→AddToCart→Purchase。

需要创建临时表，为存在**PageView后接AddToCart（时间升序）**的用户会话添加Purchase事件行：

• 该行的epoch_utc需略大于对应AddToCart事件的时间（比如加1秒）
• 其余字段完全复制对应AddToCart行的内容

以下是实现需求的Athena SQL语句，通过UNION ALL合并原数据与新增的Purchase事件，最终创建临时表：

CREATE TEMPORARY TABLE events_with_purchase AS
-- 保留原表所有数据
SELECT 
  user_id,
  event_name,
  session_id,
  epoch_utc,
  event_date,
  order_id,
  email
FROM events

UNION ALL

-- 生成需要补充的Purchase事件行
SELECT
  user_id,
  'Purchase' AS event_name,
  session_id,
  epoch_utc + 1 AS epoch_utc, -- 比对应AddToCart时间大1秒，可按需调整增量
  event_date,
  order_id,
  email
FROM (
  -- 筛选出会话内存在前置PageView的AddToCart事件
  SELECT
    *,
    EXISTS (
      SELECT 1
      FROM events e2
      WHERE e2.session_id = e1.session_id
        AND e2.event_name = 'PageView'
        AND e2.epoch_utc < e1.epoch_utc
    ) AS has_previous_pageview
  FROM events e1
  WHERE e1.event_name = 'AddToCart'
) qualified_events
WHERE has_previous_pageview = true;

逻辑说明 #

1. 子查询阶段：先筛选所有AddToCart事件，通过EXISTS子查询校验当前会话中是否存在更早的PageView事件，确保符合PageView→AddToCart的顺序要求。
2. 生成Purchase行：对符合条件的AddToCart事件，将event_name替换为Purchase，epoch_utc增加1秒（可根据业务需求调整时间增量），其余字段完全复用AddToCart行的内容。
3. 合并数据：通过UNION ALL将原表数据与新增的Purchase事件合并，创建目标临时表events_with_purchase。

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