Практика работы с PostgreSQL

Базы данных Практика работы с PostgreSQL

Помечено: , ,

  • В этой теме 0 ответов, 1 участник, последнее обновление 1 месяц, 2 недели назад сделано Васильев Владимир Сергеевич.
Просмотр 0 веток ответов
  • Автор
    Сообщения
    • #6810
      @admin
      StudLance.ru

      В этой статье решается чисто практическая задача — создается база данных, настраиваются права пользователей (ролей) и формируется ряд запросов к базе. В конце статьи приведен пример анализа запросов (EXPLAIN). В качеству СУБД используется PostgreSQL, однако, все это можно выполнить и в других СУБД. Особенности PostgreSQL и его отличия от MySQL рекомендую посмотреть в статье MySQL & PostgreSQL.

      Задача:

      1. Сформировать несколько таблиц (тема — ремонт самолетов)
      2. Предусмотреть: ввод данных, редактирование, просмотр данных.
      3. Роли: оператор базы данных; пользователь базы данных; администратор БД.

      Типы запросов с использованием нескольких таблиц:

      1. Для каждого вида самолета указать сведения о нем (тип, год выпуска, срок эксплуатации, место изготовления, цена, скорость, вместительность и т.п.).
      2. Для каждого вида самолета выдать список, отсортированный по: году выпуска; стоимости ремонтных работ; цене производителя; сроку эксплуатации.
      3. Найти долю «отремонтированных» самолетов от общего числа самолетов.
      4. Найти самый дорогой самолет, самый дешевый, среднюю стоимость.
      5. Найти самолеты с ценой свыше 8000 т.р. (и любая другая сумма, т.е. предусмотреть ввод цены с клавиатуры).
      6. Найти все самолеты, поступившие из заданной страны (выбор страны), чья вместительность (ввод) больше заданной.
      7. Найти количество самолетов на авиасалоне (по каждому типу и в целом) с заданным сроком эксплуатации (ввод срока эксплуатации).
      8. Для заданного типа самолета (выбор типа самолета) найти все, выпущенные за определенный период (ввод), чья стоимость находится в заданных пределах (ввод интервала).
      9. Найти самый дорогой вид ремонта, самый дешевый, среднюю стоимость ремонта для каждого самолета и в целом.
      10. Найти самолеты со стоимостью ремонтных работ в заданных пределах (предусмотреть ввод цены с клавиатуры).
      11. Количество отремонтированных машин за месяц, квартал; средняя стоимость ремонтных работ за этот же период.
      12. Найти долю дешевых (по стоимости ремонтных работ) самолетов (меньше заданного, ввод ограничения) от общего числа, подлежащих ремонту.

      Структура таблиц и связи между ними

      Роли и запросы

      В БД предусмотрены следующие роли:

      Роль Права
      Администратор БД Доступны все действия
      Оператор Доступны все действия, кроме удаления значений из таблиц и
      создания новых таблиц
      Пользователь Только чтение из таблиц

      Запросы выполнялись в плагине Database Support в IDE от JetBrains. Утилита отличается удобством работы и хорошо читаемым представлением результатов выполнения запросов.

      Аналоги:

      1. терминал psql (PostgreSQL interactive terminal);
      2. pgAdmin – свободное ПО, предоставляющее графический интерфейс для работы с базой данных.

      Все нижеописанные операции могут быть выполнены в командной оболочке PostgreSQL.

      Установить кодировку консоли
      > chcp 1251

      Команда для запуска оболочки:
      > psql –Upostgres

      Посмотреть права для таблицы
      > \dp table_name

      Подключение к БД заданным пользователем
      > psql -U admin -d airshow

      Запросы для вставки записей в таблицы

      Действие

      Запрос

      Добавить новую запись в таблицу самолетов aircrafts

      INSERT INTO aircrafts 
      (kind_id, type_id, country_code, year_of_issue, flight_hours, price, speed, max_range, max_payload)
      VALUES
      (идентификатор вида, идентификатор типа, код страны, год, часы налета, стоимость в $USA, скорость км/ч, максимальная дистанция, полезная нагрузка)
      Добавить новую запись в таблицу ремонтных работ repair_works

      INSERT INTO repair_works 
      (aircraft_id, work_type_id, cost, date)
      VALUES
      (идентификатор самолета, идентификатор типа работы, стоимость ремонта, дата проведения ремонта в формате yyyy-mm-dd)

      Запросы для создания таблиц

      -- Создание БД
      CREATE DATABASE airshow;
      
      -- Создание пользователей
      CREATE USER admin WITH PASSWORD 'admin';
      
      CREATE USER operator WITH PASSWORD 'operator';
      
      CREATE USER reader WITH PASSWORD 'reader';
      
      -- переключение на БД в консоли \c airshow
      -- Выдача прав для роли Администратор
      GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "admin";
      
      
      -- Настройка прав для Оператора. 
      -- Для упрощения, сначала выдаются все права, 
      -- затем забирается удаление и создание
      GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "operator";
      REVOKE CREATE ON SCHEMA public FROM "operator";
      REVOKE DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public FROM "operator";
      
      -- Настройка прав для Пользователя
      GRANT CONNECT ON DATABASE airshow TO "reader";
      GRANT USAGE ON SCHEMA public TO "reader";
      GRANT SELECT ON ALL TABLES IN SCHEMA public TO "reader";
      
      
      Дальнейшие действия выполняются пользователем Администратор БД
      -- Создание таблиц
      CREATE TABLE countries (
        country_code VARCHAR(3)   NOT NULL,
        country_name VARCHAR(255) NOT NULL UNIQUE,
        PRIMARY KEY (country_code)
      );
      COMMENT ON COLUMN countries.country_code IS 'Код названия страны в стандарте ISO 3166-1 alpha-3';
      
      CREATE TABLE aircraft_kinds (
        kind_id   SERIAL PRIMARY KEY,
        kind_name VARCHAR(255) NOT NULL UNIQUE
      );
      COMMENT ON TABLE aircraft_kinds IS 'Виды самолетов: пассажирский, грузовой и т.д.';
      
      CREATE TABLE aircraft_types (
        type_id   SERIAL PRIMARY KEY,
        type_name VARCHAR(255) NOT NULL UNIQUE
      );
      COMMENT ON TABLE aircraft_types IS 'Типы самолетов: Ту-204, Boeing-777';
      
      CREATE TABLE aircrafts (
        id            SERIAL PRIMARY KEY,
        kind_id       INTEGER NOT NULL,
        type_id       INTEGER NOT NULL,
        country_code  VARCHAR(3),
        year_of_issue INTEGER NOT NULL,
        flight_hours  INTEGER NOT NULL,
        price         INTEGER NOT NULL,
        speed         INTEGER NOT NULL,
        max_range     INTEGER NOT NULL,
        max_payload   INTEGER NOT NULL,
      
        FOREIGN KEY (kind_id) REFERENCES aircraft_types (type_id),
        FOREIGN KEY (kind_id) REFERENCES aircraft_kinds (kind_id),
        FOREIGN KEY (country_code) REFERENCES countries (country_code)
      );
      COMMENT ON TABLE aircrafts IS 'Самолеты и характеристики';
      CREATE INDEX aircrafts_year_of_issue_idx
        ON aircrafts (year_of_issue);
      CREATE INDEX aircrafts_flight_hours_idx
        ON aircrafts (flight_hours);
      CREATE INDEX aircrafts_country_code_idx
        ON aircrafts (country_code);
      CREATE INDEX aircrafts_country_payload_idx
        ON aircrafts (country_code, max_payload);
      CREATE INDEX aircrafts_price_idx
        ON aircrafts (price);
      CREATE INDEX aircrafts_max_payload_idx
        ON aircrafts (max_payload);
      
      CREATE TABLE repair_types (
        repair_type_id SERIAL PRIMARY KEY,
        repair_name    VARCHAR(200) NOT NULL UNIQUE
      );
      COMMENT ON TABLE repair_types IS 'Виды ремонтных работ';
      
      CREATE TABLE repair_works (
        id           SERIAL PRIMARY KEY,
        aircraft_id  INTEGER,
        work_type_id INTEGER,
        cost         NUMERIC(10, 2) NOT NULL,
        "date"       DATE           NOT NULL,
      
        FOREIGN KEY (aircraft_id) REFERENCES aircrafts (id),
        FOREIGN KEY (work_type_id) REFERENCES repair_types (repair_type_id)
      );
      COMMENT ON TABLE repair_works IS 'Ремонтные работы';
      CREATE INDEX repair_works_work_type_idx
        ON repair_works (work_type_id);
      CREATE INDEX repair_works_cost_idx
        ON repair_works (cost);
      CREATE INDEX repair_works_date_idx
        ON repair_works (date);

      Запросы ввода данных в таблицы

      -- Заполнение справочных данных
      INSERT INTO countries (country_code, country_name) VALUES
        ('CAN', 'Канада'),
        ('RUS', 'Россия'),
        ('USA', 'США'),
        ('FRA', 'Франция'),
        ('UKR', 'Украина');
      
      INSERT INTO aircraft_kinds (kind_id, kind_name) VALUES
        (1, 'Пассажирский'),
        (2, 'Транспортный'),
        (3, 'Сельскохозяйственный');
      
      INSERT INTO aircraft_types (type_id, type_name) VALUES
        (1, 'Airbus A220'),
        (2, 'Туполев Ту-134'),
        (3, 'Туполев Ту-154'),
        (4, 'Boeing-777'),
        (5, 'Airbus A330-200F'),
        (6, 'ИЛ-76МД'),
        (7, 'Ан-2'),
        (8, 'Ан-3');
      
      INSERT INTO repair_types (repair_type_id, repair_name)
      VALUES
        (1, 'Фюзеляж'),
        (2, 'Крыло'),
        (3, 'Оперение'),
        (4, 'Шасси'),
        (5, 'Силовая установка'),
        (6, 'Тормозная система'),
        (7, 'Навигационный комплекс'),
        (8, 'Cистема автоматического управления (САУ)'),
        (9, 'Системы оборудования силовых установок (СУ)'),
        (10, 'Система предупреждения о столкновении'),
        (11, 'Система бортового электроснабжения (БЭС)'),
        (12, 'Приборное оборудование'),
        (13, 'Радионавигационное оборудование (РНО)'),
        (14, 'Система кондиционирования (СКВ) и жизнеобеспечения'),
        (15, 'Высотное и кислородное оборудование'),
        (16, 'Аварийно-спасательное оборудование'),
        (17, 'Бытовое оборудование');
      
      -- Заполнение информации о самолетах
      INSERT INTO aircrafts (kind_id, type_id, country_code, year_of_issue, flight_hours, price, speed, max_range, max_payload)
      VALUES
        (1, 1, 'CAN', 2016, 10000, 60000000, 850, 4900, 13000),
        (1, 1, 'CAN', 2017, 6000, 76500000, 871, 5741, 15127),
        (1, 2, 'RUS', 1997, 45000, 10000000, 850, 2100, 8500),
        (1, 3, 'RUS', 2000, 10000, 12000000, 900, 2650, 18000),
        (1, 3, 'RUS', 2007, 5000, 20000000, 950, 3000, 18000),
        (2, 5, 'FRA', 2016, 3000, 150000000, 871, 7400, 68600),
        (2, 6, 'RUS', 2011, 32000, 25000000, 800, 4200, 60000),
        (3, 7, 'UKR', 1976, 4000, 200000, 258, 845, 2140),
        (3, 8, 'RUS', 2009, 10000, 350000, 255, 770, 1800);
      
      
      -- Заполнение информации о ремонтных работах
      
      INSERT INTO repair_works (aircraft_id, work_type_id, cost, date)
      VALUES
        (2, 3, 10000, '2017-05-01'),
        (2, 4, 2500, '2017-02-03'),
        (2, 13, 270285, '2018-09-14'),
        (8, 12, 6000, '2018-10-17'),
        (3, 16, 291085, '2017-11-09'),
        (8, 17, 6140, '2017-11-18'),
        (4, 12, 521498, '2018-11-18'),
        (3, 5, 466318, '2018-02-19'),
        (6, 16, 212918, '2017-07-31'),
        (6, 15, 172562, '2017-04-25'),
        (4, 4, 15523, '2018-08-13'),
        (6, 15, 466249, '2017-03-19'),
        (5, 5, 580398, '2018-06-17'),
        (2, 13, 525048, '2017-11-03'),
        (3, 7, 69291, '2017-11-22'),
        (2, 4, 413332, '2018-09-13'),
        (2, 15, 409000, '2018-01-29'),
        (5, 13, 318737, '2018-04-25'),
        (7, 10, 446520, '2017-03-11'),
        (7, 11, 372521, '2017-12-14'),
        (4, 16, 604561, '2018-01-10'),
        (7, 17, 548845, '2017-03-27');

      Запросы выборки данных

      Для каждого вида самолета выдать список, отсортированный:

      1. по году выпуска,
      2. по стоимости ремонтных работ,
      3. по цене производителя,
      4. по сроку эксплуатации.

      
      SELECT
        a.id,
        k.kind_name,
        t.type_name,
        a.country_code,
        a.year_of_issue,
        a.flight_hours,
        a.price,
        a.speed,
        a.max_range,
        a.max_payload,
        coalesce(sum(rw.cost), 0) AS sum_repair_cost
      FROM aircrafts a
        JOIN aircraft_kinds k ON k.kind_id = a.kind_id
        JOIN aircraft_types t ON t.type_id = a.type_id
        LEFT JOIN repair_works rw ON a.id = rw.aircraft_id
      WHERE a.kind_id = 1 -- вид "Пассажирский"
      GROUP BY a.id, k.kind_name, t.type_name, a.country_code, a.year_of_issue, a.flight_hours, a.price, a.speed, a.max_range,
        a.max_payload
      ORDER BY sum_repair_cost; -- по стоимости ремонтных работ

      Найти долю «отремонтированных» самолетов от общего числа самолетов.

      SELECT round(repaired_aircrafts :: DECIMAL / total_aircrafts, 2) AS repaired_rate
      FROM
        (SELECT count(DISTINCT aircraft_id) AS repaired_aircrafts FROM repair_works) AS rw,
        (SELECT count(id) AS total_aircrafts FROM aircrafts) AS a;

      Найти самый дорогой самолет, самый дешевый, среднюю стоимость.

      WITH filters AS (SELECT max(price) AS max_price, min(price) AS min_price, round(avg(price),2) AS average_price FROM aircrafts)
      SELECT aircraft_data.id, t.type_name, aircraft_data.price, aircraft_data.filter FROM
        (SELECT a_max.id, a_max.type_id,  a_max.price,  'max' as filter FROM aircrafts a_max
        WHERE a_max.price = (SELECT max_price FROM filters)
         UNION
         SELECT a_min.id, a_min.type_id, a_min.price, 'min' as filter FROM aircrafts a_min
         WHERE a_min.price = (SELECT min_price FROM filters)
         UNION
         SELECT * FROM (VALUES(-1, -1, (SELECT average_price FROM filters), 'average')) as val
        ) AS aircraft_data LEFT JOIN aircraft_types t on t.type_id = aircraft_data.type_id;

      Найти самолеты с ценой свыше 8000 т.р. (и любая другая сумма, т.е. предусмотреть ввод цены).

      SELECT
        a.id,
        k.kind_name,
        t.type_name,
        a.country_code,
        a.year_of_issue,
        a.flight_hours,
        a.price,
        a.speed,
        a.max_range,
        a.max_payload
      FROM aircrafts a
        JOIN aircraft_kinds k ON k.kind_id = a.kind_id
        JOIN aircraft_types t ON t.type_id = a.type_id
      WHERE a.price > 15000000 -- цена
      ORDER BY a.price;

      Найти все самолеты, поступившие из заданной страны (выбор страны), чья вместительность (ввод) больше заданной:

      SELECT
        a.id,
        k.kind_name,
        t.type_name,
        a.country_code,
        a.year_of_issue,
        a.flight_hours,
        a.price,
        a.speed,
        a.max_range,
        a.max_payload
      FROM aircrafts a
        JOIN aircraft_kinds k ON k.kind_id = a.kind_id
        JOIN aircraft_types t ON t.type_id = a.type_id
      WHERE a.country_code = 'RUS' -- страна
            and a.max_payload > 15000 -- вместимость
      ORDER BY t.type_name;

      Найти количество самолетов на авиасалоне (по каждому типу и в целом) с заданным сроком эксплуатации (ввод срока эксплуатации):

      SELECT t.type_name, a.flight_hours, count(a.type_id)
      FROM aircrafts a JOIN aircraft_types t on t.type_id = a.type_id
      WHERE a.flight_hours = 10000
      GROUP BY a.type_id, t.type_name, a.flight_hours;
      
      SELECT  count(*)
      FROM aircrafts a
      WHERE a.flight_hours = 10000;

      Для заданного типа самолета (выбор типа самолета) найти все, выпущенные за определенный период (ввод), чья стоимость находится в заданных пределах (ввод интервала):

      SELECT a.id, t.type_name, a.year_of_issue, a.price
      FROM aircrafts a JOIN aircraft_types t on t.type_id = a.type_id
      WHERE a.type_id = 3
            AND a.year_of_issue BETWEEN 1990 AND 2018
            AND a.price BETWEEN 1000000 AND 50000000;

      Количество отремонтированных машин за месяц, квартал; средняя стоимость ремонтных работ за этот же период:

      SELECT
        EXTRACT(YEAR FROM rw.date)    AS repair_year,
        EXTRACT(QUARTER FROM rw.date) AS repair_quarter,
        count(DISTINCT aircraft_id)   AS repair_aircrafts_count,
        round(avg(rw.cost), 2)        AS repair_avg_cost
      FROM repair_works rw
      GROUP BY repair_year, repair_quarter 
      ORDER BY repair_year, repair_quarter;
      
      -- По месяцам
      SELECT
        EXTRACT(YEAR FROM rw.date)  AS repair_year,
        EXTRACT(MONTH FROM rw.date) AS repair_month,
        count(DISTINCT aircraft_id) AS repair_aircrafts_count,
        round(avg(rw.cost), 2)      AS repair_avg_cost
      FROM repair_works rw
      GROUP BY repair_year, repair_month
      ORDER BY repair_year, repair_month;

      Найти долю дешевых (по стоимости ремонтных работ) самолетов (меньше заданного, ввод ограничения) от общего числа, подлежащих ремонту:

      SELECT
        count(DISTINCT rw.aircraft_id)                                            AS repair_aircrafts_filtered,
        (SELECT count(DISTINCT aircraft_id)
         FROM repair_works)                                                       AS repair_aircraft_total,
        round(count(DISTINCT rw.aircraft_id) :: DECIMAL / (SELECT count(DISTINCT aircraft_id)
                                                           FROM repair_works), 2) AS repair_aircraft_rate
      FROM repair_works rw
      WHERE rw.cost < 100000;

      Анализ плана запроса — EXPLAIN

      В PostgreSQL предусмотрена команда для анализа плана запроса.

      Команда EXPLAIN [ANALYZE] показывает, каким образом PostgreSQL собирается выполнять запрос. Команда EXPLAIN ANALYZE запрос[] выполняет запрос (например EXPLAIN ANALYZE DELETE … будет выполнен) и показывает как изначальный план, так и реальный процесс его выполнения. Уверенное чтение вывода этих команд требует детального понимания принципов работы СУБД.
      Внимание нужно обратить на следующее:

      1. Использование полного просмотра таблицы (seq scan);
      2. Использование наиболее примитивного способа объединения таблиц (nested loop);
      3. Для EXPLAIN ANALYZE: нет ли больших отличий в предполагаемом количестве записей и реально выбранном? Если оптимизатор использует устаревшую статистику, то он может выбирать не самый быстрый план выполнения запроса;

      Полный просмотр таблицы (seq scan) далеко не всегда медленнее просмотра по индексу. Если, например, в таблице–справочнике несколько сотен записей, умещающихся в одном-двух блоках на диске, то использование индекса приведёт лишь к тому, что придётся читать ещё и пару лишних блоков индекса. Если в запросе придётся выбрать 80% записей из большой таблицы, то полный просмотр опять же получится быстрее.
      При тестировании запросов с использованием EXPLAIN ANALYZE можно воспользоваться настройками, запрещающими оптимизатору использовать определённые планы выполнения. Например,

      SET enable_seqscan=false;

      запретит использование полного просмотра таблицы.

      Пример анализа запроса:

      EXPLAIN ANALYSE
      SELECT
        a.id, k.kind_name, t.type_name, a.country_code,  a.year_of_issue,  a.flight_hours,  a.price,  a.speed,  a.max_range,  a.max_payload
      FROM aircrafts a
        JOIN aircraft_kinds k ON k.kind_id = a.kind_id
        JOIN aircraft_types t ON t.type_id = a.type_id
      WHERE a.country_code = 'RUS' -- страна
            and a.max_payload > 15000 -- вместимость
      ORDER BY t.type_name;

      Результаты:

      Sort  (cost=29.66..29.66 rows=1 width=1076) (actual time=2.216..2.216 rows=3 loops=1)
        Sort Key: t.type_name
        Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=9.69..29.65 rows=1 width=1076) (actual time=0.965..0.975 rows=3 loops=1)
              ->  Hash Join  (cost=9.54..21.48 rows=1 width=564) (actual time=0.925..0.929 rows=3 loops=1)
                    Hash Cond: (t.type_id = a.type_id)
                    ->  Seq Scan on aircraft_types t  (cost=0.00..11.40 rows=140 width=520) (actual time=0.092..0.093 rows=8 loops=1)
                    ->  Hash  (cost=9.52..9.52 rows=2 width=52) (actual time=0.420..0.420 rows=3 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
                          ->  Bitmap Heap Scan on aircrafts a  (cost=4.17..9.52 rows=2 width=52) (actual time=0.390..0.392 rows=3 loops=1)
                                Recheck Cond: (((country_code)::text = 'RUS'::text) AND (max_payload > 15000))
                                Heap Blocks: exact=1
                                ->  Bitmap Index Scan on aircrafts_country_payload_idx  (cost=0.00..4.17 rows=2 width=0) (actual time=0.341..0.341 rows=3 loops=1)
                                      Index Cond: (((country_code)::text = 'RUS'::text) AND (max_payload > 15000))
              ->  Index Scan using aircraft_kinds_pkey on aircraft_kinds k  (cost=0.14..8.16 rows=1 width=520) (actual time=0.013..0.013 rows=1 loops=3)
                    Index Cond: (kind_id = a.kind_id)
      Planning time: 83.333 ms
      Execution time: 4.608 ms

      Существуют инструменты визуализации анализов запросов, например этот выдает результаты в таком виде:

      StudLance.ru

Просмотр 0 веток ответов
  • Для ответа в этой теме необходимо авторизоваться.
×