Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
ORDER BY range ) AS rank 176 FROM aircrafts_tmp WHERE model '^Airbus' OR model '^Boeing' ) DELETE FROM aircrafts_tmp a USING min_ranges mr WHERE a.aircraft_code = mr.aircraft_code AND mr.rank = 1 RETURNING *; Мы включили в команду DELETE предложение RETURNING *, чтобы показать, как вы- глядят комбинированные строки, сформированные с помощью предложения USING. Конечно, удаляются не комбинированные строки, а только оригинальные строки из таблицы aircrafts_tmp. aircraft_code | model | range | aircraft_code | rank ---------------+-----------------+-------+---------------+------ 321 | Airbus A321-200 | 5600 | 321 | 1 733 | Boeing 737-300 | 4200 | 733 | 1 (2 строки) В заключение этого раздела упомянем еще команду TRUNCATE, которая позволяет быстро удалить все строки из таблицы. Следующие две команды позволяют удалить все строки из таблицы aircrafts_tmp: DELETE FROM aircrafts_tmp; TRUNCATE aircrafts_tmp; Однако команда TRUNCATE работает быстрее. Контрольные вопросы и задания 1. Добавьте в определение таблицы aircrafts_log значение по умолчанию CURRENT_TIMESTAMP и соответствующим образом измените команды INSERT, приведенные в тексте главы. 2. В предложении RETURNING можно указывать не только символ «*», означаю- щий выбор всех столбцов таблицы, но также и более сложные выражения, сфор- мированные на основе этих строк. В тексте главы мы копировали содержимое таблицы «Самолеты» (aircrafts) в таблицу aircrafts_tmp. В том запросе в пред- ложении RETURNING мы использовали именно «*». Однако возможен и другой вариант запроса: WITH add_row AS ( INSERT INTO aircrafts_tmp SELECT * FROM aircrafts RETURNING aircraft_code, model, range, CURRENT_TIMESTAMP, 'INSERT' ) INSERT INTO aircrafts_log SELECT ? FROM add_row; Что нужно написать в этом запросе вместо вопросительного знака? 177 3. Если бы мы для копирования данных из таблицы «Самолеты» (aircrafts) в таб- лицу aircrafts_tmp использовали команду INSERT без общего табличного выра- жения INSERT INTO aircrafts_tmp SELECT * FROM aircrafts; то в качестве выходного результата мы увидели бы сообщение INSERT 0 9 Как вы думаете, что будет выведено, если дополнить команду предложением RETURNING * ? INSERT INTO aircrafts_tmp SELECT * FROM aircrafts RETURNING *; Проверьте ваши предположения на практике. Подумайте, каким образом мож- но использовать выведенный результат? 4. В тексте главы в предложениях ON CONFLICT команды INSERT мы использовали только выражения, состоящие из имени одного столбца. Однако в таблице «Ме- ста» (seats) первичный ключ является составным и включает два столбца. Напи- шите команду INSERT для вставки новой строки в эту таблицу и предусмотрите возможный конфликт добавляемой строки со строкой, уже имеющейся в таб- лице. Сделайте два варианта предложения ON CONFLICT: первый — с исполь- зованием перечисления имен столбцов для проверки наличия дублирования, второй — с использованием предложения ON CONSTRAINT. Для того чтобы не изменить содержимое таблицы «Места», создайте ее копию и выполняйте все эти эксперименты с таблицей-копией. 5. В предложении DO UPDATE команды INSERT может использоваться условие WHERE. Самостоятельно ознакомьтесь с этой возможностью с помощью доку- ментации и напишите такую команду INSERT. 6. Команда COPY по умолчанию ожидает получения вводимых данных в формате text, когда значения данных разделяются символами табуляции. Однако мож- но представлять входные данные в формате CSV (Comma Separated Values), т. е. использовать в качестве разделителя запятую. COPY aircrafts_tmp FROM STDIN WITH ( FORMAT csv ); Вводите данные для копирования, разделяя строки переводом строки. Закончите ввод строкой '\.'. >> IL9, Ilyushin IL96, 9800 >> I93, Ilyushin IL96-300, 9800 >> \. SELECT * FROM aircrafts_tmp; aircraft_code | model | range ---------------+---------------------+------- CN1 | Cessna 208 Caravan | 1200 CR2 | Bombardier CRJ-200 | 2700 IL9 | Ilyushin IL96 | 9800 I93 | Ilyushin IL96-300 | 9800 (11 строк) 178 Как вы думаете, почему при выводе данных из таблицы вновь введенные зна- чения в столбце model оказались смещены вправо? 7. Команда COPY позволяет получить входные данные из файла и поместить их в таблицу. Этот файл должен быть доступен тому пользователю операцион- ной системы, от имени которого запущен серверный процесс, как правило, это пользователь postgres. Подготовьте файл, например, /home/postgres/aircrafts_tmp.csv, имеющий такую структуру: – каждая строка файла соответствует одной строке таблицы aircrafts_tmp; – значения данных в строке файла разделяются запятыми. Например: 773,Boeing 777-300,11100 763,Boeing 767-300,7900 SU9,Sukhoi SuperJet-100,3000 Введите в этот файл данные о нескольких самолетах, причем часть из них уже должна быть представлена в таблице, а часть — нет. Поскольку при выполнении команды COPY выполняются проверки всех огра- ничений целостности, наложенных на таблицу, то дублирующие строки добав- лены, конечно же, не будут. А как вы думаете, строки, содержащиеся в этом же файле, но отсутствующие в таблице, будут добавлены или нет? Проверьте свою гипотезу, выполнив команду для вставки строк в таблицу из этого файла: COPY aircrafts_tmp FROM '/home/postgres/aircrafts_tmp.csv' WITH ( FORMAT csv ); 8.* В тексте главы был приведен запрос, предназначенный для учета числа биле- тов, проданных по всем направлениям на текущую дату. Однако тот запрос был рассчитан на одновременное добавление только одной записи в таблицу «Пе- релеты» (ticket_flights_tmp). Ниже мы предложим более универсальный запрос, который предусматривает возможность единовременного ввода нескольких за- писей о перелетах, выполняемых на различных рейсах. Для проверки работоспособности предлагаемого запроса выберем несколько рейсов по маршрутам: Красноярск — Москва, Москва — Сочи, Сочи — Москва, Сочи — Красноярск. Для определения идентификаторов рейсов сформируем вспомогательный запрос, в котором даты начала и конца рассматриваемого пе- риода времени зададим с помощью функции bookings.now. Использование этой функции необходимо, поскольку в будущих версиях базы данных могут быть представлены другие диапазоны дат. SELECT flight_no, flight_id, departure_city, arrival_city, scheduled_departure FROM flights_v WHERE scheduled_departure BETWEEN bookings.now() AND bookings.now() + INTERVAL '15 days' AND ( departure_city, arrival_city ) IN ( ( 'Красноярск', 'Москва' ), ( 'Москва', 'Сочи'), 179 ( 'Сочи', 'Москва' ), ( 'Сочи', 'Красноярск' ) ) ORDER BY departure_city, arrival_city, scheduled_departure; Обратите внимание на предикат IN: в нем используются не индивидуальные значения, а пары значений. Предположим, что в течение указанного интервала времени пассажир плани- рует совершить перелеты по маршруту: Красноярск — Москва, Москва — Сочи, Сочи — Москва, Москва — Сочи, Сочи — Красноярск. Выполнив вспомогатель- ный запрос, выберем следующие идентификаторы рейсов (в этом же порядке): 13829, 4728, 30523, 7757, 30829. WITH sell_tickets AS ( INSERT INTO ticket_flights_tmp ( ticket_no, flight_id, fare_conditions, amount ) VALUES ( '1234567890123', 13829, 'Economy', 10500 ), ( '1234567890123', 4728, 'Economy', 3400 ), ( '1234567890123', 30523, 'Economy', 3400 ), ( '1234567890123', 7757, 'Economy', 3400 ), ( '1234567890123', 30829, 'Economy', 12800 ) RETURNING * ) UPDATE tickets_directions td SET last_ticket_time = CURRENT_TIMESTAMP, tickets_num = tickets_num + ( SELECT count( * ) FROM sell_tickets st, flights_v f WHERE st.flight_id = f.flight_id AND f.departure_city = td.departure_city AND f.arrival_city = td.arrival_city ) WHERE ( td.departure_city, td.arrival_city ) IN ( SELECT departure_city, arrival_city FROM flights_v WHERE flight_id IN ( SELECT flight_id FROM sell_tickets ) ); UPDATE 4 В этой версии запроса предусмотрен единовременный ввод нескольких строк в таблицу ticket_flights_tmp, причем, перелеты могут выполняться на различных рейсах. Поэтому необходимо каким-то образом преобразовать список иден- тификаторов этих рейсов в множество пар «город отправления — город при- бытия», поскольку именно для таких пар и ведется подсчет числа заброниро- ванных перелетов. Эта задача решается в предложении WHERE. Здесь вложен- ный подзапрос формирует список идентификаторов рейсов, а внешний под- запрос преобразует этот список в множество пар «город отправления — город прибытия». Затем с помощью предиката IN производится отбор строк таблицы tickets_directions для обновления. 180 Теперь обратимся к предложению SET. Подзапрос с функцией count вычисляет количество перелетов по каждому направлению. Это коррелированный подза- прос: он выполняется для каждой строки, отобранной в предложении WHERE. В нем используется соединение временной таблицы sell_tickets с представлени- ем flights_v. Это нужно для того, чтобы подсчитать все перелеты, соответству- ющие паре атрибутов «город отправления — город прибытия», взятых из теку- щей обновляемой строки таблицы tickets_directions. Этот подзапрос позволя- ет учесть такой факт: рейсы могут иметь различные идентификаторы flight_id, но при этом соответствовать одному и тому же направлению, а в таблице tickets_directions учитываются именно направления. В случае попытки повторного бронирования одного и того же перелета для дан- ного пассажира, т. е. ввода строки с дубликатом первичного ключа, такая строка будет отвергнута, и будет сгенерировано сообщение об ошибке. В таком случае и таблица tickets_directions не будет обновлена. Давайте посмотрим, что изменилось в таблице tickets_directions. SELECT departure_city AS dep_city, arrival_city AS arr_city, last_ticket_time, tickets_num AS num FROM tickets_directions WHERE tickets_num > 0 ORDER BY departure_city, arrival_city; По маршруту Москва — Сочи наш пассажир приобретал два билета, что и отра- жено в выборке. dep_city | arr_city | last_ticket_time | num ------------+------------+----------------------------+----- Красноярск | Москва | 2017-02-04 14:02:23.769443 | 1 Москва | Сочи | 2017-02-04 14:02:23.769443 | 2 Сочи | Красноярск | 2017-02-04 14:02:23.769443 | 1 Сочи | Москва | 2017-02-04 14:02:23.769443 | 1 (4 строки) А это информация о каждом перелете, забронированном нашим пассажиром: SELECT * FROM ticket_flights_tmp; ticket_no | flight_id | fare_conditions | amount ---------------+-----------+-----------------+---------- 1234567890123 | 13829 | Economy | 10500.00 1234567890123 | 4728 | Economy | 3400.00 1234567890123 | 30523 | Economy | 3400.00 1234567890123 | 7757 | Economy | 3400.00 1234567890123 | 30829 | Economy | 12800.00 (5 строк) Задание: модифицируйте запрос и таблицу tickets_directions так, чтобы учет числа забронированных перелетов по различным маршрутам выполнялся для каждого класса обслуживания: Economy, Business и Comfort. 181 9.* Предположим, что руководство нашей авиакомпании решило отказаться от ис- пользования самолетов компаний Boeing и Airbus, имеющих наименьшее ко- личество пассажирских мест в салонах. Мы должны соответствующим образом откорректировать таблицу «Самолеты» (aircrafts_tmp). Мы предлагаем такой алгоритм. Шаг 1. Для каждой модели вычислить общее число мест в салоне. Шаг 2. Используя оконную функцию rank, присвоить моделям ранги на основе числа мест (упорядочив их по возрастанию числа мест). Ранжирование выпол- няется в пределах каждой компании-производителя, т. е. для Boeing и для Airbus — отдельно. Ранг, равный 1, соответствует наименьшему числу мест. Шаг 3. Выполнить удаление тех строк из таблицы aircrafts_tmp, которые удовле- творяют следующим требованиям: модель — Boeing или Airbus, а число мест в салоне — минимальное из всех моделей данной компании-производителя, т. е. модель имеет ранг, равный 1. WITH aicrafts_seats AS ( SELECT aircraft_code, model, seats_num, rank() OVER ( PARTITION BY left( model, strpos( model, ' ' ) - 1 ) ORDER BY seats_num ) FROM ( SELECT a.aircraft_code, a.model, count( * ) AS seats_num FROM aircrafts_tmp a, seats s WHERE a.aircraft_code = s.aircraft_code GROUP BY 1, 2 ) AS seats_numbers ) DELETE FROM aircrafts_tmp a USING aicrafts_seats a_s WHERE a.aircraft_code = a_s.aircraft_code AND left( a.model, strpos( a.model, ' ' ) - 1 ) IN ( 'Boeing', 'Airbus' ) AND a_s.rank = 1 RETURNING *; Шаг 1 выполняется в подзапросе в предложении WITH. Шаг 2 выполняется в главном запросе в предложении WITH. Шаг 3 реализуется командой DELETE. Обратите внимание, что название компании-производителя мы определяем путем взятия подстроки от значения атрибута model: от начала строки до про- бельного символа (используем функции left и strpos). Мы включили предложение RETURNING * для того, чтобы увидеть, какие имен- но модели были удалены. Предложение WITH выдает такой результат: aircraft_code | model | seats_num | rank ---------------+---------------------+-----------+------ 319 | Airbus A319-100 | 116 | 1 320 | Airbus A320-200 | 140 | 2 182 321 | Airbus A321-200 | 170 | 3 733 | Boeing 737-300 | 130 | 1 763 | Boeing 767-300 | 222 | 2 773 | Boeing 777-300 | 402 | 3 CR2 | Bombardier CRJ-200 | 50 | 1 CN1 | Cessna 208 Caravan | 12 | 1 SU9 | Sukhoi SuperJet-100 | 97 | 1 (9 строк) Очевидно, что должны быть удалены модели с кодами 319 и 733. После выполнения запроса получим такое сообщение (это работает предложе- ние RETURNING *): --[ RECORD 1 ]-+---------------- aircraft_code | 319 model | Airbus A319-100 range | 6700 aircraft_code | 319 model | Airbus A319-100 seats_num | 116 rank | 1 --[ RECORD 2 ]-+---------------- aircraft_code | 733 model | Boeing 737-300 range | 4200 aircraft_code | 733 model | Boeing 737-300 seats_num | 130 rank | 1 DELETE 2 Обратите внимание, что были выведены комбинированные строки, получен- ные при соединении таблицы aircrafts_tmp с временной таблицей aicrafts_seats, указанной в предложении USING. Но удалены были, конечно, строки из таблицы aircrafts_tmp. Задание: предложите другой вариант решения этой задачи. Например, можно поступить так: оставить предложение WITH без изменений, из команды DELETE убрать предложение USING, а в предложении WHERE вместо соединения таблиц использовать подзапрос с предикатом IN для получения списка кодов удаляе- мых моделей самолетов. Еще один вариант решения задачи связан с использованием представлений, ко- торые мы рассматривали в главе 5. В данном случае можно создать представле- ние на основе таблиц «Самолеты» (aircrafts) и «Места» (seats) и перенести кон- струкцию с функциями left и strpos в представление. В нем будут вычисляемые столбцы: company — «Компания — производитель самолетов» и seats_num — «Число мест». CREATE VIEW aircrafts_seats AS ( SELECT a.aircraft_code, a.model, left( a.model, strpos( a.model, ' ' ) - 1 ) AS company, count( * ) AS seats_num 183 FROM aircrafts a, seats s WHERE a.aircraft_code = s.aircraft_code GROUP BY 1, 2, 3 ); Имея это представление, можно использовать его в конструкции WITH. При этом вызов функции rank может упроститься: rank() OVER ( PARTITION BY company ORDER BY seats_num ) Для выбора удаляемых строк в команде DELETE можно использовать, например, подзапрос в предикате IN. При этом не забывайте, что значение столбца rank для них будет равно 1. Еще одна идея: для выбора минимальных значений числа мест в самолетах можно попытаться в качестве замены оконной функции rank использовать предложения LIMIT 1 и ORDER BY. В таком случае не потребуется также и функ- ция min. 10.* В реальной работе иногда возникают ситуации, когда требуется быстро запол- нить таблицу тестовыми данными. В таком случае можно воспользоваться ко- мандой INSERT с подзапросом. Конечно, число атрибутов и их типы данных в подзапросе SELECT должны быть такими, какие ожидает получить команда INSERT. Продемонстрируем такой прием на примере таблицы «Места» (seats). Для того чтобы выполнить команду, приведенную в этом упражнении, нужно либо сна- чала удалить все строки из таблицы seats, чтобы можно было добавлять строки в эту таблицу DELETE FROM seats; либо создать копию этой таблицы CREATE TABLE seats_tmp AS SELECT * FROM seats; чтобы работать с копией. Итак, как сформировать тестовые данные автоматическим способом? Для этого сначала нужно подготовить исходные данные, на основе которых и будут фор- мироваться результирующие значения для вставки в таблицу «Места». В рам- ках реляционной модели наиболее естественным будет представление исход- ных данных в виде таблиц. Для формирования каждой строки таблицы «Места» нужно задать код модели самолета, класс обслуживания и номер места, кото- рый состоит из двух компонентов: номера ряда и буквенного идентификатора позиции в ряду. Поскольку размеры и компоновки салонов различаются, необ- ходимо для каждой модели указать предельное число рядов кресел в салонах бизнес-класса и экономического класса, а также число кресел в каждом ряду. Это число можно задать с помощью указания буквенного идентификатора для самого последнего кресла в ряду. Например, если в ряду всего шесть кресел, то- гда их буквенные обозначения будут такими: A, B, C, D, E, F. Таким образом, последней будет буква F. В салоне бизнес-класса число мест в ряду меньше, чем в салоне экономического класса, но для упрощения задачи примем эти числа одинаковыми. В результате получим первую исходную таблицу с атрибутами: 184 – код модели самолета; – номер последнего ряда кресел в салоне бизнес-класса; – номер последнего ряда кресел в салоне экономического класса; – буква, обозначающая позицию последнего кресла в ряду. Классы обслуживания также поместим в отдельную таблицу. В ней будет всего один атрибут — класс обслуживания. Список номеров рядов также поместим в отдельную таблицу. В ней будет также всего один атрибут — номер ряда. Также поступим и с буквенными обозначениями кресел в ряду. В этой таблице будет один атрибут — латинская буква, обозначающая позицию кресла. В принципе можно было бы создать все четыре таблицы с помощью команды CREATE TABLE и ввести в них исходные данные, а затем использовать эти табли- цы в команде SELECT. Но команда SELECT позволяет использовать в предложе- нии FROM виртуальные таблицы, которые можно создавать с помощью предло- жения VALUES. Для этого непосредственно в текст команды записываются груп- пы значений, представляющие собой строки такой виртуальной таблицы. Каж- дая такая строка заключается в круглые скобки. Вся эта конструкция получает имя таблицы и к ней прилагается список атрибутов. Это выглядит, например, так: |