Catégories :

Syntaxe de requête

WITH

La clause WITH est une clause facultative qui précède le corps de l’instruction SELECT et définit une ou plusieurs expressions de table CTEs communes pouvant être utilisées ultérieurement dans l’instruction. Par exemple, des CTEs peuvent être référencées dans la clause FROM .

Note

Vous pouvez utiliser une clause WITH lorsque vous créez et appelez une procédure anonyme similaire à une procédure stockée. Cette clause modifie une commande CALL plutôt qu’une commande SELECT. Pour plus d’informations, voir CALL (avec procédure anonyme).

La clause WITH est utilisée avec les objets de modèles de machine learning pour créer un alias vers une version spécifique du modèle, qui peut ensuite être utilisé pour appeler les méthodes de cette version. Voir Appel de méthodes de modèle.

Voir aussi :

CONNECT BY, Commandes du modèle

Syntaxe

Sous-requête :

[ WITH
       <cte_name1> [ ( <cte_column_list> ) ] AS ( SELECT ...  )
   [ , <cte_name2> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
   [ , <cte_nameN> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
]
SELECT ...

CTE récursive :

[ WITH [ RECURSIVE ]
       <cte_name1> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause )
   [ , <cte_name2> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause ) ]
   [ , <cte_nameN> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause ) ]
]
SELECT ...

Où :

anchorClause ::=
    SELECT <anchor_column_list> FROM ...

recursiveClause ::=
    SELECT <recursive_column_list> FROM ... [ JOIN ... ]

Paramètres

cte_name1 , cte_nameN

Le nom CTE doit suivre les règles applicables aux vues et aux identificateurs d’objet similaires .

cte_column_list

Les noms des colonnes dans CTE (expression de table commune).

anchor_column_list

Les colonnes utilisées dans la clause d’ancrage pour la CTE récursive. Les colonnes de cette liste doivent correspondre aux colonnes définies dans cte_column_list.

recursive_column_list

Les colonnes utilisées dans la clause récursive pour la CTE récursive. Les colonnes de cette liste doivent correspondre aux colonnes définies dans cte_column_list.

Pour plus de détails, voir Clause d’ancrage et Clause récursive (dans cette rubrique). Pour une explication détaillée de la manière dont la clause d’ancrage et la clause récursive fonctionnent ensemble, voir Utilisation de CTEs (expressions de table communes).

Notes sur l’utilisation

Utilisation générale

  • Une clause WITH peut faire référence de manière récursive à elle-même et aux autres CTEs qui apparaissent plus tôt dans la même clause. Par exemple, cte_name2 peut faire référence à cte_name1 et à elle-même, tandis que cte_name1 peut se référer à elle-même, mais pas à cte_name2.

  • Vous pouvez mélanger des clauses CTE récursives et non récursives (itératives et non itératives) dans la clause WITH. Les clauses CTE doivent être ordonnées de sorte que si une CTE doit faire référence à une autre CTE, la CTE à laquelle il est fait référence doit avoir été définie plus tôt dans l’instruction (par exemple, la seconde CTE peut faire référence à la première CTE, mais pas l’inverse).

    Les CTEs n’ont pas besoin d’être répertoriés dans l’ordre selon qu’elles sont récursives ou non. Par exemple, une CTE non récursive peut être répertoriée immédiatement après le mot-clé RECURSIVE, et une CTE récursive peut venir après cette CTE non récursive.

    Dans une CTE récursive, la clause d’ancrage ou la clause récursive (ou les deux) peuvent faire référence à une ou plusieurs autres CTE.

  • Pour les CTEs récursives, le cte_column_list est nécessaire.

  • Pour les CTEs non récursives, le cte_column_list est facultatif.

  • Assurez-vous d’utiliser UNION ALL , et non pas UNION , dans une CTE récursive.

  • Le mot clé RECURSIVE est facultatif.

    • Les CTEs peuvent être récursives, que le mot clé RECURSIVE ait été spécifié ou non.

    • Vous pouvez utiliser le mot clé RECURSIVE même si aucune CTEs n’est récursive.

    • Si RECURSIVE est utilisé, il ne doit l’être qu’une seule fois, même si plus d’une CTE est récursive.

    Bien que les instructions SQL fonctionnent correctement avec ou sans le mot clé RECURSIVE , l’utilisation appropriée du mot clé facilite la compréhension et la maintenance du code. Snowflake recommande d’utiliser le mot clé RECURSIVE si une ou plusieurs CTEs sont récursives, et recommande vivement de ne pas utiliser le mot clé si aucune des CTEs n’est récursive.

Attention

