Google Bigquery не согласуется при изменении имен переменных в предложении ORDER BY

Question

Моя цель состоит в том, чтобы проверить, являются ли grp, сгенерированные одним запросом, теми же, что и выходные данные того же запроса. Однако, когда я изменяю одно имя переменной, я получаю разные результаты.

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

SELECT grp
FROM
(
  SELECT CONCAT(word, corpus) AS grp, rank1, rank2 
  FROM (
    SELECT
      word, corpus,
      ROW_NUMBER() OVER (PARTITION BY word ORDER BY test1 DESC) AS rank1,
      ROW_NUMBER() OVER (PARTITION BY word ORDER BY word_count DESC) AS rank2,
      ROW_NUMBER() OVER (PARTITION BY word ORDER BY corpus DESC) AS rank3,
      ROW_NUMBER() OVER (PARTITION BY word ORDER BY corpus_date DESC) AS rank4
    FROM 
    (
      SELECT *, (word_count * word_count * corpus_date) AS test1
      FROM [bigquery-public-data:samples.shakespeare]
    )
  )
)
WHERE rank1 <= 3 OR rank2 <= 3
HAVING grp NOT IN 
(
  SELECT grp FROM (
    SELECT CONCAT(word, corpus) AS grp, rank1, rank2
    FROM
    (
      SELECT
        word, corpus,
        ROW_NUMBER() OVER (PARTITION BY word ORDER BY test2 DESC) AS rank1,
        ROW_NUMBER() OVER (PARTITION BY word ORDER BY word_count DESC) AS rank2,
        ROW_NUMBER() OVER (PARTITION BY word ORDER BY corpus DESC) AS rank3,
        ROW_NUMBER() OVER (PARTITION BY word ORDER BY corpus_date DESC) AS rank4
      FROM 
      (
        SELECT *, (word_count * word_count * corpus_date) AS test2
        FROM [bigquery-public-data:samples.shakespeare]
      )
    )
  )
  WHERE rank1 <= 3 OR rank2 <= 3
)

Гораздо хуже... теперь, если вы попытаетесь выполнить точно такой же запрос, но просто измените имя переменной test1 на test3, вы получите совершенно другие результаты.

SELECT grp
FROM
(
  SELECT CONCAT(word, corpus) AS grp, rank1, rank2 
  FROM (
    SELECT
      word, corpus,
      ROW_NUMBER() OVER (PARTITION BY word ORDER BY test3 DESC) AS rank1,
      ROW_NUMBER() OVER (PARTITION BY word ORDER BY word_count DESC) AS rank2,
      ROW_NUMBER() OVER (PARTITION BY word ORDER BY corpus DESC) AS rank3,
      ROW_NUMBER() OVER (PARTITION BY word ORDER BY corpus_date DESC) AS rank4
    FROM 
    (
      SELECT *, (word_count * word_count * corpus_date) AS test3
      FROM [bigquery-public-data:samples.shakespeare]
    )
  )
)
WHERE rank1 <= 3 OR rank2 <= 3
HAVING grp NOT IN 
(
  SELECT grp FROM (
    SELECT CONCAT(word, corpus) AS grp, rank1, rank2
    FROM
    (
      SELECT
        word, corpus,
        ROW_NUMBER() OVER (PARTITION BY word ORDER BY test2 DESC) AS rank1,
        ROW_NUMBER() OVER (PARTITION BY word ORDER BY word_count DESC) AS rank2,
        ROW_NUMBER() OVER (PARTITION BY word ORDER BY corpus DESC) AS rank3,
        ROW_NUMBER() OVER (PARTITION BY word ORDER BY corpus_date DESC) AS rank4
      FROM 
      (
        SELECT *, (word_count * word_count * corpus_date) AS test2
        FROM [bigquery-public-data:samples.shakespeare]
      )
    )
  )
  WHERE rank1 <= 3 OR rank2 <= 3
)

Я не могу придумать никакого объяснения, которое удовлетворяло бы обоим этим странным поведениям, и это мешало мне иметь возможность проверять свои данные. Есть идеи?

РЕДАКТИРОВАТЬ:

Я обновил BigQuery SQL так, как подсказывают ответы, и возникают те же несоответствия.

Answer 1

Проблема в недетерминированности вашей нумерации строк.

В этой таблице много примеров (word_count * word_count * corpus_date) то же самое для нескольких корпусов. Поэтому, когда вы разделяете word и заказать по test2порядок, который вы используете для назначения номеров строк, является недетерминированным.

Когда вы запускаете один и тот же подзапрос дважды в одном и том же запросе верхнего уровня, BigQuery фактически выполняет этот подзапрос дважды и может давать разные результаты между двумя запусками из-за этого недетерминизма.

Изменение псевдонима могло привести к тому, что ваш запрос не попадал в кеш, что приводило к другому набору недетерминированных вариантов и разному перекрытию результатов.

