Redshift中UNNEST SUPER类型数据获取产品事件计数问题

核心解决方案 #

针对你的场景，需要区分不同事件类型的event_data结构，正确展开ordered事件的数组，同时统一提取product_id进行计数。以下是两种可行的SQL写法：

方法一：单查询分支处理（推荐）

通过LEFT JOIN UNNEST条件展开数组，再用CASE统一提取产品ID：

SELECT
  product_id,
  COUNT(*) AS total_events,
  SUM(CASE WHEN event_name = 'viewed' THEN 1 ELSE 0 END) AS viewed_count,
  SUM(CASE WHEN event_name = 'carted' THEN 1 ELSE 0 END) AS carted_count,
  SUM(CASE WHEN event_name = 'ordered' THEN 1 ELSE 0 END) AS ordered_count
FROM
  your_table_name
-- 仅对ordered事件展开order_products数组
LEFT JOIN UNNEST(event_data.order_products) AS op(product_obj)
  ON event_name = 'ordered'
-- 统一提取product_id
CROSS JOIN (
  SELECT
    CASE
      WHEN event_name IN ('viewed', 'carted') THEN event_data.product_id
      WHEN event_name = 'ordered' THEN op.product_obj.product_id
    END AS product_id
) AS pid
WHERE
  product_id IS NOT NULL -- 过滤无效产品ID记录
GROUP BY
  product_id
ORDER BY
  total_events DESC;

方法二：UNION ALL分事件处理（易排查）

将三种事件分别统计后合并，适合需要单独验证每种事件数据的场景：

WITH event_stats AS (
  -- 统计viewed事件
  SELECT
    event_data.product_id AS product_id,
    SUM(1) AS viewed_count,
    0 AS carted_count,
    0 AS ordered_count
  FROM your_table_name
  WHERE event_name = 'viewed' AND event_data.product_id IS NOT NULL
  GROUP BY product_id

  UNION ALL

  -- 统计carted事件
  SELECT
    event_data.product_id AS product_id,
    0 AS viewed_count,
    SUM(1) AS carted_count,
    0 AS ordered_count
  FROM your_table_name
  WHERE event_name = 'carted' AND event_data.product_id IS NOT NULL
  GROUP BY product_id

  UNION ALL

  -- 统计ordered事件（展开数组）
  SELECT
    op.product_obj.product_id AS product_id,
    0 AS viewed_count,
    0 AS carted_count,
    SUM(1) AS ordered_count
  FROM your_table_name
  LEFT JOIN UNNEST(event_data.order_products) AS op(product_obj)
    ON event_name = 'ordered'
  WHERE event_name = 'ordered' AND op.product_obj.product_id IS NOT NULL
  GROUP BY product_id
)
SELECT
  product_id,
  SUM(viewed_count + carted_count + ordered_count) AS total_events,
  SUM(viewed_count) AS viewed_count,
  SUM(carted_count) AS carted_count,
  SUM(ordered_count) AS ordered_count
FROM event_stats
GROUP BY product_id
ORDER BY total_events DESC;

常见问题排查 #

如果UNNEST后无结果，按以下步骤检查：

1. 验证数组路径正确性：确认event_data.order_products的访问路径是否匹配实际数据结构，比如是否是event_data['order_products']（针对键名带特殊字符的情况），或嵌套更深的路径如event_data.order_info.order_products。
2. 检查ordered事件数据：运行以下查询确认是否存在非空的order_products数组：

SELECT event_data.order_products
FROM your_table_name
WHERE event_name = 'ordered'
LIMIT 10;

如果结果为空或NULL，说明该事件的数组数据本身不存在，需要确认数据采集逻辑。

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