Utilisation du Pushdown de code dans ABAP SQL

Tri et compression des ensembles de données dans ABAP SQL

Objectives

After completing this lesson, you will be able to:
  • Demandez des ensembles de résultats triés à partir de la base de données.
  • Récupérez des ensembles de données agrégées et condensées.

Ensembles de résultats triés

Dans cette vidéo, vous allez apprendre à trier l'ensemble de résultats d'une instruction SELECT.

Essayer : ORDER BY

  1. Créez une nouvelle classe globale qui implémente l'interface IF_OO_ADT_CLASSRUN.
  2. Copiez l'extrait de code suivant dans la partie implémentation de la méthode if_oo_adt_classrun~main( ) :
    Code Snippet
    123456789101112131415161718
    SELECT FROM /dmo/flight FIELDS carrier_id, connection_id, flight_date, seats_max - seats_occupied AS seats WHERE carrier_id = 'AA' AND plane_type_id = 'A320-200' * ORDER BY seats_max - seats_occupied DESCENDING, * flight_date ASCENDING INTO TABLE @DATA(result). out->write( EXPORTING data = result name = 'RESULT' ).
  3. Sélectionnez CTRL + F3 pour activer la classe et F9 pour l'exécuter en tant qu'application de console.
  4. Analysez la sortie de la console.
  5. Supprimez les astérisques (*) dans les lignes avec l'option ORDER BY et activez-les à nouveau.
  6. Exécutez à nouveau et voyez comment l'ordre de tri de la sortie de console a changé.
  7. Jouez autour avec l'ajout ORDER BY. Modifiez le sens du tri, triez le résultat en fonction d'autres champs ou expressions pour vous familiariser avec la syntaxe.

Ensembles de résultats uniques

L'option DISTINCT vous permet de supprimer les doublons de l'ensemble de résultats d'une instruction SELECT. L'option doit être placée juste avant le premier élément de la liste de zones.

Regardez cette vidéo pour savoir comment faire.

Essayer: SELECT DISTINCT

  1. Créez une nouvelle classe globale qui implémente l'interface IF_OO_ADT_CLASSRUN.
  2. Copiez l'extrait de code suivant dans la partie implémentation de la méthode if_oo_adt_classrun~main( ) :
    Code Snippet
    12345678910111213141516
    SELECT FROM /dmo/connection FIELDS * DISTINCT airport_from_id, distance_unit ORDER BY airport_from_id INTO TABLE @DATA(result). out->write( EXPORTING data = result name = 'RESULT' ).
  3. Sélectionnez CTRL + F3 pour activer la classe et F9 pour l'exécuter en tant qu'application de console.
  4. Analysez la sortie de la console.
  5. Supprimez les astérisques (*) dans la ligne avec l'option DISTINCT et activez-les à nouveau.
  6. Exécutez à nouveau et voyez comment la sortie de la console a changé.

Fonctions d'agrégat

Une expression d'agrégat calcule une valeur unique à partir d'une expression_sql (sql_exp) en appelant une fonction d'agrégat pour plusieurs lignes d'un ensemble de résultats.

