BigQuery UNNEST无结果

Question

我对嵌套数据的概念比较陌生，正在尝试以正确的方式在BigQuery (https://support.google.com/analytics/answer/3437719?hl=en)中扁平化一些GA数据。

现在给出一些上下文，对于每个访问者会话，我试图捕获所查看的产品SKU的列表(详细视图)，如果存在事务，则捕获事务id。根据我的估计，在做了一些研究之后，最简单的方法如下所示，使用左连接来返回所有内容：

SELECT fullVisitorId as uId, visitId as vId, h.transaction.transactionId as 
trId, STRING_AGG(p.productSKU, "|") as skus
FROM
`test-bigquery.12345678.ga_sessions_*` t
  LEFT JOIN UNNEST(hits) h
  LEFT JOIN UNNEST(h.product) p
WHERE 
_TABLE_SUFFIX = '20170709'
AND h.eCommerceAction.action_type = '2'
GROUP BY uId, vId, trId

但是，在trId不为null的情况下，这似乎返回零结果。

然后，我尝试将上面的查询分成两个查询并连接。这似乎是可行的，并返回一个看似合理的行数(~1000)，其中trId不为null。

WITH skus AS
(SELECT fullVisitorId as uId, visitId as vId, STRING_AGG(p.productSKU, "|") as skus
    FROM
    `test-bigquery.12345678.ga_sessions_*` t
      LEFT JOIN UNNEST(hits) h
      LEFT JOIN UNNEST(h.product) p
    WHERE 
    _TABLE_SUFFIX = '20170709'
    AND h.eCommerceAction.action_type = '2'
    GROUP BY uId, vId),
transactions AS
  (SELECT fullVisitorId as uId_trans, visitId as vId_trans,  h.transaction.transactionId as trId
    FROM
    `test-bigquery.12345678.ga_sessions_*` t
      LEFT JOIN UNNEST(hits) h
    WHERE 
      _TABLE_SUFFIX = '20170709'
      AND h.transaction.transactionId IS NOT NULL
      GROUP BY uId_trans, vId_trans, trId)
SELECT skus.uId, skus.vId, transactions.trId, skus.skus
FROM skus
LEFT JOIN transactions ON transactions.vId_trans = skus.vId AND transactions.uId_trans = skus.uId

如果有人能解释为什么这两个人不能给出相同的答案，并希望能让我在未来参与到各种嵌套的乐趣中去，那就太棒了……谢谢!

Answer 1

您所犯的错误发生在以下代码行中：

AND h.eCommerceAction.action_type = '2'

如果您在字段action_type中检查ga sessions schema，您将看到当客户刚刚看到产品时它的值是'2‘，而当交易发生时它的值是'6’。因此，如果您只过滤掉等于'2‘的操作，那么您将无法获得事务，因为它们的值是'6’。

在您的第二个查询中，请注意，您对transactions的查询不再过滤出action为'2‘的地方，而是过滤掉了trasactionId不为null的地方，因此您现在成功地检索到了这些行。

仍然有一些方法可以极大地优化查询，例如：

SELECT 
  fullvisitorid,
  visitid,
  ARRAY(SELECT STRUCT(prods.productsku AS sku, MAX(IF(hits.ecommerceaction.action_type = '6', hits.transaction.transactionID, NULL)) AS transactionID) FROM UNNEST(hits) hits, UNNEST(hits.product) prods WHERE hits.ecommerceaction.action_type IN ('2', '6') GROUP BY prods.productsku) result
FROM `test-bigquery.12345678.ga_sessions_*`
WHERE TRUE
  AND _TABLE_SUFFIX = '20170709'
  AND EXISTS(SELECT 1 FROM UNNEST(hits) hits WHERE hits.ecommerceaction.action_type IN ('2', '6'))
LIMIT 1000

这个查询产生与您的相同的结果，但是它更简洁，性能更好(它避免了不必要的JOIN和UNNEST操作，并且很好地利用了数据中的数组和结构)。

我强烈建议您学习如何将这些技术用于嵌套数据，因为您将能够在几秒钟内查询数百千兆的数据。以下是我在数据集中得到的结果：

​

​

它在15秒内处理了超过500 in的数据。