Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
подзапрос в предложении FROM 139 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; Подзапрос формирует временную таблицу в таком виде: model | fare_conditions | num ---------------------+-----------------+----- Airbus A319-100 | Business | 20 Airbus A319-100 | Economy | 96 Airbus A320-200 | Business | 20 Airbus A320-200 | Economy | 120 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 ре- шим такую задачу: получить перечень аэропортов в тех городах, в которых больше одного аэропорта. SELECT aa.city, aa.airport_code, aa.airport_name FROM ( 140 SELECT city, count( * ) FROM airports GROUP BY city HAVING count( * ) > 1 ) AS a JOIN airports AS aa ON a.city = aa.city ORDER BY aa.city, aa.airport_name; Благодаря использованию предложения HAVING, подзапрос выбирает города, в ко- торых более одного аэропорта, и формирует временную таблицу в таком виде: city | count -----------+------- Ульяновск | 2 Москва | 3 (2 строки) А в главном запросе выполняется соединение временной таблицы с таблицей «Аэро- порты» (airports). city | airport_code | airport_name -----------+--------------+--------------------- Москва | VKO | Внуково Москва | DME | Домодедово Москва | SVO | Шереметьево Ульяновск | ULV | Баратаевка Ульяновск | ULY | Ульяновск-Восточный (5 строк) Для иллюстрации использования подзапросов в предложении HAVING решим та- кую задачу: определить число маршрутов, исходящих из тех аэропортов, которые расположены восточнее географической долготы 150 ◦ SELECT departure_airport, departure_city, count( * ) FROM routes GROUP BY departure_airport, departure_city HAVING departure_airport IN ( SELECT airport_code FROM airports WHERE longitude > 150 ) ORDER BY count DESC; Подзапрос формирует список аэропортов, которые и будут отобраны с помощью предложения HAVING после выполнения группировки. departure_airport | departure_city | count -------------------+--------------------------+------- DYR | Анадырь | 4 GDX | Магадан | 3 PKC | Петропавловск-Камчатский | 1 (3 строки) 141 В сложных запросах могут использоваться вложенные подзапросы. Это означает, что один подзапрос находится внутри другого. Давайте в качестве примера рассмот- рим такую ситуацию: руководство авиакомпании хочет выяснить степень заполне- ния самолетов на всех рейсах, ведь отправлять полупустые самолеты не очень выгод- но. Таким образом, запрос должен не только выдавать число билетов, проданных на данный рейс, и общее число мест в самолете, но должен также вычислять отношение этих двух показателей. Вот какой запрос получился: SELECT ts.flight_id, ts.flight_no, ts.scheduled_departure_local, ts.departure_city, ts.arrival_city, a.model, ts.fact_passengers, ts.total_seats, round( ts.fact_passengers::numeric / ts.total_seats::numeric, 2 ) AS fraction FROM ( SELECT f.flight_id, f.flight_no, f.scheduled_departure_local, f.departure_city, f.arrival_city, f.aircraft_code, count( tf.ticket_no ) AS fact_passengers, ( SELECT count( s.seat_no ) FROM seats s WHERE s.aircraft_code = f.aircraft_code ) AS total_seats FROM flights_v f JOIN ticket_flights tf ON f.flight_id = tf.flight_id WHERE f.status = 'Arrived' GROUP BY 1, 2, 3, 4, 5, 6 ) AS ts JOIN aircrafts AS a ON ts.aircraft_code = a.aircraft_code ORDER BY ts.scheduled_departure_local; Самый внутренний подзапрос — total_seats — выдает общее число мест в самолете. Этот подзапрос — коррелированный, т. к. он выполняется для каждой строки, обра- батываемой во внешнем подзапросе, т. е. для каждой модели самолета. Для подсче- та числа проданных билетов мы использовали соединение представления «Рейсы» (flights_v) с таблицей «Перелеты» (ticket_flights) с последующей группировкой строк и вызовом функции count. Конечно, можно было бы вместо такого решения исполь- зовать еще один коррелированный подзапрос: ( SELECT count( tf.ticket_no ) FROM ticket_flights tf WHERE tf.flight_id = f.flight_id ) AS fact_passengers 142 В таком случае уже не потребовалось бы выполнять соединение представления flights_v с таблицей ticket_flights и группировать строки, достаточно было бы сделать так: FROM flights_v WHERE f.status = 'Arrived' ) AS ts JOIN aircrafts AS a ... Внешний запрос вместо кода самолета выводит наименование модели, выбирает остальные столбцы из подзапроса без изменений и дополнительно производит вы- числение степени заполнения самолета пассажирами, а также сортирует результиру- ющие строки. --[ RECORD 1 ]-------------+------------------------- flight_id | 28205 flight_no | PG0032 scheduled_departure_local | 2016-09-13 08:00:00 departure_city | Пенза arrival_city | Москва model | Cessna 208 Caravan fact_passengers | 2 total_seats | 12 fraction | 0.17 --[ RECORD 2 ]-------------+------------------------- flight_id | 9467 flight_no | PG0360 scheduled_departure_local | 2016-09-13 08:00:00 departure_city | Санкт-Петербург arrival_city | Оренбург model | Bombardier CRJ-200 fact_passengers | 6 total_seats | 50 fraction | 0.12 --[ RECORD 3 ]-------------+------------------------- flight_id | 7130 flight_no | PG0591 scheduled_departure_local | 2016-09-13 08:00:00 departure_city | Москва arrival_city | Томск model | Sukhoi SuperJet-100 fact_passengers | 25 total_seats | 97 fraction | 0.26 Рассмотренный сложный запрос можно сделать более наглядным за счет выделения подзапроса в отдельную конструкцию, которая называется общее табличное выра- жение (Common Table Expression — CTE) WITH ts AS ( SELECT f.flight_id, f.flight_no, f.scheduled_departure_local, f.departure_city, 143 f.arrival_city, f.aircraft_code, count( tf.ticket_no ) AS fact_passengers, ( SELECT count( s.seat_no ) FROM seats s WHERE s.aircraft_code = f.aircraft_code ) AS total_seats FROM flights_v f JOIN ticket_flights tf ON f.flight_id = tf.flight_id WHERE f.status = 'Arrived' GROUP BY 1, 2, 3, 4, 5, 6 ) SELECT ts.flight_id, ts.flight_no, ts.scheduled_departure_local, ts.departure_city, ts.arrival_city, a.model, ts.fact_passengers, ts.total_seats, round( ts.fact_passengers::numeric / ts.total_seats::numeric, 2 ) AS fraction FROM ts JOIN aircrafts AS a ON ts.aircraft_code = a.aircraft_code ORDER BY ts.scheduled_departure_local; Конструкция WITH ts AS (...) и представляет собой общее табличное выражение (CTE). Такие конструкции удобны тем, что позволяют упростить основной запрос, сделать его менее громоздким. В общем табличном выражении может присутство- вать больше одного подзапроса. Каждый подзапрос формирует временную таблицу с указанным именем. Если имена столбцов этой таблицы не заданы явным образом в виде списка, тогда они определяются на основе списка столбцов в предложении SELECT. В нашем примере это будет именно так. Теперь мы можем в главном запросе обращаться к временной таблице ts так, как если бы она существовала постоянно. Но важно учитывать, что временная таблица, создаваемая в общем табличном выраже- нии, существует только во время выполнения запроса. В этой главе мы уже решали задачу распределения сумм бронирований по диапазо- нам с шагом в сто тысяч рублей. Тогда мы использовали предложение VALUES для формирования виртуальной таблицы. Можно решить эту задачу более рациональ- ным способом с использованием конструкции WITH ... AS (...). Сначала покажем, как можно сформировать диапазоны сумм бронирований с помо- щью рекурсивного общего табличного выражения: WITH RECURSIVE ranges ( min_sum, max_sum ) AS ( VALUES ( 0, 100000 ) UNION ALL SELECT min_sum + 100000, max_sum + 100000 FROM ranges WHERE max_sum < ( SELECT max( total_amount ) FROM bookings ) ) SELECT * FROM ranges; 144 В этом примере мы явно указали имена столбцов для временной таблицы ranges — это min_sum и max_sum. Рекурсивный алгоритм работает следующим образом: – сначала выполняется предложение VALUES (0, 100000) и результат записывается во временную область памяти; – затем к этой временной области памяти применяется запрос SELECT min_sum + 100000, max_sum + 100000 ... в результате его выполнения формируется только одна строка, поскольку в исход- ном предложении VALUES была сформирована только одна строка и только одна строка была помещена во временную область памяти; – вновь сформированная строка вместе с исходной строкой помещаются в другую временную область, в которой происходит накапливание результирующих строк; – к той строке, которая была на предыдущем шаге сформирована с помощью коман- ды SELECT, опять применяется эта же команда и т. д.; – работа завершится, когда перестанет выполняться условие max_sum < ( SELECT max( total_amount ) FROM bookings ) Важную роль в этом процессе играет предложение UNION ALL, благодаря которо- му происходит объединение сформированных строк в единую таблицу. Поскольку в нашем примере в рекурсивном алгоритме участвует только одна строка, то строк- дубликатов не возникает, поэтому мы используем не UNION, а UNION ALL. При ис- пользовании предложения UNION выполняется устранение строк-дубликатов, но в данном случае необходимости в выполнении этой операции нет, следовательно, це- лесообразно использовать именно UNION ALL. Получим такую таблицу: min_sum | max_sum ---------+-------- 0 | 100000 100000 | 200000 200000 | 300000 300000 | 400000 400000 | 500000 500000 | 600000 600000 | 700000 700000 | 800000 800000 | 900000 900000 | 1000000 1000000 | 1100000 1100000 | 1200000 1200000 | 1300000 (13 строк) Здесь в предложении WHERE используется скалярный подзапрос. С результатом его выполнения сравнивается значение столбца max_sum: WHERE max_sum < ( SELECT max( total_amount ) FROM bookings ) 145 Теперь давайте скомбинируем рекурсивное общее табличное выражение с выборкой из таблицы bookings: WITH RECURSIVE ranges ( min_sum, max_sum ) AS ( VALUES( 0, 100000 ) UNION ALL SELECT min_sum + 100000, max_sum + 100000 FROM ranges WHERE max_sum < ( SELECT max( total_amount ) FROM bookings ) ) SELECT r.min_sum, r.max_sum, count( b.* ) FROM bookings b RIGHT OUTER JOIN ranges r ON b.total_amount >= r.min_sum AND b.total_amount < r.max_sum GROUP BY r.min_sum, r.max_sum ORDER BY r.min_sum; min_sum | max_sum | count ---------+---------+-------- 0 | 100000 | 198314 100000 | 200000 | 46943 200000 | 300000 | 11916 300000 | 400000 | 3260 400000 | 500000 | 1357 500000 | 600000 | 681 600000 | 700000 | 222 700000 | 800000 | 55 800000 | 900000 | 24 900000 | 1000000 | 11 1000000 | 1100000 | 4 1100000 | 1200000 | 0 1200000 | 1300000 | 1 (13 строк) Обратите внимание, что для диапазона от 1100 до 1200 тысяч рублей значение числа бронирований равно нулю. Для того чтобы была выведена строка с нулевым значе- нием столбца count, мы использовали внешнее соединение. В заключение рассмотрим команду для создания материализованного представле- ния «Маршруты» (routes), которое было описано в главе 5. Но тогда мы не стали рас- сматривать эту команду, т. к. еще не ознакомились с подзапросами, которые в ней используются. 146 Описание атрибута Имя атрибута Тип PostgreSQL Номер рейса flight_no char(6) Код аэропорта отправления departure_airport char(3) Название аэропорта отправления departure_airport_name text Город отправления departure_city text Код аэропорта прибытия arrival_airport char(3) Название аэропорта прибытия arrival_airport_name text Город прибытия arrival_city text Код самолета, IATA aircraft_code char(3) Продолжительность полета duration interval Дни недели, когда выполняются рейсы days_of_week integer[] Эта команда выглядит так: CREATE MATERIALIZED VIEW routes AS WITH f3 AS ( SELECT f2.flight_no, f2.departure_airport, f2.arrival_airport, f2.aircraft_code, f2.duration, array_agg( f2.days_of_week ) AS days_of_week FROM ( SELECT f1.flight_no, f1.departure_airport, f1.arrival_airport, f1.aircraft_code, f1.duration, f1.days_of_week FROM ( SELECT flights.flight_no, flights.departure_airport, flights.arrival_airport, flights.aircraft_code, ( flights.scheduled_arrival - flights.scheduled_departure ) AS duration, ( to_char( flights.scheduled_departure, 'ID'::text ))::integer AS days_of_week FROM flights ) f1 GROUP BY f1.flight_no, f1.departure_airport, f1.arrival_airport, f1.aircraft_code, f1.duration, f1.days_of_week ORDER BY f1.flight_no, f1.departure_airport, f1.arrival_airport, f1.aircraft_code, f1.duration, f1.days_of_week ) f2 GROUP BY f2.flight_no, f2.departure_airport, f2.arrival_airport, f2.aircraft_code, f2.duration ) SELECT f3.flight_no, f3.departure_airport, dep.airport_name AS departure_airport_name, dep.city AS departure_city, 147 f3.arrival_airport, arr.airport_name AS arrival_airport_name, arr.city AS arrival_city, f3.aircraft_code, f3.duration, f3.days_of_week FROM f3, airports dep, airports arr WHERE f3.departure_airport = dep.airport_code AND f3.arrival_airport = arr.airport_code; Начнем ознакомление с запросом с его верхней части. Здесь мы видим конструкцию WITH f3 AS (...), т. е. общее табличное выражение. В результате его выполнения бу- дет сформирована временная таблица f3. Запрос, который ее формирует, содержит в предложении FROM подзапрос, формирующий временную таблицу f2. А этот подза- прос, в свою очередь, также содержит в предложении FROM подзапрос, формирую- щий временную таблицу f1. Таким образом, в этой команде используется вложенный подзапрос. Во вложенном подзапросе используется функция to_char. Второй ее параметр — «ID» — указывает на то, что из значения даты/времени вылета будет извлечен но- мер дня недели. При этом нумерация дней недели соответствует стандарту ISO 8601: понедельник — 1, воскресенье — 7. Поскольку номер дня недели представлен в виде символьной строки, он преобразуется в тип данных integer. Таким образом, вложенный подзапрос вычисляет плановую длительность полета (столбец duration) и извлекает номер дня недели из даты/времени вылета по расписанию (столбец days_of_week). Подзапрос следующего, более высокого уровня, получив результат вложенного под- запроса, просто группирует строки, готовя столбец days_of_week к объединению от- дельных номеров дней недели в массивы целых чисел. При этом в предложение GROUP BY включен столбец days_of_week, чтобы заменить дубликаты дней недели од- ним значением. Ведь таблица flights содержит расписание рейсов на длительный пе- риод. Поэтому рейс, который отправляется, скажем, по вторникам, появится в этом расписании несколько раз, следовательно, день недели с номером 2 также появится в столбце days_of_week для этого номера рейса несколько раз. В результате, если не при- бегнуть к группировке по этому столбцу, то при формировании массива дней недели в этом массиве будут многократные вхождения каждого дня недели, когда этот рейс летает. В этом подзапросе присутствует и предложение ORDER BY, в которое включен столбец days_of_week. Это необходимо для того, чтобы агрегатная функция array_agg собрала номера дней недели в массив в возрастающем порядке этих номеров. Во внешнем запросе вызывается функция array_agg, которая агрегирует номера дней недели, содержащиеся в сгруппированных строках, в массивы целых чисел. На этом работа конструкции WITH f3 AS (...) завершается. В результате вместо нескольких строк в таблице flights, соответствующих вылетам конкретного рейса в различные дни недели, формируется одна строка в представлении routes, в этой строке все дни недели, в которые выполняется конкретный рейс, собраны в массив целых чисел. И, наконец, главный запрос выполняет соединение временной таблицы f3 с табли- цей «Аэропорты» (airports), причем, дважды. Это нужно потому, что в таблице f3 есть 148 столбец f3.departure_airport (аэропорт отправления) и столбец f3.arrival_airport (аэро- порт прибытия), для каждого из них нужно выбрать наименование аэропорта и на- именование города из таблицы airports. О том, как нужно рассуждать при двукратном использовании одной и той же таблицы в соединении, мы уже говорили ранее в раз- деле 5.4 «Представления». |