db

Le SQL ne sert pas qu’à récupérer des données existantes, il peut aussi servir à créer des jeux de données plus ou moins compliqués en utilisant des générateurs. Les générateurs sont une façon d’utiliser les CTE pour créer des données. Nous allons voir comment, en partant d’exemples simples et en allant dans des cas plus compliqués.



11 min.

Partager l’article CTE, générateur et récursivité avec SQLite sur les réseaux sociaux

Une CTE, c’est quoi ?

Une CTE, ou Common Table Expression est un moyen en SQL d’isoler et de nommer une requête afin de la réutiliser immédiatement dans une autre requête.

Voyons par exemple cette CTE :

WITH very_simple_cte AS (
    SELECT 0
)
SELECT * FROM very_simple_cte;

C’est l’exact équivalent de cela :

SELECT * FROM (SELECT 0) AS very_simple_cte;

Les CTE permettent donc, dans le cas des requêtes imbriquées complexes, d’en simplifier l’écriture et la lecture.

Mais il n’y a pas que ça ! En effet, il est aussi possible d’avoir des CTE récursives !

Et maintenant, qu’est-ce qu’une CTE récursive ?

Imaginons une CTE assez simple, retournant 2 lignes. Nous pouvons d’abord l’envisager comme suit, avec un UNION en son sein :

WITH simple_cte(x) AS ( 
    SELECT 0 

    UNION ALL 

    SELECT 0
)
SELECT * FROM simple_cte;

Notez le (x). Cela permet de nommer les champs retournés par la CTE.

Nous pouvons aussi faire 2 CTEs et les appeler ensuite en externalisant l’UNION :

WITH simple_cte_bis(x) AS ( 
    SELECT 0
)
SELECT * FROM simple_cte_bis

UNION ALL

SELECT * FROM simple_cte_bis
;

Bien. Ça peut vite devenir lourd si on a un nombre élevé de lignes devant respecter un critère dans leur succession… Imaginons que nous souhaitons générer n lignes…

C’est là qu’entre en jeux la récursivité des CTEs !

Pour cela, il suffit d’introduire, lors de la déclaration de la CTE, le mot clé RECURSIVE, mais aussi un garde fou, sinon on tourne en boucle sans fin. Disons que nous arrêterons la suite au bout de 10 itérations.

WITH RECURSIVE recursive_cte(x) AS ( 
    SELECT  0 

    UNION ALL 

    SELECT  x
    FROM    recursive_cte
    LIMIT   10
)
SELECT * FROM recursive_cte;
  • La première partie de l’UNION définit le point de départ, la première ligne.
  • La deuxième partie fait un SELECT simple mais… en appelant la CTE elle-même, et en sélectionnant le champ x déclaré au début.

Ce petit bout de code SQL nous donne en sortie 10 lignes contenant un champ ayant zéro comme valeur.

Voilà, sans le savoir, vous venez de créer votre premier générateur. Basique, soit, mais un générateur tout de même, puisque parti de rien vous avez obtenu 10 lignes en sortie.

OK ! Donc, une CTE récursive, c’est un générateur ?

Oui, une CTE récursive est un moyen de créer un générateur.

Pratiquement, les générateurs peuvent servir à générer des suites de nombres, des distributions temporelles (mois, trimestres), ou des alphabets et même d’autres cas encore.

Voyons déjà le cas des générateurs de nombres avec quelques cas simples.

Créons des générateurs de nombres !

Un premier cas simple est tout simplement l’incrémentation de nombres par 1.

Si nous voulons récupérer les nombres de 0 à 15, nous pouvons faire ainsi :

WITH RECURSIVE generator(x) AS ( 
    SELECT 0 

    UNION ALL 

    SELECT  x + 1 
    FROM    generator 
    WHERE   x < 15 
)
SELECT * FROM generator;
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Mais nous pouvons également faire comme cela :

WITH RECURSIVE generator(x) AS ( 
    SELECT 0 

    UNION ALL 

    SELECT  x + 1 
    FROM    generator 
    LIMIT   16
)
SELECT * FROM generator;

Dans chaque cas, la deuxième partie de l’UNION reprend la valeur précédente et y ajoute 1.

