лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
PARTITION BY. Именно скорость текущей строки таблицы и будет определять группу для вычисления среднего значения. Решение 1. SELECT *, price - AVG ( price ) OVER ( PARTITION BY speed ) AS dprice 2. FROM PC; Другое решение этой задачи можно построить с помощью коррелирующего подзапроса 1. SELECT *, price - ( SELECT AVG ( price ) FROM PC AS PC1 WHERE PC1.speed = PC.speed ) AS dprice 2. FROM PC; Функции LAG и LEAD Синтаксис: 1. LAG | LEAD ( < скалярное выражение > [ ,< сдвиг > ] [ , < значение по умолчанию > ]) 2. OVER ( [ < предложение partition BY > ] < предложение ORDER BY > ) Оконные функции LAG и LEAD появились в SQL Server, начиная с версии 2012. Эти функции возвращают значение выражения, вычисленного для предыдущей строки (LAG) или следующей строки (LEAD) результирующего набора соответственно. Рассмотрим простой пример запроса, выводящего коды (code) принтеров вместе с кодами из предыдущей и следующей строк: 1. SELECT code, 2. LAG ( code ) OVER ( ORDER BY code ) prev_code, 3. LEAD ( code ) OVER ( ORDER BY code ) next_code 4. FROM printer; code prev_code next_code 1 NULL 2 2 1 3 3 2 4 4 3 5 5 4 6 6 5 NULL Обратите внимание, что если следующей или предыдущей строки (в порядке возрастания значения code) не существует, то используется NULL-значение. Однако такое поведение можно поменять с помощью необязательного (третьего) параметра каждой функции. Значение этого параметра будет использоваться в том случае, если соответствующей строки не существует. В нижеследующем примере используется значение -999, если предыдущей строки не существует, и 999, если не существует следующей строки. 1. SELECT code, 2. LAG ( code, 1 , -999 ) OVER ( ORDER BY code ) prev_code, 3. LEAD ( code, 1 , 999 ) OVER ( ORDER BY code ) next_code 4. FROM printer; code prev_code next_code 1 -999 2 2 1 3 3 2 4 4 3 5 5 4 6 6 5 999 Чтобы указать третий параметр, нам пришлось использовать и второй необязательный параметр с значением 1, которое принимается по умолчанию. Этот параметр определяет, какую из предыдущих (последующих) строк следует использовать, т.е. на сколько данная строка отстоит от текущей. В следующем примере берется строка, идущая через одну от текущей. 1. SELECT code, 2. LAG ( code, 2 , -999 ) OVER ( ORDER BY code ) prev_code, 3. LEAD ( code, 2 , 999 ) OVER ( ORDER BY code ) next_code 4. FROM printer; code prev_code next_code 1 -999 3 2 -999 4 3 1 5 4 2 6 5 3 999 6 4 999 В заключение отметим, что порядок, в котором выбираются следующие и предыдущие строки задаётся предложением ORDER BY в предложении OVER, а не сортировкой, используемой в запросе. Вот пример, который иллюстрирует сказанное. 1. SELECT code, 2. LAG ( code ) OVER ( ORDER BY code ) prev_code, 3. LEAD ( code ) OVER ( ORDER BY code ) next_code 4. FROM printer 5. ORDER BY code DESC ; code prev_code next_code 6 5 NULL 5 4 6 4 3 5 3 2 4 2 1 3 1 NULL 2 Чтобы оценить преимущество, которое предоставляет появление в языке SQL данных функций, рассмотрим "классические" решения данной задачи. Самосоединение 1. SELECT p1.code,p3.code,p2.code 2. FROM printer p1 LEFT JOIN Printer p2 ON p1.code=p2.code - 1 3. LEFT JOIN Printer p3 ON p1.code=p3.code +1 ; Коррелирующий подзапрос 1. SELECT p1.code, 2. ( SELECT MAX ( p3.code ) FROM Printer p3 WHERE p3.code < p1.code ) prev_code, 3. ( SELECT MIN ( p2.code ) FROM Printer p2 WHERE p2.code > p1.code ) next_code 4. FROM printer p1; Функции 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. COUNT DISTINCT и оконные функции Мы без проблем можем посчитать общее количество ПК для каждого производителя, а также количество уникальных моделей данного производителя в таблице PC: 1. SELECT maker, COUNT ( * ) models, COUNT ( DISTINCT pc.model ) unique_models 2. FROM product p JOIN pc ON p.model=pc.model 3. GROUP BY maker 4. ORDER BY maker; maker models unique_models A 8 2 B 3 1 E 1 1 Если нам требуется получить детальную информацию о каждой модели, наряду с их общим количеством для каждого производителя, то можно использовать оконную функцию: 1. SELECT maker, pc.model,pc.price, 2. COUNT ( * ) over ( partition BY maker ) models 3. FROM product p JOIN pc ON p.model=pc.model 4. ORDER BY maker, pc.model; maker model price models A 1232 600,00 8 A 1232 400,00 8 A 1232 350,00 8 A 1232 350,00 8 A 1233 600,00 8 A 1233 950,00 8 A 1233 980,00 8 A 1233 970,00 8 B 1121 850,00 3 B 1121 850,00 3 B 1121 850,00 3 E 1260 350,00 1 Теперь представим, что нам требуется дополнить эту информацию количеством уникальных моделей. Естественная попытка 1. SELECT maker, pc.model,pc.price, 2. COUNT ( * ) over ( partition BY maker ) models, 3. COUNT ( DISTINCT pc.model ) over ( partition BY maker ) unique_models 4. FROM product p JOIN pc ON p.model=pc.model 5. ORDER BY maker, pc.model; терпит неудачу: Использование ключевого слова DISTINCT не допускается с предложением OVER. Сообщение об ошибке ясно описывает проблему. Вопрос в том, как её обойти. Использование подзапроса 1. WITH cte AS 2. ( SELECT maker, pc.model,pc.price, 3. COUNT ( * ) over ( partition BY maker ) models 4. FROM product p JOIN pc ON p.model=pc.model ) 5. SELECT maker, model, models, 6. ( SELECT COUNT ( DISTINCT model ) 7. FROM cte t WHERE t.maker=cte.maker ) unique_models 8. FROM cte 9. ORDER BY maker,model; maker model models unique_models A 1232 8 2 A 1232 8 2 A 1232 8 2 A 1232 8 2 A 1233 8 2 A 1233 8 2 A 1233 8 2 A 1233 8 2 B 1121 3 1 B 1121 3 1 B 1121 3 1 E 1260 1 1 Использование DENSE_RANK 1. WITH cte AS 2. ( SELECT maker, pc.model,pc.price, 3. COUNT ( * ) over ( partition BY maker ) models, 4. DENSE_RANK () over ( partition BY maker ORDER BY pc.model ) drnk 5. FROM product p JOIN pc ON p.model=pc.model ) 6. SELECT maker, model, price, models, 7. MAX ( drnk ) over ( partition BY maker ) unique_models FROM cte 8. ORDER BY maker, model; Здесь мы воспользовались тем фактом, что последнее ранговое значение - max(drnk) - оказывается равным числу уникальных моделей. CROSS APPLY / OUTER APPLY Оператор CROSS APPLY появился в SQL Server 2005. Он позволяет выполнить соединение двух табличных выражений. При этом каждая строка из левой таблицы сочетается с каждой строкой из правой. Давайте попробуем разобраться в том, какие преимущества дает нам использование этого нестандартного оператора. Первый пример. 1. SELECT * FROM 2. Product 3. CROSS APPLY 4. Laptop; Мы получили просто декартово произведение таблиц Product и Laptop. Аналогичный результат мы можем получить с помощью следующих стандартных запросов: 1. SELECT * FROM 2. Product 3. CROSS JOIN 4. Laptop; Или 1. SELECT * FROM 2. Product, Laptop; Поставим теперь более осмысленную задачу. Для каждого ноутбука дополнительно вывести имя производителя. Эту задачу мы можем решить с помощью обычного соединения: 1. SELECT P.maker, L.* FROM 2. Product P JOIN Laptop L ON P.model= L.model; С помощью CROSS APPLY решение этой же задачи можно написать так: 1. SELECT P.maker, L.* FROM 2. Product P 3. CROSS APPLY 4. ( SELECT * FROM Laptop L WHERE P.model= L.model ) L; "И что тут нового"? - спросите вы. Запрос стал даже более громоздким. Пока да, можно согласиться. Но уже здесь можно заметить весьма важную вещь, которая отличает CROSS APPLY от других видов соединений. А именно, мы используем коррелирующий подзапрос в предложении FROM, передавая в него значения из левого табличного выражения. В данном примере это значения из столбца P.model. Т.е. для каждой строки из левой таблицы правая таблица будет своя. Поняв это, мы можем воспользоваться данными преимуществами. Рассмотрим следующую задачу. Для каждого ноутбука дополнительно вывести максимальную цену среди ноутбуков того же производителя. Эту задачу мы можем решить с помощью коррелирующего подзапроса в предложении SELECT: 1. SELECT *, ( SELECT MAX ( price ) FROM Laptop L2 2. JOIN Product P1 ON L2.model=P1.model 3. WHERE maker = ( SELECT maker FROM Product P2 WHERE P2.model= L1.model )) max_price 4. FROM laptop L1; Пока решение, использующее CROSS APPLY, будет мало чем отличаться от вышеприведенного: 1. SELECT * 2. FROM laptop L1 3. CROSS APPLY 4. ( SELECT MAX ( price ) max_price FROM Laptop L2 5. JOIN Product P1 ON L2.model=P1.model 6. WHERE maker = ( SELECT maker FROM Product P2 WHERE P2.model= L1.model )) X; А теперь представьте, что нам нужно, помимо максимальной цены, вывести минимальную, среднюю цены и т.д. Поскольку коррелирующий подзапрос в предложении SELECT должен возвращать только одно значение, в первом варианте решения нам придется фактически дублировать код для каждого агрегата: 1. SELECT *, ( SELECT MAX ( price ) FROM Laptop L2 2. JOIN Product P1 ON L2.model=P1.model 3. WHERE maker = ( SELECT maker FROM Product P2 WHERE P2.model= L1.model )) max_price, 4. ( SELECT MIN ( price ) FROM Laptop L2 5. JOIN Product P1 ON L2.model=P1.model 6. WHERE maker = ( SELECT maker FROM Product P2 WHERE P2.model= L1.model )) min_price 7. FROM Laptop L1; и т.д. А при использовании CROSS APPLY мы просто добавим в подзапрос требуемую агрегатную функцию: 1. SELECT * 2. FROM laptop L1 3. CROSS APPLY 4. ( SELECT MAX ( price ) max_price, MIN ( price ) min_price FROM Laptop L2 5. JOIN Product P1 ON L2.model=P1.model 6. WHERE maker = ( SELECT maker FROM Product P2 WHERE P2.model= L1.model )) X; Рассмотрим еще один пример. Соединить каждую строку из таблицы Laptop со следующей строкой в порядке, заданном сортировкой (model, code). Столбец code в сортировке используется для того, чтобы задать однозначный порядок для строк, имеющих одинаковые значения в столбце model. С помощью CROSS APPLY мы можем передать в подзапрос параметры текущей строки и выбрать первую строку из тех, которые идут ниже текущей в заданном сортировкой порядке. Итак, 1. SELECT * FROM laptop L1 2. CROSS APPLY 3. ( SELECT TOP 1 * FROM Laptop L2 4. WHERE L1.model < L2.model OR ( L1.model = L2.model AND L1.code < L2.code ) 5. ORDER BY model, code ) X 6. ORDER BY L1.model; Попробуйте решить эту задачу традиционными средствами, чтобы сравнить трудозатраты. Оператор OUTER APPLY Как показывают результаты предыдущего запроса, мы "потеряли" последнюю (шестую) строку из таблицы Laptop, поскольку ее не с чем соединять. Другими словами, CROSS APPLY ведет себя как внутренне соединение. Аналогом же внешнего (левого) соединения является оператор OUTER APPLY. Он отличается от CROSS APPLY только тем, что выводит все строки из левой таблицы, заменяя отсутствующие значения из правой таблицы NULL-значениями. Замена CROSS APPLY на OUTER APPLY в предыдущем запросе иллюстрирует сказанное. 1. SELECT * FROM laptop L1 2. OUTER APPLY 3. ( SELECT TOP 1 * 4. FROM Laptop L2 5. WHERE L1.model < L2.model OR ( L1.model = L2.model AND L1.code < L2.code ) 6. ORDER BY model, code ) X 7. ORDER BY L1.model; Еще одной популярной задачей является вывод по N строк из каждой группы. Примером может служить вывод 5 наиболее популярных товаров в каждой категории. Рассмотрим следующую задачу. Вывести из таблицы Product по три модели с наименьшими номерами из каждой группы, характеризуемой типом продукции. Дополним решения , предложенные на сайте sql-ex.ru, решением, использующим CROSS APPLY. Идея заключается в соединении уникальных типов (первый запрос) с запросом, выводящих по 3 модели модели каждого типа из первого запроса в соответствии с требуемой сортировкой. 1. SELECT X.* FROM 2. ( SELECT DISTINCT type FROM product ) Pr1 3. CROSS APPLY 4. ( SELECT TOP 3 * FROM product Pr2 WHERE Pr1.type=Pr2.type ORDER BY pr2.model ) x; В заключение давайте рассмотрим пример задачи, которая часто встречается на практике, а именно, задачи расположения в столбец значений из строки таблицы. Для конкретизации сформулируем задачу таким образом. Для таблицы Laptop представить информацию о продуктах в три столбца: code, название характеристики (speed, ram, hd или screen), значение характеристики. Метод решения состоит в использовании конструктора таблицы , куда с помощью CROSS APPLY будут передаваться значения столбцов. Давайте разберем этот метод подробно. Конструктор таблицы может использоваться не только в операторе INSERT , но и для задания таблицы в предложении FROM, например, 1. SELECT name, value 2. FROM ( 3. VALUES ( 'speed' , 1 ) 4. , ( 'ram' , 1 ) 5. , ( 'hd' , 1 ) 6. , ( 'screen' , 1 ) 7. ) Spec ( name, value ) ; Эта таблица у нас называется Spec и содержит два столбца - name (символьные строки) и value (числа). Давайте теперь включим эту таблицу в оператор CROSS APPLY, который будет соединять каждую строку из таблицы Laptop с четырьмя строками из сгенерированной таблицы: 1. SELECT code, name, value 2. FROM Laptop 3. CROSS APPLY ( 4. VALUES ( 'speed' , 1 ) 5. , ( 'ram' , 1 ) 6. , ( 'hd' , 1 ) 7. , ( 'screen' , 1 ) 8. ) Spec ( name, value ) 9. WHERE code < 4 -- для уменьшения размера выборки 10. ; Собственно, нам осталось воспользоваться основным свойством оператора CROSS APPLY - коррелированностью табличного выражения - и заменить единички в столбце value на имена столбцов из соединяемой таблицы: 1. SELECT code, name, value FROM Laptop 2. CROSS APPLY 3. ( VALUES ( 'speed' , speed ) 4. , ( 'ram' , ram ) 5. , ( 'hd' , hd ) 6. , ( 'screen' , screen ) 7. ) spec ( name, value ) 8. WHERE code < 4 -- для уменьшения размера выборки 9. ORDER BY code, name, value; PostgreSQL обладает аналогичной функциональностью. Синтаксические отличия незначительны и состоят в замене CROSS APPLY на CROSS JOIN LATERAL. Сравните три примера, которые рассматривались на предыдущих страницах этой главы. Пример 1 SQL Server 1. SELECT * 2. FROM laptop L1 3. CROSS APPLY 4. ( SELECT MAX ( price ) max_price, MIN ( price ) min_price FROM Laptop L2 5. JOIN Product P1 ON L2.model=P1.model 6. WHERE maker = ( SELECT maker FROM Product P2 WHERE P2.model= L1.model )) X; PostgreSQL 1. SELECT * 2. FROM laptop L1 3. CROSS JOIN LATERAL 4. ( SELECT MAX ( price ) max_price, MIN ( price ) min_price FROM Laptop L2 5. JOIN Product P1 ON L2.model=P1.model 6. WHERE maker = ( SELECT maker FROM Product P2 WHERE P2.model= L1.model )) X; Пример 2 SQL Server 1. SELECT code, name, value FROM Laptop 2. CROSS APPLY 3. ( VALUES ( 'speed' , speed ) 4. , ( 'ram' , ram ) 5. , ( 'hd' , hd ) 6. , ( 'screen' , screen ) 7. ) spec ( name, value ) 8. WHERE code < 4 9. ORDER BY code, name, value; PostgreSQL 1. SELECT code, name, value FROM Laptop 2. CROSS JOIN LATERAL 3. ( VALUES ( 'speed' , speed ) 4. , ( 'ram' , ram ) 5. , ( 'hd' , hd ) 6. , ( 'screen' , screen ) 7. ) spec ( name, value ) 8. WHERE code < 4 9. ORDER BY code, name, value; Пример 3 SQL Server 1. SELECT * FROM laptop L1 2. CROSS APPLY 3. ( SELECT TOP 1 * FROM Laptop L2 4. WHERE L1.model < L2.model OR ( L1.model = L2.model AND L1.code < L2.code ) 5. ORDER BY model, code ) X 6. ORDER BY L1.model; PostgreSQL Дополнительное отличие в этом примере связано не с реализацией CROSS APPLY, а с тем, что для ограничения выборки PostgreSQL вместо конструкции TOP(n) использеут LIMIT n в предложении ORDER BY. 1. SELECT * FROM laptop L1 2. CROSS JOIN LATERAL 3. ( SELECT * FROM Laptop L2 4. WHERE L1.model < L2.model OR ( L1.model = L2.model AND L1.code < L2.code ) 5. ORDER BY model, code LIMIT 1 ) X 6. ORDER BY L1.model; OUTER APPLY Для данного "внешнего" соединения в PostgreSQL используется LEFT JOIN LATERAL. Сравните запросы в примере 4. Пример 4 SQL Server 1. SELECT * FROM laptop L1 2. OUTER APPLY 3. ( SELECT TOP 1 * 4. FROM Laptop L2 5. WHERE L1.model < L2.model OR ( L1.model = L2.model AND L1.code < L2.code ) 6. ORDER BY model, code ) X 7. ORDER BY L1.model; PostgreSQL Обратите внимание на предикат ON TRUE. Поскольку синтаксис соединения [LEFT|RIGHT [OUTER]] JOIN требует предиката, то для единообразия используется "фиктивный" предикат, имеющий значение ИСТИНА. 1. SELECT * FROM laptop L1 2. LEFT JOIN LATERAL 3. ( SELECT * 4. FROM Laptop L2 5. WHERE L1.model < L2.model OR ( L1.model = L2.model AND L1.code < L2.code ) 6. ORDER BY model, code LIMIT 1 ) X ON TRUE 7. ORDER BY L1.model; |