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

1. Описание исследовательской предметной области

Вам поручена разработка и оптимизация модуля формирования аналитических выписок в информационной системе корпоративного лизинга транспортных средств «ТрансЛизинг-Корп».

База данных содержит основную учетную таблицу договоров лизинга LeaseContracts со следующей структурой:

• contract_id (BIGINT, PRIMARY KEY) — уникальный идентификатор договора;
• client_id (BIGINT, NOT NULL) — идентификатор клиента-лизингополучателя;
• region_code (VARCHAR(10), NOT NULL) — код субъекта РФ (например, 'RU-MOW', 'RU-SPE', 'RU-KHM');
• vehicle_category (VARCHAR(30), NOT NULL) — категория транспорта ('TRUCK', 'CAR', 'BUS', 'SPECIAL');
• contract_status (VARCHAR(20), NOT NULL) — статус ('ACTIVE', 'CLOSED', 'DEFAULT', 'PENDING');
• contract_sum (NUMERIC(15,2), NOT NULL) — общая сумма финансирования в рублях;
• monthly_payment (NUMERIC(15,2), NOT NULL) — ежемесячный лизинговый платеж;
• signing_date (DATE, NOT NULL) — дата подписания договора;
• credit_rating (NUMERIC(3,1), NULL) — скоринговый балл риска клиента (от 1.0 до 10.0; может быть NULL, если оценка не проводилась).

2. Задание 1. Построение сложного комбинированного запроса фильтрации и многоуровневой сортировки

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

1. Условия фильтрации:
   • В выборку должны войти только действующие договоры (contract_status = 'ACTIVE');
   • Категория транспорта строго грузовые или спецтехника ('TRUCK' или 'SPECIAL');
   • Сумма договора находится в диапазоне от 3 000 000 до 25 000 000 рублей включительно;
   • Договор заключен в период с 1 января 2024 года по 31 декабря 2025 года включительно (условие должно быть строго SARGable).
2. Правила многоуровневой сортировки:
   • 1-й уровень: по коду региона (region_code) по возрастанию с явным указанием локали COLLATE "C";
   • 2-й уровень: по скоринговому рейтингу (credit_rating) по убыванию (DESC), при этом договоры с нерассчитанным рейтингом (NULL) должны обязательно выводиться в самом конце списка (NULLS LAST);
   • 3-й уровень: по сумме договора (contract_sum) по убыванию;
   • 4-й уровень (детерминирующий tie-breaker): по первичному ключу (contract_id) по возрастанию.
3. Ограничение выборки: вернуть первые 25 записей первой страницы.

3. Задание 2. Инженерное обоснование поведения NULL и Collation

Ответьте на аналитические вопросы по поведению сформированного запроса:

1. Что произойдет с выводом строк, если удалить явную директиву NULLS LAST в PostgreSQL при сортировке ORDER BY credit_rating DESC? Опишите конкретный бизнес-риск для отчета финансового мониторинга.
2. Почему для строкового поля region_code допустимо использование COLLATE "C", тогда как для поля с кириллическим наименованием клиента (ФИО) использование COLLATE "C" привело бы к грубой ошибке алфавитной сортировки?

4. Задание 3. Оптимизация плана выполнения и проектирование составного B-Tree индекса

В таблице LeaseContracts содержится 12 000 000 строк. При выполнении запроса из Задания 1 планировщик EXPLAIN ANALYZE показывает:

->  Seq Scan on leasecontracts (cost=0.00..385412.00 rows=142000 width=128)
      Filter: ((contract_status = 'ACTIVE') AND ...)
      Rows Removed by Filter: 11858000
->  Sort (cost=412050..412405 rows=142000)
      Sort Key: region_code, credit_rating DESC NULLS LAST, contract_sum DESC, contract_id
      Sort Method: external merge  Disk: 32400kB

Требуется:

1. Спроектировать DDL-команду CREATE INDEX составного B-Tree индекса, который устранит операцию external merge Disk и позволит выполнить запрос через чистый Index Scan без отдельного узла сортировки в памяти (правило ESR: Equality → Sort → Range).
2. Сформулировать переписанный SQL-запрос для перехода на курсорную пагинацию (Keyset Pagination) при переходе со страницы 1 на страницу 2, если последняя запись первой страницы имела значения: region_code = 'RU-MOW', credit_rating = 8.5, contract_sum = 12500000.00, contract_id = 948120.