Gestion des valeurs nulles avec COALESCE

SaraDev · Septembre 13, 2023, 1:15

Dans ce tutoriel, nous explorerons l’utilisation de la fonction COALESCE dans les requêtes SQL de Data Explorer.

COALESCE vous permet de gérer les valeurs NULL dans les résultats de vos requêtes. Si vos données contiennent des valeurs NULL, vous pouvez utiliser COALESCE pour fournir une valeur par défaut (comme 0) à la place de ces valeurs NULL.

COALESCE est particulièrement utile lorsque vous devez effectuer ultérieurement des calculs ou des analyses de données sur les résultats de votre requête, où des valeurs NULL pourraient causer des problèmes ou des interprétations erronées.

Syntaxe

La fonction COALESCE prend deux arguments ou plus et renvoie la première valeur non NULL rencontrée de gauche à droite dans la liste. Si tous les arguments sont NULL, COALESCE renvoie NULL.

La syntaxe de base pour COALESCE est la suivante :

COALESCE(valeur1, valeur2, ..., valeurN)

Par exemple, COALESCE(NULL, 1, 2) renverra 1 car 1 est le premier argument non NULL.

Requêtes d’exemple

Examinons quelques requêtes d’exemple pour comprendre comment COALESCE est utilisé dans les requêtes de Data Explorer.

Messages créés, J’aime reçus et Signets reçus

Niveau de complexité : Débutant

Cette requête récupère le nombre total de messages créés, de J’aime reçus et de signets reçus sur les messages pour chaque utilisateur d’un site. Si un utilisateur n’a aucun message, J’aime ou signet reçu, la fonction COALESCE renverra 0 au lieu de NULL.

SELECT 
    users.id AS user_id,
    users.username,
    COALESCE(COUNT(posts.id), 0) AS post_count,
    COALESCE(SUM(posts.like_count), 0) AS likes_received,
    COALESCE(SUM(posts.bookmark_count), 0) AS bookmarks_received
FROM 
    users
LEFT JOIN 
    posts ON users.id = posts.user_id
GROUP BY 
    users.id, users.username
ORDER BY 
    post_count DESC, likes_received DESC, bookmarks_received DESC

Résultats d’exemple :

user	username	post_count	likes_received	bookmarks_received
1	alice	345	6	9
2	bella	278	5	6
3	charlie	37	3	3
4	dave	0	0	0

Dans cette requête, nous joignons la table users à la table posts sur le champ user_id. Nous utilisons ensuite la fonction COALESCE pour nous assurer que si un utilisateur n’a aucun message, J’aime reçu ou signet reçu, nous renvoyons 0 au lieu de NULL. Les résultats sont regroupés par ID d’utilisateur et nom d’utilisateur, puis triés par nombre de messages, J’aime et signets dans l’ordre décroissant.

Sujets et Réponses par Utilisateur

Niveau de complexité : Intermédiaire

Cette requête obtient le nombre de sujets et de réponses publiés par chaque utilisateur entre deux dates. Si un utilisateur n’a aucun sujet ou réponse, COALESCE renverra 0 au lieu de NULL.

-- [params]
-- date :start_date 
-- date :end_date
-- string NULL:username

WITH qtt_topics AS (
    SELECT 
        t.user_id,
        COUNT(*) AS topics
    FROM topics t
    WHERE    
        t.user_id > 0 
        AND t.deleted_at ISNULL
        AND t.archetype = 'regular'
        AND t.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY t.user_id
    ),
    
qtt_replies AS (
    SELECT 
        p.user_id,
        COUNT(*) AS replies
    FROM posts p
    WHERE    
        p.user_id > 0 
        AND p.deleted_at ISNULL
        AND p.post_number != 1
        AND p.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY p.user_id
    ),

total AS (
    SELECT
        COALESCE(qr.user_id, qt.user_id) user_id,
        COALESCE(topics,0) qtt_topics,
        COALESCE(replies,0) qtt_replies
    FROM qtt_topics qt
    FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id
    ORDER BY user_id)

SELECT 
    username,
    qtt_topics,
    qtt_replies
FROM total
INNER JOIN users u ON u.id = user_id
WHERE :username ISNULL OR LOWER(username) LIKE '%'||LOWER(:username)||'%'

Résultats d’exemple:

username	qtt_topics	qtt_replies
Alice	10	50
Bella	15	45
Charlie	12	30