Lorsqu’on utilise une CTE récursive, il est possible de créer une requête qui tourne en boucle à l’infini et consomme des crédits jusqu’à ce que la requête aboutisse, qu’elle expire (par exemple, qu’elle dépasse le nombre de secondes spécifié par le paramètre STATEMENT_TIMEOUT_IN_SECONDS) ou que vous annuliez la requête.

Pour plus d’informations sur la façon dont les boucles infinies peuvent se produire et pour des instructions sur la façon d’éviter ce problème, voir Dépannage d’une CTE récursive.

Par exemple, pour limiter le nombre d’itérations à moins de 10 :

WITH cte AS (
  SELECT ..., 1 as level ...

  UNION ALL

  SELECT ..., cte.level + 1 as level
   FROM cte ...
   WHERE ... level < 10
) ...

Limitations

  • L’implémentation de CTEs récursives par Snowflake ne prend pas en charge les mots clés suivants que certains autres systèmes prennent en charge :

    • SEARCH DEPTH FIRST BY ...

    • CYCLE ... SET ...

Clause d’ancrage

La clause d’ancrage dans une CTE récursive est une instruction SELECT.

La clause d’ancrage est exécutée une fois lors de l’exécution de l’instruction dans laquelle elle est intégrée. Elle s’exécute avant la clause récursive et génère le premier ensemble de lignes à partir de la CTE récursive. Ces lignes ne sont pas seulement incluses dans la sortie de la requête, mais sont également référencées par la clause récursive.

La clause d’ancrage peut contenir toute construction SQL autorisée dans une clause SELECT. Cependant, la clause d’ancrage ne peut pas faire référence à cte_name1 ; seule la clause récursive peut faire référence à cte_name1.

Bien que la clause d’ancrage effectue généralement des sélections dans la même table que la clause récursive, cela n’est pas obligatoire. La clause d’ancrage peut sélectionner une source de données semblable à une table, y compris une autre table, une vue, une UDTF , ou une valeur constante.

La clause d’ancrage sélectionne un seul « niveau » de la hiérarchie, généralement le niveau supérieur ou le niveau d’intérêt le plus élevé. Par exemple, si la requête est destinée à afficher « la vue éclatée des pièces » d’une voiture, la clause d’ancrage renvoie le composant de niveau le plus élevé, à savoir la voiture elle-même.

La sortie de la clause d’ancrage représente une couche de la hiérarchie et cette couche est stockée en tant que contenu de la « vue » à laquelle on a accédé lors de la première itération de la clause récursive.

Clause récursive

La clause récursive est une instruction SELECT. Ce SELECT est limité aux projections, aux filtres et aux jointures (jointures internes et jointures externes dans lesquelles la référence récursive se trouve du côté préservé de la jointure externe). La clause récursive ne peut pas contenir les éléments suivants :

  • Fonctions d’agrégation ou de fenêtre,

  • GROUP BY, ORDER BY, LIMIT, ou DISTINCT.

La clause récursive peut référencer (et le fait généralement) le cte_name1 comme si la CTE était une table ou une vue.

La clause récursive inclut généralement un JOIN qui joint la table utilisée dans la clause d’ancrage à la CTE. Cependant, le JOIN peut joindre plusieurs tables ou sources de données de type table (vue, etc.).

La première itération de la clause récursive commence par les données de la clause d’ancrage. Ces données sont ensuite jointes aux autres tables dans la clause FROM de la clause récursive.

Chaque itération suivante commence par les données de l’itération précédente.

Vous pouvez considérer la clause CTE ou la « vue » comme contenant le contenu de l’itération précédente, de sorte que ce contenu soit disponible pour être joint. Notez que lors d’une itération, la CTE ne contient que le contenu de l’itération précédente, pas les résultats accumulés à partir de toutes les itérations précédentes. Les résultats accumulés (y compris ceux de la clause d’ancrage) sont stockés dans un endroit séparé.

Listes de colonnes dans une CTE récursive

Il y a trois listes de colonnes dans une CTE récursive :

  • cte_column_list

  • anchor_column_list (dans la clause d’ancrage)

  • recursive_column_list (dans la clause récursive)

Une CTE récursive peut contenir d’autres listes de colonnes (par exemple, dans une sous-requête), mais ces trois listes de colonnes doivent être présentes.

Ces trois listes de colonnes doivent toutes se correspondre.

En pseudo-code, cela ressemble à ceci :

WITH RECURSIVE cte_name (X, Y) AS
(
  SELECT related_to_X, related_to_Y FROM table1
  UNION ALL
  SELECT also_related_to_X, also_related_to_Y
    FROM table1 JOIN cte_name ON <join_condition>
)
SELECT ... FROM ...

Les colonnes X et related_to_X doivent correspondre ; la clause d’ancrage génère le « contenu » initial de la « vue » que représente la CTE ; chaque colonne de la clause d’ancrage (par exemple, la colonne related_to_x) doit générer une sortie qui appartiendra à la colonne correspondante de la CTE (exemple : colonne X).

