如何从事件中的多个参数中取出数据，且其中一个参数的值在事件中相同 [英] How do I take out data from an event for multiple parameters with value of one parameter being the same in the event
问题描述
例如
event_dim.name = "Start_Level"
event_dim.params.key = "Chapter_Name"
event_dim.params.value.string_value = "chapter_1" (or "chapter_2" or "chapter_3" and so on)
event_dim.params.key = "Level"
event_dim.params.value.int_value = 1 or 2 or 3 or 4 and so on
event_dim.params.key = "Opening_Balance"
event_dim.params.value = 1000 or 1200 or 300 or so on
如果要执行以下操作，如何取出数据: -查看仅为event_dim.params.string_value ="chapter_1"玩过关卡"的唯一用户(这表示第1章中的关卡) -仅在"event_dim.params.key ="Chapter_Name"和event_dim.params.value.string_value ="chapter_2"
How do I take out the data if I want to: - Look at unique users who've played "Level" only for event_dim.params.string_value = "chapter_1" (meaning for levels in Chapter 1) - Look at the "Opening_Balance" per "Level" only the levels in the chapter where event_dim.params.key = "Chapter_Name" and event_dim.params.value.string_value = "chapter_2"
目前，我正在尝试按以下方式进行操作，以获取我认为无法提供适当数据的数据.我正在尝试从特定日期(通过first_open)到特定来源之间安装游戏的用户获取关卡数据.
Currently, I am trying to do it as below to grab the data which I don't think is giving me proper data. I am trying to take out level data for users who've installed the game between a particular date (through first_open) and from a particular source.:
SELECT
COUNT(DISTINCT(app_instance)),
event_value.int_value
FROM (
SELECT
user_dim.app_info.app_instance_id AS app_instance,
event.name AS event,
(
SELECT
user_prop.value.value.int_value
FROM
UNNEST(user_dim.user_properties) AS user_prop
WHERE
user_prop.key = 'first_open_time') AS first_open,
params.key AS event_param,
params.value AS event_value
FROM
`app_package.app_events_*`,
UNNEST(event_dim) AS event,
UNNEST(event.params) AS params
WHERE
event.name = "start_level"
AND user_dim.traffic_source.user_acquired_source = "source"
AND params.key != 'firebase_event_origin'
AND params.key != 'firebase_screen_class'
AND params.key != 'firebase_screen_id' )
WHERE
event_param = "Level"
AND (first_open >= 1516579200000 AND first_open <= 1516924800000)
GROUP BY
event_value.int_value
但是，我无法隔离特定于该事件中的Chapter_name ="chapter_1"的事件. (不幸的是，我不知道该怎么办，因此是个问题)
However, I am not able to segregate events which are specific to when chapter_name = "chapter_1" in the event. (I don't know how to do it unfortunately and hence the question)
更新:(根据米哈伊尔的要求添加了一些其他信息)
Update: (Some additional information added as requested by Mikhail)
示例输入事件如下:
+-----------------+-------------+-----------------+--------------+-----------+
| app_instance_id | event_name | param_key | string_value | int_value |
+-----------------+-------------+-----------------+--------------+-----------+
| 100001 | start_level | chapter_name | chapter_1 | null |
| | | level | null | 1 |
| | | opening_balance | null | 2000 |
| | start_level | chapter_name | chapter_1 | null |
| | | level | null | 2 |
| | | opening_balance | null | 2500 |
| | start_level | chapter_name | chapter_1 | null |
| | | level | null | 2 |
| | | opening_balance | null | 2750 |
| | start_level | chapter_name | chapter_1 | null |
| | | level | null | 3 |
| | | opening_balance | null | 3000 |
| | start_level | chapter_name | chapter_2 | null |
| | | level | null | 1 |
| | | opening_balance | null | 3100 |
| | start_level | chapter_name | chapter_2 | null |
| | | level | null | 2 |
| | | opening_balance | null | 3500 |
| | start_level | chapter_name | chapter_2 | null |
| | | level | null | 3 |
| | | opening_balance | null | 3800 |
| 100002 | start_level | chapter_name | chapter_1 | null |
| | | level | null | 1 |
| | | opening_balance | null | 2000 |
| | start_level | chapter_name | chapter_1 | null |
| | | level | null | 2 |
| | | opening_balance | null | 2250 |
| | start_level | chapter_name | chapter_1 | null |
| | | level | null | 2 |
| | | opening_balance | null | 2400 |
| | start_level | chapter_name | chapter_1 | null |
| | | level | null | 3 |
| | | opening_balance | null | 2800 |
| | start_level | chapter_name | chapter_2 | null |
| | | level | null | 1 |
| | | opening_balance | null | 3000 |
| | start_level | chapter_name | chapter_2 | null |
| | | level | null | 2 |
| | | opening_balance | null | 3200 |
+-----------------+-------------+-----------------+--------------+-----------+
所需的输出如下:
+-----------+-------+--------------+-------------------+---------------+
| Chapter | Level | Unique Users | Total Level Start | Avg. Open Bal |
+-----------+-------+--------------+-------------------+---------------+
| chapter_1 | 1 | 2 | 2 | 2000 |
| chapter_1 | 2 | 2 | 3 | 2383 |
| chapter_1 | 3 | 2 | 3 | 2850 |
| chapter_2 | 1 | 2 | 2 | 3050 |
| chapter_2 | 2 | 2 | 2 | 3350 |
| chapter_2 | 3 | 1 | 1 | 3800 |
+-----------+-------+--------------+-------------------+---------------+
推荐答案
对于正在寻找该问题答案的任何人，您都可以尝试下面的标准sql查询:
For anyone who is looking for an answer to this question, you can try the below standard sql query:
SELECT
chapter,
level,
count(distinct id) as Unique_Users,
count(id) as Level_start,
avg(opening_balance) as Avg_Open_Bal,
FROM(
SELECT
user_dim.app_info.app_instance_id AS id,
event.date,
event.name,
(SELECT value.string_value FROM UNNEST(event.params) WHERE key = "chapter_name") AS chapter,
(SELECT value.int_value FROM UNNEST(event.params) WHERE key = "level") AS level,
(SELECT value.int_value FROM UNNEST(event.params) WHERE key = "opening_coin_balance") AS open_bal
FROM
`<table_name>`,
UNNEST(event_dim) AS event
WHERE
event.name = "start_level"
)
GROUP BY
chapter,
level
这篇关于如何从事件中的多个参数中取出数据，且其中一个参数的值在事件中相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
