CODEWITHSUNDEEP
sqlgoogle-bigquery
eugene
eugene

Reputation: 41665

combine two queries with group by

Suppose I have a table with columns

event_name,
event_date,
event_key,
event_value

I 'm interested in two event_names review_meta_with_product and review_meta_click_purchase_product

For each of the two event_names, I want aggregate count for each (event_date, event_key, event_value) group.

I have currently two queries for two event_names, wonder if they could be combined.

(The following is a query for BigQuery, but I think the idea is still applicable to regular sql.)

   select event.date, event.name as event_name, event_param.value.string_value as review_meta_name, count(*) as ct from `com_zibann_littlehome_debugEug_ANDROID.app_events_20180403`,
       UNNEST(event_dim) as event,
       UNNEST(event.params) as event_param
   where DATE(TIMESTAMP_MICROS(event.timestamp_micros)) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
       and event.name = 'review_meta_with_product'
   and event_param.key = "review_meta_name"
 group by event.date, event.name, event_param.key, event_param.value.string_value


 select event.date, event.name as event_name, event_param.value.string_value as review_meta_name, count(*) as ct from `com_zibann_littlehome_debugEug_ANDROID.app_events_20180403`,
       UNNEST(event_dim) as event,
       UNNEST(event.params) as event_param
   where DATE(TIMESTAMP_MICROS(event.timestamp_micros)) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
       and event.name = 'review_meta_click_purchase_product'
   and event_param.key = "review_meta_name"
 group by event.date, event.name, event_param.key, event_param.value.string_value

Upvotes: 1

Views: 117

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Yes , it is possible to combine the two queries using conditional aggregation:

select event.date, event.name as event_name, 
       event_param.value.string_value as review_meta_name, 
       count(case 
                when event.name = 'review_meta_click_purchase_product' 
                   then 1 
             end) as ct_purchase,
       count(case 
                when event.name = 'review_meta_with_product' 
                   then 1 
             end) as ct_product 
from `com_zibann_littlehome_debugEug_ANDROID.app_events_20180403`,
      UNNEST(event_dim) as event,
      UNNEST(event.params) as event_param
where DATE(TIMESTAMP_MICROS(event.timestamp_micros)) >=
         DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
      and event.name IN ('review_meta_click_purchase_product', 'review_meta_with_product')
      and event_param.key = "review_meta_name"
group by event.date, 
         event.name, 
         event_param.key, 
         event_param.value.string_value

Upvotes: 1

Related Questions