CODEWITHSUNDEEP
sqlgoogle-bigquerystring-aggregation
Karl Lin
Karl Lin

Reputation: 317

Find elements in group by result use SQL

I have a table contain event log, schema:

userid, event, timestamp

I want group by userid and find that user have specific event

ex.

userid, events
A, "open, click, remove, restart"

so I want make group by result as a string / array

is there any way can do this?

thanks!

Upvotes: 0

Views: 201

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Below is for BigQuery Standard SQL

as an example - assuming you want to get list of all ordered events of users who have remove event: 

#standardSQL
SELECT userid,
  STRING_AGG(event ORDER BY ts) AS events
FROM `project.dataset.yourtable`
GROUP BY userid
HAVING REGEXP_CONTAINS(LOWER(events), r'\bremove\b')

You can test / play with above using dummy data as below 

#standardSQL
WITH `project.dataset.yourtable` AS (
  SELECT 'A' userid, 'open' event, 1 ts UNION ALL
  SELECT 'A', 'click', 2 UNION ALL
  SELECT 'A', 'remove', 3 UNION ALL
  SELECT 'A', 'restart', 4 UNION ALL
  SELECT 'B', 'open', 1 UNION ALL
  SELECT 'B', 'click', 2   
)
SELECT userid,
  STRING_AGG(event ORDER BY ts) AS events
FROM `project.dataset.yourtable`
GROUP BY userid
HAVING REGEXP_CONTAINS(LOWER(events), r'\bremove\b')

with result as below (user B is not presented because no remove even present for this user) 

userid  events   
A       open,click,remove,restart

Upvotes: 2

gmiley
gmiley

Reputation: 6604

You can achieve this with the group_concat function in BigQuery.

select userid, group_concat(event, ', ') as events
from EventLog
group by userid;

You also briefly mention that you would like to find users that have specific events, you can add that in as part of the where clause, but as you did not give any example of how you are looking to narrow it down, I have omitted that portion. If you can give a more specific case example of what you are looking for, I can update my answer to reflect that.

Upvotes: 1

Related Questions