sql - 显示来自在其 session 中看到X页的用户的交易

我正在使用BigQuery中的Google Analytics（分析）数据。

我想显示在其会话中访问过网站特定页面的用户的交易ID列表，为了识别特定页面，我未设置hits.page.pagepath，但是由于我不知道我在返回有意义的结果时会遇到实际的交易ID。

我的代码看起来像这样，但由于所有事务Id均为NULL值，因此返回0的结果，因为它们不在页面路径满足AND hits.page.pagePath LIKE "%clear-out%"条件的行上发生：

SELECT hits.transaction.transactionId AS orderid
FROM `xxx.xxx.ga_sessions_20*` AS t
  CROSS JOIN UNNEST(hits) AS hits
WHERE parse_date('%y%m%d', _table_suffix) between
DATE_sub(current_date(), interval 1 day) and
DATE_sub(current_date(), interval 1 day)
AND totals.transactions  > 0
AND hits.page.pagePath LIKE "%clear-out%"
AND hits.transaction.transactionId IS NOT NULL

例如，如何说出用户查看AND hits.page.pagePath LIKE "%clear-out%"的所有会话的交易ID？

最佳答案

交叉加入时，您将为每个匹配重复整个会话。每次点击使用此嵌套信息来查找您的页面，而不是交叉连接的点击。
不幸的是，您给两个名字相同。最好将它们分开-这是可能的样子：

SELECT
  h.transaction.transactionId AS orderId
  --,ARRAY( (SELECT AS STRUCT hitnumber, page.pagePath, transaction.transactionId FROM t.hits ) ) AS hitInfos -- test: show all hits in this session
FROM
  `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910` AS t
  CROSS JOIN t.hits AS h
WHERE
  totals.transactions > 0 AND h.transaction.transactionId IS NOT NULL
  AND
  -- use the repeated hits nest (not the cross joined 'h') to check all pagePaths in the session
  (SELECT LOGICAL_OR(page.pagePath LIKE "/helmets/%") FROM t.hits )

LOGICAL_OR()是OR的聚合函数-因此，如果命中匹配条件，则返回TRUE

（此查询使用来自Google的公开可用的GA数据。虽然年代久远，但可以使用。）