CODEWITHSUNDEEP
sqlfirebase-realtime-databasegoogle-bigquery
xTwisteDx
xTwisteDx

Reputation: 2472

Count BigQuery event.params based on event.key

I am attempting to count the events filtering on their parameter.

For example, suppose that I have the following. Here's the real one for the inquisitive minds out there. https://i.sstatic.net/MUzcR.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 and event_params.value is an array of values. I need to filter out anything in that array that doesn't match my key. In addition, I need to count each occurrence of a specific string value. I attempted to do something like the following:

 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

It returns null for all columns. What I'm expecting to get would be something along the lines of this, horizontal or vertical, makes no difference.

Expected Output

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

Each Count per column is derived from the value associated with some_key in the above example. The value is static, from a list that is known. For example, the value for relationship_gender could be Male, Female, etc.. and I'd compare to match that.

Upvotes: 0

Views: 1977

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

I really recommend you to adjust your output requirements and have below output format

key                         value         cnt
relationship_gender         Male          10
relationship_gender         Female        11
relationship_gender         Trans         1
relationship_gender         Non-Binary    3
relationship_location       New York      3
relationship_location       San Antonio   17
relationship_lenght         1-5mo         1
relationship_lenght         5-10mo        61

above format much more flexible for any further processing (including pivoting if you still need it that way)

So, if above is acceptable for you - below is the query for it

#standardSQL
SELECT key, value.string_value AS value, COUNT(1) cnt
FROM `stormbreaker-studios.analytics_243434300.events_20200902` t,
UNNEST(event_params) 
WHERE STARTS_WITH(LOWER(event_name), 'relationship_created_')
AND STARTS_WITH(LOWER(key), 'relationship_')
GROUP BY key, value

As I mentioned - if you still need it - you can easily add pivoting here - there are multiple related posts including answered by myself ...

Upvotes: 1

rtenha
rtenha

Reputation: 3616

I would generally structure your query like this:

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

You might have to make some adjustments based on if the things you want to count are in the string_value or int_value format. If you have a unique event_id, I would count distinct that instead of summing 1s.

Upvotes: 3

Related Questions