google-analytics - BigQuery：过滤表后出现意外结果

Question

给定以下查询（非常简化）：

SELECT hits.page.pagepath AS Page
FROM
    `[projectid].[datasetid].ga_sessions_*` t, t.hits as hits
  WHERE
  _TABLE_SUFFIX BETWEEN '20190123' AND '20190123' 
  AND (SELECT COUNT(*)>0 FROM t.hits WHERE REGEXP_CONTAINS(hits.page.pagepath,r'dames'))

我预计此查询仅返回包含“女士”的页面，但实际上并非如此。在 WHERE 部分使用此过滤器..

(SELECT COUNT(*)>0 FROM t.hits WHERE REGEXP_CONTAINS(hits.page.pagepath,r'dames'))

..在命中级别上被压平，并且仅在女士页面上被过滤。在主查询中，命中级别也被展平。所以我希望每次点击都会有 TRUE 和 FALSE，其中只有 TRUE 保留在最终数据集中，即只有包含“dames”的页面。

我知道确实返回预期输出的查询，但我的主要问题（纯粹是为了理解为什么这个查询不工作）实际上更多：为什么这个查询不能按预期工作？

提前致谢！

标签： google-analyticsgoogle-bigquery

Answer 1

您必须了解，将未嵌套数组与其父行交叉连接并不能完全展平源表。它为数组中的每一行重复父行：在这种情况下，每次命中都会重复每个会话信息：命中数组本身也是如此！

这意味着对于每一次点击，您都可以在整个会话中查找内容，因为对于每一次点击，都有所有可用点击，因为它们也被重复了。

您正在您的WHERE子句中访问这个重复的 hits 数组。

您不想在此重复数组上编写子选择，而是要使用该数组中新可用的交叉连接字段，即AND REGEXP_CONTAINS(hits.page.pagepath,r'dames')

在您的情况下可能有点令人困惑，因为您的扁平化命中的别名也是命中 - 您可能需要考虑将其重命名为不同的名称，h这样您的不工作查询看起来像这样

SELECT h.page.pagepath AS Page
FROM
    `[projectid].[datasetid].ga_sessions_*` t, t.hits as h
  WHERE
  _TABLE_SUFFIX BETWEEN '20190123' AND '20190123' 
  AND (SELECT COUNT(*)>0 FROM t.hits h2 WHERE REGEXP_CONTAINS(h.page.pagepath,r'dames'))

您正在检查每个页面是否整个会话包含满足您条件的页面。

工作示例是

SELECT h.page.pagepath AS Page
FROM
    `[projectid].[datasetid].ga_sessions_*` t, t.hits as h
  WHERE
  _TABLE_SUFFIX BETWEEN '20190123' AND '20190123' 
  AND REGEXP_CONTAINS(h.page.pagepath,r'dames')