J.5. Использование
Пред. НаверхПриложение J. Демонстрационная база данных «Авиаперевозки»Начало След.

J.5. Использование

J.5.1. Схема bookings
J.5.2. Примеры запросов
J.5.3. Бронирования
J.5.4. Новое бронирование

J.5.1. Схема bookings

Все объекты демонстрационной базы данных находятся в схеме bookings. Это означает, что при обращении к объектам вам необходимо либо явно указывать имя схемы (например: bookings.flights), либо предварительно изменить конфигурационный параметр search_path (например: SET search_path = bookings, public;).

Однако для функции bookings.now в любом случае необходимо явно указывать схему, чтобы отличать её от стандартной функции now.

J.5.2. Примеры запросов

Чтобы лучше познакомиться с содержимым демонстрационной базы данных, посмотрим на результаты нескольких простых запросов.

Результаты, представленные ниже, были получены для версии с небольшой базой данных (demo_small) от 13 октября 2016. Если в вашей системе запросы выдают другие данные, проверьте версию демонстрационной базы (функция bookings.now). Незначительные отклонения могут быть связаны с местным временем, отличным от московского, и настройками локализации.

Все рейсы выполняются несколькими типами самолетов: 

SELECT * FROM aircrafts;
    

 aircraft_code |        model        | range
---------------+---------------------+-------
 773           | Boeing 777-300      | 11100
 763           | Boeing 767-300      |  7900
 SU9           | Sukhoi SuperJet-100 |  3000
 320           | Airbus A320-200     |  5700
 321           | Airbus A321-200     |  5600
 319           | Airbus A319-100     |  6700
 733           | Boeing 737-300      |  4200
 CN1           | Cessna 208 Caravan  |  1200
 CR2           | Bombardier CRJ-200  |  2700
(9 строк)

Для каждого типа самолета поддерживается список мест в салоне. Например, вот где можно разместиться в небольшом самолете Cessna 208 Caravan: 

SELECT   a.aircraft_code,
         a.model,
         s.seat_no,
         s.fare_conditions
FROM     aircrafts a
         JOIN seats s ON a.aircraft_code = s.aircraft_code
WHERE    a.model = 'Cessna 208 Caravan'
ORDER BY s.seat_no;
    

 aircraft_code |       model        | seat_no | fare_conditions
---------------+--------------------+---------+-----------------
 CN1           | Cessna 208 Caravan | 1A      | Economy
 CN1           | Cessna 208 Caravan | 1B      | Economy
 CN1           | Cessna 208 Caravan | 2A      | Economy
 CN1           | Cessna 208 Caravan | 2B      | Economy
 CN1           | Cessna 208 Caravan | 3A      | Economy
 CN1           | Cessna 208 Caravan | 3B      | Economy
 CN1           | Cessna 208 Caravan | 4A      | Economy
 CN1           | Cessna 208 Caravan | 4B      | Economy
 CN1           | Cessna 208 Caravan | 5A      | Economy
 CN1           | Cessna 208 Caravan | 5B      | Economy
 CN1           | Cessna 208 Caravan | 6A      | Economy
 CN1           | Cessna 208 Caravan | 6B      | Economy
(12 строк)

Самолеты большего размера имеют больше посадочных мест с разными классами обслуживания: 

SELECT   s2.aircraft_code,
         string_agg (s2.fare_conditions || '(' || s2.num::text || ')',
                     ', ') as fare_conditions
FROM     (
          SELECT   s.aircraft_code, s.fare_conditions, count(*) as num
          FROM     seats s
          GROUP BY s.aircraft_code, s.fare_conditions
          ORDER BY s.aircraft_code, s.fare_conditions
         ) s2
GROUP BY s2.aircraft_code
ORDER BY s2.aircraft_code;
    

 aircraft_code |             fare_conditions
---------------+-----------------------------------------
 319           | Business(20), Economy(96)
 320           | Business(20), Economy(120)
 321           | Business(28), Economy(142)
 733           | Business(12), Economy(118)
 763           | Business(30), Economy(192)
 773           | Business(30), Comfort(48), Economy(324)
 CN1           | Economy(12)
 CR2           | Economy(50)
 SU9           | Business(12), Economy(85)
(9 строк)

База данных содержит список аэропортов практически всех крупных городов России. В большинстве городов есть только один аэропорт. Исключение составляют: 

SELECT   a.airport_code as code,
         a.airport_name,
         a.city,
         a.longitude,
         a.latitude,
         a.timezone
