WITH¶

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.

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 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 |
+----------------------+

Copy

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   |
+----------------------+-----------+

Copy

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    |
+-------------+---------------+

Copy

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;

Copy

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    |
+-------------+---------------+

Copy

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    |
+-------------+---------------+

Copy

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;

Copy

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;

Copy

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    |
+-------------+---------------+

Copy

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    |
+-------------+---------------+

Copy

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 |
+-------------+

Copy

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
    ;

Copy

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 |
+-------------------------+--------------+---------------------+

Copy

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