Tableaux De Dérivés Latéraux

COMMENTSInstructions de procédure SQL (PSQL)

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;

Échantillonnage à partir d’expressions de tables communes (CTE)

Les expressions de tables communes sont une variante plus complexe et plus puissante des tables dérivées. Les CTE sont constituées d’un préambule commençant par le mot clé WITH qui définit une ou plusieurs expressions de tables communes (chacune d’entre elles peut avoir une liste d’alias de champs). La requête principale, qui suit le préambule, peut faire référence aux CTE comme s’il s’agissait de tableaux réguliers. Les CTE sont disponibles pour toute partie de la requête située en dessous de leur point de déclaration.

Les CTE sont décrits en détail dans CTE Common table expressions (WITH … AS … SELECT), et voici juste quelques utilisations en exemples.

La requête suivante présente notre exemple avec une variante de table dérivée pour les expressions de table génériques :

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

Ce n’est pas une grande amélioration par rapport à l’option des tableaux dérivés (sauf que les calculs sont effectués avant la requête principale). Nous pouvons encore améliorer la requête en éliminant le double calcul sqrt(D) pour chaque ligne :

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

Le texte de la requête semble plus complexe, mais il est devenu plus efficace (en supposant que l’exécution de la fonction SQRT prend plus de temps que le passage des valeurs des variables b, d et denom par un CTE supplémentaire).

Note

En fait, toutes les colonnes calculées dans le CTE 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 qu’il en est :

WITH T(X)
AS (SELECT GEN_UUID()
    FROM RDB$DATABASE)
SELECT
    UUID_TO_CHAR(X) as c1,
    UUID_TO_CHAR(X) as c2,
    UUID_TO_CHAR(X) as c3
FROM 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 :

WITH T(X)
AS (SELECT GEN_UUID()
    FROM RDB$DATABASE
    UNION ALL
    SELECT NULL FROM RDB$DATABASE WHERE 1=0)
SELECT
    UUID_TO_CHAR(X) as c1,
    UUID_TO_CHAR(X) as c2,
    UUID_TO_CHAR(X) as c3
FROM 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

WITH T(X)
AS (SELECT (SELECT GEN_UUID() FROM RDB$DATABASE)
    FROM RDB$DATABASE)
SELECT
    UUID_TO_CHAR(X) as c1,
    UUID_TO_CHAR(X) as c2,
    UUID_TO_CHAR(X) as c3
FROM T;

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

Bien entendu, nous pourrions également obtenir ce résultat en utilisant des tableaux dérivés, mais cela nécessiterait d’imbriquer les requêtes les unes dans les autres.

Voir aussi :

Expressions de table communes CTE (WITH … AS … SELECT).