Unnest Hit BigQuery - повторяющиеся значения)

Это может помочь:

SELECT
  date,
  channelGrouping,
  SUM(Revenue) Revenue,
  SUM(Shipping) Shipping,
  SUM(bounces) bounces,
  SUM(transactions) transactions,
  hostname,
  COUNT(date) sessions
FROM(
  SELECT 
    date,
    channelGrouping,
    totals.totaltransactionrevenue / 1e6 Revenue,
    ARRAY((SELECT DISTINCT page.hostname FROM UNNEST(hits) hits WHERE page.hostname IS NOT NULL)) hostnames,
    (SELECT SUM(hits.transaction.transactionshipping) / 1e6 FROM UNNEST(hits) hits) Shipping,
    totals.bounces bounces,
    totals.transactions transactions
  FROM `project_id.dataset_id.ga_sessions_*`
  WHERE 1 = 1
  AND ARRAY_LENGTH(ARRAY((SELECT DISTINCT page.hostname FROM UNNEST(hits) hits WHERE page.hostname IS NOT NULL))) > 0
  AND _TABLE_SUFFIX BETWEEN '20170601' AND '20170609'

  UNION ALL
  (...)

),
UNNEST(hostnames) hostname
GROUP BY
  date, channelGrouping, hostname

Обратите внимание, что в этом запросе я избегал применения операции UNNEST в поле hits и делаю это только внутри подзапросов.

Чтобы понять, почему это так, вы должны понять, как данные ga объединяется в BigQuery. Обратите внимание, что в основном у нас есть 2 типа данных: данные уровня session и уровень hits. Каждый клиент, посещающий ваш сайт, в конечном итоге создает строку в BigQuery, например:

{fullvisitorid: 1, visitid:1, date: '20170601', channelGrouping: "search", hits: [{hitNumber: 1, page: {hostname: "yourserverhostname"}}, {hitNumber: 2, page: {hostname: "yourserverhostname"}}, totals: {totalTransactionRevenue:0, bounces: 0}]

Если тот же клиент возвращается днем ​​позже, он генерирует другую строку в BQ, что-то вроде:

{fullvisitorid: 1, visitid:2, date: '20170602', channelGrouping: "search", hits: [{hitNumber: 1, page: {hostname: "yourserverhostname"}}, {hitNumber: 2, page: {hostname: "yourserverhostname"}}, totals: {totalTransactionRevenue:50000000, bounces: 2}]

Как видите, поля за пределами ключа hits связаны с уровнем сеанса (и поэтому каждое обращение, то есть каждое взаимодействие клиента на вашем веб-сайте, добавляет сюда еще одну запись). Когда вы применяете UNNEST, вы в основном применяете перекрестное соединение со всеми значениями внутри массива с внешними полями.

Именно здесь и происходит дублирование!

В предыдущем примере, если мы применим UNNEST к полю hits, вы получите что-то вроде:

fullvisitorid    visitid    totals.totalTransactionRevenue    hits.hitNumber
1                1          0                                 1
1                1          0                                 2
1                2          50000000                          1
1                2          50000000                          2

Обратите внимание, что для каждого попадания в поле hits внешние поля, такие как totals.totalTransactionRevenue, дублируются для каждого hitNumber, произошедшего внутри hits ARRAY.

Итак, если позже вы примените некоторую операцию типа SUM(totals.totalTransactionRevenue), вы в конечном итоге суммируете это поле, умноженное на каждое попадание, которое клиент имел в этом visitid.

Я стараюсь избегать (дорогостоящей, в зависимости от объема данных) операции UNNEST в поле hits, и я делаю это только в подзапросах (где деактивация происходит только на уровне строки, которая не дублирует данные).