FROM     airports a
WHERE    a.city IN (
            SELECT   aa.city
            FROM     airports aa
            GROUP BY aa.city
            HAVING   COUNT(*) > 1
         )
ORDER BY a.city, a.airport_code;
    

 code |    airport_name     |   city    | longitude | latitude  |   timezone
------+---------------------+-----------+-----------+-----------+---------------
 DME  | Домодедово          | Москва    | 37.906111 | 55.408611 | Europe/Moscow
 SVO  | Шереметьево         | Москва    | 37.414589 | 55.972642 | Europe/Moscow
 VKO  | Внуково             | Москва    | 37.261486 | 55.591531 | Europe/Moscow
 ULV  | Баратаевка          | Ульяновск |   48.2267 | 54.268299 | Europe/Samara
 ULY  | Ульяновск-Восточный | Ульяновск |   48.8027 |    54.401 | Europe/Samara
(5 строк)

Чтобы понять, откуда и куда можно улететь, удобно использовать материализованное представление routes, в котором агрегируется информация о всех рейсах. Вот, например, куда, в какие дни недели и за какое время можно долететь из Волгограда: 

SELECT r.arrival_city as city,
       r.arrival_airport as airport_code,
       r.arrival_airport_name as airport_name,
       r.days_of_week,
       r.duration
FROM   routes r
WHERE  r.departure_city = 'Волгоград';
    

      city      | airport_code |  airport_name  |  days_of_week   | duration
----------------+--------------+----------------+-----------------+----------
 Москва         | SVO          | Шереметьево    | {1,2,3,4,5,6,7} | 01:15:00
 Челябинск      | CEK          | Челябинск      | {1,2,3,4,5,6,7} | 01:50:00
 Ростов-на-Дону | ROV          | Ростов-на-Дону | {1,2,3,4,5,6,7} | 00:30:00
 Москва         | VKO          | Внуково        | {1,2,3,4,5,6,7} | 01:10:00
 Чебоксары      | CSY          | Чебоксары      | {1,2,3,4,5,6,7} | 02:45:00
 Томск          | TOF          | Богашёво       | {3}             | 03:50:00
(6 строк)

База данных была сформирована на момент времени, возвращаемый функцией bookings.now(): 

SELECT bookings.now() as now;
    

          now
------------------------
 2016-10-13 17:00:00+03

Относительно именно этого момента времени все рейсы делятся на прошедшие и будущие: 

SELECT   status,
         count(*) as count,
         min(scheduled_departure) as min_scheduled_departure,
         max(scheduled_departure) as max_scheduled_departure
FROM     flights
GROUP BY status
ORDER BY min_scheduled_departure;
    

  status   | count | min_scheduled_departure | max_scheduled_departure
-----------+-------+-------------------------+-------------------------
 Arrived   | 16707 | 2016-09-13 00:50:00+03  | 2016-10-13 16:25:00+03
 Cancelled |   414 | 2016-09-16 10:35:00+03  | 2016-11-12 19:55:00+03
 Departed  |    58 | 2016-10-13 08:55:00+03  | 2016-10-13 16:50:00+03
 Delayed   |    41 | 2016-10-13 14:15:00+03  | 2016-10-14 16:25:00+03
 On Time   |   518 | 2016-10-13 16:55:00+03  | 2016-10-14 17:00:00+03
 Scheduled | 15383 | 2016-10-14 17:05:00+03  | 2016-11-12 19:40:00+03
(6 строк)

Найдем ближайший рейс, вылетающий из Екатеринбурга в Москву. Использовать для такого запроса таблицу flight не очень удобно, так как в ней нет информации о городах отправления и прибытия. Поэтому воспользуемся представлением flights_v: 

\x
SELECT   f.*
FROM     flights_v f
WHERE    f.departure_city = 'Екатеринбург'
AND      f.arrival_city = 'Москва'
AND      f.scheduled_departure > bookings.now()
ORDER BY f.scheduled_departure
LIMIT    1;
    

