Bigquery & Firebase Analytics - 如何根据不同的参数选择一个事件

Question

我试图创建一个查询，让我也能看到firebse_event_origin="自动"和firebase_screen_class=TicketViewController的次数。

我尝试如下图所示放置 AND 语句，但我没有得到任何结果。(附言在某些情况下会发生(。

https://i.stack.imgur.com/XyX2R.png

基本上我想知道的是： 当firebase_event_origin = "auto" firebase_screen_class = "TicketViewController"
时
发生了多少screen_view事件

Answer 1

这可能是您正在寻找的，或者可能会为您提供一些见解：

SELECT
count(1) screen_views
FROM `ruter-pilot.no_ruter_RuterPilot_WPI_IOS.app_events_20170816`
WHERE EXISTS(SELECT 1 FROM UNNEST(event_dim) ed WHERE ed.name = 'user_engagement' AND (EXISTS(SELECT 1 FROM UNNEST(ed.params) params WHERE params.key = 'firebase_event_origin' AND value.string_value = 'auto')) AND(EXISTS(SELECT 1 FROM UNNEST(ed.params) params WHERE params.key = 'firebase_screen_class' AND value.string_value = 'TicketViewController')))

您可以使用模拟数据：

WITH data AS(
SELECT ARRAY< STRUCT<name STRING, params ARRAY< STRUCT<key STRING, value STRUCT<string_value STRING> > > > > [STRUCT('user_engagement' AS name, [STRUCT('key' AS key, STRUCT('value' AS string_value) AS value ), STRUCT('firebase_event_origin' AS key, STRUCT('auto' AS string_value) AS value )] AS params)] event_dim UNION ALL
SELECT ARRAY< STRUCT<name STRING, params ARRAY< STRUCT<key STRING, value STRUCT<string_value STRING> > > > > [STRUCT('user_engagement' AS name, [STRUCT('firebase_event_origin' AS key, STRUCT('auto' AS string_value) AS value ), STRUCT('firebase_screen_class' AS key, STRUCT('TicketViewController' AS string_value) AS value )] AS params)] event_dim UNION ALL
SELECT ARRAY< STRUCT<name STRING, params ARRAY< STRUCT<key STRING, value STRUCT<string_value STRING> > > > > [STRUCT('user_engagement' AS name, [STRUCT('firebase_event_origin' AS key, STRUCT('auto' AS string_value) AS value ), STRUCT('firebase_screen_class' AS key, STRUCT('TicketViewController' AS string_value) AS value ), STRUCT('firebase_event_origin' AS key, STRUCT('auto' AS string_value) AS value ), STRUCT('firebase_screen_class' AS key, STRUCT('TicketViewController' AS string_value) AS value )] AS params)] event_dim UNION ALL
SELECT ARRAY< STRUCT<name STRING, params ARRAY< STRUCT<key STRING, value STRUCT<string_value STRING> > > > > [STRUCT('user_not_engagement' AS name, [STRUCT('key' AS key, STRUCT('value' AS string_value) AS value ), STRUCT('firebase_event_origin' AS key, STRUCT('auto' AS string_value) AS value )] AS params)] event_dim
)
SELECT
count(1) screen_views
FROM data
WHERE EXISTS(SELECT 1 FROM UNNEST(event_dim) ed WHERE ed.name = 'user_engagement' AND (EXISTS(SELECT 1 FROM UNNEST(ed.params) params WHERE params.key = 'firebase_event_origin' AND value.string_value = 'auto')) AND(EXISTS(SELECT 1 FROM UNNEST(ed.params) params WHERE params.key = 'firebase_screen_class' AND value.string_value = 'TicketViewController')))

基本上，查询会查找其给定事件名称同时触发指定params的用户，并计算发生这种情况的所有行。