BigQuery使用CASE语句时同日期输出两行问题求解

你在运行BigQuery查询时碰到了同一日期对应两行结果的情况——一行值是0，另一行是事件统计数，我来帮你分析下问题出在哪，以及怎么解决。

先看看你原来的查询语句：

(Select distinct(case when event_text = 'poll_vote' THEN device_id Else 0 END) as pollvote,event_date from (Select event_date,event_text,count(distinct users) as device_id from (SELECT event.name as event_text, ( user.value.value.string_value) AS users, CAST(TIMESTAMP_ADD(TIMESTAMP_MICROS(event.timestamp_micros), INTERVAL 330 MINUTE) AS date) AS event_date FROM `dataset.tablename`, UNNEST(event_dim) AS event, UNNEST(user_dim.user_properties) AS user where user.key="context_device_id" GROUP BY event_date,event_text,users) GROUP BY event_text,event_date))

问题原因分析 #

你这里用了case when event_text = 'poll_vote' THEN device_id Else 0 END，再加上distinct，会把两种情况完全分开：

• 当event_text是poll_vote时，返回统计得到的device_id数值
• 所有其他event_text的情况，都会返回0
  因为distinct的存在，同一日期下这两种结果会各自保留一行，所以就出现了同一个日期对应两行的情况。

解决方案 #

我们需要调整查询逻辑，用条件聚合来实现“每个日期只返回一行，值为当天poll_vote事件的设备统计数，无数据则返回0”的需求。修改后的查询语句如下：

SELECT
  event_date,
  COUNT(DISTINCT CASE WHEN event_text = 'poll_vote' THEN users END) AS pollvote
FROM (
  SELECT
    event.name AS event_text,
    user.value.value.string_value AS users,
    CAST(TIMESTAMP_ADD(TIMESTAMP_MICROS(event.timestamp_micros), INTERVAL 330 MINUTE) AS date) AS event_date
  FROM `dataset.tablename`,
    UNNEST(event_dim) AS event,
    UNNEST(user_dim.user_properties) AS user
  WHERE user.key = "context_device_id"
)
GROUP BY event_date

调整说明 #

1. 内层查询和你原来的一致，负责提取基础数据：事件名称、设备ID、转换后的日期
2. 外层直接按event_date分组，用COUNT(DISTINCT CASE WHEN ...)来统计仅poll_vote事件的设备数——如果当天没有这个事件，case语句会返回null，count不会统计null，所以结果就是0
3. 去掉了多余的中间分组和distinct，逻辑更简洁，同时保证每个日期只有一行结果

这样修改后，你就能得到每个日期唯一一行的结果，值为当天poll_vote事件的设备统计数，没有数据的日期会显示0，不会再出现同一日期两行的情况啦。

内容的提问来源于stack exchange，提问作者Vishu