Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
Контрольные вопросы и задания 1. В документации сказано, что служебный символ «%» в шаблоне оператора LIKE соответствует любой последовательности символов, в том числе и пустой после- довательности, однако ничего не сказано насчет правил обработки пробелов. В таблице «Билеты» (tickets) столбец passenger_name содержит имя и фамилию пассажира, записанные заглавными латинскими буквами и разделенные одним пробелом. Выясните правила обработки пробелов самостоятельно, выполнив следующие команды и сравнив полученные результаты: SELECT count( * ) FROM tickets; SELECT count( * ) FROM tickets WHERE passenger_name LIKE '% %'; SELECT count( * ) FROM tickets WHERE passenger_name LIKE '% % %'; SELECT count( * ) FROM tickets WHERE passenger_name LIKE '% %%'; 2. Этот запрос выбирает из таблицы «Билеты» (tickets) всех пассажиров с именами, состоящими из трех букв (в шаблоне присутствует три символа «_»): SELECT passenger_name FROM tickets WHERE passenger_name LIKE '___ %'; Предложите шаблон поиска в операторе LIKE для выбора из этой таблицы всех пассажиров с фамилиями, состоящими из пяти букв. 3. В разделе 9.7.2 «Регулярные выражения SIMILAR TO» рассматривается оператор SIMILAR TO. Он работает аналогично оператору LIKE, но использует шаблоны, соответствующие определению регулярных выражений, приведенному в стан- дарте SQL. Регулярные выражения SQL представляют собой комбинацию син- таксиса LIKE с синтаксисом обычных регулярных выражений. Самостоятельно ознакомьтесь с оператором SIMILAR TO. 4. В разделе 9.2 «Функция и операторы сравнения» представлены различные пре- дикаты сравнения, кроме предиката BETWEEN, рассмотренного в этой главе. Самостоятельно ознакомьтесь с ними. 5. В разделе 9.17 «Условные выражения» представлены условные выражения, ко- торые поддерживаются в PostgreSQL. В тексте главы была рассмотрена кон- струкция CASE. Самостоятельно ознакомьтесь с функциями COALESCE, NULLIF, GREATEST и LEAST. 149 6. Выясните, на каких маршрутах используются самолеты компании Boeing? В выборке вместо кода модели должно выводиться ее наименование, например, вместо кода 733 должно быть Boeing 737-300. Указание: можно воспользоваться соединением представления «Маршруты» (routes) и таблицы «Самолеты» (aircrafts). 7. Самые крупные самолеты в нашей авиакомпании — это Boeing 777-300. Выяс- нить, между какими парами городов они летают, поможет запрос: SELECT DISTINCT departure_city, arrival_city FROM routes r JOIN aircrafts a ON r.aircraft_code = a.aircraft_code WHERE a.model = 'Boeing 777-300' ORDER BY 1; departure_city | arrival_city ----------------+-------------- Екатеринбург | Москва Москва | Екатеринбург Москва | Новосибирск Москва | Пермь Москва | Сочи Новосибирск | Москва Пермь | Москва Сочи | Москва (8 строк) К сожалению, в этой выборке информация дублируется. Пары городов приведе- ны по два раза: для рейса «туда» и для рейса «обратно». Модифицируйте запрос таким образом, чтобы каждая пара городов была выведена только один раз: departure_city | arrival_city ----------------+-------------- Москва | Екатеринбург Новосибирск | Москва Пермь | Москва Сочи | Москва (4 строки) 8. В тексте главы мы рассматривали различные примеры использования левого и правого внешних соединений: LEFT OUTER JOIN и RIGHT OUTER JOIN. Напи- шите запрос, в котором использовалось бы полное внешнее соединение — FULL OUTER JOIN. 9. Для получения ответа на вопрос, сколько рейсов выполняется из Москвы в Санкт-Петербург, можно написать совсем простой запрос: SELECT count( * ) FROM routes WHERE departure_city = 'Москва' AND arrival_city = 'Санкт-Петербург'; 150 count ------- 12 (1 строка) А с помощью какого запроса можно получить результат в таком виде? departure_city | arrival_city | count ----------------+-----------------+------- Москва | Санкт-Петербург | 12 (1 строка) 10. Выяснить, сколько различных рейсов выполняется из каждого города, без уче- та частоты рейсов в неделю, можно с помощью обращения к представлению «Маршруты» (routes): SELECT departure_city, count( * ) FROM routes GROUP BY departure_city ORDER BY count DESC; departure_city | count --------------------------+------- Москва | 154 Санкт-Петербург | 35 Новосибирск | 19 Екатеринбург | 15 Ростов-на-Дону | 14 Сочи | 14 Красноярск | 13 Ульяновск | 11 Благовещенск | 1 Братск | 1 (101 строка) Модифицируйте этот запрос так, чтобы он выводил число направлений, по ко- торым летают самолеты из каждого города. Например, из Москвы в Санкт- Петербург летает несколько различных рейсов, но все эти рейсы относятся к одному направлению. Указание: нужно передать параметр в функцию count. 11. В материализованном представлении «Маршруты» (routes) есть столбец days_of_week, который содержит списки (массивы) номеров дней недели, когда выполняется каждый рейс. Для оптимизации расписания вылетов из Москвы нужно выявить пять горо- дов, в которые из столицы отправляется наибольшее число ежедневных рей- сов (маршрутов). Строки в выборке следует расположить в убывающем порядке числа выполняемых рейсов. Указание. Воспользуйтесь функцией array_length. 151 12.* Предположим, что служба материального снабжения нашей авиакомпании за- просила информацию о числе рейсов, выполняющихся из Москвы в каждый день недели. Результат можно получить путем выполнения семи аналогичных запросов: по одному для каждого дня недели. Начнем с понедельника: SELECT 'Понедельник' AS day_of_week, count( * ) AS num_flights FROM routes WHERE departure_city = 'Москва' AND days_of_week @> '{ 1 }'::integer[]; В этом запросе используется оператор «@>», который проверяет, содержатся ли все элементы массива, стоящего справа от него, в том массиве, который нахо- дится слева. В правом массиве всего один элемент — номер интересующего нас дня недели. day_of_week | num_flights -------------+------------- Понедельник | 131 (1 строка) Запрос для вторника отличается лишь названием дня недели и его номером в массиве. SELECT 'Вторник' AS day_of_week, count( * ) AS num_flights FROM routes WHERE departure_city = 'Москва' AND days_of_week @> '{ 2 }'::integer[]; day_of_week | num_flights -------------+------------- Вторник | 134 (1 строка) Нужно выполнить еще пять аналогичных команд, чтобы получить результаты для всех дней недели. Очевидно, что это нерациональный способ. Получить требуемый результат можно с помощью одного запроса: SELECT unnest( days_of_week ) AS day_of_week, count( * ) AS num_flights FROM routes WHERE departure_city = 'Москва' GROUP BY day_of_week ORDER BY day_of_week; day_of_week | num_flights -------------+------------- 1 | 131 2 | 134 3 | 126 4 | 136 5 | 124 6 | 133 7 | 124 (7 строк) 152 Задание 1. Самостоятельно разберитесь, как работает приведенный запрос. Выясните, что делает функция unnest. Для того чтобы найти ее описание, можно воспользоваться теми разделами документации, которые были указаны в тек- сте главы 4. Однако можно воспользоваться и предметным указателем (Index), ссылка на который находится в самом низу оглавления документации. В качестве вспомогательного запроса, проясняющего работу функции unnest, можно выполнить следующий: SELECT flight_no, unnest( days_of_week ) AS day_of_week FROM routes WHERE departure_city = 'Москва' ORDER BY flight_no; Задание 2. Использование номеров дней недели в предыдущей выборке не должно вызывать затруднений. Но все-таки предположим, что нас попросили модифицировать запрос, чтобы результат выводился в таком виде: name_of_day | num_flights -------------+------------- Пн. | 131 Вт. | 134 Ср. | 126 Чт. | 136 Пт. | 124 Сб. | 133 Вс. | 124 (7 строк) Покажем одно из возможных решений задачи. Оно основано на использова- нии специальной табличной функции unnest в предложении FROM. Подробно об этом написано в документации в разделе 7.2.1.4 «Табличные функции». Функ- ция unnest может принимать любое число параметров-массивов, а возвращает набор строк, которые могут использоваться в запросах, как обычные таблицы. В этих наборах строк столбцы формируются из значений, содержащихся в мас- сивах. SELECT dw.name_of_day, count( * ) AS num_flights FROM ( SELECT unnest( days_of_week ) AS num_of_day FROM routes WHERE departure_city = 'Москва' ) AS r, unnest( '{ 1, 2, 3, 4, 5, 6, 7 }'::integer[], '{ "Пн.", "Вт.", "Ср.", "Чт.", "Пт.", "Сб.", "Вс."}'::text[] ) AS dw( num_of_day, name_of_day ) WHERE r.num_of_day = dw.num_of_day GROUP BY r.num_of_day, dw.name_of_day ORDER BY r.num_of_day; Этот запрос можно упростить, воспользовавшись предложением WITH ORDINALITY. Оно позволяет в нашем примере избавиться от массива це- лых чисел, обозначающих дни недели, поскольку автоматически формируется 153 столбец целых чисел, нумерующих строки результирующего набора. По умол- чанию этот столбец называется ordinality. Это имя можно использовать в запросе. Самостоятельно модифицируйте запрос с применением предложения WITH ORDINALITY. 13. Ответить на вопрос о том, каковы максимальные и минимальные цены билетов на все направления, может такой запрос: SELECT f.departure_city, f.arrival_city, max( tf.amount ), min( tf.amount ) FROM flights_v f JOIN ticket_flights tf ON f.flight_id = tf.flight_id GROUP BY 1, 2 ORDER BY 1, 2; departure_city | arrival_city | max | min ---------------------+---------------------+-----------+---------- Абакан | Москва | 101000.00 | 33700.00 Абакан | Новосибирск | 5800.00 | 5800.00 Абакан | Томск | 4900.00 | 4900.00 Анадырь | Москва | 185300.00 | 61800.00 Анадырь | Хабаровск | 92200.00 | 30700.00 Якутск | Мирный | 8900.00 | 8100.00 Якутск | Санкт-Петербург | 145300.00 | 48400.00 (367 строк) А как выявить те направления, на которые не было продано ни одного билета? Один из вариантов решения такой: если на рейсы, отправляющиеся по какому- то направлению, не было продано ни одного билета, то максимальная и мини- мальная цены будут равны NULL. Нужно получить выборку в таком виде: departure_city | arrival_city | max | min ---------------------+---------------------+-----------+---------- Абакан | Архангельск | | Абакан | Грозный | | Абакан | Кызыл | | Абакан | Москва | 101000.00 | 33700.00 Абакан | Новосибирск | 5800.00 | 5800.00 Модифицируйте запрос, приведенный выше. 14. Предположим, что маркетологи нашей авиакомпании хотят знать, как часто встречаются различные имена среди пассажиров? Получить распределение ча- стот имен пассажиров в таблице «Билеты» (tickets) поможет такой запрос: SELECT left( passenger_name, strpos( passenger_name, ' ' ) - 1 ) AS firstname, count( * ) FROM tickets GROUP BY 1 ORDER BY 2 DESC; 154 firstname | count ------------+------- ALEKSANDR | 20328 SERGEY | 15133 VLADIMIR | 12806 TATYANA | 12058 ELENA | 11291 OLGA | 9998 MAGOMED | 14 ASKAR | 13 RASUL | 11 (363 строки) Напишите запрос для ответа на аналогичный вопрос насчет распределения ча- стот фамилий пассажиров. Подробные сведения о других функциях для работы со строковыми данными приведены в документации в разделе 9.4 «Строковые функции и операторы». 15.* В тексте главы были кратко рассмотрены оконные функции. Самостоятельно ознакомьтесь с разделами документации, которые рекомендуется изучить для более детального ознакомления с этим классом функций. Подумайте, в какой ситуации, связанной с базой данных «Авиаперевозки», было бы полезно применить оконные функции, и напишите запрос. 16.* Вместе с агрегатными функциями может использоваться предложение FILTER. Самостоятельно ознакомьтесь с этой темой, обратившись к разделу документа- ции 4.2.7 «Агрегатные выражения». Напишите запрос с использованием пред- ложения FILTER с агрегатной функцией. 17. В тексте главы в разделе 6.4 мы рассмотрели два способа получения ответа на вопрос: как распределяются места с разными классами обслуживания в самоле- тах всех типов? А с помощью какого запроса можно получить результат в таком виде: aircraft_code | model | fare_conditions | count ---------------+---------------------+-----------------+------- 319 | Airbus A319-100 | Business | 20 319 | Airbus A319-100 | Economy | 96 CR2 | Bombardier CRJ-200 | Economy | 50 SU9 | Sukhoi SuperJet-100 | Business | 12 SU9 | Sukhoi SuperJet-100 | Economy | 85 (17 строк) 18. В разделе 6.2 «Соединения» мы находили ответ на вопрос: сколько маршрутов обслуживают самолеты каждого типа? Но для повышения наглядности получа- емых результатов необходимо еще рассчитывать относительные величины, т. е. доли от общего числа маршрутов. Вот что требуется получить: 155 a_code | model | r_code | num_routes | fraction --------+---------------------+--------+------------+---------- CR2 | Bombardier CRJ-200 | CR2 | 232 | 0.327 CN1 | Cessna 208 Caravan | CN1 | 170 | 0.239 773 | Boeing 777-300 | 773 | 10 | 0.014 320 | Airbus A320-200 | | 0 | 0.000 (9 строк) 19.* В разделе 6.4 «Подзапросы» мы использовали рекурсивный алгоритм в общем табличном выражении. Изучите этот пример, чтобы лучше понять работу ре- курсивного алгоритма: WITH RECURSIVE ranges ( min_sum, max_sum ) AS ( VALUES( 0, 100000 ), ( 100000, 200000 ), ( 200000, 300000 ) UNION ALL SELECT min_sum + 100000, max_sum + 100000 FROM ranges WHERE max_sum < ( SELECT max( total_amount ) FROM bookings ) ) SELECT * FROM ranges; min_sum | max_sum ---------+--------- 0 | 100000 исходные строки 100000 | 200000 200000 | 300000 100000 | 200000 результат первой итерации 200000 | 300000 300000 | 400000 200000 | 300000 результат второй итерации 300000 | 400000 400000 | 500000 300000 | 400000 400000 | 500000 500000 | 600000 1000000 | 1100000 результат (n-3)-й итерации 1100000 | 1200000 1200000 | 1300000 1100000 | 1200000 результат (n-2)-й итерации 1200000 | 1300000 1200000 | 1300000 результат (n-1)-й итерации (предпоследней) (36 строк) Здесь мы с помощью предложения VALUES специально создали виртуальную таблицу из трех строк, хотя для получения требуемого результата достаточно 156 только одной строки (0, 100000). Еще важно то, что предложение UNION ALL не удаляет строки-дубликаты, поэтому мы можем видеть весь рекурсивный про- цесс порождения новых строк. При рекурсивном выполнении запроса SELECT min_sum + 100000, max_sum + 100000 ... каждый раз выполняется проверка в условии WHERE. И на (n−2)-й итерации это условие отсеивает одну строку, т. к. после (n − 3)-й итерации значение атрибута max_sum в третьей строке было равно 1 300 000. Ведь запрос SELECT max( total_amount ) FROM bookings; выдаст значение max ------------ 1204500.00 (1 строка) Таким образом, после (n − 2)-й итерации во временной области остается всего две строки, после (n − 1)-й итерации во временной области остается только од- на строка. Заключительная итерация уже не добавляет строк в результирующую таблицу, поскольку единственная строка, поданная на вход команде SELECT, бу- дет отклонена условием WHERE. Работа алгоритма завершается. Задание 1. Модифицируйте запрос, добавив в него столбец level (можно назвать его и iteration). Этот столбец должен содержать номер текущей итерации, поэто- му нужно увеличивать его значение на единицу на каждом шаге. Не забудьте задать начальное значение для добавленного столбца в предложении VALUES. Задание 2. Для завершения экспериментов замените UNION ALL на UNION и выполните запрос. Сравните этот результат с предыдущим, когда мы использо- вали UNION ALL. 20.* В тексте главы есть такой запрос, вычисляющий распределение сумм брониро- ваний по диапазонам в сто тысяч рублей: 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; 157 Как вы думаете, почему функция count получает в качестве параметра выраже- ние «b.*», а не просто «*»? Что изменится, если оставить только «*», и почему? 21. В тексте главы был приведен запрос, выводящий список городов, в которые нет рейсов из Москвы. 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; Можно предложить другой вариант, в котором используется одна из операций над множествами строк: объединение, пересечение или разность. Вместо зна- ка «?» поставьте в запросе нужное ключевое слово — UNION, INTERSECT или EXCEPT — и обоснуйте ваше решение. |