Taux d'occupation des vols par classe en SQL

Leçon 12.2 · Temps de lecture : ~7 min · Exercices pratiques ↓

Cette leçon détaille la solution de l'exercice SQL Taux d'occupation des vols par classe sur la base de données Bookings (PostgreSQL). Vous apprendrez à compter tous les sièges et les sièges occupés dans une seule requête, à séparer les comptages par classe avec FILTER, et pourquoi la réponse nécessite deux niveaux d'agrégation : d'abord par vol, puis par aéroport.

L'exercice : pour chaque aéroport de départ, calculer l'occupation moyenne de la classe affaires et de la classe économique sur les vols réellement partis en août 2017. L'occupation d'un vol est le pourcentage de sièges occupés parmi tous les sièges de cette classe. Le résultat a trois colonnes : departure_airport, average_business_occupancy et average_economy_occupancy, en pourcentage arrondi à deux décimales.

Essayez d'abord de résoudre l'exercice vous-même ; la requête complète est cachée à la fin de la leçon.

Quelles tables utiliser

flights : le vol, son departure_airport, actual_departure et l'avion (aircraft_code).
seats : tous les sièges de chaque modèle d'avion avec leur classe (fare_conditions).
boarding_passes : les sièges réellement occupés sur un vol (flight_id, seat_no).

Le plan : pour chaque vol, lister tous les sièges de son avion et marquer ceux qui ont une carte d'embarquement.

Solution pas à pas

1. Tous les sièges et les sièges occupés de chaque vol

Joignez chaque vol aux sièges de son avion, puis rattachez les cartes d'embarquement avec un LEFT JOIN, pour que les sièges vides restent dans le résultat :

SELECT
    flights.flight_id,
    flights.departure_airport,
    COUNT(boarding_passes.seat_no) FILTER (WHERE seats.fare_conditions = 'Business') AS business_occupied,
    COUNT(seats.seat_no) FILTER (WHERE seats.fare_conditions = 'Business') AS business_seats,
    COUNT(boarding_passes.seat_no) FILTER (WHERE seats.fare_conditions = 'Economy') AS economy_occupied,
    COUNT(seats.seat_no) FILTER (WHERE seats.fare_conditions = 'Economy') AS economy_seats
FROM flights
JOIN seats USING (aircraft_code)
LEFT JOIN boarding_passes
    ON boarding_passes.seat_no = seats.seat_no
   AND boarding_passes.flight_id = flights.flight_id
WHERE flights.actual_departure BETWEEN '2017-08-01' AND '2017-09-01'
GROUP BY flights.flight_id, flights.departure_airport
ORDER BY flights.flight_id
LIMIT 3;

Résultat : par exemple, le vol 2 au départ de DME a 14 sièges affaires occupés sur 28 et 87 sièges économiques sur 142.

COUNT(seats.seat_no) compte tous les sièges ; COUNT(boarding_passes.seat_no) ne compte que les sièges avec une carte d'embarquement, car pour les sièges vides le LEFT JOIN donne NULL.
Avec un JOIN interne sur boarding_passes, les sièges vides disparaîtraient et chaque vol paraîtrait plein à 100 %.
La carte d'embarquement doit correspondre au siège et au vol : le même numéro de siège existe sur tous les vols de cet avion.
COUNT(...) FILTER (WHERE ...) ne compte que les lignes d'une classe. Dans d'autres bases, on écrit la même chose COUNT(CASE WHEN ... THEN 1 END) (leçon 4.4).

2. Occupation de chaque vol

Divisez les sièges occupés par tous les sièges. Attention aux types : COUNT renvoie un entier, et dans PostgreSQL un entier divisé par un entier donne un entier :

SELECT 3 / 12 AS integer_division, 3::numeric / 12 AS numeric_division;

Résultat : integer_division vaut 0, numeric_division vaut 0.25.

C'est pourquoi la solution convertit le nombre de sièges en numeric. Certains avions n'ont pas du tout de classe affaires, par exemple le Bombardier CRJ-200 et le Cessna 208 Caravan. Pour eux, le nombre de sièges affaires vaut 0 et la division doit être évitée : CASE WHEN business_seats > 0 THEN ... END renvoie NULL, que AVG ignore.

