Une suite de nombres

Fait partie de la section Au-delà des bases du Journey SQL de Coddy — leçon 20 sur 27.

Vous avez découvert le mot-clé WITH dans Fondamentaux pour nommer une sous-requête, également connue sous le nom de CTE (Common Table Expression). Une CTE récursive va plus loin : elle permet à la sous-requête de se référer à elle-même, en construisant un résultat ligne par ligne.

La version la plus simple génère une séquence de nombres sans table sous-jacente. La forme est toujours la même :

WITH RECURSIVE counter(n) AS (
    SELECT 1                              -- ancrage : la ligne de départ
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 5 -- récursif : construit le suivant à partir du précédent
)
SELECT n FROM counter

Lisez-le ainsi : commencez par n = 1, puis continuez à ajouter des lignes où chaque n est le précédent n + 1, en vous arrêtant quand le WHERE ne correspond plus. Le résultat est 1, 2, 3, 4, 5.

Les deux parties du corps sont jointes par UNION ALL. La première est l'ancre : les lignes de départ. La seconde est l'étape récursive : elle interroge la CTE elle-même.

Défi

Facile

Écrivez une CTE récursive nommée powers avec deux colonnes :

i : l'index d'itération, commençant à 1
p : 2 élevé à la puissance i-ième (2, 4, 8, …)

Arrêtez-vous une fois que p dépasserait 1000. Retournez les deux colonnes, triées par i par ordre croissant.

Aide-mémoire

Une CTE récursive permet à une sous-requête de se référer à elle-même, en construisant les résultats ligne par ligne :

WITH RECURSIVE counter(n) AS (
    SELECT 1                               -- ancrage : ligne de départ
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 5  -- étape récursive
)
SELECT n FROM counter

Ancrage : la ou les lignes de départ (premier SELECT)
Étape récursive : interroge la CTE elle-même pour produire la ligne suivante
S'arrête lorsque la condition WHERE n'est plus satisfaite
Les deux parties sont jointes avec UNION ALL

Essayez vous-même

WITH RECURSIVE powers(i, p) AS (
    -- ancre : i=1, p=2
    -- récursif : i+1, p*2, jusqu'à ce que p atteigne 1000
)
SELECT i, p FROM powers ORDER BY i