Схема мест в самолёте на SQL с json_agg

Урок 12.3 · Время чтения: ~5 мин · Задачи для практики ↓

В этом уроке разобрано решение SQL-задачи Схема мест в самолёте на базе данных Bookings (PostgreSQL). Вы увидите, как разделить номер места вроде 12A на ряд и букву с помощью строковых функций, как сгруппировать места по ряду и классу и как собрать буквы каждого ряда в отсортированный JSON-массив с помощью json_agg.

Задача: построить схему мест Boeing 777-300. Каждая строка результата описывает один ряд кресел в салоне: номер ряда (row), буквы мест этого ряда в виде JSON-массива, отсортированного по алфавиту (seats), и класс обслуживания (fare_conditions). Например, первый ряд бизнес-класса - это 1, ["A", "C", "D", "G", "H", "K"], Business.

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

Где лежат данные

• seats хранит все места каждой модели самолёта: aircraft_code, seat_no (например, 12A) и fare_conditions.
• aircrafts_data хранит модели; название лежит в JSON-столбце model на нескольких языках: { "en": "Boeing 777-300", "ru": "Боинг 777-300" }. Оператор ->> извлекает значение как текст: model->>'en'.

Отдельного столбца с рядом нет: и ряд, и буква находятся внутри seat_no.

Решение по шагам

1. Разделение номера места

SELECT seat_no, rtrim(seat_no, 'ABCDEFGHKJ') AS row_text, right(seat_no, 1) AS letter
FROM seats
WHERE aircraft_code = '773' AND seat_no IN ('1A', '11D');

Результат: из 1A получаются ряд 1 и буква A; из 11D - ряд 11 и буква D.

• rtrim(seat_no, 'ABCDEFGHKJ') удаляет любые из перечисленных символов с конца строки и оставляет цифры. В списке есть все буквы, которые встречаются в базе; буква I в схемах мест самолётов не используется, потому что её легко спутать с 1.
• right(seat_no, 1) берёт последний символ - букву места.

Более общий вариант - регулярное выражение: (regexp_match(seat_no, '^(\d+)([A-Z])$'))[1] возвращает цифры при любых буквах.

2. Группировка по ряду и классу

Одна строка результата - один ряд салона одного класса, поэтому группируйте по извлечённому номеру ряда и fare_conditions. Группировка по выражению работает так же, как по столбцу; то же выражение затем используется в SELECT.

3. Сбор букв в JSON-массив

json_agg(value) - агрегатная функция: как COUNT или SUM, она обрабатывает все строки группы, но возвращает их одним JSON-массивом. Порядок элементов внутри задаётся с помощью ORDER BY внутри вызова функции:

json_agg(right(seat_no, 1) ORDER BY right(seat_no, 1))

Без ORDER BY внутри буквы придут в том порядке, в каком были прочитаны строки, например ["C", "A", "D"].

4. Сортировка рядов как чисел

rtrim() возвращает текст, а текст сортируется посимвольно: 1, 11, 12, … 19, 2, 20. Чтобы сортировать ряды как числа, приведите ряд к целому: rtrim(seat_no, 'ABCDEFGHKJ')::int.

Результат: 46 строк. Бизнес-класс занимает ряды 1–5 (по 6 мест), комфорт - ряды 11–16, эконом - ряды 17–51. Рядов 6–10 нет, а в последнем, 51-м ряду всего 4 места: ["D", "E", "F", "G"].

Похожие функции в других СУБД

select
    rtrim(seat_no, 'ABCDEFGHKJ')::int "row",
    json_agg(right(seat_no, 1) order by right(seat_no, 1)) seats,
    fare_conditions
from seats
join aircrafts_data using (aircraft_code)
where aircrafts_data.model->>'en' = 'Boeing 777-300'
group by rtrim(seat_no, 'ABCDEFGHKJ'), fare_conditions
order by "row";
    

Ключевые выводы:

• Если в одной строке хранятся два значения, разделите их строковыми функциями: rtrim() для цифр, right() для последней буквы или regexp_match() для любого формата.
• GROUP BY можно выполнять по выражению, например по извлечённому номеру ряда.
• json_agg() собирает значения группы в JSON-массив; ORDER BY внутри вызова задаёт их порядок.
• Числа, извлечённые из текста, нужно привести к int, чтобы они правильно сортировались.
• row - ключевое слово в PostgreSQL, поэтому как имя столбца его пишут в двойных кавычках: "row".

Часто задаваемые вопросы

Как собрать значения в массив в SQL?

В PostgreSQL используйте array_agg() для массива или json_agg() для JSON-массива, с ORDER BY внутри вызова для сортировки элементов. В MySQL - JSON_ARRAYAGG() или GROUP_CONCAT(), в SQL Server - STRING_AGG().

Как разделить строку на числовую и буквенную части в SQL?

Если буквы стоят в конце, rtrim(value, 'ABC…') возвращает числовую часть, а right(value, 1) - последнюю букву. Для любого формата используйте регулярное выражение, например regexp_match(value, '^(\d+)([A-Z])$') в PostgreSQL или REGEXP_SUBSTR() в MySQL.

Почему числа сортируются как 1, 10, 11, 2?

Потому что они хранятся или вычисляются как текст, а текст сравнивается посимвольно. Приведите их к числу перед сортировкой: ORDER BY value::int в PostgreSQL или ORDER BY CAST(value AS UNSIGNED) в MySQL.