-[ RECORD 1 ]-------------+-----------------------
flight_id                 | 10927
flight_no                 | PG0226
scheduled_departure       | 2016-10-14 07:10:00+03
scheduled_departure_local | 2016-10-14 09:10:00
scheduled_arrival         | 2016-10-14 08:55:00+03
scheduled_arrival_local   | 2016-10-14 08:55:00
scheduled_duration        | 01:45:00
departure_airport         | SVX
departure_airport_name    | Кольцово
departure_city            | Екатеринбург
arrival_airport           | SVO
arrival_airport_name      | Шереметьево
arrival_city              | Москва
status                    | On Time
aircraft_code             | 773
actual_departure          |
actual_departure_local    |
actual_arrival            |
actual_arrival_local      |
actual_duration           |

Обратите внимание, что в представлении flights_v указано не только московское время, но и местное время в аэропортах вылета и прилета.

J.5.3. Бронирования

Каждое бронирование может включать несколько билетов, по одному на каждого пассажира. Билет, в свою очередь, может включать несколько перелетов. Полная информация о бронировании находится в трёх таблицах: bookings, tickets и ticket_flights.

Найдём несколько бронирований с самой высокой стоимостью: 

SELECT   *
FROM     bookings
ORDER BY total_amount desc
LIMIT    10;
    

 book_ref |       book_date        | total_amount
----------+------------------------+--------------
 3B54BB   | 2016-09-02 16:08:00+03 |   1204500.00
 3AC131   | 2016-09-28 00:06:00+03 |   1087100.00
 65A6EA   | 2016-08-31 05:28:00+03 |   1065600.00
 D7E9AA   | 2016-10-06 04:29:00+03 |   1062800.00
 EF479E   | 2016-09-30 14:58:00+03 |   1035100.00
 521C53   | 2016-09-05 08:25:00+03 |    985500.00
 514CA6   | 2016-09-24 04:07:00+03 |    955000.00
 D70BD9   | 2016-09-02 11:47:00+03 |    947500.00
 EC7EDA   | 2016-08-30 15:13:00+03 |    946800.00
 8E4370   | 2016-09-25 01:04:00+03 |    945700.00
(10 строк)

Посмотрим, из каких билетов состоит бронирование с кодом 521C53: 

SELECT ticket_no,
       passenger_id,
       passenger_name
FROM   tickets
WHERE  book_ref = '521C53';
    

   ticket_no   | passenger_id |   passenger_name
---------------+--------------+--------------------
 0005432661914 | 8234 547529  | IVAN IVANOV
 0005432661915 | 2034 201228  | ANTONINA KUZNECOVA
(2 строки)

Если нас интересует, какие перелеты включены в билет Антонины Кузнецовой, то это можно узнать запросом: 

SELECT   to_char(f.scheduled_departure, 'DD.MM.YYYY') as when,
         f.departure_city || '(' || f.departure_airport || ')' as departure,
         f.arrival_city || '(' || f.arrival_airport || ')' as arrival,
         tf.fare_conditions as class,
         tf.amount
FROM     ticket_flights tf
         JOIN flights_v f ON tf.flight_id = f.flight_id
WHERE    tf.ticket_no = '0005432661915'
ORDER BY f.scheduled_departure;
    

    when    |     departure     |      arrival      |  class   |  amount
------------+-------------------+-------------------+----------+-----------
 26.09.2016 | Москва(SVO)       | Анадырь(DYR)      | Business | 185300.00
 30.09.2016 | Анадырь(DYR)      | Хабаровск(KHV)    | Business |  92200.00
 01.10.2016 | Хабаровск(KHV)    | Благовещенск(BQS) | Business |  18000.00
 06.10.2016 | Благовещенск(BQS) | Хабаровск(KHV)    | Business |  18000.00
 10.10.2016 | Хабаровск(KHV)    | Анадырь(DYR)      | Economy  |  30700.00
 15.10.2016 | Анадырь(DYR)      | Москва(SVO)       | Business | 185300.00
(6 строк)

Как видим, высокая стоимость бронирования объясняется большим количеством перелётов на дальние расстояния бизнес-классом.

Часть перелётов в этом билете имеет более ранние даты, чем значение bookings.now(): это значит, что они уже выполнены. А последний полет ещё предстоит. После регистрации на рейс выписывается посадочный талон с указанием места в самолете. Мы можем посмотреть какие именно места занимала Антонина (обратите внимание на внешнее левое соединение с таблицей boarding_passes): 

SELECT   to_char(f.scheduled_departure, 'DD.MM.YYYY') as when,
         f.departure_city || '(' || f.departure_airport || ')' as departure,
         f.arrival_city || '(' || f.arrival_airport || ')' as arrival,
         f.status,
         bp.seat_no
