google-analytics - 解除自定义维度的嵌套会导致 BigQuery 中的交易收入重复/膨胀

Question

取消嵌套hits.customdimension和hits.product.customdimension会增加交易收入

SELECT
     sum(totals.totalTransactionRevenue)/1000000 as revenue,
     (SELECT MAX(IF(index=10,value,NULL)) FROM UNNEST(product.customDimensions)) AS product_CD10,
     (SELECT MAX(IF(index=1,value,NULL)) FROM UNNEST(hits.customDimensions)) AS CD1
    FROM
      `XXXXXXXXXXXXXXX.ga_sessions_*`, 
      UNNEST(hits) AS hits, 
      UNNEST(hits.product) as product
     WHERE
      _TABLE_SUFFIX BETWEEN "20180608"
      AND "20180608"
      group by product_CD10,CD1

有没有一种方法可以让我获得一张平坦的 table ，如果我应用收入总额，它应该给出正确的结果。

Answer 1

将 UNNEST() 移至顶部子查询 - 这样行就不会重复:

SELECT row
 ,  (SELECT MAX(letter) FROM UNNEST(row), UNNEST(qq)) max_letter
 ,  (SELECT MAX(n) FROM UNNEST(row), UNNEST(qq), UNNEST(qb) n) max_number
FROM (
  SELECT [
    STRUCT(1 AS p,[STRUCT('a' AS letter, [4,5,6] AS qb)] AS qq)
    , STRUCT(2,[STRUCT('b', [7,8,9])])
    , STRUCT(3,[STRUCT('c', [10,11,12])])
   ] AS row
)

还没有测试过这个:

SELECT
 sum(totals.totalTransactionRevenue)/1000000 as revenue,
 (SELECT MAX(IF(index=10,value,NULL)) FROM UNNEST(hits) AS hit, UNNEST(hit.products) product,  UNNEST(product.customDimensions)) AS product_CD10,
 (SELECT MAX(IF(index=1,value,NULL)) FROM UNNEST(hits) AS hit, UNNEST(hit.customDimensions)) AS CD1
FROM `XXXXXXXXXXXXXXX.ga_sessions_*`, 
WHERE _TABLE_SUFFIX BETWEEN "20180608" AND "20180608"
group by product_CD10,CD1