时间戳

首页 > 解决方案 > 如何在 Google Big Query 中将两个值与 SQL 进行比较？

select - 如何在 Google Big Query 中将两个值与 SQL 进行比较？

问题描述

我正在尝试从 Google Big Query 数据库中获取在不同列中具有相同值的所有记录。比方说，当从手机发送一些事件时，我将变量设置machine_name为 firebase user_properties。然后我发送事件event_notification_send。当我查询表时 - 我想从数据库中获取所有数据，其名称为事件的名称event_notification_send，该事件的参数machine_name具有某个值 X1，并且该记录必须同时具有一个参数 in user_properties，键Last_notification具有相同的值 X1。

我该怎么做那个 SQL 查询？

谢谢。

这是我的代码示例：

#standardSQL
SELECT *
FROM 
`myProject.analytics_159820162.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20180725' AND '20180727' 
AND event_name in ("event_notification_received", "event_notification_dissmissed")
AND platform = "ANDROID"
AND
(SELECT COUNTIF((key = "machine_name")) 
              FROM UNNEST(event_params) 
) > 0  -- to see if specified event has such key
AND
(SELECT COUNTIF((key = "Last_notification")) 
              FROM UNNEST(user_properties) 
) > 0  -- to see if specified event has such key
ORDER BY event_timestamp ASC

标签： selectgoogle-bigqueryfirebase-cloud-messagingcountifunnest

解决方案

要检查行/事件是否具有具有相同值的参数“machine_name”和“Last_notification”，您可以使用以下语句 

SELECT COUNT(DISTINCT key) cnt
FROM UNNEST(event_params) 
WHERE key IN ("machine_name", "Last_notification")
GROUP BY value
ORDER BY cnt DESC
LIMIT 1

假设您的其余查询是正确的 - 下面将您的条件添加到它 

#standardSQL
SELECT *
FROM `myProject.analytics_159820162.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20180725' AND '20180727' 
AND event_name IN ("event_notification_received", "event_notification_dissmissed")
AND platform = "ANDROID"
AND (
    SELECT COUNT(DISTINCT key) cnt
    FROM UNNEST(event_params) 
    WHERE key IN ("machine_name", "Last_notification")
    GROUP BY value
    ORDER BY cnt DESC
    LIMIT 1
  ) = 2  
ORDER BY event_timestamp ASC

注意：下面使用只是为了安全起见，以防事件有多个参数具有相同的键但不同的值 

ORDER BY cnt DESC
LIMIT 1

推荐阅读