Перейти к основному содержимому

Практикум demo — авиакомпания PostgreSQL

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ
Разработчику Аналитику Архитектору

Контекст: учебная база demo — открытый логический дамп для практики SQL. Предметная область — воображаемая авиакомпания — бронирования, билеты, рейсы, аэропорты. После Первые шаги с SQL и PostgreSQL — практическая работа и API — лучший следующий шаг для JOIN, агрегатов и оконных функций на осмысленных данных.

См. также: JOIN — четыре типа на одном примере · Практикум shop_data · мета-команды psql

Установка базы demo​

Официальные архивы — логический дамп pg_dump, восстанавливается через psql. Три размера (данные о полётах за 1 / 3 / 12 месяцев):

АрхивОбъём архиваРазмер БД после restore
demo-small.zip~21 МБ~280 МБ
demo-medium.zip~62 МБ~702 МБ
demo-big.zip~232 МБ~2,6 ГБ

Для первых запросов достаточно small; для экспериментов с планами и индексами — big.

Linux (от пользователя postgres):

sudo su - postgres
wget https://edu.postgrespro.ru/demo-small.zip
zcat demo-small.zip | psql

Windows. Скачайте архив, распакуйте SQL-файл (имя с датой среза, например demo-small-20170815.sql), положите в рабочую папку psql и выполните:

\i demo-small-20170815.sql
Существующая база demo

Скрипт восстановления пересоздаёт базу demo. Владельцем станет роль, под которой выполнялся restore.

Подключение:

psql -U postgres -d demo
\c demo
SELECT bookings.now();
SELECT count(*) FROM flights;

Схема данных (схема bookings)​

Все учебные объекты лежат в схеме bookings. При подключении к demo она в search_path, поэтому в запросах можно писать flights, а не bookings.flights.

ТаблицаНазначение
bookingsБронирование (book_ref, дата, total_amount)
ticketsБилет на пассажира (ticket_no, passenger_name, …)
ticket_flightsСвязь билет ↔ рейс, класс, стоимость перелёта
flightsКонкретный рейс (расписание, статус, фактические времена)
airportsКод IATA, город, coordinates (point), timezone
aircraftsМодель ВС и дальность полёта
seatsМеста в салоне по модели и классу обслуживания
boarding_passesПосадочный талон (рейс, место, порядок посадки)

Представления

  • flights_v — рейсы с расшифровкой аэропортов, локальным временем и длительностью полёта (\d+ flights_v).
  • routes — "маршрут без даты" — номер рейса, аэропорты, модель, массив дней недели days_of_week, плановая duration.

Служебное

  • bookings.now() — фиксированный "момент среза" дампа (как now() в учебных запросах). В small-дампе срез — 2017-08-15.
  • Параметр bookings.lang (ru / en) — язык названий городов и моделей: ALTER DATABASE demo SET bookings.lang = 'en'; затем \c.

Подробное описание столбца — \d+ имя_таблицы в psql.

Базовые запросы​

Справочники​

SELECT airport_code, city FROM airports LIMIT 5;

SELECT aircraft_code, model, range FROM aircrafts ORDER BY range DESC;

JOIN — рейсы с городами​

SELECT f.flight_no,
       f.scheduled_departure,
       da.city AS from_city,
       aa.city AS to_city
FROM flights f
JOIN airports da ON da.airport_code = f.departure_airport
JOIN airports aa ON aa.airport_code = f.arrival_airport
WHERE f.scheduled_departure::date = bookings.now()::date
LIMIT 10;

Тот же смысл короче через представление:

SELECT flight_no, departure_city, arrival_city, scheduled_departure
FROM flights_v
WHERE scheduled_departure::date = bookings.now()::date
LIMIT 10;

Агрегаты и GROUP BY​

Сколько билетов в каждом бронировании:

SELECT cnt, count(*) AS bookings_count
FROM (
  SELECT book_ref, count(*) AS cnt
  FROM tickets
  GROUP BY book_ref
) tt
GROUP BY cnt
ORDER BY cnt;

Доля пассажиров по фамилии (оконная функция):

SELECT passenger_name,
       round(100.0 * cnt / sum(cnt) OVER (), 2) AS percent
FROM (
  SELECT passenger_name, count(*) AS cnt
  FROM tickets
  GROUP BY passenger_name
) t
ORDER BY percent DESC
LIMIT 10;

Задержки рейсов​

SELECT f.flight_no,
       f.scheduled_departure,
       f.actual_departure,
       f.actual_departure - f.scheduled_departure AS delay
FROM flights f
WHERE f.actual_departure IS NOT NULL
ORDER BY delay DESC
LIMIT 10;

NOT EXISTS — свободные места на рейсе​

SELECT count(*) AS free_seats
FROM flights f
JOIN seats s ON s.aircraft_code = f.aircraft_code
WHERE f.flight_no = 'PG0404'
  AND f.scheduled_departure::date = bookings.now()::date - INTERVAL '1 day'
  AND NOT EXISTS (
    SELECT NULL
    FROM boarding_passes bp
    WHERE bp.flight_id = f.flight_id
      AND bp.seat_no = s.seat_no
  );

Расстояние между аэропортами​

Задача — расстояние KGD (Калининград) и PKC (Петропавловск-Камчатский). Координаты в airports.coordinates; для сферической модели достаточно расширений cube и earthdistance:

CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS earthdistance;

SELECT round(
  (a_from.coordinates <@> a_to.coordinates) * 1.609344
) AS km
FROM airports a_from,
     airports a_to
WHERE a_from.airport_code = 'KGD'
  AND a_to.airport_code = 'PKC';

Оператор <@> даёт расстояние в милях; множитель 1.609344 переводит в километры. Для продакшена с геодезической точностью смотрите PostGIS — в PostgreSQL — практическая работа и API и справочнике.

Задания для самопроверки​

  1. Список аэропортов России (city или код страны в данных) с числом исходящих рейсов за "сегодня" (bookings.now()).
  2. Топ-5 маршрутов (departure_airport → arrival_airport) по числу рейсов за последние 7 дней от bookings.now().
  3. Пассажиры, зарегистрировавшиеся первыми (boarding_no = 1) более чем на одном рейсе (подсказка — GROUP BY + HAVING).
  4. Граф рейсов — пары аэропортов, между которыми есть хотя бы один рейс в routes или flights (можно начать с SELECT DISTINCT departure_airport, arrival_airport FROM routes).
  5. Переключите bookings.lang на en, переподключитесь (\c) и сравните вывод SELECT city FROM airports LIMIT 5.
Дальше

Сложные маршруты с пересадками решаются через рекурсивный CTE — см. Общие табличные выражения (CTE). Оптимизацию планов — Оптимизация SQL-запросов. Администрирование сервера — справочник PostgreSQL.

Содержание