Voici quelques fonctions d'agrégat importantes :

  • SUM( sql_exp )

    Détermine la somme des résultats de l'expression SQL sql_exp dans un ensemble de lignes. Le type de données de l'ensemble de résultats est le type de l'expression SQL. Le type de résultat de l'expression SQL sql_exp doit être numérique.

  • MIN( sql_exp ) et MAX( sql_exp )

    Détermine la valeur maximale ou minimale des résultats de l'expression SQL sql_exp dans un jeu de lignes. Le type de données du résultat est le type de résultat de l'expression SQL.

    Le résultat de l'expression SQL sql_exp doit être de type numérique. Si une seule colonne est spécifiée pour l'expression, cette colonne peut également avoir n'importe quel type de données.

  • AVG( sql_exp [ AS dtype ] )

    Détermine la valeur moyenne du contenu d'une expression SQL sql_exp dans un ensemble de lignes. Le type de données implicite du résultat pour les nombres décimaux en virgule flottante est le type de données correspondant (DECFLOAT16 ou DECFLOAT34 ou DF16_DEC ou DF34_DEC) et est sinon le type FLTP ou est déterminé par l'option AS dtype.

  • COUNT( * ) ou COUNT(*)

    Les deux orthographes ont la même signification et produisent le nombre de lignes dans un ensemble de lignes. Dans la plupart des cas, le type de résultat est INT4. Le type de résultat est INT8 si COUNT( * ) ou COUNT(*) est le seul élément de la liste de zones.

  • COUNT( DISTINCT sql_exp )

    Détermine le nombre de valeurs distinctes des résultats de l'expression SQL sql_exp dans un ensemble de lignes. Le type de données du résultat est INT4.

Uniquement les fonctions d'agrégation dans la liste de zones

Si la liste de zones d'une instruction SELECT ne contient rien d'autre que des fonctions d'agrégation, l'ensemble de résultats se compose toujours d'une seule ligne. Notez que c'est également le cas si l'agrégation est basée sur un ensemble de données vide.

Dans notre exemple, l'instruction SELECT à gauche illustre l'ensemble de données sur lequel sont basées les agrégations dans SELECT à droite.

Vous pouvez voir que le maximum dans la colonne DISTANCE est 6217 alors que le minimum est 6162. Le nombre total de lignes est de 4 et la colonne AIRPORT_FROM_ID contient 3 valeurs distinctes (la valeur "FRA" apparaît deux fois).

Essayer : fonctions d'agrégation

  1. Créez une nouvelle classe globale qui implémente l'interface IF_OO_ADT_CLASSRUN.
  2. Copiez l'extrait de code suivant dans la partie implémentation de la méthode if_oo_adt_classrun~main( ) :
    Code Snippet
    1234567891011121314151617181920212223242526272829303132333435
    SELECT FROM /dmo/connection FIELDS carrier_id, connection_id, airport_from_id, distance WHERE carrier_id = 'LH' INTO TABLE @DATA(result_raw). out->write( EXPORTING data = result_raw name = 'RESULT_RAW' ). ********************************************************************* SELECT FROM /dmo/connection FIELDS MAX( distance ) AS max, MIN( distance ) AS min, SUM( distance ) AS sum, AVG( distance ) AS average, COUNT( * ) AS count, COUNT( DISTINCT airport_from_id ) AS count_dist WHERE carrier_id = 'LH' INTO TABLE @DATA(result_aggregate). out->write( EXPORTING data = result_aggregate name = 'RESULT_AGGREGATED' ).
  3. Sélectionnez CTRL + F3 pour activer la classe et F9 pour l'exécuter en tant qu'application de console.
  4. Analysez la sortie de la console.

L'option GROUP BY

Si la liste de champs d'une instruction SELECT contient un mélange de fonctions d'agrégat et d'autres éléments, par exemple des colonnes, des expressions, etc., la syntaxe SQL requiert une clause GROUP BY. La clause GROUP BY doit répertorier toutes les colonnes qui apparaissent dans la liste de champs en dehors des agrégations.

Dans l'instruction SELECT à droite, deux colonnes sont utilisées dans la liste de zones : CARRIER_ID et DISTANCE. La colonne DISTANCE apparaît uniquement comme entrée pour les fonctions d'agrégation et n'est donc pas requise après GROUP BY. Cependant, la colonne CARRIER_ID est utilisée en dehors des fonctions d'agrégat et doit donc être répertoriée après GROUP BY.

L'effet de GROUP BY est visible dans le résultat de l'instruction SELECT. L'ensemble de données sur lequel l'agrégation est effectuée est séparé en sous-ensembles (groupes) ayant les mêmes valeurs dans les zones GROUP BY. L'ensemble de résultats final contient une ligne pour chaque groupe. Les expressions d'agrégat sont évaluées séparément pour chaque groupe.

