google-analytics - 大查询 : two hitlevel custom dimensions

Question

我似乎无法获得一个查询，它为我提供了在同一命中中 customdimensionX 具有值 X 和 customdimensionY 具有值 Y 的所有 session 。我目前的查询结果未找到任何结果。

有人可以帮助我吗:)？
谢谢!

SELECT  sum(totals.visits)

from TABLE_DATE_RANGE([xxxx.ga_sessions_], TIMESTAMP('2016-3-1'),TIMESTAMP('2016-3-1'))

WHERE 
  (hits.customDimensions.index=x AND hits.customDimensions.value='x') 
  AND (hits.customDimensions.index=y AND hits.customDimensions.value='y')

Answer 1

回答我自己的问题有点奇怪，但它可能对其他人有用:) 我通过以下方式找到了正确的号码:

SELECT EXACT_COUNT_DISTINCT(uniqueVisitId) as sessions
FROM( 
 SELECT
   CONCAT(fullvisitorid,"_",string(visitId)) AS uniqueVisitId,
   MAX(IF(hits.customDimensions.index=x,hits.customDimensions.value,NULL)) WITHIN hits AS x,
   MAX(IF(hits.customDimensions.index=y,hits.customDimensions.value,NULL)) WITHIN hits AS y,
   hits.hitNumber

   FROM TABLE_DATE_RANGE([xxxxxx.ga_sessions_], TIMESTAMP('2016-3-1'),TIMESTAMP('2016-3-1'))

   having
    (x contains 'x' and y contains 'y')
)