sql - 为什么BigQuery中的case语句无法达到预期的效果？

我正在学习BigQuery，并且对SQL有一些了解。我和我的同事都建立了一个查询，以计算我们站点上已看到eventInfo.eventCategory = "view"和hits.eventInfo.eventAction = "basket"特定事件的会话数。我使用了case语句，他们使用了带有联接的子查询。

带有case语句的版本给出的答案与Google Analytics（分析）中给出的答案不同，在使用子查询和左联接的版本中给出匹配的结果（这是我们正在寻找的结果）。

第一个查询是否有问题，或者我对案例陈述和实现的理解有误？

我之所以选择使用case语句，是因为我们使用了许多事件来构建漏斗，并且我认为添加更多case语句比编写子查询和联接要容易得多。

我们将不胜感激，以帮助您理解为什么它们会产生不同的结果。

（这是我的第一篇文章，所以我希望我提供的信息足够/不太多）。

使用case语句的版本：

#standardSQL

select
  count(CONCAT(t1.fullvisitorid, CAST(t1.visitID AS string))) AS sessions
  ,case
    when (lower(hits.eventInfo.eventCategory) = "view"
      and lower(hits.eventInfo.eventAction) = "basket")
      then "Basket"
    end
      as funnel_stage
  ,hits.eventInfo.eventCategory
  ,hits.eventInfo.eventAction
  ,t1.date as _date
FROM
  `table_name` t1
  ,UNNEST(hits) hits
where totals.visits = 1
group by _date
  ,hits.eventInfo.eventCategory
  ,hits.eventInfo.eventAction
having funnel_stage is not null

使用子查询和联接的版本：

#standardSQL

select
count(distinct(s.session)) as All_Sessions,
count(distinct(e.session)) as Segment
from
(
  select CONCAT(fullvisitorid, CAST(visitID AS string)) as session
  from `table_name`
) s
left join
(
select CONCAT(fullvisitorid, CAST(visitID AS string)) as session
  from `table_name`,
  unnest(hits) h
where
lower(eventInfo.eventCategory) = 'view'
AND lower(eventinfo.eventAction) = 'basket'
) e

on s.session = e.session

最佳答案

查找特定事件的方法是cross join具有子表/数组hits的表。但是，由于您希望在没有命中级别的情况下对会话进行计数，因此不应将表扩展到命中范围。

而是使用子查询访问这些子表/数组-您可以在SELECT中使用它们来创建段的种类，或者在WHERE中使用它们来直接进行过滤。

细分方法（使用GA样本数据集-这是一个不同的事件）：

SELECT
  date,
  (SELECT
    coalesce( LOGICAL_OR(eventinfo.eventcategory = 'Enhanced Ecommerce'
      AND eventinfo.eventaction = 'Add to Cart'),false) FROM t.hits) hasAdd2CartEvent,
   SUM(totals.visits) AS sessions
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_201707*` t
GROUP BY
  1,2
ORDER BY 1,2

如果在会话中根本找不到任何要导致false的事件，则Coalesce只是消除了NULL值。

WHERE方法使用相同的子查询：

SELECT
  date,
  SUM(totals.visits) AS sessions
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_201707*` t
WHERE
  (SELECT
    coalesce( LOGICAL_OR(eventinfo.eventcategory = 'Enhanced Ecommerce'
      AND eventinfo.eventaction = 'Add to Cart'),false) FROM t.hits)
GROUP BY
  1
ORDER BY 1

希望这使您能够编写更强大的查询:)

编辑：
万一您需要交叉参加命中范围并计算会话数，则必须COUNT(DISTINCT CONCAT(fullvisitorid, CAST(visitstarttime AS string))) AS sessions