NTH в устаревшем SQL в BigQuery не работает должным образом

У меня есть этот запрос, написанный в Legacy SQL:

select
    nth(1, a) first_a, 
    nth(1, b) first_b
from (
    select *
    from
        (select 12 a, null b),
        (select null a, 54 b)
)

В результате я ожидал одну строку со значениями (12, null), но вместо этого получил (12, 54). В документации для NTH сказано:

NTH(n, поле)

Возвращает n-е последовательное значение в области действия функции, где n — константа. Функция NTH начинает считать с 1, поэтому нулевого члена нет. Если в области действия функции меньше n значений, функция возвращает NULL.

Нет ничего, указывающего на то, что нули будут игнорироваться. Это ошибка в BigQuery?


google-bigquery legacy-sql
person Luka    schedule 10.04.2018    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Это важная часть документации:

в рамках функции

Область обычно представляет собой "запись" (в устаревших терминах SQL), где вы выбираете n-е значение в повторяющемся поле. Однако, как написано, этот запрос имеет эффект использования NTH в качестве агрегатной функции. Значения в группе не имеют четко определенного порядка, но бывает так, что NULL упорядочивается после ненулевых значений, поэтому NTH(1, ...) дает ненулевое значение. Вместо этого попробуйте использовать 2 в качестве порядкового номера, например:

select
    nth(2, a) first_a, 
    nth(2, b) first_b
from (
    select *
    from
        (select 12 a, null b),
        (select null a, 54 b)
)

Это возвращает null, null в качестве вывода.

С учетом сказанного, чтобы обеспечить четко определенную семантику в ваших запросах, лучше всего использовать стандартный SQL вместо этого. Некоторые аналоги оператора NTH при использовании стандартного SQL:

  • Оператор скобки массива, например. array_column[OFFSET(0)] для получения первого элемента массива.
  • Функция окна NTH_VALUE, например. NTH_VALUE(x, 1) OVER (PARTITION BY y ORDER BY z). См. также FIRST_VALUE и LAST_VALUE.
person Elliott Brossard    schedule 10.04.2018
comment
Значения в группе не имеют четко определенного порядка - не уверен в этом. Я могу упорядочить записи перед вызовом nth, и он будет работать так, как ожидалось (беря значения из первой записи после упорядочивания), за исключением нулевых значений. - person Luka; 10.04.2018
comment
Конечно, вы можете получать стабильные результаты, но это не означает, что результаты четко определены или не изменятся в будущем. Например, если хэш-карта имеет определенный порядок итерации, нет гарантии, что он всегда будет таким. - person Elliott Brossard; 10.04.2018
comment
Если вы select nth(1, b), first(b), last(b) from ..., вы получите (54, 54, 54). Означает ли это, что каждая из этих трех функций имеет свой собственный порядок, который может отличаться от двух других? - person Luka; 10.04.2018
comment
Поведение не четко определено. Почему это имеет значение, если это последовательно? Для четко определенной семантики используйте вместо этого стандартный SQL в BigQuery или не используйте аспекты устаревшего SQL, где семантика неоднозначна. - person Elliott Brossard; 10.04.2018
comment
Вы говорите мне, что эти функции есть, и я могу их использовать, но мне не следует ожидать согласованности? Это нормально, если результаты случайны? Кроме того, результаты не соответствуют документации, но кого это волнует, потому что какая разница, если они непротиворечивы? - person Luka; 11.04.2018
comment
Устаревший SQL не имеет четко определенной семантики в некоторых областях. Это одна из них. - person Elliott Brossard; 11.04.2018
comment
Хорошо, спасибо! Тогда я думаю, что нашел ошибку в устаревшем SQL. - person Luka; 11.04.2018