Les colonnes also_related_to_X et X doivent correspondre ; à chaque itération de la clause récursive, la sortie de cette clause devient le nouveau contenu de la CTE/vue pour la prochaine itération.

De plus, les colonnes related_to_X et also_related_to_X doivent correspondre, car elles sont situées d’un côté de l’opérateur UNION ALL et les colonnes situées de chaque côté d’un opérateur UNION ALL doivent correspondre.

Exemples

Exemples non récursifs

Dans cette section, nous fournirons des exemples de requêtes et des exemples de sorties. Pour que les exemples soient courts, le code omet les instructions pour créer et charger les tables.

Dans cet exemple, nous utilisons une simple clause WITH comme vue pour extraire un sous-ensemble de données, dans ce cas les albums de musique sortis en 1976. Pour notre petite base de données, la sortie de requête est l’album « Amigos » et l’album « Look Into The Future », tous deux de l’année 1976 :

with
  albums_1976 as (select * from music_albums where album_year = 1976)
select album_name from albums_1976 order by album_name;
+----------------------+
| ALBUM_NAME           |
|----------------------|
| Amigos               |
| Look Into The Future |
+----------------------+

L’exemple suivant utilise une clause WITH avec une clause WITH antérieure ; la CTE nommée journey_album_info_1976 utilise la CTE nommée album_info_1976. La sortie est l’album « Look Into The Future », avec le nom du groupe :

with
   album_info_1976 as (select m.album_ID, m.album_name, b.band_name
      from music_albums as m inner join music_bands as b
      where m.band_id = b.band_id and album_year = 1976),
   Journey_album_info_1976 as (select *
      from album_info_1976 
      where band_name = 'Journey')
select album_name, band_name 
   from Journey_album_info_1976;
+----------------------+-----------+
| ALBUM_NAME           | BAND_NAME |
|----------------------+-----------|
| Look Into The Future | Journey   |
+----------------------+-----------+

Cet exemple répertorie les musiciens qui ont joué sur les albums de Santana et de Journey. Cet exemple n’utilise pas la clause WITH. Pour cette requête (et les quelques requêtes suivantes, qui sont toutes équivalentes), la sortie est les IDs et les noms des musiciens qui ont joué sur les albums de Santana et Journey.

select distinct musicians.musician_id, musician_name
 from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
 where musicians.musician_ID = musicians_and_albums.musician_ID
   and musicians_and_albums.album_ID = music_albums.album_ID
   and music_albums.band_ID = music_bands.band_ID
   and music_bands.band_name = 'Santana'
intersect
select distinct musicians.musician_id, musician_name
 from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
 where musicians.musician_ID = musicians_and_albums.musician_ID
   and musicians_and_albums.album_ID = music_albums.album_ID
   and music_albums.band_ID = music_bands.band_ID
   and music_bands.band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Comme vous pouvez le constater, la requête précédente contient du code dupliqué. Au cours des prochains exemples, nous montrerons comment simplifier cette requête en utilisant une ou plusieurs vues explicites, puis comment la simplifier en utilisant des clauses CTEs.

Cette requête montre comment utiliser les vues pour réduire la complexité et la duplication de l’exemple précédent (comme dans l’exemple précédent, ceci n’utilise pas de clause WITH) :

create or replace view view_musicians_in_bands AS
  select distinct musicians.musician_id, musician_name, band_name
    from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
    where musicians.musician_ID = musicians_and_albums.musician_ID
      and musicians_and_albums.album_ID = music_albums.album_ID
      and music_albums.band_ID = music_bands.band_ID;

Avec cette vue, vous pouvez réécrire la requête d’origine sous la forme :

select musician_id, musician_name from view_musicians_in_bands where band_name = 'Santana'
intersect
select musician_id, musician_name from view_musicians_in_bands where band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Cet exemple utilise une clause WITH pour réaliser l’équivalent de ce que la requête précédente a réalisé :

with
  musicians_in_bands as (
     select distinct musicians.musician_id, musician_name, band_name
      from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
      where musicians.musician_ID = musicians_and_albums.musician_ID
        and musicians_and_albums.album_ID = music_albums.album_ID
        and music_albums.band_ID = music_bands.band_ID)
select musician_ID, musician_name from musicians_in_bands where band_name = 'Santana'
intersect
select musician_ID, musician_name from musicians_in_bands where band_name = 'Journey'
order by musician_ID
  ;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Ces instructions créent des vues plus granulaires (cet exemple n’utilise pas de clause WITH) :

Répertoriez les albums d’un groupe particulier :

create or replace view view_album_IDs_by_bands AS
 select album_ID, music_bands.band_id, band_name
  from music_albums inner join music_bands
  where music_albums.band_id = music_bands.band_ID;