Par exemple, la ligne avec CARRIER_ID = 'AA' contient COUNT = 5 et MAX = 4156 car le sous-ensemble avec CARRIER_ID = 'AA' contient 5 lignes et dans ce sous-ensemble, la valeur maximale pour la distance est 4156.

Essayer : GROUP BY

  1. Créez une nouvelle classe globale qui implémente l'interface IF_OO_ADT_CLASSRUN.
  2. Copiez l'extrait de code suivant dans la partie implémentation de la méthode if_oo_adt_classrun~main( ) :
    Code Snippet
    123456789101112131415161718
    SELECT FROM /dmo/connection FIELDS * carrier_id, MAX( distance ) AS max, MIN( distance ) AS min, SUM( distance ) AS sum, COUNT( * ) AS count * GROUP BY carrier_id INTO TABLE @DATA(result). out->write( EXPORTING data = result name = 'RESULT' ).
  3. Sélectionnez CTRL + F3 pour activer la classe et F9 pour l'exécuter en tant qu'application de console.
  4. Analysez la sortie de la console.
  5. Supprimez l'astérisque (*) dans la ligne après la ligne avec le mot-clé FIELDS (celui avec le nom de zone carrier_id). Notez l'erreur de syntaxe qui vous indique que la zone CARRIER_ID est manquante dans la clause GROUP BY.
  6. Supprimez l'astérisque (*) de la ligne avec l'option GROUP BY et activez-la à nouveau.
  7. Exécutez à nouveau et voyez comment la sortie de la console a changé.

Récupérer les données triées et agrégées

Dans cet exercice, vous triez les vols par date et calculez l'occupation moyenne des vols dans la base de données.

Modèle :

  • /LRN/CL_S4D401_DBS_FUNCTIONS (classe globale)

Solution :

  • /LRN/CL_S4D401_DBS_AGGREGATE (classe globale)

Tâche 1: Copier modèle (facultatif)

Copiez la classe de modèle /LRN/CL_S4D401_DBS_FUNCTIONS. Si vous avez terminé l'exercice précédent, vous pouvez ignorer cette tâche et continuer à modifier votre classe ZCL_##_SOLUTION.

Étapes

  1. Copiez la classe /LRN/CL_S4D401_DBS_FUNCTIONS dans une classe de votre propre package (nom proposé : ZCL_##_SOLUTION, ## correspondant à votre numéro de groupe).

    1. Dans l'explorateur de projet, cliquez avec le bouton droit de la souris sur la classe /LRN/CL_S4D401_DBS_FUNCTIONS pour ouvrir le menu contextuel.

    2. Dans le menu contextuel, sélectionnez Dupliquer....

    3. Saisissez le nom de votre package dans la zone Package. Dans la zone Nom, saisissez ZCL_##_SOLUTION, où ## représente votre numéro de groupe.

    4. Cliquez sur Next.

    5. Confirmez l'ordre de transport et cliquez sur Terminer.

  2. Activez la copie.

    1. Appuyez sur Ctrl + F3 pour activer la classe.

Tâche 2: Trier les données

Dans la classe locale LCL_PASSENGER_FLIGHT, ajustez l'implémentation de la méthode GET_FLIGHTS_BY_CARRIER. Ajoutez une directive appropriée pour récupérer la liste des vols classés par date de vol dans l'ordre croissant. Procédez de même dans la classe locale LCL_CARGO_FLIGHT.

Étapes

  1. Dans la classe locale LCL_PASSENGER_FLIGHT, naviguez jusqu'à l'implémentation de la méthode GET_FLIGHTS_BY_CARRIER.

    1. Pour cette étape, procédez comme vous l'avez fait précédemment.

  2. Ajustez l'instruction SELECT pour vous assurer que la base de données renvoie le résultat trié par date de vol dans l'ordre croissant.

    1. Ajoutez une clause ORDER BY avant la clause INTO.

    2. Adaptez le code comme suit :

      ABAP
      1234567891011121314151617
      SELECT FROM /lrn/passflight FIELDS carrier_id, connection_id, flight_date, plane_type_id, seats_max, seats_occupied, seats_max - seats_occupied AS seats_free, currency_conversion( amount = price, source_currency = currency_code, target_currency = @currency, exchange_rate_date = flight_date, on_error = @sql_currency_conversion=>c_on_error-set_to_null ) AS price, @currency AS currency_code WHERE carrier_id = @i_carrier_id ORDER BY flight_date ASCENDING INTO TABLE @flights_buffer.
  3. Naviguez vers la méthode GET_FLIGHTS_BY_CARRIER dans la classe locale LCL_CARGO_FLIGHT.

    1. Pour cette étape, procédez comme vous l'avez fait précédemment.

  4. Ajustez l'instruction SELECT pour vous assurer que la base de données renvoie le résultat trié par date de vol dans l'ordre croissant.

    1. Ajoutez une clause ORDER BY avant la clause INTO.

    2. Adaptez le code comme suit :

      ABAP
      12345678
      SELECT FROM /lrn/cargoflight FIELDS carrier_id, connection_id, flight_date, plane_type_id, maximum_load, actual_load, load_unit, airport_from_id, airport_to_id, departure_time, arrival_time WHERE carrier_id = @i_carrier_id ORDER BY flight_date ASCENDING INTO CORRESPONDING FIELDS OF TABLE @flights_buffer.

Tâche 3: Utiliser la fonction Agréger

Dans la classe locale LCL_CARRIER, ajustez l'implémentation de la méthode GET_AVERAGE_FREE_SEATS. Au lieu de calculer le résultat dans une boucle sur tous les vols passagers, effectuez le calcul avec un SELECT de la table de base de données /LRN/PASSFLIGHT.

Étapes

  1. Dans la classe locale LCL_CARRIER, naviguez vers l'implémentation de la méthode GET_AVERAGE_FREE_SEATS.

    1. Pour cette étape, procédez comme vous l'avez fait précédemment.

  2. Supprimez ou commentez le code source de l'implémentation de méthode.

    1. Marquez le code entre les instructions METHOD get_average_free_seats. et ENDMETHOD. et appuyez sur Ctrl + <.

  3. Implémentez maintenant une instruction SELECT qui récupère la somme des sièges libres et le nombre de vols passagers pour la compagnie aérienne actuelle, qui est stockée dans l'attribut carrier_id. Stockez le résultat dans un objet de données déclaré en ligne (nom suggéré : agrégats).

    1. Ajoutez le code suivant :

      ABAP
      12345
      SELECT FROM /lrn/passflight FIELDS SUM( seats_max - seats_occupied ) AS sum, COUNT(*) AS count WHERE carrier_id = @carrier_id INTO @DATA(aggregates).
  4. Après l'instruction SELECT, calculez le ratio des deux agrégats et affectez le résultat au paramètre de retour r_result.

    1. Adaptez le code comme suit :

      ABAP
      1234567
      SELECT FROM /lrn/passflight FIELDS SUM( seats_max - seats_occupied ) AS sum, COUNT(*) AS count WHERE carrier_id = @carrier_id INTO @DATA(aggregates). r_result = aggregates-sum / aggregates-count.

      Remarque

      Une implémentation encore plus concise de la méthode GET_AVERAGE_FREE_SEATS peut être réalisée via le code suivant :
      ABAP
      1234
      SELECT FROM /lrn/passflight FIELDS CAST( AVG( seats_max - seats_occupied ) AS INT4 ) WHERE carrier_id = @carrier_id INTO @r_result.
  5. Activez et testez votre classe globale comme application de console.

    1. Appuyez sur Ctrl + F3.

    2. Appuyez sur F9.