Le garde-fou diffère : soit nous considérons une limitation par rapport à une valeur du champs calculé, soit nous considérons une limite par rapport au nombre de résultats retournés.

L’utilisation de tel ou tel garde-fou dépendra de chaque cas.

De ce principe d’incrémentation simple, nous pouvons en déduire la conception de nombres avec un pas spécifique.

Mettons que nous souhaitons générer des nombres espacés de 5 à chaque fois, en ne dépassant pas la valeur de 100 :

WITH RECURSIVE generator_step(x) AS ( 
    SELECT 0 
    
    UNION ALL 
    
    SELECT  x + 5 
    FROM    generator_step 
    WHERE   x < 100
)
SELECT * FROM generator_step;
0
5
10
15
20
25
30
35
40
45
50
55
60
65
70
75
80
85
90
95
100

Juste pour le fun, on double à chaque fois…

WITH RECURSIVE generator_double(x) AS ( 
    SELECT 1 

    UNION ALL 

    SELECT  x * 2 
    FROM    generator_double 
    LIMIT   30 
)
SELECT * FROM generator_double;
1
2
4
8
16
32
64
128
256
512
1024
2048
4096
8192
16384
32768
65536
131072
262144
524288
1048576
2097152
4194304
8388608
16777216
33554432
67108864
134217728
268435456
536870912

Je pense que vous avez compris le principe, beaucoup de suites mathématiques sont réalisables ainsi. Voyez par exemple la suite de Fibonacci :

WITH RECURSIVE generator_fibo (p, n) AS
(
     SELECT 0,
            1

     UNION ALL
     
     SELECT n,
            p + n
     FROM   generator_fibo
     WHERE  n < 100
)
SELECT  p
FROM    generator_fibo;
0
1
1
2
3
5
8
13
21
34
55
89

Nous pouvons faire mumuse avec les nombres grâce aux générateurs. Faisons maintenant mumuse avec l’alphabet…

Quid des générateurs d’alphabet ?

Vu que les lettres peuvent être codées à partir de code ASCII, alors un générateur d’alphabet est tout aussi possible.

Voyons cela avec l’alphabet latin en générant des lettres majuscules :

WITH RECURSIVE alphabet(code) AS ( 
    SELECT 65 

    UNION ALL 
    
    SELECT  code + 1 
    FROM    alphabet 
    WHERE   code < (65 + 25) 
) 
SELECT CHAR(code) FROM alphabet;

Le code ASCII 65 est celui codant pour la lettre A. Les autres suivent dans l’ordre, donc un processus incrémental de un en un suffit. Au final on applique la fonction CHAR() de SQLite pour avoir la lettre à partir du code.

Cool ! On peut faire plus compliqué ? Avec les dates par exemple…

Oui ! Voyons deux cas pratiques : générer la liste des mois dans une année et la liste des trimestres, avec les bornes de début et de fin.

Commençons par la base : afficher les mois de l’année 1995 (pourquoi pas…).

WITH RECURSIVE by_month(d) AS ( 
    SELECT '1995-01-01' 

    UNION ALL 
    
    SELECT  DATE(d, '+1 MONTHS') 
    FROM    by_month 
    WHERE   d < '1995-12-01' 
) 
SELECT * FROM by_month;
1995-01-01
1995-02-01
1995-03-01
1995-04-01
1995-05-01
1995-06-01
1995-07-01
1995-08-01
1995-09-01
1995-10-01
1995-11-01
1995-12-01

Jusqu’ici, rien de bien compliqué. Notez l’utilisation de la fonction SQLite DATE(), qui permet de faire des incréments de durées, ici, 1 mois ajouté à la date à chaque fois.

Bien.

Maintenant, vous allez me dire : l’année en dur, c’est bête !

En effet, prenons donc en compte l’année courante :

WITH RECURSIVE by_month(d) AS ( 
    SELECT strftime('%Y', 'now')||'-01-01' 
    
    UNION ALL 
    
    SELECT  DATE(d, '+1 MONTHS') 
    FROM    by_month 
    WHERE   d < (strftime('%Y', 'now')||'-12-01') 
) 
SELECT * FROM by_month;