Dans cette requête, COALESCE est utilisé dans l’expression de table commune (CTE) total. Il garantit que si l’user_id est NULL dans qtt_topics ou qtt_replies, l’autre valeur est utilisée. Ceci est important car une FULL JOIN est utilisée pour combiner qtt_topics et qtt_replies, et si un utilisateur n’a que des sujets mais aucune réponse (ou inversement), son user_id serait NULL dans l’une des tables. COALESCE empêche cela de se produire.

Explication détaillée avec commentaires en ligne

-- [params]
-- date :start_date 
-- date :end_date
-- string NULL:username

-- Définir une CTE (Expression de table commune) pour compter les sujets par utilisateur
WITH qtt_topics AS (
    SELECT 
        t.user_id,  -- identifiant de l'utilisateur
        COUNT(*) AS topics  -- nombre de sujets
    FROM topics t  -- depuis la table topics
    WHERE    
        t.user_id > 0  -- ne considérer que les identifiants d'utilisateurs non nuls
        AND t.deleted_at ISNULL  -- ne considérer que les sujets non supprimés
        AND t.archetype = 'regular'  -- ne considérer que les sujets réguliers
        AND t.created_at::date BETWEEN :start_date AND :end_date  -- ne considérer que les sujets créés entre start_date et end_date
    GROUP BY t.user_id  -- regrouper par identifiant d'utilisateur pour obtenir le nombre de sujets par utilisateur
),
    
-- Définir une CTE pour compter les réponses par utilisateur
qtt_replies AS (
    SELECT 
        p.user_id,  -- identifiant de l'utilisateur
        COUNT(*) AS replies  -- nombre de réponses
    FROM posts p  -- depuis la table posts
    WHERE    
        p.user_id > 0  -- ne considérer que les identifiants d'utilisateurs non nuls
        AND p.deleted_at ISNULL  -- ne considérer que les messages non supprimés
        AND p.post_number != 1  -- ne considérer que les messages qui ne sont pas le premier message d'un sujet (c.-à-d. les réponses)
        AND p.created_at::date BETWEEN :start_date AND :end_date  -- ne considérer que les messages créés entre start_date et end_date
    GROUP BY p.user_id  -- regrouper par identifiant d'utilisateur pour obtenir le nombre de réponses par utilisateur
),

-- Définir une CTE pour combiner les comptes de sujets et de réponses par utilisateur
total AS (
    SELECT
        COALESCE(qr.user_id, qt.user_id) user_id,  -- identifiant de l'utilisateur (provenant soit de qtt_replies soit de qtt_topics)
        COALESCE(topics,0) qtt_topics,  -- nombre de sujets (si NULL, retourner 0)
        COALESCE(replies,0) qtt_replies  -- nombre de réponses (si NULL, retourner 0)
    FROM qtt_topics qt  -- depuis la CTE qtt_topics
    FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id  -- jointure avec la CTE qtt_replies sur l'identifiant de l'utilisateur
    ORDER BY user_id  -- trier par identifiant de l'utilisateur
)

-- Requête principale pour obtenir le jeu de résultats final
SELECT 
    username,  -- nom d'utilisateur
    qtt_topics,  -- nombre de sujets
    qtt_replies  -- nombre de réponses
FROM total  -- depuis la CTE total
INNER JOIN users u ON u.id = user_id  -- jointure avec la table users sur l'identifiant de l'utilisateur
WHERE :username ISNULL OR LOWER(username) LIKE '%'||LOWER(:username)||'%'  -- filtrer par nom d'utilisateur (si fourni)

Données sur les questions résolues

Niveau de complexité : Avancé / Nécessite le plugin Discourse Solved

Cette requête est utilisée pour obtenir des détails sur les sujets, y compris s’ils sont résolus ou non, le temps écoulé avant la première réponse, le temps écoulé avant la solution, et d’autres statistiques connexes.

Cette requête suppose que tous les sujets d’un site peuvent être résolus.

--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all


WITH valid_topics AS (
    SELECT 
        t.id,
        t.user_id,
        t.title,
        t.views,
        posts_count-1 AS "posts_count", 
        t.created_at,
        (CURRENT_DATE::date-t.created_at::date) AS "total_days",
        string_agg(tags.name, ', ') AS tag_names
    FROM topics t
    LEFT JOIN topic_tags tt ON tt.topic_id = t.id
    LEFT JOIN tags ON tags.id = tt.tag_id
    LEFT JOIN tag_group_memberships tgm ON tgm.tag_id = tt.tag_id
    LEFT JOIN (SELECT topic_id, MIN(created_at) AS "created_at"
                FROM posts
                WHERE deleted_at ISNULL
                    AND post_type = 1
                    AND post_number > 1
                GROUP BY topic_id) date_first_reply ON date_first_reply.topic_id = t.id
    WHERE t.deleted_at ISNULL
        AND t.created_at::date BETWEEN :start_date AND :end_date
        AND t.archetype = 'regular'
    GROUP BY t.id, t.user_id, t.title, t.views, posts_count, t.created_at
),

