google-analytics - Google Bigquery标准SQL-如何:如果匹配包含sku，则选择hitnumber，否则选择max(hitnumber)？

select
  concat(fullvisitorid,cast(visitid as string)) as unique_session_id
  ,case
    when h.item.productSku is not null then h.hitNumber
    else max(h.hitnumber)
   end
  ,h.item.transactionid
  ,h.item.itemrevenue/pow(10,6)
  ,h.item.productSku
from `myproject.mydataset.ga_sessions_20180101`, unnest(hits) as h
group by 1

查看上面的案例陈述（第3行）


如何在productsku所在的位置返回该hitnumber
否则，返回最大点击数，然后将其按unique_session_id分组？
如何同时过滤包含“ _ABC”的transactionid？

最佳答案

我建议进行分组并在子查询中找到最大匹配数。如果要在select子句中使用MAX()之类的聚合函数，则需要对select中的其他字段进行分组或具有聚合函数。使用公用表表达式进行汇总子查询可能很有用。

WITH data AS (
  SELECT
    CONCAT(fullvisitorid, CAST(visitid AS string)) AS unique_session_id,
    h.hitNumber,
    h.item.transactionid,
    h.item.itemrevenue/POW(10,6) AS itemRevenue,
    h.item.productSku
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
    UNNEST(hits) AS h
),
max_hits AS (
  SELECT
    unique_session_id,
    MAX(hitNumber) AS max_hit_number
  FROM data
  GROUP BY 1
)
SELECT
  d.unique_session_id,
  CASE
    WHEN d.productSku IS NOT NULL THEN d.hitNumber
    ELSE m.max_hit_number
  END,
  d.transactionid,
  d.itemrevenue,
  d.productSku
FROM
  data AS d JOIN max_hits AS m
ON d.unique_session_id = m.unique_session_id

关于google-analytics - Google Bigquery标准SQL-如何:如果匹配包含sku，则选择hitnumber，否则选择max(hitnumber)？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50912264/