La fonction SQLite STRFTIME() permet de formater la date courante ('now'). Ici, on ne récupère que l’année sur 4 chiffres.

C’est bien, mais on peut faire mieux en ne répétant pas le strftime('%Y', 'now'). Pour cela, utilisons le fait qu’il est possible d’avoir plusieurs CTE enchaînées en définissant cet appel de fonction dans une CTE :

WITH RECURSIVE const AS (
    SELECT strftime('%Y', 'now') AS y
),
by_month(d) AS ( 
    SELECT  const.y||'-01-01'
    FROM    const
        
    UNION ALL 
    
    SELECT  DATE(d, '+1 MONTHS') 
    FROM    by_month, const
    WHERE   d < (const.y||'-12-01') 
) 
SELECT * FROM by_month;
2021-01-01
2021-02-01
2021-03-01
2021-04-01
2021-05-01
2021-06-01
2021-07-01
2021-08-01
2021-09-01
2021-10-01
2021-11-01
2021-12-01

Voilà, en ne se répétant pas, on évite les erreurs stupides.

La CTE const ne sort qu’une seule ligne contenant le champ y stockant l’année en cours sur 4 chiffres.

La CTE by_month utilise la CTE const pour avoir l’année qui est nécessaire à la construction des dates.

Ajoutons-y les bornes définissant les débuts et les fins de mois, avec encore notre super fonction SQLite DATE() :

WITH RECURSIVE const AS (
    SELECT strftime('%Y', 'now') AS y
),by_month(d) AS ( 
    SELECT  const.y||'-01-01'
    FROM    const
    
    UNION ALL 
    
    SELECT  DATE(d, '+1 MONTHS')
    FROM    by_month, const
    WHERE   d < (const.y||'-12-01') 
)
SELECT  d AS date_start,
        date(
            d,
            'start of month',
            '+1 month',
            '-1 day'
        ) AS date_end
FROM    by_month;
2021-01-01|2021-01-31
2021-02-01|2021-02-28
2021-03-01|2021-03-31
2021-04-01|2021-04-30
2021-05-01|2021-05-31
2021-06-01|2021-06-30
2021-07-01|2021-07-31
2021-08-01|2021-08-31
2021-09-01|2021-09-30
2021-10-01|2021-10-31
2021-11-01|2021-11-30
2021-12-01|2021-12-31

Voilà ! Nous avons un moyen de générer une liste de chaque mois de l’année courante avec les dates de début et de fin de chaque mois.

De la même façon, la réalisation par trimestre en découle (encore merci la fonction DATE() !) :

WITH RECURSIVE const AS (
    SELECT strftime('%Y', 'now') AS y
), by_trimester(d) AS ( 
    SELECT  const.y||'-01-01' 
    FROM    const
    
    UNION ALL 
    
    SELECT  DATE(d, '+3 MONTHS') 
    FROM    by_trimester 
    LIMIT   4 
) 
SELECT  d AS date_start,
        date(
            d,
            'start of month',
            '+3 month',
            '-1 day'
        ) AS date_end
FROM    by_trimester;
2021-01-01|2021-03-31
2021-04-01|2021-06-30
2021-07-01|2021-09-30
2021-10-01|2021-12-31

Voilà ! Nos trimestres sont bien générés avec leurs bornes respectives.

Notre table servant d’exemple

Avoir des générateurs, c’est bien, les utiliser dans quelques cas concrets, c’est mieux.

Notre exemple se basera sur une table comportant des commentaires laissés par des internautes sur un site.

Un commentaire peut avoir un commentaire parent (dans ce cas, c’est une réponse) sans limitation dans la profondeur de la hiérarchie.

Le login de l’utilisateur, comportant que des caractères ASCII et commençant obligatoirement par une lettre, est enregistré.

La date de création du commentaire est enregistrée.

Voici la table en question :

CREATE TABLE comments (
    id          INTEGER PRIMARY KEY UNIQUE,
    parent_id   INTEGER REFERENCES comments(id),
    author      TEXT NOT NULL
                CHECK(author <> '' AND LENGTH(TRIM(author)) > 0),
    content     TEXT NOT NULL
                DEFAULT '',
    created_at  DATETIME NOT NULL
                DEFAULT (datetime('now'))
);

