Блог

Главная - Вопросы по программированию - BigQuery НЕ СУЩЕСТВУЕТ… НЕ РАВНО (!=) и СУЩЕСТВУЕТ… РАВНО дает разные результаты

BigQuery НЕ СУЩЕСТВУЕТ… НЕ РАВНО (!=) и СУЩЕСТВУЕТ… РАВНО дает разные результаты

# #sql #google-bigquery

Вопрос:

Я потерян и пытаюсь выяснить причину, по которой я получаю разные выходные данные для одной и той же логики. Я хочу узнать все вызовы (варианты и не варианты) для каждого набора вызовов и пропускает любой вызов с непроходным фильтром.

Запрос приведен по ссылке:

 #standardSQL
SELECT
  call.name AS call_name,
  COUNT(1) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
  NOT EXISTS (SELECT 1 FROM UNNEST(call.FILTER) AS call_filter WHERE call_filter != 'PASS')
GROUP BY
  call_name
ORDER BY
  call_name
Строка имя_звонка number_of_calls
1 NA12877 29795946
2 NA12878 26118774
3 NA12889 29044992
4 NA12890 28717437
5 NA12891 31395995
6 NA12892 25349974

Это возвращает количество строк, которые имеют фильтр при ПРОХОЖДЕНИИ.

Но, когда я пытаюсь использовать аналогичную логику, я получаю другой результат.

 #standardSQL
SELECT
  call.name AS call_name,
  COUNT(1) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
   EXISTS (SELECT 1 FROM UNNEST(call.FILTER) AS call_filter WHERE call_filter = 'PASS')
GROUP BY
  call_name
ORDER BY
  call_name
Строка имя_звонка number_of_calls
1 NA12877 4488086
2 NA12878 4503443
3 NA12889 4423974
4 NA12890 4529950
5 NA12891 4425316
6 NA12892 4497085

Почему это? Моя логика неверна? Может кто-нибудь объяснить, почему пустой массив является «ПРОХОДОМ» в этом случае? Спасибо, что нашли время, чтобы помочь мне!

Комментарии:

1. Первое было бы истинным, если filter является пустым списком.

2. @shawnt00, большое вам спасибо! Кажется, что пустой массив ([]) устанавливает условие where в TRUE . Следовательно, считается превышение. Знаете ли вы, почему пустой массив делает значение not exists равным TRUE?

3. Такой строки не существует, поэтому условие все еще выполняется. Вы просили о несуществовании, и вы его получили! Я никогда не был в Нью-Йорке, но каждый раз, когда я ездил, я не видел Статую Свободы.

Ответ №1:

Спасибо @shawnt00 за предоставление решения. Я продемонстрирую на нескольких примерах меньшего размера, что дополнительные счетчики действительно являются пустыми массивами.
Запрос 1

 with smalltable as (
    select ["PASS"] as filter, 'NA12877' as name union all 
    select ["PASS"], 'NA12877' union all 
    select ["PASS"], 'NA12879' union all 
    select ["PASS",'RefCall'], 'NA12877' union all 
    select ["RefCall"], 'NA12877' union all 
    select ["RefCall"], 'NA12877' union all 
    select ["RefCall"], 'NA12879' union all 
    select ["RefCall"], 'NA12879' union all 
    select ["RefCall"], 'NA12879' union all 
    select ["RefCall"], 'NA12879', union all
    select [], 'NA12879'   
)
select name, count(1) as count 
from smalltable 
where not exists (select 1 from unnest(filter) as f where f != 'PASS')
group by name

Вывод:

Строка Имя количество
1 NA12877 2
2 NA12879 2

Как мы можем видеть, запрос рассматривает [] как ‘PASS’ . Я не уверен, почему?

Запрос 2

 with smalltable as (
    select ["PASS"] as filter, 'NA12877' as name union all 
    select ["PASS"], 'NA12877' union all 
    select ["PASS"], 'NA12879' union all 
    select ["PASS",'RefCall'], 'NA12877' union all 
    select ["RefCall"], 'NA12877' union all 
    select ["RefCall"], 'NA12877' union all 
    select ["RefCall"], 'NA12879' union all 
    select ["RefCall"], 'NA12879' union all 
    select ["RefCall"], 'NA12879' union all 
    select ["RefCall"], 'NA12879', union all
    select [], 'NA12879'   
)
select name, count(1) as count 
from smalltable 
where exists (select 1 from unnest(filter) as f where f = 'PASS')
group by name

Вывод:

Строка Имя количество
1 NA12877 3
2 NA12879 1

Это, как и ожидалось, не учитывает пустой массив.

Метки: