Блог

Домен представляет собой систему бронирования авиабилетов. Пользователи могут забронировать рейс. Полет состоит из одной или нескольких ног.

   ┌─────┐        ┌──────┐      ┌───────┐
  │ Leg ├────────┤Flight├──────┤Booking│
  └─────┘  *   * └──────┘1   * └───────┘
 

Чтобы показать пользователю список действительных рейсов, система должна выяснить, есть ли на рейсе свободные места. Максимальное количество доступных мест сохраняется в виде поля в Leg таблице. Забронированные места хранятся в Booking .

Полеты могут состоять из нескольких этапов. Например, может быть 3 ноги A -> B ->> C. И два рейса, которые должны быть забронированы пользователями:

1. A -> B: Перелет из пункта А в пункт Б.
2. A -> B ->> C: Перелет из A в C через B.

Я создал dbfiddle с попыткой группировки по ногам, которая дает мне количество забронированных мест на одну ногу. Но мне нужно сгенерировать список рейсов с наибольшим количеством забронированных пассажиров на один рейс. В приведенном выше примере: На рейсе 1 может быть меньше свободных мест, если пассажиры забронированы на рейс 2, потому что они оба «разделяют» первый этап.

 select l.id as Leg, sum(b.number_passengers) as BookedSeats
FROM flight f
     JOIN flight_legs fl on f.id = fl.flight_id
     JOIN leg l on fl.legs_id = l.id
     JOIN booking b on f.id = b.flight_id
GROUP BY l.id;
 

Как я могу создать список рейсов с доступными местами?

Пример

Схема

Нога

Полет

Flight_Leg

Примечание: У рейса 1 две ноги, а у рейса 2 только одна нога. Оба рейса «разделяют» один и тот же первый этап.

Бронирование

Примечание: Один человек забронировал рейс 1 и летит обеими ногами. Другой человек забронировал рейс 2 только с первым этапом. Это означает, что два пассажира на первом этапе и один пассажир на оставшемся этапе.

Ожидаемый результат

Найдите все рейсы с доступными местами. Места могут быть заняты и другими рейсами.

Примечание: На рейс 2 нет свободных мест, так как первый рейс полностью забронирован

Диаграмма

Следующий запрос реализует желаемый результат. Я использовал эту Lag функцию, чтобы перейти к предыдущей строке, если предыдущая часть была заполнена.

 SELECT Leg,
   case
     when Free_Seats = 0 then 0 
        else (case when PrevLeg = 0 then 0 else Free_Seats end) end as Free_Seats

FROM (SELECT Leg,Free_Seats,LAG(Free_Seats,1) OVER(ORDER BY Leg) PrevLeg
  FROM (SELECT l.id AS Leg,l.seats_available,b.number_passengers,fl.flight_id,
             (max(l.seats_available) OVER(PARTITION BY fl.flight_id) - sum(b.number_passengers) OVER(PARTITION BY fl.flight_id)) AS Free_Seats,
             ROW_NUMBER() OVER(PARTITION BY fl.flight_id ORDER BY fl.flight_id) AS seq
         FROM flight f
          JOIN flight_legs fl ON f.id = fl.flight_id
          JOIN leg l ON fl.legs_id = l.id
          JOIN booking b ON f.id = b.flight_id) T
 WHERE t.seq=1) T