CODEWITHSUNDEEP
google-bigquery
Subah
Subah

Reputation: 61

Bigquery multiple unnest in a single select

Following up on this - Bigquery combining repeated fields from 2 different tables

The above solution from @ElliottBrossard was what I was looking for. Thanks! I need to UNNEST the fields from above to get aggregations by student. Say, there is an additional field for cost (per course) For e.g. assuming the output from the above in a student_courses table, I will need to do something like,

SELECT
  COUNT(DISTINCT phone.number),
  COUNT(DISTINCT courses.Id),
  SUM(courses.Cost)
FROM
  student_courses,
  UNNEST(phone),
  UNNEST(courses)

My expected answer from the above is something like, 3, 4, $800 (assuming the 4 courses cost $200 each). The above query ends up acting like a cross join. Is it possible to get aggregate from multiple nests using a single Select?

Upvotes: 4

Views: 11511

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33745

Here's an idea, although it's a bit verbose:

#standardSQL
SELECT
  (SELECT COUNT(DISTINCT number)
   FROM UNNEST(numbers) AS number),
  (SELECT COUNT(DISTINCT course_id)
   FROM UNNEST(course_ids) AS course_id),
  course_sum
FROM (
  SELECT
    ARRAY_CONCAT_AGG(
      ARRAY(SELECT number FROM UNNEST(phone))
    ) AS numbers,
    ARRAY_CONCAT_AGG(
      ARRAY(SELECT id FROM UNNEST(courses))
    ) AS course_ids,
    SUM((SELECT SUM(cost) FROM UNNEST(courses))) AS course_sum
  FROM YourTable
);

References:

Upvotes: 6

Related Questions