BigQuery Standard SQL count original rows after CROSS JOIN UNNEST

1
votes

I have a table with a repeated field that requires a CROSS JOIN UNNEST and I want to be able to get the count of the original, nested rows. For example.

SELECT studentId, COUNT(1) as studentCount
FROM myTable
CROSS JOIN UNNEST classes
WHERE classes.id in ('1', '2')

Right now, if a student is in class 1 and 2 it will count that student twice in studentCount.

I know I can do count(distinct(student.id)) to workaround this, but this ends up being a lot slower than a simple count. It's not taking advantage of the fact there's exactly one row per student.

So is there any way to compute count of the original rows before unnesting (but after the where clause) but still include the unnest in the query?

Note this must be in Standard SQL.

2
sqlgoogle-bigquery
I have the feeling that it doesn't actually require the CROSS JOIN UNNEST. Maybe you can say more about what the query is supposed to compute? - Elliott Brossard
It is possible there's a workaround where I can remove CROSS JOIN UNNEST, but I'd like to know if there's a way of doing it without having to remove that - Kat

2 Answers

2
votes

I understood your "challenge" as to show only students from classes id 1 and 2 while still showing total count of student in all classes. If this is it - see below 

#standardSQL
SELECT studentId, studentCount
FROM myTable
CROSS JOIN (SELECT COUNT(1) studentCount FROM myTable)
WHERE studentId IN (
  SELECT studentID FROM UNNEST(classes) AS classes
  WHERE classes.id IN ('1', '2')
)

you can test / play with it using dummy data as below 

#standardSQL
WITH myTable AS (
  SELECT 1 AS studentId, [STRUCT<id STRING>('1'),STRUCT('2'),STRUCT('3')] AS classes UNION ALL
  SELECT 2, [STRUCT<id STRING>('4'),STRUCT('5')]
)
SELECT studentId, studentCount
FROM myTable
CROSS JOIN (SELECT COUNT(1) studentCount FROM myTable)
WHERE studentId IN (
  SELECT studentID FROM UNNEST(classes) AS classes
  WHERE classes.id IN ('1', '2')
)

If your desired output is different from what I guessed - you still might find above useful for calculating studentCount

1
votes

Just given the original constraints--that unnesting is required and you need to count the number of students--you can use a query of this form:

SELECT studentId, (SELECT COUNT(*) FROM myTable) AS studentCount
FROM myTable
CROSS JOIN UNNEST classes
WHERE classes.id in ('1', '2')