BigQuery get and aggregate data joined using values from an array

Question

Say I have 2 tables

owners

owner	car_ids
John	1, 2, 3
Sue	3, 4, 5

cars

car_id	price	last_driven
1	100	2022-01-01
2	200	2022-01-02
3	300	2022-01-03
4	400	2022-01-04
5	500	2022-01-05
6	600	2022-01-06

And I want to get the sum of the price of all the cars per driver, and when they last drove, so the data looks like:

owner	total_worth	last_driven
John	600	2022-01-03
Sue	1500	2022-01-06

How would I do that? I can't figure out how to aggregate over values in a different table joined by values in the array field.

So far what I've got is

SELECT distinct
  owner,
  car_id,
  cars.worth,
  cars.last_driven,
FROM `owners` cross join unnest(card_id) as car_id
join `cars` cars on cars.owner = owner

but this won't aggregate the data, it'll only output each row.

Thanks in advance!

Mikhail Berlyant · Accepted Answer · 2022-08-22 14:47:10Z

1

Consider below approach

select owner, sum(price) total_worth, max(last_driven) last_driven
from owners, unnest(car_ids) car_id
left join cars using(car_id)
group by owner

if applied to sample data in your question - output is

answered Aug 22, 2022 at 14:47

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tom Hamilton Stubber Over a year ago

This is great @Mikhail; I have a small follow up. You're doing max on last driven to get that value, but what if there are other fields that don't need to be aggregated? For instance, if I wanted to add a field from the owner table owner.email_address? Do I have to either aggregate on it in some way or group by it?

Mikhail Berlyant Over a year ago

usually in such cases I use something like array_agg(struct(last_driven, email_address) order by last_driven desc limit 1)[offset(0)].* instead of max(last_driven) last_driven OR just adding any_value(email_address) email_address to the select list.

Jaytiger · Accepted Answer · 2022-08-22 14:15:41Z

1

Consider below query:

SELECT owner, SUM(price) total_worth, MAX(last_driven) last_driven
  FROM cars c JOIN owners o ON c.car_id IN UNNEST(o.car_ids)
 GROUP BY 1;

Query results:

owner	total_worth	last_driven
John	600	2022-01-03
Sue	1500	2022-01-06

Sample tables

WITH owners AS (
  SELECT 'John' owner, [1, 2, 3] car_ids
   UNION ALL 
  SELECT 'Sue', [4, 5, 6]
),
cars AS (
  SELECT * EXCEPT(o1, o2, o3)
    FROM UNNEST(GENERATE_ARRAY(1, 6)) car_id WITH OFFSET o1,
         UNNEST(GENERATE_ARRAY(100, 600, 100)) price WITH OFFSET o2,
         UNNEST(GENERATE_DATE_ARRAY('2022-01-01', '2022-01-06')) last_driven WITH OFFSET o3
   WHERE o1 = o2 AND o2 = o3
)

answered Aug 22, 2022 at 14:15

Jaytiger

12.3k2 gold badges7 silver badges16 bronze badges

1 Comment

Tom Hamilton Stubber Over a year ago

Thanks @Jaytiger, I have a small follow up question written above under the other answer.

Collectives™ on Stack Overflow

BigQuery get and aggregate data joined using values from an array

2 Answers 2

2 Comments

Query results:

Sample tables

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Query results:

Sample tables

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related