在BigQuery中获取各会话记录类型的最后匹配测试ID

没问题，我来帮你搞定这个需求！针对你提供的嵌套数组结构数据，我们可以通过BigQuery的数组处理和正则提取功能来实现目标，下面是具体的实现方案：

核心思路 #

• 展开嵌套数组：把每个会话的pagesViewed数组拆分成独立的行，方便逐个处理页面URL。
• 提取Test参数：用正则表达式从页面URL中提取test参数的值。
• 筛选并取最后一个：针对每个会话，按hit序号倒序排列，取第一个有效的test ID（也就是hit最大的那个页面的test值）。

实现代码（推荐方案） #

这个方案使用QUALIFY子句，语法简洁且性能优秀，同时能自动过滤掉没有test参数的会话：

WITH expanded_sessions AS (
  SELECT 
    sessionID,
    revenue,
    pv.hit,
    -- 提取test参数的值，处理URL中可能存在的其他参数
    REGEXP_EXTRACT(pv.val, r'test=([^&]+)') AS test_id
  FROM 
    -- 替换成你的项目、数据集和表名
    `your-project.your-dataset.your-table`,
    UNNEST(pagesViewed) AS pv
)
SELECT 
  sessionID,
  revenue,
  test_id AS last_test_id
FROM expanded_sessions
WHERE test_id IS NOT NULL
-- 按会话分组，每个会话取hit最大的那个test ID
QUALIFY ROW_NUMBER() OVER (PARTITION BY sessionID ORDER BY hit DESC) = 1
ORDER BY sessionID;

代码解释 #

• UNNEST(pagesViewed) AS pv：把每个会话的pagesViewed数组展开成多行，每行对应一个页面访问记录。
• REGEXP_EXTRACT(pv.val, r'test=([^&]+)')：正则表达式匹配test=后面的所有字符，直到遇到&（其他参数的分隔符）或字符串结尾，精准提取test参数的值。
• QUALIFY ROW_NUMBER() OVER (...) = 1：按会话ID分组，组内按hit序号倒序排列，取第一行数据，也就是该会话中最后出现（hit最大）的test ID。

可选方案：保留所有会话（包括无test参数的） #

如果需要保留没有任何test参数的会话，并返回NULL作为last_test_id，可以用下面的代码：

SELECT
  sessionID,
  revenue,
  -- 从按hit倒序排列的数组中取第一个非空的test_id
  (SELECT test_id 
   FROM UNNEST(ARRAY_AGG(STRUCT(hit, test_id) ORDER BY hit DESC)) 
   WHERE test_id IS NOT NULL 
   LIMIT 1).test_id AS last_test_id
FROM (
  SELECT 
    sessionID,
    revenue,
    pv.hit,
    REGEXP_EXTRACT(pv.val, r'test=([^&]+)') AS test_id
  FROM 
    `your-project.your-dataset.your-table`,
    UNNEST(pagesViewed) AS pv
)
GROUP BY sessionID, revenue
ORDER BY sessionID;

这个方案会返回所有会话，即使某个会话没有包含test参数的页面，last_test_id会显示为NULL。

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