Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
ORDER BY b.book_date ) AS count FROM ticket_flights tf JOIN tickets t ON tf.ticket_no = t.ticket_no JOIN bookings b ON t.book_ref = b.book_ref WHERE tf.flight_id = 1 ORDER BY b.book_date; Рассмотрим конструкцию, предназначенную для вызова оконной функции: count( * ) OVER ( PARTITION BY date_trunc( 'month', b.book_date ) ORDER BY b.book_date ) AS count В этой конструкции обязательным является ключевое слово OVER. Функция count — это обычная агрегатная функция, но если вслед за ней идет это ключевое слово, то она становится оконной функцией. Предложение PARTITION BY задает правило раз- биения строк выборки на разделы. Предложение ORDER BY предписывает порядок сортировки строк в разделах. Обобщая приведенные объяснения, можно сказать, что раздел включает в себя все строки выборки, имеющие в некотором смысле одинаковые свойства, например, одинаковые значения определенных выражений, задаваемых с помощью предложе- ния PARTITION BY. Это могут быть выражения, построенные на основе одного или нескольких столбцов таблицы (или таблиц, участвующих в соединении). Оконный кадр состоит из подмножества строк данного раздела и привязан к текущей стро- ке. Для определения границ кадра важным является наличие предложения ORDER BY при формировании раздела. В рассмотренном примере границы оконного кад- ра определялись по умолчанию. Однако для указания этих границ предусмотрены различные способы. Подробно о них сказано в разделе документации 4.2.8 «Вызовы оконных функций». Не только функция count, но и другие агрегатные функции (например, sum, avg) то- же могут применяться в качестве оконных функций. Полный перечень собственно оконных функций приведен в документации в разделе 9.21 «Оконные функции». Оконные функции, в отличие от обычных агрегатных функций, не требуют группи- ровки строк, а работают на уровне отдельных (несгруппированных) строк. Однако 132 если в запросе присутствуют предложения GROUP BY и HAVING, тогда оконные функ- ции вызываются уже после них. В таком случае оконные функции будут работать со строками, являющимися результатом группировки. Рассмотрим еще один пример. Покажем, как с помощью оконной функции rank мож- но проранжировать аэропорты в пределах каждого часового пояса на основе их гео- графической широты. Причем будем присваивать более высокий ранг тому аэропор- ту, который находится севернее. SELECT airport_name, city, round( latitude::numeric, 2 ) AS ltd, timezone, rank() OVER ( PARTITION BY timezone ORDER BY latitude DESC ) FROM airports WHERE timezone IN ( 'Asia/Irkutsk', 'Asia/Krasnoyarsk' ) ORDER BY timezone, rank; В этом запросе в предложении OVER ( PARTITION BY timezone ... ) указывается, что строки относятся к одному разделу на основе совпадения значений в столбце timezone. Обратите внимание, что хотя в предложении OVER задан порядок сорти- ровки, действующий в пределах каждого окна, тем не менее, с помощью предложе- ния ORDER BY указан также и порядок сортировки на уровне всего запроса. airport_name | city | ltd | timezone | rank ---------------+---------------+-------+------------------+------ Усть-Илимск | Усть-Илимск | 58.14 | Asia/Irkutsk | 1 Усть-Кут | Усть-Кут | 56.85 | Asia/Irkutsk | 2 Братск | Братск | 56.37 | Asia/Irkutsk | 3 Иркутск | Иркутск | 52.27 | Asia/Irkutsk | 4 Байкал | Улан-Удэ | 51.81 | Asia/Irkutsk | 5 Норильск | Норильск | 69.31 | Asia/Krasnoyarsk | 1 Стрежевой | Стрежевой | 60.72 | Asia/Krasnoyarsk | 2 Богашёво | Томск | 56.38 | Asia/Krasnoyarsk | 3 Емельяново | Красноярск | 56.18 | Asia/Krasnoyarsk | 4 Абакан | Абакан | 53.74 | Asia/Krasnoyarsk | 5 Барнаул | Барнаул | 53.36 | Asia/Krasnoyarsk | 6 Горно-Алтайск | Горно-Алтайск | 51.97 | Asia/Krasnoyarsk | 7 Кызыл | Кызыл | 51.67 | Asia/Krasnoyarsk | 8 (13 строк) Усложним запрос — для каждого аэропорта будем вычислять разницу между его гео- графической широтой и широтой, на которой находится самый северный аэропорт в этом же часовом поясе. Поскольку в запросе используются три конструкции с окон- ными функциями и при этом способ формирования разделов и порядок сортировки строк в разделах один и тот же, то вводится предложение WINDOW. Оно позволяет создать определение раздела, а затем ссылаться на него при вызове оконных функ- ций. Самый северный аэропорт в каждом часовом поясе, т. е. самая первая строка в каждом разделе, выбирается с помощью оконной функции first_value. Строго говоря, эта функция получает доступ к первой строке оконного кадра, а не раздела. Однако когда используются правила формирования оконного кадра по умолчанию, тогда его начало совпадает с началом раздела. 133 Обратите внимание, что в этом запросе в каждой конструкции OVER используется ссылка на одно и то же окно, т. е. имеет место один и тот же порядок разбиения на разделы и сортировки строк, поэтому данные будут обработаны за один проход по таблице. SELECT airport_name, city, timezone, latitude, first_value( latitude ) OVER tz AS first_in_timezone, latitude - first_value( latitude ) OVER tz AS delta, rank() OVER tz FROM airports WHERE timezone IN ( 'Asia/Irkutsk', 'Asia/Krasnoyarsk' ) WINDOW tz AS ( PARTITION BY timezone ORDER BY latitude DESC ) ORDER BY timezone, rank; --[ RECORD 4 ]-----+----------------- airport_name | Иркутск city | Иркутск timezone | Asia/Irkutsk latitude | 52.268028 first_in_timezone | 58.135 delta | -5.866972 rank | 4 --[ RECORD 5 ]-----+----------------- airport_name | Байкал city | Улан-Удэ timezone | Asia/Irkutsk latitude | 51.807764 first_in_timezone | 58.135 delta | -6.327236 rank | 5 --[ RECORD 6 ]-----+----------------- airport_name | Норильск city | Норильск timezone | Asia/Krasnoyarsk latitude | 69.311053 first_in_timezone | 69.311053 delta | 0 rank | 1 --[ RECORD 7 ]-----+----------------- airport_name | Стрежевой city | Стрежевой timezone | Asia/Krasnoyarsk latitude | 60.716667 first_in_timezone | 69.311053 delta | -8.594386 rank | 2 --[ RECORD 8 ]-----+----------------- airport_name | Богашёво city | Томск 134 timezone | Asia/Krasnoyarsk latitude | 56.380278 first_in_timezone | 69.311053 delta | -12.930775 rank | 3 Более подробно использование оконных функций описано в документации. Мы ре- комендуем начать с раздела 3.5 «Оконные функции», в котором приводятся приме- ры их использования. В разделе 9.21 «Оконные функции» приводятся описания всех оконных функций, предлагаемых PostgreSQL. В разделе 4.2.8 «Вызовы оконных функ- ций» детально рассматривается синтаксис вызова оконных функций. В разделе 7.2.5 «Обработка оконных функций» говорится о том, на каком этапе выполнения запроса производится обработка этих функций. 6.4 Подзапросы Прежде чем приступить к рассмотрению столь сложной темы, как подзапросы, опи- шем, как в общем случае работает команда SELECT. Согласно описанию этой коман- ды, приведенному в документации на PostgreSQL, дело, в несколько упрощенном ви- де, обстоит так. 1. Сначала вычисляются все элементы, приведенные в списке после ключевого слова FROM. Под такими элементами подразумеваются не только реальные таб- лицы, но также и виртуальные таблицы, создаваемые с помощью ключевого слова VALUES. Если таблиц больше одной, то формируется декартово произве- дение из множеств их строк. Например, в случае двух таблиц будут сформиро- ваны попарные комбинации каждой строки из одной таблицы с каждой строкой из другой таблицы. При этом в комбинированных строках сохраняются все ат- рибуты из каждой исходной таблицы. 2. Если в команде присутствует условие WHERE, то из полученного декартова про- изведения исключаются строки, которые этому условию не соответствуют. Та- ким образом, первоначальное множество строк, сформированное без всяких условий, сужается. 3. Если присутствует предложение GROUP BY, то результирующие строки группи- руются на основе совпадения значений одного или нескольких атрибутов, а за- тем вычисляются значения агрегатных функций. Если присутствует предложе- ние HAVING, то оно отфильтровывает результирующие строки (группы), не удо- влетворяющие критерию. 4. Ключевое слово SELECT присутствует всегда. Но в списке выражений, идущих после него, могут быть не только простые имена атрибутов, но и их комбина- ции, созданные с использованием арифметических и других операций, а также вызовы функций. Причем эти функции могут быть не только встроенные, но и созданные пользователем. В списке выражений не обязаны присутствовать все 135 атрибуты, представленные в строках используемых таблиц. Например, атрибу- ты, на основе которых формируются условия в предложении WHERE, могут от- сутствовать в списке выражений после ключевого слова SELECT. Предложение SELECT DISTINCT удаляет дубликаты строк. 5. Если присутствует предложение ORDER BY, то результирующие строки сорти- руются на основе значений одного или нескольких атрибутов. По умолчанию сортировка производится по возрастанию значений. 6. Если присутствует предложение LIMIT или OFFSET, то возвращается только под- множество строк из выборки. Приведенная схема описывает работу команды SELECT на логическом уровне, а на уровне реализации запросов в дело вступает планировщик, который и формирует план выполнения запроса. А теперь перейдем непосредственно к теме этого параграфа — подзапросам. Предположим, что сотрудникам аналитического отдела потребовалось провести ста- тистическое исследование финансовых результатов работы авиакомпании. В каче- стве первого шага они решили подсчитать количество операций бронирования, в ко- торых общая сумма превышает среднюю величину по всей выборке. SELECT count( * ) FROM bookings WHERE total_amount > ( SELECT avg( total_amount ) FROM bookings ); count ------- 87224 (1 строка) В приведенном запросе присутствует два предложения SELECT, но при этом толь- ко одно из них является главным в этом запросе, а другое представляет собой под- запрос . Он заключается в круглые скобки и является частью более общего запроса. Подзапросы могут присутствовать в предложениях SELECT, FROM, WHERE и HAVING, а также в предложении WITH, о котором мы расскажем позднее. В приведенном примере в предложении WHERE используется так называемый ска- лярный подзапрос . Это означает, что в результате его выполнения возвращается только одно скалярное значение (один столбец и одна строка), с которым можно срав- нивать другие скалярные значения. Продолжим рассмотрение примеров использования подзапросов в предложении WHERE. Если подзапрос выдает множество скалярных значений (или даже только одно), то можно использовать такой подзапрос в предикате IN. Этот предикат позволяет ор- ганизовать проверку на предмет принадлежности какого-либо значения определен- ному множеству значений. В качестве примера давайте выясним, какие маршруты существуют между городами часового пояса Asia/Krasnoyarsk. Подзапрос будет выдавать список городов из этого часового пояса, а в предложении WHERE главного запроса с помощью предиката IN 136 будет выполняться проверка на принадлежность города этому списку. При этом под- запрос выполняется только один раз для всего внешнего запроса, а не при обработ- ке каждой строки из таблицы routes во внешнем запросе. Повторного выполнения подзапроса не требуется, т. к. его результат не зависит от значений, хранящихся в таблице routes. Такие подзапросы называются некоррелированными. SELECT flight_no, departure_city, arrival_city FROM routes WHERE departure_city IN ( SELECT city FROM airports WHERE timezone 'Krasnoyarsk' ) AND arrival_city IN ( SELECT city FROM airports WHERE timezone 'Krasnoyarsk' ); flight_no | departure_city | arrival_city -----------+----------------+-------------- PG0070 | Абакан | Томск PG0071 | Томск | Абакан PG0313 | Абакан | Кызыл PG0314 | Кызыл | Абакан PG0653 | Красноярск | Барнаул PG0654 | Барнаул | Красноярск (6 строк) Можно сформировать множество значений для предиката IN с помощью скалярных подзапросов. Если мы захотим найти самый западный и самый восточный аэропор- ты и представить полученные сведения в наглядной форме, то запрос может быть таким: SELECT airport_name, city, longitude FROM airports WHERE longitude IN ( ( SELECT max( longitude ) FROM airports ), ( SELECT min( longitude ) FROM airports ) ) ORDER BY longitude; airport_name | city | longitude --------------+-------------+------------ Храброво | Калининград | 20.592633 Анадырь | Анадырь | 177.741483 (2 строки) Конечно, в случае, когда необходимо, наоборот, исключить какие-либо значения из рассмотрения, можно использовать конструкцию NOT IN. Иногда возникают ситуации, когда от подзапроса требуется лишь установить сам факт наличия или отсутствия строк в конкретной таблице, удовлетворяющих опре- деленному условию, а непосредственные значения атрибутов в этих строках инте- 137 реса не представляют. В подобных случаях используют предикат EXISTS (или NOT EXISTS). В качестве примера выясним, в какие города нет рейсов из Москвы. SELECT DISTINCT a.city FROM airports a WHERE NOT EXISTS ( SELECT * FROM routes r WHERE r.departure_city = 'Москва' AND r.arrival_city = a.city ) AND a.city <> 'Москва' ORDER BY city; В этом запросе мы не можем ограничиться только лишь материализованным пред- ставлением «Маршруты» (routes), поскольку в нем представлены лишь существующие маршруты. Полный список городов можно найти в таблице «Аэропорты» (airports). Для каждой строки (каждого города) из таблицы airports выполняется поиск строки в представлении routes, в которой значение атрибута arrival_city такое же, как в те- кущей строке таблицы airports. Если такой строки не найдено, значит, в этот город маршрута из Москвы нет. Поскольку от подзапроса в предикате EXISTS требуется только установить факт на- личия или отсутствия строк, соответствующих критерию отбора, то в документации рекомендуется вместо списка столбцов (или символа «*») в предложении SELECT де- лать так: WHERE NOT EXISTS ( SELECT 1 FROM routes r ... Обратите внимание на ключевое слово DISTINCT в запросе. Оно необходимо, т. к. кроме Москвы могут быть другие города, в которых есть более одного аэропорта. Один такой город уже существует — Ульяновск. Если не использовать DISTINCT, то, в принципе, возможно появление строк-дубликатов в выборке. И еще одна важная деталь. В представленном запросе мы использовали так называ- емый коррелированный (связанный) подзапрос. В подзапросах такого типа при- сутствует ссылка (ссылки) на таблицу из внешнего запроса, как здесь: WHERE ... AND r.arrival_city = a.city В теории это означает, что подзапрос выполняется не один раз для всего внешнего за- проса, а для каждой строки, обрабатываемой во внешнем запросе. Однако на практи- ке важную роль играет умение планировщика (это специальная подсистема в СУБД) оптимизировать подобные запросы с тем, чтобы, по возможности, избегать выпол- нения подзапроса для каждой строки из внешнего запроса. Получаем такой результат: city ---------------------- Благовещенск Иваново 138 Якутск Ярославль (20 строк) Рассмотрим использование подзапросов в предложениях SELECT, FROM и HAVING. Предположим, что для выработки ценовой политики авиакомпании необходимо знать, как распределяются места разных классов в самолетах всех типов. Первый ва- риант решения этой задачи основан на включении подзапросов в предложение SELECT SELECT a.model, ( SELECT count( * ) FROM seats s WHERE s.aircraft_code = a.aircraft_code AND s.fare_conditions = 'Business' ) AS business, ( SELECT count( * ) FROM seats s WHERE s.aircraft_code = a.aircraft_code AND s.fare_conditions = 'Comfort' ) AS comfort, ( SELECT count( * ) FROM seats s WHERE s.aircraft_code = a.aircraft_code AND s.fare_conditions = 'Economy' ) AS economy FROM aircrafts a ORDER BY 1; Обратите внимание, что в этом запросе мы использовали коррелированные подза- просы. Все они ссылаются на столбец таблицы «Самолеты» (aircrafts), которая обраба- тывается во внешнем запросе. Для каждой обрабатываемой строки таблицы aircrafts подсчитывается число строк в таблице seats, в которых атрибут aircraft_code имеет такое же значение, что и в строке таблицы aircrafts. Подзапросы отличаются друг от друга только условием fare_conditions. Поскольку все эти подзапросы не зависят друг от друга, то, хотя все они обращаются к таблице «Места» (seats), не требуется исполь- зовать для нее различные псевдонимы в этих подзапросах. model | business | comfort | economy ---------------------+----------+---------+--------- Airbus A319-100 | 20 | 0 | 96 Airbus A320-200 | 20 | 0 | 120 Airbus A321-200 | 28 | 0 | 142 Boeing 737-300 | 12 | 0 | 118 Boeing 767-300 | 30 | 0 | 192 Boeing 777-300 | 30 | 48 | 324 Bombardier CRJ-200 | 0 | 0 | 50 Cessna 208 Caravan | 0 | 0 | 12 Sukhoi SuperJet-100 | 12 | 0 | 85 (9 строк) А в этом варианте решения задачи используется |