如何在BigQuery中结合Cross Join与String Agg拆分用户路径？

嘿，我来帮你搞定这个BigQuery里的路径拆分问题！

你的需求是把包含多个Purchase的用户路径，拆分成每个Purchase截止的独立行，这个用正则提取+UNNEST就能完美解决，比用STRING_AGG加CASE要直接得多。

具体实现SQL #

-- 替换成你的实际表名
WITH your_table AS (
  SELECT 1 AS user_id, 'Impression,Impression,Purchase, Impression, Email, Purchase' AS path
)
SELECT
  user_id,
  TRIM(path_segment) AS path
FROM your_table,
-- 提取所有以Purchase结尾的路径片段
UNNEST(REGEXP_EXTRACT_ALL(path, r'(?:[^,]+,)*?[^,]+Purchase')) AS path_segment

代码解释 #

1. 正则匹配逻辑：REGEXP_EXTRACT_ALL用正则(?:[^,]+,)*?[^,]+Purchase精准提取每个到Purchase为止的子路径：
   • (?:[^,]+,)*?：非贪婪匹配零个或多个「渠道名+逗号」的组合，确保我们只抓到最近的Purchase之前的内容
   • [^,]+Purchase：匹配最后一个渠道（包括Purchase本身）
2. 拆分成多行：UNNEST把提取出来的片段数组转换成独立的行，每个片段对应一条用户记录
3. 清理空格：TRIM处理片段里可能存在的空格（比如原路径中Purchase后面的逗号带空格的情况）

为什么之前的方法没生效？ #

STRING_AGG是用来合并多行内容成字符串的聚合函数，而你的需求是拆分字符串生成多行，方向刚好相反，所以用CASE配合它自然达不到预期。改用拆分+展开的思路就完全贴合需求啦。

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