BigQuery中查询分组内缺失行的SQL实现方法

核心逻辑：先构造每个分组维度下理论应存在的全量合法subject+slot组合，再通过反连接筛出现有数据中不存在的组合，即为缺失项，适配亿级数据量场景。

• 合法枚举值固定：subject仅可取physics/chemistry，slot仅可取1/2，全量合法组合共4种
• 先提取去重后的全量分组维度（包含示例中的name、roll_num，以及你业务中的其他分组列），再和枚举值做笛卡尔积，避免全表大笛卡尔积的性能问题
• 左关联原表筛出关联不上的记录，就是缺失数据

可直接运行的示例代码如下：

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
),
-- 固定合法枚举组合
enum_dim as (
  select "physics" as subject, 1 as slot
  union all select "physics", 2
  union all select "chemistry", 1
  union all select "chemistry", 2
),
-- 提取去重后的全量学生分组维度，有其他分组列直接加在select和group by中即可
student_dim as (
  select name, roll_num
  from base_tbl
  group by name, roll_num
),
-- 生成每个分组理论上应存在的全量组合
should_have as (
  select 
    s.name as student,
    s.roll_num,
    e.subject as subject_missing,
    e.slot as slot_missing
  from student_dim s
  cross join enum_dim e
)
-- 反连接筛出缺失记录
select sh.*
from should_have sh
left join base_tbl t
  on sh.student = t.name
  and sh.roll_num = t.roll_num
  and sh.subject_missing = t.subject
  and sh.slot_missing = t.slot
where t.name is null;

运行结果和给出的预期输出完全一致。

1.7亿行数据优化建议 #

• 建联合覆盖索引：将所有分组列、subject、slot建成联合索引，关联时不需要回表，性能提升非常明显
• 禁止直接对原表做笛卡尔积：必须先对分组维度去重后再关联枚举值，实际参与计算的是去重后的分组数，数据量远小于1.7亿
• 新增其他分组列时，只需要在student_dim模块和关联条件中补充对应字段即可，核心逻辑不需要改动

内容的提问来源于stack exchange，提问作者Ashwin