时间戳

首页 > 解决方案 > 根据 event.key 统计 BigQuery event.params

sql - 根据 event.key 统计 BigQuery event.params

问题描述

我正在尝试计算根据其参数过滤的事件。

例如，假设我有以下内容。对于那些好奇的人来说，这是真正的。 https://i.imgur.com/rPwmR9i.png

event_date | event_timestamp | event_name | event_params.key | event_params.value.string_value
-----------------------------------------------------------------------------------------------
some_date  | some_timestamp  | some_name  | some_key_1       | some_string_1
                                          | some_key_2       | some_string_2
                                          | some_key_3       | some_string_3
                                          | some_key_4       | some_string_4

event_params.key 和 event_params.value 是一个值数组。我需要过滤掉该数组中与我的密钥不匹配的任何内容。此外，我需要计算特定字符串值的每次出现。我试图做类似以下的事情：

 SELECT DISTINCT event_name,
(SELECT value.string_value FROM UNNEST(event_params)
  WHERE key = 'relationship_lenght') AS Length,
(SELECT value.string_value FROM UNNEST(event_params)
  WHERE key = 'relationship_location') AS Location,
(SELECT value.string_value FROM UNNEST(event_params)
  WHERE key = 'relationship_gender') AS Gender

FROM `stormbreaker-studios.analytics_243434300.events_20200902`

WHERE 
  event_name = 'relationship_created_gender' OR
  event_name = 'relationship_created_location' OR
  event_name = 'relationship_created_interests'

LIMIT 10

它返回null所有列。我期望得到的是沿着这条线的东西，水平或垂直，没有区别。

预期产出

Male | Female | Trans | Non-Binary | New York | San Antonio | 1-5mo | 5-10mo |
------------------------------------------------------------------------------
10   |   11   |   1   |     3      |     3    |      17     |   1   |    61  |

每列的每一Count列都派生自上例中关联的值some_key。该值是静态的，来自已知的列表。例如， 的值relationship_gender可能是Male,Female等。我会比较以匹配它。

标签： sqlfirebase-realtime-databasegoogle-bigquery

解决方案

我通常会像这样构造您的查询：

with temp as (
  select * from `project.dataset.table`
  left join unnest(event_params) ep
  where event_name in('relationship_created_gender',
                      'relationship_created_location',
                      'relationship_created_interests')
),
logic as(
  select
    case when key = 'relationship_gender' and value.string_value = 'Male' then 1 else 0 end as gender_male,
    case when key = 'relationship_gender' and value.string_value = 'Female' then 1 else 0 end as gender_female,
    ... etc
    case when key = 'relationship_location' and value._value = 'New York' then 1 else 0 end as location_ny,
    ... etc
  from temp
)
select
  sum(gender_male) as Male,
  sum(gender_female) as Female,
  ...etc
from logic

您可能需要根据您要计算的内容是否为string_valueorint_value格式进行一些调整。如果你有一个 unique event_id，我会count distinct这样而不是 summing 1s。

推荐阅读