Учебное пособие СанктПетербург бхвпетербург
 Скачать 1.88 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; 172 6.3. Агрегирование и группировка Рассмотрим конструкцию, предназначенную для вызова оконной функции: 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 «Оконные функции». Оконные функции, в отличие от обычных агрегатных функций, не требуют группи- ровки строк, а работают на уровне отдельных (несгруппированных) строк. Однако ес- ли в запросе присутствуют предложения GROUP BY и HAVING, тогда оконные функции вызываются уже после них. В таком случае оконные функции будут работать со стро- ками, являющимися результатом группировки. Рассмотрим еще один пример. Покажем, как с помощью оконной функции rank мож- но проранжировать аэропорты в пределах каждого часового пояса на основе их гео- графической широты. Причем будем присваивать более высокий ранг тому аэропор- ту, который находится севернее. 173 Глава 6. Запросы 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 Абакан | Абакан | 53.74 | Asia/Krasnoyarsk | 5 Барнаул | Барнаул | 53.36 | Asia/Krasnoyarsk | 6 Горно-Алтайск | Горно-Алтайск | 51.97 | Asia/Krasnoyarsk | 7 Кызыл | Кызыл | 51.67 | Asia/Krasnoyarsk | 8 (13 строк) Усложним запрос — для каждого аэропорта будем вычислять разницу между его гео- графической широтой и широтой, на которой находится самый северный аэропорт в этом же часовом поясе. Поскольку в запросе используются три конструкции с окон- ными функциями и при этом способ формирования разделов и порядок сортировки строк в разделах один и тот же, то вводится предложение WINDOW. Оно позволяет со- здать определение раздела, а затем ссылаться на него при вызове оконных функций. Самый северный аэропорт в каждом часовом поясе, т. е. самая первая строка в каж- дом разделе, выбирается с помощью оконной функции first_value. Строго говоря, эта функция получает доступ к первой строке оконного кадра, а не раздела. Однако когда используются правила формирования оконного кадра по умолчанию, тогда его начало совпадает с началом раздела. 174 6.3. Агрегирование и группировка Обратите внимание, что в этом запросе в каждой конструкции 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 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 Более подробно использование оконных функций описано в документации. Мы ре- комендуем начать с раздела 3.5 «Оконные функции», в котором приводятся приме- ры их использования. В разделе 9.21 «Оконные функции» приводятся описания всех оконных функций, предлагаемых PostgreSQL. В разделе 4.2.8 «Вызовы оконных функ- ций» детально рассматривается синтаксис вызова оконных функций. В разделе 7.2.5 «Обработка оконных функций» говорится о том, на каком этапе выполнения запроса производится обработка этих функций. 175 Глава 6. Запросы 6.4. Подзапросы Прежде чем приступить к рассмотрению столь сложной темы, как подзапросы, опи- шем, как в общем случае работает команда SELECT. Согласно описанию этой коман- ды, приведенному в документации, дело, в несколько упрощенном виде, обстоит так. 1. Сначала вычисляются все элементы, приведенные в списке после ключевого слова FROM. Под такими элементами подразумеваются не только реальные таб- лицы, но также и виртуальные таблицы, создаваемые с помощью ключевого слова VALUES. Если таблиц больше одной, то формируется декартово произве- дение из множеств их строк. Например, в случае двух таблиц будут сформиро- ваны попарные комбинации каждой строки из одной таблицы с каждой строкой из другой таблицы. При этом в комбинированных строках сохраняются все ат- рибуты из каждой исходной таблицы. 2. Если в команде присутствует условие WHERE, то из полученного декартова про- изведения исключаются строки, которые этому условию не соответствуют. Та- ким образом, первоначальное множество строк, сформированное без всяких условий, сужается. 3. Если присутствует предложение GROUP BY, то результирующие строки группи- руются на основе совпадения значений одного или нескольких атрибутов, а за- тем вычисляются значения агрегатных функций. Если присутствует предложе- ние HAVING, то оно отфильтровывает результирующие строки (группы), не удо- влетворяющие критерию. 4. Ключевое слово SELECT присутствует всегда. Но в списке выражений, идущих после него, могут быть не только простые имена атрибутов, но и их комбина- ции, созданные с использованием арифметических и других операций, а также вызовы функций. Причем эти функции могут быть не только встроенные, но и созданные пользователем. В списке выражений не обязаны присутствовать все атрибуты, представленные в строках используемых таблиц. Например, ат- рибуты, на основе которых формируются условия в предложении WHERE, могут отсутствовать в списке выражений после ключевого слова SELECT. Предложе- ние SELECT DISTINCT удаляет дубликаты строк. 5. Если присутствует предложение ORDER BY, то результирующие строки сорти- руются на основе значений одного или нескольких атрибутов. По умолчанию сортировка производится по возрастанию значений. 6. Если присутствует предложение LIMIT или OFFSET, то возвращается только подмножество строк из выборки. 176 6.4. Подзапросы Приведенная схема описывает работу команды SELECT на логическом уровне, а на уровне реализации запросов в дело вступает планировщик, который и формирует план выполнения запроса. А теперь перейдем непосредственно к теме этого раздела — подзапросам. Предположим, что сотрудникам аналитического отдела потребовалось провести ста- тистическое исследование финансовых результатов работы авиакомпании. В каче- стве первого шага они решили подсчитать количество операций бронирования, в ко- торых общая сумма превышает среднюю величину по всей выборке. SELECT count( * ) FROM bookings WHERE total_amount > ( SELECT avg( total_amount ) FROM bookings ); count ------- 87224 (1 строка) В приведенном запросе присутствует два предложения SELECT, но при этом толь- ко одно из них является главным в этом запросе, а другое представляет собой под- запрос . Он заключается в круглые скобки и является частью более общего запроса. Подзапросы могут присутствовать в предложениях SELECT, FROM, WHERE и HAVING, а также в предложении WITH, о котором мы расскажем позднее. В приведенном примере в предложении WHERE используется так называемый ска- лярный подзапрос . Это означает, что в результате его выполнения возвращается только одно скалярное значение (один столбец и одна строка), с которым можно срав- нивать другие скалярные значения. Если подзапрос выдает множество скалярных значений (или даже только одно), мож- но использовать такой подзапрос в предикате IN. Этот предикат позволяет органи- зовать проверку на предмет принадлежности какого-либо значения определенному множеству значений. В качестве примера давайте выясним, какие маршруты существуют между городами часового пояса Asia/Krasnoyarsk. Подзапрос будет выдавать список городов из это- го часового пояса, а в предложении WHERE главного запроса с помощью предиката IN будет выполняться проверка на принадлежность города этому списку. При этом подзапрос выполняется только один раз для всего внешнего запроса, а не при обра- ботке каждой строки из таблицы routes во внешнем запросе. Повторного выполне- ния подзапроса не требуется, т. к. его результат не зависит от значений, хранящихся в таблице routes. Такие подзапросы называются некоррелированными. 177 Глава 6. Запросы 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. 178 6.4. Подзапросы Иногда возникают ситуации, когда от подзапроса требуется лишь установить сам факт наличия или отсутствия строк в конкретной таблице, удовлетворяющих опре- деленному условию, а непосредственные значения атрибутов в этих строках инте- реса не представляют. В подобных случаях используют предикат 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 179 Глава 6. Запросы В теории это означает, что подзапрос выполняется не один раз для всего внешне- го запроса, а для каждой строки, обрабатываемой во внешнем запросе. Однако на практике важную роль играет умение планировщика (это специальная подсистема в СУБД) оптимизировать подобные запросы с тем, чтобы по возможности избегать выполнения подзапроса для каждой строки из внешнего запроса. Получаем такой результат: city ---------------------- Благовещенск Иваново Якутск Ярославль (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; 180 6.4. Подзапросы Обратите внимание, что в этом запросе мы использовали коррелированные под- запросы. Все они ссылаются на столбец таблицы «Самолеты» (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 строк) А в этом варианте решения задачи используется подзапрос в предложении FROM. SELECT s2.model, string_agg( s2.fare_conditions || ' (' || s2.num || ')', ', ' ) FROM ( SELECT a.model, s.fare_conditions, count( * ) AS num FROM aircrafts a JOIN seats s ON a.aircraft_code = s.aircraft_code GROUP BY 1, 2 ORDER BY 1, 2 ) AS s2 GROUP BY s2.model ORDER BY s2.model; 181 Глава 6. Запросы Подзапрос формирует временную таблицу в таком виде: model | fare_conditions | num ---------------------+-----------------+----- Airbus A319-100 | Business | 20 Airbus A319-100 | Economy | 96 Sukhoi SuperJet-100 | Business | 12 Sukhoi SuperJet-100 | Economy | 85 (17 строк) А в главном (внешнем) запросе используется агрегатная функция string_agg для формирования результирующего значения на основе сгруппированных строк. Эта функция отличается от агрегатных функций avg, min, max, sum и count тем, что воз- вращает не числовое значение, а строку символов, составленную из значений атрибу- тов, указанных в качестве ее параметров. Эти значения берутся из сгруппированных строк. model | string_agg ---------------------+-------------------------------------------- Airbus A319-100 | Business (20), Economy (96) Airbus A320-200 | Business (20), Economy (120) Airbus A321-200 | Business (28), Economy (142) Boeing 737-300 | Business (12), Economy (118) Boeing 767-300 | Business (30), Economy (192) Boeing 777-300 | Business (30), Comfort (48), Economy (324) Bombardier CRJ-200 | Economy (50) Cessna 208 Caravan | Economy (12) Sukhoi SuperJet-100 | Business (12), Economy (85) (9 строк) В качестве еще одного примера использования подзапроса в предложении FROM ре- шим такую задачу: получить перечень аэропортов в тех городах, в которых больше одного аэропорта. |