SQLtest уроки SQL

Объяснение задачи "Карта мест в самолете"

Основная цель задачи

Задача заключается в том, чтобы создать визуальную карту мест для конкретной модели самолета (в данном случае, 'Boeing 777-300'). Результат должен представлять собой список рядов, где для каждого ряда и класса обслуживания ('Business', 'Economy', 'Comfort') показан массив доступных мест.

Например, для 1-го ряда бизнес-класса это может выглядеть так:

• Ряд: 1
• Места: ["A", "C", "D", "F"]
• Класс: Business

Логика решения (по шагам)

1. Шаг 1: Выбор нужного самолета.

   • Прежде всего, необходимо отфильтровать данные, чтобы работать только с местами, относящимися к модели 'Boeing 777-300'.
   • Для этого нужно соединить таблицу seats (места) с таблицей aircrafts_data (данные о самолетах) по общему полю aircraft_code.
   • Затем в условии WHERE указать нужную модель самолета.

2. Шаг 2: Разделение номера места на ряд и букву.

   • Номер места в таблице seats имеет формат типа 12A, 30K, где 12 — это номер ряда, а A — буква места в ряду.
   • Номер ряда можно получить, удалив буквенную часть из строки seat_no. Например, с помощью функции rtrim или регулярных выражений.
   • Букву места можно получить, взяв последний символ из строки seat_no (например, функцией right).

3. Шаг 3: Группировка мест.

   • Основная идея — сгруппировать все места, которые находятся в одном ряду и относятся к одному классу обслуживания (fare_conditions).
   • Таким образом, GROUP BY должен включать и вычисленный номер ряда, и класс обслуживания.

4. Шаг 4: Агрегация букв мест в массив.

   • Для каждой группы (например, "ряд 10, эконом-класс") нам нужно собрать все буквы мест (A, B, C и т.д.) в единый список или массив.
   • Для этого идеально подходит агрегатная функция json_agg (в PostgreSQL), которая создает JSON-массив из значений.
   • Чтобы места в массиве шли в правильном порядке (A, B, C, а не C, A, B), внутри json_agg необходимо добавить сортировку по букве места.

5. Шаг 5: Финальная сортировка.

   • Чтобы карта мест выглядела логично (ряды шли от начала к концу самолета), итоговый результат нужно отсортировать по номеру ряда.

        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";