Échantillonnage À Partir D'une Table Dérivée (Derived Table)

COMMENTSInstructions de procédure SQL (PSQL)

Échantillonnage à partir d’une table dérivée (derived table)

Table dérivé — est la commande SELECT correcte, entre parenthèses, éventuellement marquée d’un alias de table et d’alias de champ.

Syntaxe

<derived table> ::=
  (<select-query>)
  [[AS] derived-table-alias]
  [(<derived-column-aliases>)]

<derived-column-aliases> := column-alias [, column-alias ...]

<lateral-derived-table> ::= LATERAL <derived-table>

L’ensemble de données retourné par une telle déclaration est une table virtuelle dans laquelle on peut effectuer des requêtes comme s’il s’agissait d’une table ordinaire.

La table dérivée dans la requête ci-dessous fournit une liste de noms de tables dans la base de données et le nombre de colonnes qu’elles contiennent. La requête de table dérivée fournit le nombre de champs et le nombre de tables avec ce nombre de champs.

SELECT
  FIELDCOUNT,
  COUNT(RELATION) AS NUM_TABLES
FROM (SELECT
        R.RDB$RELATION_NAME RELATION,
        COUNT(*) AS FIELDCOUNT
      FROM RDB$RELATIONS R
        JOIN RDB$RELATION_FIELDS RF
          ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
      GROUP BY RELATION)
GROUP BY FIELDCOUNT

Un exemple trivial démontrant l’utilisation d’un alias de table dérivé et d’une liste d’alias de colonne (tous deux facultatifs) :

SELECT
  DBINFO.DESCR, DBINFO.DEF_CHARSET
FROM (SELECT *
      FROM RDB$DATABASE) DBINFO (DESCR, REL_ID, SEC_CLASS, DEF_CHARSET)

Note

Notes:

Les tables dérivées peuvent être imbriquées ;
Les tables dérivées peuvent être jointes et utilisées dans les jointures. Ils peuvent contenir des fonctions d’agrégation, des sous-requêtes et des jointures, et peuvent eux-mêmes être utilisés dans des fonctions d’agrégation, des sous-requêtes et des jointures. Il peut également s’agir de procédures stockées ou de requêtes à partir de celles-ci. Ils peuvent comporter des clauses du type "OR", "ORDRE BY" et "GROUPE BY", des instructions du type "FIRST", "SKIPE" , etc ;
Chaque colonne d’une table dérivée doit avoir un nom. Si ce n’est pas le cas par nature (par exemple parce qu’il s’agit d’une constante), vous devez alors aliaser ou ajouter une liste d’alias de colonnes à la spécification de la table dérivée de la manière habituelle ;
La liste des alias de colonnes est facultative, mais si elle est présente, elle doit être complète (c’est-à-dire qu’elle doit contenir un alias pour chaque colonne de la table dérivée) ;
L’optimiseur peut gérer les tables dérivées très efficacement. Toutefois, si la table dérivée est incluse dans une jointure interne et contient une sous-requête, aucun ordre de jointure ne peut être utilisé par l’optimiseur ;
Le mot-clé LATERAL permet à une table dérivée de faire référence à des champs de tables précédemment listées dans la liste de référence courante <table.Voir Linking with LATERAL derived tables pour plus de détails.

Voici un exemple de la manière dont l’utilisation de tableaux dérivés peut simplifier la solution d’un problème.

Supposons que nous ayons un tableau COEFFS contenant les coefficients d’une série d’équations quadratiques que nous allons résoudre. Il peut être défini comme suit :

CREATE TABLE coeffs (
  a DOUBLE PRECISION NOT NULL,
  b DOUBLE PRECISION NOT NULL,
  c DOUBLE PRECISION NOT NULL,
  CONSTRAINT chk_a_not_zero CHECK (a <> 0)
)

En fonction des valeurs des coefficients a, b et c, chaque équation peut avoir zéro, une ou deux solutions. Nous pouvons trouver ces solutions en utilisant une requête à un niveau dans la table COEFFS, mais le code d’une telle requête serait lourd et certaines valeurs (comme les discriminants) seraient calculées plusieurs fois dans chaque ligne.

Si nous utilisons un tableau dérivé, la requête peut être rendue beaucoup plus élégante :

SELECT
  IIF (D >= 0, (-b - sqrt(D)) / denom, NULL) AS sol_1,
  IIF (D > 0, (-b + sqrt(D)) / denom, NULL) AS sol_2
FROM
  (SELECT b, b*b - 4*a*c, 2*a FROM coeffs) (b, D, denom)

Si nous voulons montrer les coefficients à côté des solutions des équations, nous pouvons modifier la requête comme suit :

SELECT
  a, b, c,
  IIF (D >= 0, (-b - sqrt(D)) / denom, NULL) sol_1,
  IIF (D > 0, (-b + sqrt(D)) / denom, NULL) sol_2
FROM
  (SELECT a, b, c, b*b - 4*a*c AS D, 2*a AS denom
   FROM coeffs)

Notez que dans la première requête, nous avons attribué des alias à tous les champs de la table dérivée sous forme de liste après la table, et que dans la seconde, nous ajoutons des alias dans la requête de la table dérivée selon les besoins. Ces deux méthodes sont correctes, car elles garantissent que chaque champ de la table dérivée a un nom unique lorsqu’elles sont appliquées correctement.

Note

En fait, toutes les colonnes calculées dans la table dérivée seront recalculées autant de fois qu’elles sont spécifiées dans la requête principale. Cela peut conduire à des résultats inattendus lors de l’utilisation de fonctions non déterministes. L’exemple suivant montre ce qui a été dit :

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE) T;

le résultat de cette demande sera

C1                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2                              C1214CD3-423C-406D-B5BD-95BF432ED3E3
C3                              EB176C10-F754-4689-8B84-64B666381154

Vous pouvez utiliser la méthode suivante pour matérialiser le résultat de la fonction GEN_UUID :

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE
      UNION ALL
      SELECT NULL FROM RDB$DATABASE WHERE 1=0) T;

le résultat de cette demande sera

C1                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C3                              80AAECED-65CD-4C2F-90AB-5D548C3C7279

ou envelopper la fonction GEN_UUID dans une sous-requête

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT
          (SELECT GEN_UUID() FROM RDB$DATABASE) AS X
      FROM RDB$DATABASE) T;

Il s’agit d’une caractéristique de l’implémentation actuelle qui peut être modifiée dans les futures versions du serveur.

Tableaux de dérivés latéraux

Une table dérivée définie avec le mot-clé LATERAL est appelée table dérivée latérale. Si une table dérivée est définie comme latérale, elle est autorisée à faire référence à d’autres tables dans la même clause FROM, mais seulement à celles qui ont été déclarées avant dans la clause FROM.

Example 1. Requêtes avec des tables dérivées latéralement

select dt.population, dt.city_name, c.country_name
from (select distinct country_name from cities) AS c,
LATERAL (select first 1 city_name, population
         from cities
         where cities.country_name = c.country_name
         order by population desc) AS dt;

select salespeople.name,
       max_sale.amount,
       customer_of_max_sale.customer_name
from salespeople,
LATERAL ( select max(amount) as amount from all_sales
          where all_sales.salesperson_id = salespeople.id
         ) as max_sale,
LATERAL ( select customer_name from all_sales
          where all_sales.salesperson_id = salespeople.id
            and all_sales.amount = max_sale.amount
        ) as customer_of_max_sale;