Querying missing rows within a group in Bigquery

Question

I have a table that looks something like this:

with base_tbl as (
  select 
    "A" as name, 123 as roll_num, "chemistry" as subject, 1 as slot
  union all
  select 
    "A" as name, 123 as roll_num, "chemistry" as subject, 2 as slot
  union all
  select 
    "A" as name, 123 as roll_num, "physics" as subject, 1 as slot
  union all

  select 
    "B" as name, 234 as roll_num, "physics" as subject, 1 as slot
  union all
  select 
    "B" as name, 234 as roll_num, "physics" as subject, 2 as slot
)

The column subject can only take values physics or chemistry and the column slot can take values 1 or 2.

Looking for recommendations on how I can flag students who have either one of the subjects missing or a slot missing: In the example above, expected output would be:

student	roll_num	subject_missing	slot_missing
A	123	physics	2
B	234	chemistry	1
B	234	chemistry	2

My real data has about ~170m rows, with several other grouping columns (student and roll_num here). Essentially I am trying to gauge the "completeness" of the dataset.

user_19039920 · Answer

Using a set operation,

SELECT t.* REPLACE(sj AS subject, sl AS slot)
  FROM base_tbl t,UNNEST(["physics", "chemistry"]) sj, UNNEST([1, 2]) sl
EXCEPT DISTINCT
SELECT * FROM base_tbl;

output:

user_5221944 · Answer

Consider below approach

select * except(missing_subjects_slots) 
from (
  select name, roll_num, 
    array(
      select as struct subject as subject_missing, slot as slot_missing
      from unnest(['chemistry', 'physics']) subject, unnest([1, 2]) slot
      where not (subject, slot) in (
        select as struct subject, slot
        from t1.subjects_slots
      )
    ) missing_subjects_slots
  from (
    select name, roll_num, 
      array_agg(struct(subject, slot)) subjects_slots,
    from base_tbl
    group by name, roll_num
  ) t1
) t2, t2.missing_subjects_slots

if applied to sample data in your question - output is

Querying missing rows within a group in Bigquery

2 Answers