Функции FIRST_VALUE и LAST_VALUE

Использование коррелирующего подзапроса

В подзапросе для каждой компании данные сортируются случайным образом при использовании функции newid(), после чего выбирается одна (первая строка) этого отсортированного набора:

SELECT id_comp,
  (SELECT TOP 1 trip_no FROM trip t WHERE c.id_comp = t.id_comp ORDER BY NEWID()) trip_no
FROM company c
ORDER BY id_comp;

Разумеется, вы скорее всего получите другой результат, но, поскольку данных в таблице немного, рано или поздно вы сможете получить и такой. :-)

Использование функции FIRST_VALUE

Эта оконная функция возвращает первое из упорядоченного набора значений. Теперь мы можем сделать все без подзапросов, выделив в окне набор рейсов для компании из текущей строки запроса с помощью предложения PARTITION BY и упорядочив его, как и в предыдущем примере, случайным образом в предложении ORDER BY:

SELECT DISTINCT id_comp,
FIRST_VALUE(trip_no) OVER(PARTITION BY id_comp ORDER BY NEWID()) trip_no
FROM trip
ORDER BY id_comp;

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

Засада с LAST_VALUE

Казалось бы, какая разница брать первое или последнее значение из случайным образом упорядоченного набора? Но давайте посмотрим, что мы получим, если в предыдущем запросе заменить FIRST_VALUE на LAST_VALUE:

SELECT DISTINCT id_comp,
LAST_VALUE(trip_no) OVER(PARTITION BY id_comp ORDER BY NEWID()) trip_no
FROM trip
ORDER BY id_comp;

Я приведу результаты только для id_comp = 1. Вы можете сами выполнить запрос, чтобы убедиться, что будут выводиться абсолютно все рейсы из таблицы Trip.

Что мы делаем в подобных случаях? Конечно, обращаемся к документации, а там мы читаем… Нет, постойте, сначала полный синтаксис:

LAST_VALUE | FIRST_VALUE ( [ скалярное_выражение ] )  [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ предложение_partition_by ] предложение_order_by [ предложение_rows_range ] )

Здесь
IGNORE NULLS или RESPECT NULLS определяют, будут ли учитываться NULL-значения;
предложение_rows_range задает параметры окна.

А теперь читаем:

Т.е. окном является диапазон от текущей строки и неограниченно выше. Поскольку мы выбираем последнюю строку диапазона, то всегда будет выводиться текущая строка, как бы не сортировались строки. Т.е. сколько бы строк выше не оказалось при случайной сортировке.

Потому и DISTINCT не помогает, т.к. все выводимые строки оказываются уникальными.

Значит нам просто нужно явно (и правильно!) задать параметры окна, а именно, от текущей строки и неограниченно ниже, поскольку мы выбираем последнее значение:

SELECT DISTINCT id_comp,
LAST_VALUE(trip_no) OVER(PARTITION BY id_comp ORDER BY NEWID()
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) trip_no
FROM trip
ORDER BY id_comp;

Остался последний вопрос. Если мы не задавали параметры окна, почему у нас правильно отработал запрос с FIRST_VALUE?

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

Могу предположить, что тем же, что и для LAST_VALUE.