On y injecte tout un tas de données, que vous retrouverez dans le fichier source.

Lister le nombre de commentaires par trimestre

Au premier abord, vous pouvez me dire : « Réalisons juste des GROUP BY et basta ! ». Oui mais non. L’idée est de faire comme un rapport, donc s’il n’y a rien pour une période donnée, le GROUP BY ne le montrera pas. Par contre, si on utilise en plus les CTE vues précédemment pour les mois, alors là ça devient intéressant : il est possible de voir, si, pour un mois, un trimestre, il n’y a rien eu.

Pour notre exemple, nous allons lister le nombre de commentaires laissés pour chaque trimestre. Je vous livre l’exemple tel quel :

WITH RECURSIVE by_trimester(d) AS ( 
    SELECT '2021-01-01' 
    
    UNION ALL 
    
    SELECT  DATE(d, '+3 MONTHS') 
    FROM    by_trimester 
    LIMIT   4 
),
comments_by_date AS (
    SELECT      created_at
    FROM        comments 
),
borned_trimester AS (
    SELECT  d AS date_start,
            date(
                d,
                'start of month',
                '+3 month',
                '-1 day'
            ) AS date_end
    FROM    by_trimester
)
SELECT  t.date_start,
        t.date_end,
        COUNT(1) AS amount
FROM    comments_by_date AS c,
        borned_trimester AS t
WHERE   strftime('%Y-%m-%d', c.created_at) >= t.date_start
        AND
        strftime('%Y-%m-%d', c.created_at) <= t.date_end
GROUP BY t.date_start
;
2021-01-01|2021-03-31|17
2021-04-01|2021-06-30|4
2021-07-01|2021-09-30|4
2021-10-01|2021-12-31|10

Vous reconnaissez les différentes parties ? La CTE des trimestres est prise telle quelle, ainsi que sa partie des bornes, maintenant dans sa propre CTE (orned_trimester). La CTE comments_by_date récupère juste les dates de création des commentaires.

La requête finale utilisant ces trois CTE est assez simple :

  • Elle reprend les date de début et de fin de chaque trimestre pour les deux premiers champs.
  • Elle compte le nombre d’éléments pour chaque trimestre.
  • Le partie WHERE détermine les dates des commentaires devant se trouver dans tel ou tel trimestre.
  • Le GROUP BY compacte tout par trimestre, et permet ainsi le calcul du COUNT dans le SELECT pour avoir le nombre de commentaires.

Lister le nombre d’identifiants par ordre alphabétique

Imaginons que, pour des besoins en interne, nous devons lister, pour chaque lettre de l’alphabet, le nombre de commentaires correspondant au login commençant par cette lettre.

Sans surprise, nous allons reprendre notre CTE alphabet vue plus haut.

À celle-ci, nous ajoutons un autre CTE, qui va récupérer la première lettre de chaque login (via la fonction SUBSTR()) et la passer en majucule (via la fonctionUPPER()), et, dans un autre champ, y placer le nombre de messages qui y correspondent :

SELECT  UPPER(SUBSTR(author, 1,1)) AS letter,
        COUNT(*) AS amount
FROM    comments
GROUP BY UPPER(SUBSTR(author, 1,1));

Et enfin, une requête exploite ces deux CTE pour afficher l’alphabet avec pour chaque lettre le nombre de commentaires.

WITH RECURSIVE alphabet(code) AS ( 
    SELECT 65 
        UNION ALL 
    SELECT  code + 1 
    FROM    alphabet 
    WHERE   code < (65 + 25) 
),
by_authors (letter, amount) AS (
    SELECT  UPPER(SUBSTR(author, 1,1)) AS letter,
            COUNT(*) AS amount
    FROM    comments
    GROUP BY UPPER(SUBSTR(author, 1,1))
)
SELECT      CHAR(code) AS letter,
            COALESCE(amount, 0) AS comments
