Неожиданные результаты при использовании FIRST_VALUE() в SQL Server 2012

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

Может ли кто-нибудь сказать мне, если я что-то не понял?

Этот SQL производит ожидаемый результат, что FIRST_VALUE имеет значение NULL и LAST_VALUE равно 30.

SELECT
  agroup,
  aval,
  FIRST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv,
  LAST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
FROM
(
  SELECT 1 agroup, 10 aval
  UNION ALL SELECT 1, NULL
  UNION ALL SELECT 1, 30
) T

Этот SQL использует LEFT JOIN, который приводит к тому же набору данных, что и выше, но FIRST_VALUE, как представляется, игнорирует NULL.

SELECT 
  agroup,
  aval,
  FIRST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv,
  LAST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
FROM
(
  SELECT 
    T1.agroup,
    T1.akey,
    T2.aval 
  FROM 
  (
    SELECT 1 agroup, 1 akey
    UNION ALL SELECT 1, 2
    UNION ALL SELECT 1, 3
  ) T1
  LEFT JOIN
  (
    SELECT 1 akey, 10 aval
    UNION ALL SELECT 3,30
  ) T2 ON T1.akey = T2.akey
) T

Я также могу показать, что поведение левого соединения отличается при использовании переменной таблицы против CTE. При использовании CTE для генерации данных FIRST_VALUE игнорирует NULL. Использование одного и того же SQL, но включение результатов в переменную таблицы или временную таблицу приводит к принятию значения NULL.

При CTE результаты SQL Server не включают NULL в определение FIRST_VALUE:

WITH T AS
(
  SELECT 
    T1.agroup,
    T1.akey,
    T2.aval 
  FROM 
  (
    SELECT 1 agroup, 1 akey
    UNION ALL SELECT 1, 2
    UNION ALL SELECT 1, 3
  ) T1
  LEFT JOIN
  (
    SELECT 1 akey, 10 aval
    UNION ALL SELECT 3,30
  ) T2 ON T1.akey = T2.akey
)

SELECT 
  agroup,
  aval,
  FIRST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv,
  LAST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
FROM
 T

Но с переменной таблицы она:

DECLARE @T TABLE (agroup INT,akey INT,aval INT)

INSERT INTO
  @T
SELECT 
  T1.agroup,
  T1.akey,
  T2.aval 
FROM 
(
  SELECT 1 agroup, 1 akey
  UNION ALL SELECT 1, 2
  UNION ALL SELECT 1, 3
) T1
LEFT JOIN
(
  SELECT 1 akey, 10 aval
  UNION ALL SELECT 3,30
) T2 ON T1.akey = T2.akey


SELECT 
agroup,
aval,
FIRST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv,
LAST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
FROM
@T

Ответ 1

Приведенные примеры очень четко показывают, что существует несогласованность в реализации аналитической функции FIRST_VALUE().

В зависимости от того, является ли базовая таблица в предложении FROM базовой таблицей (или временной или табличной переменной или даже производной таблицей, созданной "на лету" ) в одном случае и производной таблицей (или cte), созданной LEFT JOIN из двух созданных на лету таблиц во втором случае, результаты разные. Похоже, что значения NULL игнорируются во втором случае или рассматриваются как высокие значения.

И они не должны отличаться, потому что результат SQL-запроса не должен зависеть от того, как предложение FROM получает значения таблицы, которую он предоставляет в предложении SELECT, а также потому, что документация OVER ясно указывает, как следует обрабатывать значения NULL:

order_by_expression

Задает столбец или выражение для сортировки. order_by_expression может ссылаться только на столбцы, доступные в предложении FROM. Целое число не может быть указано для представления имени столбца или псевдонима.

...

ASC | DESC

Указывает, что значения в указанном столбце должны быть отсортированы в порядке возрастания или убывания. ASC - это порядок сортировки по умолчанию. Нулевые значения считаются наименьшими возможными значениями.

Таким образом, правильные результаты - в соответствии с документацией SQL-Server - это те, которые не игнорируют значения NULL. Любой другой результат не должен происходить, и поскольку это происходит, это ошибка.

Я предложил вам протестировать в самой последней версии (и не только в RTM), поскольку она может быть идентифицирована и исправлена в каком-либо пакете обновления или обновлении, и если она все еще существует (или если у вас нет новой версии доступно), чтобы отправить это как ошибку на сайте Connect.

Update

Для справок в будущем ошибка была представлена ОП. Ссылка: Подключить элемент и (наш) @Aaron Bertrand прокомментировал что он также появляется в большинстве существующих сборников SQL 2014.

Ответ 2

Немного позднего ответа на этот пост, но тем не менее, чтобы поделиться.

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

Итак, в вашем случае... вы можете использовать

... FIRST_VALUE (aval) OVER (ОПРЕДЕЛЕНИЕ ГРУППЫ ORDER BY (iif (aval - null, 1,0)), aval ROWS МЕЖДУ НЕОГРАНИЧЕННЫМИ ПРЕДПОЛАГАЕМОЙ И НЕОГРАНИЧЕННОЙ СЛЕДУЮЩЕЙ) fv ...

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

Cheers - LA.