Utilisation de STRING_AGG pour des résultats de requête concis

SaraDev · Septembre 11, 2023, 11:43

La fonction string_agg en SQL est une fonction d’agrégation qui concatène (joint) des chaînes de caractères provenant de plusieurs lignes en une seule chaîne avec diverses options.

Elle est particulièrement utile lorsque vous souhaitez combiner des valeurs d’une colonne pour chaque groupe de données dans vos rapports, et elle peut rendre le résultat de votre requête plus facile à lire, surtout lorsque vous traitez des relations plusieurs-à-plusieurs. Par exemple, si vous souhaitez créer un rapport qui montre toutes les étiquettes utilisées dans chaque sujet, vous pourriez utiliser string_agg pour concaténer toutes les étiquettes de chaque sujet en une seule chaîne.

Syntaxe

Voici la syntaxe de base de string_agg :

STRING_AGG(expression, délimiteur)

La fonction string_agg prend deux paramètres :

expression : La valeur à concaténer.
délimiteur : Le délimiteur à insérer entre les chaînes. Le délimiteur peut être une chaîne de caractères ou un caractère.

Exemple d’utilisation dans les requêtes DE

Examinons quelques exemples de requêtes qui utilisent string_agg

Lister toutes les catégories sur un site

Cette requête concatène les champs name et id de la table categories en une seule chaîne, chaque paire name et id étant séparée par ’ : ', et chaque paire étant séparée par ', '. Les paires sont ensuite triées par id.

SELECT  
    -- La fonction STRING_AGG concatène les champs 'name' et 'id' en une seule chaîne.
    -- Chaque paire 'name' et 'id' est séparée par ' : ', et chaque paire est séparée par ', '.
    -- Les paires sont triées par 'id'.
    STRING_AGG(name || ' : ' || id, ', ' ORDER BY id) AS category_list
FROM 
    categories -- Les données sont sélectionnées à partir de la table 'categories'.

Exemple de résultats :

category_list
Non classé : 1, Commentaires sur le site : 2, Personnel : 3, Salon : 4, E-mail : 5, Événement : 6, Catégorie parente : 7, Sous-catégorie

Lister tous les sujets et leurs étiquettes associées

Cette requête sélectionne l’id de la table topics et concatène les tags associés pour chaque sujet en une seule chaîne. Chaque étiquette est séparée par une virgule et un espace, et les étiquettes sont triées par ordre alphabétique. Le résultat est regroupé par l’id du sujet et trié par les étiquettes. Si un sujet n’a pas d’étiquettes, la colonne tags sera NULL.

SELECT t.id topic_id,
    string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM topics t
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
LEFT JOIN tags ON tags.id = tt.tag_id
GROUP BY t.id   
ORDER BY tags

Le résultat sera une liste d’identifiants de sujets formatés, chacun avec une chaîne d’étiquettes associées triées par ordre alphabétique. Par exemple :

Exemple de résultats :

topic	tags
Bienvenue dans le salon (3)	tag1, tag2
Un post très sûr pour le travail (3)	tag3, tag4, tag,5
Ceci est un post de blog (4)	tag3
À propos de la catégorie Salon (3)	tag5
Bienvenue dans votre essai d’hébergement standard de 14 jours !	NULL

Explication détaillée avec commentaires en ligne

-- Cette instruction SQL sélectionne des données de la table 'topics' et des 'tags' associés.
SELECT 
    t.id topic_id, -- Sélectionne l''id' de la table 'topics'.
    -- La fonction STRING_AGG concatène les 'tags' associés pour chaque sujet en une seule chaîne.
    -- Chaque étiquette est séparée par une virgule et un espace, et les étiquettes sont triées par ordre alphabétique.
    string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM 
    topics t -- Les données sont sélectionnées à partir de la table 'topics'.
    -- La table 'topic_tags' est jointe à gauche sur 'topic_id' de la table 'topics'.
    LEFT JOIN topic_tags tt ON tt.topic_id = t.id
    -- La table 'tags' est jointe à gauche sur 'tag_id' de la table 'topic_tags'.
    LEFT JOIN tags ON tags.id = tt.tag_id
GROUP BY 
    t.id -- Le résultat est regroupé par l''id' du sujet.
ORDER BY
    "tags" -- Le résultat est trié par les 'tags'.

Utilisateurs ayant posté dans des sujets

Cette requête retournera une liste des ids et titres de topic, les noms d'utilisateur des utilisateurs ayant posté dans chaque sujet, et le nombre d'utilisateurs distincts ayant posté dans chaque sujet. Les résultats sont ensuite triés par le nombre d’utilisateurs, dans l’ordre décroissant.

SELECT
    topics.id AS "topic_id",
    topics.title AS "topic_title",
    STRING_AGG(DISTINCT users.username, ', ' ORDER BY users.username) AS "users_posted",
    COUNT(DISTINCT users.id) AS "user_count"
FROM
    topics
JOIN
    posts ON posts.topic_id = topics.id
JOIN
    users ON users.id = posts.user_id
GROUP BY
    topics.id, topics.title
ORDER BY
    "user_count" DESC

Exemple de résultats

topic	topic_title	users_posted	user_count
Créons un sujet (10)	Créons un sujet	anonymous, user1, user2, user3	4
Autre Sujet (3)	Autre Sujet	user3, user4, user5	3
Test Discobot (2)	Test Discobot	user6	1