loading..
Русский    English
00:00
листать

Функции FIRST_VALUE и LAST_VALUE

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

(база данных аэропорт).

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

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

Консоль
Выполнить
  1. SELECT id_comp,
  2.   (SELECT TOP 1 trip_no FROM trip t WHERE c.id_comp = t.id_comp ORDER BY NEWID()) trip_no
  3. FROM company c
  4. ORDER BY id_comp;

id_comp    trip_no
1    1188
2    1146
3    1124
4    1101
5    7771

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

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

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

Консоль
Выполнить
  1. SELECT DISTINCT id_comp,
  2. FIRST_VALUE(trip_no) OVER(PARTITION BY id_comp ORDER BY NEWID()) trip_no
  3. FROM trip
  4. ORDER BY id_comp;

id_comp    trip_no
1    1195
2    1145
3    1124
4    1100
5    8882

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

Засада с LAST_VALUE

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

Консоль
Выполнить
  1. SELECT DISTINCT id_comp,
  2. LAST_VALUE(trip_no) OVER(PARTITION BY id_comp ORDER BY NEWID()) trip_no
  3. FROM trip
  4. ORDER BY id_comp;

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

id_comp    trip_no
1    1181
1    1182
1    1187
1    1188
1    1195
1    1196

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

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

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

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

Внимание:

Диапазоном по умолчанию является RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

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

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

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

Консоль
Выполнить
  1. SELECT DISTINCT id_comp,
  2. LAST_VALUE(trip_no) OVER(PARTITION BY id_comp ORDER BY NEWID()
  3. RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) trip_no
  4. FROM trip
  5. ORDER BY id_comp;

id_comp    trip_no
1    1188
2    1145
3    1123
4    1101
5    7773

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

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

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

Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
вчера
Иван Малюшин (IvashKO)
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.