Répertoriez les musiciens qui ont joué sur ces albums :

create or replace view view_musicians_in_bands AS
 select distinct musicians.musician_id, musician_name, band_name
  from musicians inner join musicians_and_albums inner join view_album_IDs_by_bands
  where musicians.musician_ID = musicians_and_albums.musician_ID
    and musicians_and_albums.album_ID = view_album_IDS_by_bands.album_ID;

Utilisez maintenant ces vues pour interroger les musiciens ayant joué sur les albums de Santana et de Journey :

select musician_id, musician_name from view_musicians_in_bands where band_name = 'Santana'
intersect
select musician_id, musician_name from view_musicians_in_bands where band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Ces instructions créent des vues implicites plus granulaires (cet exemple utilise une clause WITH) :

with
  album_IDs_by_bands as (select album_ID, music_bands.band_id, band_name
                          from music_albums inner join music_bands
                          where music_albums.band_id = music_bands.band_ID),
  musicians_in_bands as (select distinct musicians.musician_id, musician_name, band_name
                          from musicians inner join musicians_and_albums inner join album_IDs_by_bands
                          where musicians.musician_ID = musicians_and_albums.musician_ID
                            and musicians_and_albums.album_ID = album_IDS_by_bands.album_ID)
select musician_ID, musician_name from musicians_in_bands where band_name = 'Santana'
intersect
select musician_ID, musician_name from musicians_in_bands where band_name = 'Journey'
order by musician_ID
  ;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Exemples récursifs

Voici un exemple de base d’utilisation d’une CTE récursive pour générer une série de Fibonacci :

WITH RECURSIVE current_f (current_val, previous_val) AS
    (
    SELECT 0, 1
    UNION ALL 
    SELECT current_val + previous_val, current_val FROM current_f
      WHERE current_val + previous_val < 100
    )
  SELECT current_val FROM current_f ORDER BY current_val;
+-------------+
| CURRENT_VAL |
|-------------|
|           0 |
|           1 |
|           1 |
|           2 |
|           3 |
|           5 |
|           8 |
|          13 |
|          21 |
|          34 |
|          55 |
|          89 |
+-------------+

Cet exemple est une requête avec une CTE récursive qui montre un « éclatement de nomenclature » pour une automobile :

-- The components of a car.
CREATE TABLE components (
    description VARCHAR,
    component_ID INTEGER,
    quantity INTEGER,
    parent_component_ID INTEGER
    );

INSERT INTO components (description, quantity, component_ID, parent_component_ID) VALUES
    ('car', 1, 1, 0),
       ('wheel', 4, 11, 1),
          ('tire', 1, 111, 11),
          ('#112 bolt', 5, 112, 11),
          ('brake', 1, 113, 11),
             ('brake pad', 1, 1131, 113),
       ('engine', 1, 12, 1),
          ('piston', 4, 121, 12),
          ('cylinder block', 1, 122, 12),
          ('#112 bolt', 16, 112, 12)   -- Can use same type of bolt in multiple places
    ;

WITH RECURSIVE current_layer (indent, layer_ID, parent_component_ID, component_id, description, sort_key) AS (
  SELECT 
      '...', 
      1, 
      parent_component_ID, 
      component_id, 
      description, 
      '0001'
    FROM components WHERE component_id = 1
  UNION ALL
  SELECT indent || '...',
      layer_ID + 1,
      components.parent_component_ID,
      components.component_id, 
      components.description,
      sort_key || SUBSTRING('000' || components.component_ID, -4)
    FROM current_layer JOIN components 
      ON (components.parent_component_id = current_layer.component_id)
  )
SELECT
  -- The indentation gives us a sort of "side-ways tree" view, with
  -- sub-components indented under their respective components.
  indent || description AS description, 
  component_id,
  parent_component_ID
  -- The layer_ID and sort_key are useful for debugging, but not
  -- needed in the report.
--  , layer_ID, sort_key
  FROM current_layer
  ORDER BY sort_key;
+-------------------------+--------------+---------------------+
| DESCRIPTION             | COMPONENT_ID | PARENT_COMPONENT_ID |
|-------------------------+--------------+---------------------|
| ...car                  |            1 |                   0 |
| ......wheel             |           11 |                   1 |
| .........tire           |          111 |                  11 |
| .........#112 bolt      |          112 |                  11 |
| .........brake          |          113 |                  11 |
| ............brake pad   |         1131 |                 113 |
| ......engine            |           12 |                   1 |
| .........#112 bolt      |          112 |                  12 |
| .........piston         |          121 |                  12 |
| .........cylinder block |          122 |                  12 |
+-------------------------+--------------+---------------------+

Pour plus d’exemples, voir Utilisation de CTEs (expressions de table communes).