作用域聚合和WHERE作用域

Question

下面的查询运行并生成16行输出(通过在SELECT count(*) FROM (query)中包装来验证)命中是重复的记录。hits.customDimensions在命中结果中重复出现。customDimensions在主记录中重复。

SELECT
  fullVisitorId,
  visitId,
  hits.page.pagePath,
  hits.type,
  FIRST(IF(customDimensions.index = 10, customDimensions.value, NULL)) WITHIN RECORD AS gacid,
  FIRST(IF(hits.customDimensions.index = 11, hits.customDimensions.value, NULL)) WITHIN hits AS blogCategories
FROM
  [dataset.ga_sessions_20160902]
WHERE
  fullVisitorId ='55555555555'

然而，

SELECT
  fullVisitorId,
  visitId,
  hits.page.pagePath,
  hits.type,
  FIRST(IF(customDimensions.index = 10, customDimensions.value, NULL)) WITHIN RECORD AS gacid,
  FIRST(IF(hits.customDimensions.index = 11, hits.customDimensions.value, NULL)) WITHIN hits AS blogCategories
FROM
  [dataset.ga_sessions_20160902]
WHERE
  fullVisitorId ='55555555555'
  AND hits.type = 'PAGE'

失败，错误为

Cannot query the cross product of repeated fields customDimensions.index and hits.type.

是否只返回了一条(未平整的)记录，并且我的包装计数没有给出真正的结果？为什么两个作用域聚合可以在不同的作用域上工作，而最内部作用域上的WHERE却失败了？

Answer 1

要避免产生交叉产品，请尝试下面的方法

SELECT
  fullVisitorId,
  visitId,
  hits.page.pagePath,
  hits.type,
  FIRST(IF(customDimensions.index = 10, customDimensions.value, NULL)) WITHIN RECORD AS gacid,
  FIRST(IF(hits.customDimensions.index = 11, hits.customDimensions.value, NULL)) WITHIN hits AS blogCategories
FROM [dataset.ga_sessions_20160902]
WHERE fullVisitorId ='55555555555'
HAVING hits.type = 'PAGE' 

顺便说一句，在Legacy SQL中，任何最外层的SELECT都会产生扁平化的结果(除非您将resuly写到表中，并使用相应的选项- large result和unflattened )-这解释了示例中的问题