CODEWITHSUNDEEP
google-bigquery
Tom Hamilton Stubber
Tom Hamilton Stubber

Reputation: 767

BigQuery get and aggregate data joined using values from an array

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!

Upvotes: 0

Views: 46

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

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

enter image description here

Upvotes: 1

Jaytiger
Jaytiger

Reputation: 12234

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
)

Upvotes: 1

Related Questions