google-analytics - 取消自定义维度后，就会在BigQuery中复制/增加交易收入

毫无保留的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

有没有一种方法可以使我得到一个平板，如果我应用收入总和，它应该给出正确的结果。

最佳答案

将您的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

关于google-analytics - 取消自定义维度后，就会在BigQuery中复制/增加交易收入，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50879444/