3. Moyenne par aéroport

Placez le calcul par vol dans une CTE et agrégez-le à nouveau, cette fois par departure_airport :

AVG(business_occupied / business_seats) * 100 donne le pourcentage moyen d'occupation ;
::numeric(5, 2) l'arrondit à deux décimales.

Résultat : 104 aéroports, à commencer par AAQ (82.83 % en affaires, 81.54 % en économique).

Moyenne des pourcentages ou pourcentage global

L'exercice demande la moyenne des taux d'occupation par vol : chaque vol a le même poids, un petit avion presque vide compte autant qu'un gros avion plein. L'autre métrique possible est l'occupation globale : tous les sièges occupés de l'aéroport divisés par tous les sièges, SUM(occupied) / SUM(seats), où les gros avions pèsent davantage. Les deux sont valables, mais donnent des chiffres différents : lisez donc attentivement l'énoncé. Ici, l'agrégation à deux niveaux est nécessaire précisément parce que la moyenne porte sur les vols.

Spoiler : la requête SQL avec la solution est cachée ci-dessous. Cliquez pour l'afficher.


with occupancy as (
    select
        flights.flight_id, departure_airport,
        count(boarding_passes.seat_no) filter (where seats.fare_conditions = 'Business') business_occupancy,
        (count(seats.seat_no) filter (where seats.fare_conditions = 'Business'))::numeric business_seats,
        count(boarding_passes.seat_no) filter (where seats.fare_conditions = 'Economy') economy_occupancy,
        (count(seats.seat_no) filter (where seats.fare_conditions = 'Economy'))::numeric economy_seats
    from flights
    join seats using (aircraft_code)
    left join boarding_passes on
        boarding_passes.seat_no = seats.seat_no and
        boarding_passes.flight_id = flights.flight_id
    where flights.actual_departure between '2017-08-01' and '2017-09-01'
    group by flights.flight_id, departure_airport
)
select
    departure_airport,
    (avg(case when business_seats > 0 then business_occupancy / business_seats end) * 100)::numeric(5, 2) average_business_occupancy,
    (avg(case when economy_seats > 0 then economy_occupancy / economy_seats end) * 100)::numeric(5, 2) average_economy_occupancy
from occupancy
group by departure_airport
order by departure_airport;

Points clés :

Comptez tous les sièges à partir de seats et les sièges occupés à partir de boarding_passes rattachée par un LEFT JOIN ; une jointure interne masquerait les sièges vides.
COUNT(column) ignore les NULL et ne compte donc que les lignes trouvées ; FILTER (WHERE ...) sépare les comptages par catégorie.
La division entière dans PostgreSQL supprime la partie décimale : convertissez en numeric avant de diviser.
Ignorez les classes absentes d'un avion avec CASE WHEN seats > 0 ; AVG ignore les NULL.
Une moyenne de taux par vol nécessite deux niveaux d'agrégation : une CTE par vol, puis un GROUP BY par aéroport.

Questions fréquentes

Pourquoi mon calcul de pourcentage renvoie-t-il 0 en SQL ?

Parce que les deux opérandes sont des entiers, et PostgreSQL, SQL Server et SQLite effectuent une division entière : 3 / 12 = 0. Convertissez un opérande en numeric ou decimal, ou multipliez d'abord par 100.0. MySQL renvoie un résultat décimal pour / et n'a pas ce problème.

Que fait COUNT(...) FILTER (WHERE ...) ?

Il ne compte que les lignes qui satisfont la condition, ce qui permet de faire plusieurs comptages avec des conditions différentes dans un même GROUP BY. FILTER est pris en charge par PostgreSQL et SQLite ; dans MySQL et SQL Server, utilisez COUNT(CASE WHEN condition THEN 1 END) ou SUM(CASE WHEN condition THEN 1 ELSE 0 END).

Quand faut-il deux niveaux d'agrégation ?

Quand la métrique est calculée par objet puis moyennée par groupe : occupation par vol, puis moyenne par aéroport. Un seul GROUP BY ne peut pas faire les deux, donc le premier niveau va dans une CTE ou une sous-requête.