FROM     ticket_flights tf
         JOIN flights_v f ON tf.flight_id = f.flight_id
         LEFT JOIN boarding_passes bp ON tf.flight_id = bp.flight_id
                                     AND tf.ticket_no = bp.ticket_no
WHERE    tf.ticket_no = '0005432661915'
ORDER BY f.scheduled_departure;
    

    when    |     departure     |      arrival      |  status   | seat_no
------------+-------------------+-------------------+-----------+---------
 26.09.2016 | Москва(SVO)       | Анадырь(DYR)      | Arrived   | 5C
 30.09.2016 | Анадырь(DYR)      | Хабаровск(KHV)    | Arrived   | 1D
 01.10.2016 | Хабаровск(KHV)    | Благовещенск(BQS) | Arrived   | 2C
 06.10.2016 | Благовещенск(BQS) | Хабаровск(KHV)    | Arrived   | 2D
 10.10.2016 | Хабаровск(KHV)    | Анадырь(DYR)      | Arrived   | 20B
 15.10.2016 | Анадырь(DYR)      | Москва(SVO)       | Scheduled |
(6 строк)

J.5.4. Новое бронирование

Попробуем отправить Александра Николаевича Радищева по маршруту, который принес ему известность. Разумеется бесплатно и бизнес-классом. Предварительно найдем «завтрашний» рейс, а также позаботимся о возвращении через неделю. 

BEGIN;

INSERT INTO bookings (book_ref, book_date, total_amount)
VALUES      ('_QWE12', bookings.now(), 0);

INSERT INTO tickets (ticket_no, book_ref, passenger_id, passenger_name)
VALUES      ('_000000000001', '_QWE12', '1749 051790', 'ALEKSANDR RADISHCHEV');

INSERT INTO ticket_flights (ticket_no, flight_id, fare_conditions, amount)
VALUES      ('_000000000001', 9720, 'Business', 0),
            ('_000000000001', 6662, 'Business', 0);

COMMIT;

Мы начинаем идентификаторы с подчёркивания, чтобы не пересекаться с диапазоном значений, присутствующих в базе.

Сразу зарегистрируемся на завтрашний рейс: 

INSERT INTO boarding_passes (ticket_no, flight_id, boarding_no, seat_no)
VALUES      ('_000000000001', 9720, 1, '1A');

Проверим информацию о созданном бронировании: 

SELECT   b.book_ref,
         t.ticket_no,
         t.passenger_id,
         t.passenger_name,
         tf.fare_conditions,
         tf.amount,
         f.scheduled_departure_local,
         f.scheduled_arrival_local,
         f.departure_city || '(' || f.departure_airport || ')' as departure,
         f.arrival_city || '(' || f.arrival_airport || ')' as arrival,
         f.status,
         bp.seat_no
FROM     bookings b
         JOIN tickets t ON b.book_ref = t.book_ref
         JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no
         JOIN flights_v f ON tf.flight_id = f.flight_id
         LEFT JOIN boarding_passes bp ON tf.flight_id = bp.flight_id
                                     AND tf.ticket_no = bp.ticket_no
WHERE    b.book_ref = '_QWE12'
ORDER BY t.ticket_no, f.scheduled_departure;
    

-[ RECORD 1 ]-------------+---------------------
book_ref                  | _QWE12
ticket_no                 | _000000000001
passenger_id              | 1749 051790
passenger_name            | ALEKSANDR RADISHCHEV
fare_conditions           | Business
amount                    | 0.00
scheduled_departure_local | 2016-10-14 08:45:00
scheduled_arrival_local   | 2016-10-14 09:35:00
departure                 | Санкт-Петербург(LED)
arrival                   | Москва(SVO)
status                    | On Time
seat_no                   | 1A
-[ RECORD 2 ]-------------+---------------------
book_ref                  | _QWE12
ticket_no                 | _000000000001
passenger_id              | 1749 051790
passenger_name            | ALEKSANDR RADISHCHEV
fare_conditions           | Business
amount                    | 0.00
scheduled_departure_local | 2016-10-21 09:20:00
scheduled_arrival_local   | 2016-10-21 10:10:00
departure                 | Москва(SVO)
arrival                   | Санкт-Петербург(LED)
status                    | Scheduled
seat_no                   |

Надеемся, что эти несколько простых примеров помогли составить представление о содержимом демонстрационной базы данных.

Пред. Начало След.
J.4. Объекты схемы Наверх Приложение K. Сокращения

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.