solved_topics AS (
    SELECT 
        vt.id,
        tcf.created_at
    FROM topic_custom_fields tcf
    INNER JOIN valid_topics vt ON vt.id = tcf.topic_id
    WHERE tcf.name = 'accepted_answer_post_id'
),

last_reply AS (
    SELECT p.topic_id, p.user_id FROM posts p
    INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p
                WHERE deleted_at ISNULL
                AND post_type = 1
                GROUP BY topic_id) x ON x.post = p.id
),

first_reply AS (
    SELECT p.topic_id, p.user_id, p.created_at FROM posts p
    INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p
                WHERE deleted_at ISNULL
                AND post_type = 1
                AND post_number > 1
                GROUP BY topic_id) x ON x.post = p.id
)

SELECT
    CASE 
        WHEN st.id IS NOT NULL THEN 'solved'
        ELSE 'unsolved'
    END AS status,
    vt.tag_names,
    vt.id AS topic_id,
    vt.user_id topic_user_id,
    ue.email,
    vt.title,
    vt.views,
    lr.user_id AS last_reply_user_id,
    ue2.email AS last_reply_user_email,
    vt.created_at::date topic_create,
    COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create,
    COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create,
    COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)",
    COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)",
    COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)",
    COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)",
    posts_count AS number_of_replies,
    total_days AS total_days_without_solution
FROM valid_topics vt
LEFT JOIN last_reply lr ON lr.topic_id = vt.id
LEFT JOIN first_reply fr ON fr.topic_id = vt.id
LEFT JOIN solved_topics st ON st.id = vt.id
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true
WHERE (:tag_name =  'all' OR LOWER(vt.tag_names) LIKE '%' || LOWER(:tag_name) || '%')
ORDER BY tag_names, total_days DESC

Résultats d’exemple:

status	tag_names	topic	topic_user	email	title	views	last_reply_user	last_reply_user_email	topic_create	first_reply_create	solution_create	time_first_reply(days)	time_first_reply(hours)	time_solution(days)	time_solution(hours)	number_of_replies	total_days_without_solution
solved	a, c, b	A Topic Title (7)	alice	alice@example.com	A Topic Title	58	bella	bella@example.com	2023-08-25	2023-08-25	2023-08-29	0	1	1	24	9	4
unsolved	tag1	Welcome to the Lounge (3)	system	no_email	Welcome to the Lounge	3	system	no_email	2023-05-01			0	0	0	0	2	134

Dans cette requête, COALESCE est utilisé dans les lignes suivantes :

COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create : Cette ligne convertit la date created_at de la première réponse en chaîne de caractères. Si la première réponse n’existe pas (c.-à-d. fr.created_at est null), elle renverra une chaîne vide (``).
COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create : De même, cette ligne convertit la date created_at de la solution en chaîne de caractères. Si la solution n’existe pas (c.-à-d. st.created_at est null), elle renverra une chaîne vide (``).
COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)" : Cette ligne calcule la différence de temps en jours entre la création du sujet et la première réponse. Si la première réponse n’existe pas (c.-à-d. fr.created_at est null), elle renverra 0.
COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)" : Cette ligne calcule la différence de temps en heures entre la création du sujet et la première réponse. Si la première réponse n’existe pas (c.-à-d. fr.created_at est null), elle renverra 0.
COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)" : Cette ligne calcule la différence de temps en jours entre la création du sujet et la solution. Si la solution n’existe pas (c.-à-d. st.created_at est null), elle renverra 0.
COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)" : Cette ligne calcule la différence de temps en heures entre la création du sujet et la solution. Si la solution n’existe pas (c.-à-d. st.created_at est null), elle renverra 0.

Dans tous ces cas, COALESCE est utilisé pour empêcher l’apparition de valeurs NULL dans les résultats finaux, ce qui améliore la lisibilité de la requête résultante et peut être utile pour un traitement ou une analyse ultérieure des données.