BigQuery中COUNT不统计NULL却统计IS NOT NULL的异常问题排查

问题根源 #

你的统计逻辑和子查询结构不匹配，导致结果不符合预期：

1. 子查询a中，同一个ji.id会对应多条记录——因为table2(mh)中一个issue_id（关联ji.id）可能对应多个不同的field_id（'value'、'value2'、'value3'），每个id在子查询结果里会生成多行。
2. 执行count(distinct case when a.column1 is null then a.id end)时，只要某个id对应的任意一行满足column1 is null，这个id就会被计入统计。而几乎所有id都会有field_id='value2'或'value3'的行，这些行的column1天生为null（因为column1仅在field_id='value'时才会赋值），所以最终统计出的是所有唯一id的总数（1500），而非你想要的“field_id='value'时c.name为null的id数”。
3. 而count(distinct case when a.column1 is not null then a.id end)统计的是至少有一行column1非null的id数，也就是存在field_id='value'且c.name不为null的id，所以结果为800；剩余700个id要么没有field_id='value'的记录，要么该记录的c.name为null，但这些id因其他field_id的行导致column1为null，被包含在了第一个统计结果中。

修正方案 #

方案一：子查询先聚合每个id的column1状态 #

先对每个id单独提取field_id='value'对应的c.name值，确保每个id仅对应一行数据，再进行统计：

select 
  count(distinct case when column1 is null then id end) as null_id_count,
  count(distinct case when column1 is not null then id end) as not_null_id_count,
  count(distinct id) as total_id_count
from (
  select 
    ji.id as id,
    -- 提取该id对应的field_id='value'的c.name值（一个id最多对应一条该field_id的记录）
    max(case when mh.field_id = 'value' then c.name end) as column1
  from `table1` ji
  join table2 mh on ji.id = mh.issue_id
  join `lotus-dev-gcp.lotus_fivetran_jira.field` f on mh.field_id = f.id
  left join table3 c on mh.value = CAST(c.id as string) 
  where mh.field_id in ('value','value2','value3')
    and ji.project = 1111
    and ji.issue_type = 222
    and mh.something = true
  group by ji.id
) a

方案二：直接过滤目标field_id进行统计 #

仅针对field_id='value'的记录统计，逻辑更直接：

select 
  count(distinct case when c.name is null then ji.id end) as null_id_count,
  count(distinct case when c.name is not null then ji.id end) as not_null_id_count,
  count(distinct ji.id) as total_id_count
from `table1` ji
join table2 mh on ji.id = mh.issue_id
join `lotus-dev-gcp.lotus_fivetran_jira.field` f on mh.field_id = f.id
left join table3 c on mh.value = CAST(c.id as string) 
where mh.field_id = 'value' -- 仅聚焦field_id='value'的记录
  and ji.project = 1111
  and ji.issue_type = 222
  and mh.something = true

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