FROM        alphabet AS a
LEFT JOIN   by_authors AS b ON b.letter = CHAR(a.code);
A|0
B|5
C|7
D|0
E|0
F|0
G|0
H|0
I|0
J|0
K|0
L|6
M|0
N|0
O|0
P|0
Q|6
R|1
S|9
T|0
U|0
V|1
W|0
X|0
Y|0
Z|0

Ainsi, on peut voir que plein de logins ne déposent jamais de message…

Arbre des réponses

Et si, pour chaque ID de commentaire, on afficher son cheminement jusqu’à lui avec le contenu des commentaires parents ?

Pour cela, nous allons nous inspirer de l’article « Chemin d’un nœud dans un arbre hiérarchique de données avec PostgreSQL ».

Nous définissons donc une CTE parents contenant l’ID, le nombre de parents en cours et enfin le contenu.

Un premier jet peut être ceci :

WITH RECURSIVE parents AS (
    SELECT  id                          AS id,
            0                           AS ancestors,
            content                     AS tree
    FROM    comments
    WHERE   parent_id IS NULL

    UNION
    
    SELECT  child.id            AS id,
            c.ancestors + 1     AS ancestors,
            (
                c.tree 
                || CHAR(10)
                || child.content
            ) AS tree
    FROM comments as child
    INNER JOIN parents c ON c.id = child.parent_id
)
SELECT id, tree FROM parents;

Ce qui donne une sortie… pas très sexy.

Ce qui serait bien, c’est :

  • mettre le nom de l’auteur en première ligne, en majuscules
  • mettre le commentaire, tronqué.
  • décaler à chaque réponse d’un fil de discussion.
  • d’en faire une vue SQL :-)

Allons-y ! Sortons l’arcenal de fonctions disponibles dans SQLite en natif :

  • UPPER() pour mettre les auteurs en majuscule
  • CHAR(10) pour avoir un retour à la ligne
  • SUBSTR() pour tronquer les chaînes de caractères
  • PRINTF() pour créer une indentation du texte croissante

Voici le code :

CREATE VIEW comments_tree AS
    WITH RECURSIVE parents AS (
        SELECT  id                          AS id,
                0                           AS ancestors,
                UPPER(author)||':'||CHAR(10)||substr(content, 1,50)||'…'  AS tree
        FROM    comments
        WHERE   parent_id IS NULL

        UNION
        
        SELECT  child.id            AS id,
                c.ancestors + 1     AS ancestors,
                (
                    char(10) || c.tree 
                    || char(10) 
                    || printf("%"||(2 * (c.ancestors + 1)) || "s", ' ') 
                    || UPPER(child.author) || ':'
                    || char(10) 
                    || printf("%"||(2 * (c.ancestors + 1)) || "s", ' ') 
                    || substr(child.content, 1, (50 - (2 * (c.ancestors + 1))))
                    ||'…'
                ) AS tree
        FROM comments as child
        INNER JOIN parents c ON c.id = child.parent_id
    )
    SELECT id, tree FROM parents;

Et voyons, par exemple pour l’ID 29, le fil de discussion :

SELECT tree FROM comments_tree WHERE id = 29;
QUIDAM:
Quo vadis?…
  CAESAR:
  Ego vadam Romam…
    SPQR:
    Quid??!!!…
      SPQR:
      Ut a sem ornare tellus faucibus tincidunt a …

Conclusion

Les CTE, c’est pratique, il faut les utiliser sans modération :

  • Elles améliorent la lisibilité des requêtes complexes.
  • Elles permettent de réaliser des générateurs de données !
  • La possibilité d’avoir des CTE récursives, outre le fait de créer des générateurs, permet d’avoir une souplesse et un contrôle supplémentaire sur les jeux de données.
  • La syntaxe est largement répendue dans les bases de données modernes.

Attention toutefois : je vous ai exposé ici le cas de SQLite, pour d’autres bases de données, il peut y avoir des moyens plus simples encore que les CTE pour générer des données.

Par exemple, sous PostgreSQL, la fonction generate_series() permet de générer du contenu.

L’ensemble des codes sources de cet article sont disponibles dans ce fichier SQL.

Photo de Ant Rozetsky sur Unsplash