SQLtest

📚 leçons & ✍️ pratique

Explication de la Carte des sièges d'avion

Objectif principal de la tâche

L'objectif est de créer une carte visuelle des sièges pour un modèle d'avion spécifique (ici, le 'Boeing 777-300'). Le résultat doit être une liste de rangées, où pour chaque rangée et classe de service ('Business', 'Économie', 'Confort'), un tableau des sièges disponibles est affiché.

Par exemple, pour la 1ère rangée en classe business :

• Rangée : 1
• Sièges : ["A", "C", "D", "F"]
• Classe : Business

Logique de la solution (étape par étape)

1. Étape 1 : Sélection du bon avion.

   • Filtrez les données pour ne travailler qu'avec les sièges du modèle 'Boeing 777-300'.
   • Pour cela, faites une jointure entre la table seats et aircrafts_data sur le champ commun aircraft_code.
   • Ajoutez dans la clause WHERE le modèle d'avion souhaité.

2. Étape 2 : Séparer le numéro de siège en rangée et lettre.

   • Le numéro de siège dans la table seats est du type 12A, 30K, où 12 est la rangée et A la lettre du siège.
   • La rangée s'obtient en retirant la partie lettre du champ seat_no (par exemple avec la fonction rtrim ou des expressions régulières).
   • La lettre du siège s'obtient en prenant le dernier caractère de seat_no (par exemple avec la fonction right).

3. Étape 3 : Regrouper les sièges.

   • L'idée est de regrouper tous les sièges qui sont dans la même rangée et la même classe de service (fare_conditions).
   • La clause GROUP BY doit donc inclure la rangée calculée et la classe de service.

4. Étape 4 : Agréger les lettres de sièges dans un tableau.

   • Pour chaque groupe (ex : "rangée 10, classe économie"), il faut rassembler toutes les lettres de sièges (A, B, C, etc.) dans une liste ou un tableau.
   • La fonction d'agrégation json_agg (PostgreSQL) est idéale, elle crée un tableau JSON à partir des valeurs.
   • Pour garantir l'ordre des sièges (A, B, C, pas C, A, B), il faut ajouter un tri dans la fonction json_agg.

5. Étape 5 : Tri final.

   • Pour que la carte soit logique (rangées de l'avant vers l'arrière), le résultat doit être trié par numéro de rangée.

        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->>'fr' = 'Boeing 777-300'
        group by rtrim(seat_no, 'ABCDEFGHKJ'), fare_conditions
        order by "row";