Вы можете подтвердить это, изменив ORDER BY пункт в ваших аналитических функциях, чтобы включить corpus, Например, изменить ORDER BY test2 в ORDER BY test2, corpus, Тогда нумерация строк будет детерминированной, и запросы будут возвращать ноль результатов независимо от того, какие псевдонимы вы используете.

Answer 2

Я не понимаю вопроса. Синтаксис SQL в целом и BigQuery в частности достаточно ясны: псевдонимы, определенные в SELECT не может быть использован в SELECT для других выражений. Как объяснено в документации BigQuery:

Псевдонимы, определенные в SELECT на статью можно сослаться в GROUP BY, HAVING, а также ORDER BY пункты запроса, но не FROM, WHERE, или же OMIT RECORD IF пункты, ни с помощью других выражений в том же SELECT пункт. [акцент мой]

Следовательно, ваш запрос будет работать только если test1, test2, а также test3 столбцы в таблице Шекспира. Нет оснований полагать, что такие столбцы будут иметь одинаковые значения, поэтому я не ожидаю, что запросы будут возвращать одинаковые результаты.

РЕДАКТИРОВАТЬ:

Если мы предположим, что документация неверна, то проблема, вероятно, дублируется в order by критерии для row_number(), Сортировка в SQL нестабильна - это означает, что две строки с одинаковым значением ключа сортировки могут появляться в любом порядке во время сортировки. Даже один и тот же запрос может вернуть разные результаты за два запуска. Сортировки SQL, очевидно, нестабильны, поскольку таблицы не имеют внутреннего порядка в строках (порядок определяется только столбцами).

Итак, все, что происходит, - это то, что выбираются разные строки с одинаковыми значениями ключа сортировки. Я не думаю, что это связано с псевдонимами.

Как вы можете это исправить? Добавить дополнительный ключ сортировки, такой как id, в качестве окончательного ключа. Альтернативно использовать rank() или же dense_rank() и выяснить, что делать с дубликатами.

Answer 3

Я заметил, что вы всегда задаете сложные вопросы, а потом вам трудно принять или даже проголосовать за ответ. Это нормально! И я хочу попробовать еще раз, так что давайте перейдем к теме:

Похоже, использование псевдонимов в одном и том же операторе SELECT недокументировано и не поддерживается. Примечание ниже в документации по предложению SELECT:

Каждому выражению можно присвоить псевдоним, добавив пробел с последующим идентификатором после выражения. Необязательное ключевое слово AS может быть добавлено между выражением и псевдонимом для улучшения читабельности. На псевдонимы, определенные в предложении SELECT, можно ссылаться в предложениях GROUP BY, HAVING и ORDER BY запроса, но не в предложениях FROM, WHERE или OMIT RECORD IF, а также в других выражениях этого же предложения SELECT.

Таким образом, здесь происходит странное поведение без ошибок. Таким образом, вы можете использовать его на свой страх и риск, но лучше этого не делать (все равно было бы здорово услышать от Google Team - но, поскольку он не поддерживается - вы можете ожидать, что не будет много информации, объясняющей это поведение)

А пока - я бы предложил просто следовать тому, что поддерживается, и преобразовать ваш запрос в версию ниже "стабильной".
Это не проблема, с которой вы сталкиваетесь в оригинале!
(обратите внимание, что я изменил предложение WHERE в первом подзапросе - иначе он всегда возвращает ноль строк - что имеет смысл)

SELECT grp
FROM
(
  SELECT CONCAT(word, corpus) AS grp, rank2, 
    ROW_NUMBER() OVER (PARTITION BY word ORDER BY [try_any_alias_1] DESC) AS rank1
  FROM (
    SELECT
      word, corpus,
      (word_count * word_count * corpus_date) AS [try_any_alias_1],
      ROW_NUMBER() OVER (PARTITION BY word ORDER BY word_count DESC) AS rank2,
      ROW_NUMBER() OVER (PARTITION BY word ORDER BY corpus DESC) AS rank3,
      ROW_NUMBER() OVER (PARTITION BY word ORDER BY corpus_date DESC) AS rank4
    FROM [bigquery-public-data:samples.shakespeare]
  )
)
WHERE rank1 <= 3 OR rank2 <= 4 // if rank2 <= 3 as in second subquery - result is always empty as expected
HAVING grp NOT IN 
(
  SELECT grp FROM (
    SELECT CONCAT(word, corpus) AS grp, rank2,
      ROW_NUMBER() OVER (PARTITION BY word ORDER BY [try_any_alias_2] DESC) AS rank1
    FROM
    (
      SELECT
        word, corpus,
        (word_count * word_count * corpus_date) AS [try_any_alias_2],
        ROW_NUMBER() OVER (PARTITION BY word ORDER BY word_count DESC) AS rank2,
        ROW_NUMBER() OVER (PARTITION BY word ORDER BY corpus DESC) AS rank3,
        ROW_NUMBER() OVER (PARTITION BY word ORDER BY corpus_date DESC) AS rank4
      FROM [bigquery-public-data:samples.shakespeare]
    )
  )
  WHERE rank1 <= 3 OR rank2 <= 3
)