Практическое занятие №7: сортировка и фильтрация

Дисциплина: ОП.08 Основы проектирования баз данных | Тема 3. Этапы проектирования баз данных
Критерии оценивания (4 балла):
• Часть 1. Построение корректных предикатов фильтрации WHERE (2 балла).
• Часть 2. Многоуровневая сортировка ORDER BY и позиционирование NULL (1 балл).
• Часть 3. Оптимизация пагинации (Keyset Cursor), правила Collation и самопроверка (1 балл).
Выберите ваш практический сценарий (вариант):

Сценарий 1. Каталог интернет-магазина электроники

Предметная область: Таблица Products содержит 3 500 000 товаров: id (BIGINT, PK), category_id (INT), title (VARCHAR), price (NUMERIC(10,2)), stock_quantity (INT), rating (NUMERIC(2,1), NULL), is_active (BOOLEAN).

Задание 1.1. Комплексный запрос фильтрации, сортировки и пагинации (2 балла)

Сформируйте SQL-запрос для вывода первой страницы каталога (ровно 20 товаров):

  • Товар активен (is_active = TRUE) и имеется на складе (stock_quantity > 0);
  • Диапазон цен: от 15 000 до 120 000 рублей включительно;
  • Многоуровневая сортировка: Категория (category_id) по возрастанию (ASC), Рейтинг (rating) по убыванию (DESC) с явным позиционированием NULLS LAST (товары без рейтинга в конце), Цена (price) по возрастанию (ASC), детерминирующий суффикс по id ASC;
  • Пагинация: ограничение первой страницы (LIMIT 20).

Задание 1.2. Обоснование NULLS LAST и детерминизма (1 балл)

Объясните, почему при сортировке rating DESC в PostgreSQL критически необходимо указывать NULLS LAST, и зачем в конце списка полей добавлен id ASC.

Задание 1.3. Оптимизация пагинации и индекс ESR (1 балл)

1) Предложите DDL-команду создания составного B-Tree индекса по правилу ESR (Equality → Sort → Range), устраняющего сброс на диск external merge Disk.
2) Напишите запрос для 2-й страницы с использованием Keyset-пагинации, если последний товар 1-й страницы имел category_id=5, rating=4.8, price=45000, id=10421.

Сценарий 2. База авиарейсов международного аэропорта

Предметная область: Таблица Flights: flight_id (BIGINT, PK), flight_number (VARCHAR(10)), departure_time (TIMESTAMP), destination (VARCHAR(100)), terminal (CHAR(1)), gate (VARCHAR(5), NULL), status (VARCHAR(20)) — ('ON_TIME', 'DELAYED', 'CANCELLED', 'BOARDING').

Задание 2.1. Комплексный запрос табло вылета (2 балла)

Сформируйте SQL-запрос для оперативного табло диспетчера на ближайшие 12 часов:

  • Фильтрация: рейсы со статусом 'ON_TIME' или 'DELAYED';
  • Время вылета строго в интервале от '2026-10-01 08:00:00' до '2026-10-01 20:00:00' (SARGable предикат);
  • Сортировка: Время_вылета (departure_time) по возрастанию (ASC), Номер_терминала (terminal) по возрастанию;
  • Корректный вывод отмененных или экстренных рейсов с неназначенным гейтом (gate IS NULL): гейт должен быть указан с NULLS FIRST на случай оперативного мониторинга нераспределенных стоянок; завершение детерминирующим полем flight_id ASC;
  • Ограничение выборки: первые 30 рейсов.

Задание 2.2. Анализ поведения NULLS FIRST и трехзначной логики (1 балл)

Опишите, чем грозит диспетчеру аэропорта запрос без явного указания NULLS FIRST при сортировке по гейту в различных СУБД (PostgreSQL vs MySQL). Как трехзначная логика SQL влияет на отбор записей, если добавить условие WHERE gate != 'A1'?

Задание 2.3. Индексное покрытие и частичный индекс (1 балл)

1) Спроектируйте частичный индекс (Partial Index) для оперативного табло, учитывая, что завершенные рейсы составляют 95% таблицы.
2) Как переход на Keyset-пагинацию защитит табло от сдвига строк (дубликатов рейсов) при постоянном добавлении новых записей?

Сценарий 3. Реестр договоров страховой компании

Предметная область: Таблица InsuranceContracts: contract_id (BIGINT, PK), policy_number (VARCHAR(30)), region_code (VARCHAR(10)), sign_date (DATE), total_payout (NUMERIC(15,2)), premium_sum (NUMERIC(15,2)), loss_ratio (NUMERIC(5,2), NULL), branch_id (INT).

Задание 3.1. Запрос реестра крупных убытков за квартал (2 балла)

Сформируйте SQL-запрос для аудита убыточности филиалов:

  • Фильтрация: договоры заключены за 1-й квартал 2026 года (с 2026-01-01 по 2026-03-31 включительно, без использования функций над колонкой даты);
  • Сумма выплат строго превышает 500 000 рублей (total_payout > 500000);
  • Многоуровневая сортировка: Регион (region_code) по возрастанию (ASC), Убыточность (loss_ratio) по убыванию (DESC) с NULLS LAST, детерминирующий суффикс contract_id ASC;
  • Ограничение выборки: 25 договоров первой страницы.

Задание 3.2. Алгоритмический анализ: Quicksort vs External Merge Sort (1 балл)

В таблице 5 000 000 договоров. При выполнении запроса в плане EXPLAIN ANALYZE зафиксировано: Sort Method: external merge Disk: 48MB. Объясните физику происходящего: почему СУБД сбросила серии на диск, как связаны параметр work_mem и временные файлы, и как это устранить.

Задание 3.3. Составной индекс и курсорная пагинация (1 балл)

1) Напишите CREATE INDEX составного индекса для данного отчета по правилу ESR.
2) Напишите запрос второй страницы реестра через Keyset (последняя строка 1-й страницы: region_code='RU-KHM', loss_ratio=3.45, contract_id=89104).

Сценарий 4. Электронный журнал оценок колледжа

Предметная область: Таблица StudentGrades: record_id (BIGINT, PK), student_id (INT), last_name (VARCHAR(60)), first_name (VARCHAR(60)), group_code (VARCHAR(20)), debts_count (INT), gpa (NUMERIC(3,2), NULL), is_expelled (BOOLEAN).

Задание 4.1. Ведомость академических задолженностей (2 балла)

Сформируйте SQL-запрос для деканата для анализа неуспевающих студентов группы:

  • Студент не отчислен (is_expelled = FALSE) и числится в группе 'СА-201';
  • Имеются академические задолженности (debts_count > 0);
  • Многоуровневая сортировка: Фамилия студента (last_name) по возрастанию (ASC) с явным указанием русской локали Collation COLLATE "ru_RU", Средний балл (gpa) по убыванию (DESC) с явным позиционированием NULLS LAST, детерминирующий суффикс record_id ASC;
  • Ограничение выборки: 30 записей первой страницы.

Задание 4.2. Анализ Collation и позиционирования буквы «Ё» (1 балл)

Объясните, почему при стандартном бинарном сопоставлении COLLATE "C" студент с фамилией «Ёлкин» окажется в самом начале ведомости перед «Абрамовым». Как локаль ru_RU решает эту проблему?

Задание 4.3. Оптимизация индексами и блок самопроверки (1 балл)

1) Напишите CREATE INDEX для оптимизации ведомости деканата с учетом Collation.
2) Блиц-самопроверка: почему условие WHERE lower(group_code) = 'са-201' заблокирует использование созданного B-Tree индекса?

Все изменения сохранены в браузере