Umgang mit Nullwerten mit COALESCE

Gemeinschaft Daten & Berichterstattung
1

In diesem Tutorial erkunden wir die Verwendung der Funktion COALESCE in Data Explorer SQL-Abfragen.

COALESCE ermöglicht es Ihnen, NULL-Werte in Ihren Abfrageergebnissen zu behandeln. Wenn Ihre Daten NULL-Werte enthalten, können Sie COALESCE verwenden, um einen Standardwert (wie 0) für diese NULL-Werte bereitzustellen.

COALESCE ist besonders nützlich, wenn Sie später Berechnungen oder Datenanalysen auf Ihren Abfrageergebnissen durchführen, bei denen NULL-Werte potenziell Probleme oder Fehlinterpretationen verursachen könnten.

Syntax

Die Funktion COALESCE nimmt zwei oder mehr Argumente entgegen und gibt den ersten Nicht-NULL-Wert zurück, den sie von links nach rechts in der Liste findet. Wenn alle Argumente NULL sind, gibt COALESCE NULL zurück.

Die grundlegende Syntax für COALESCE lautet wie folgt:

COALESCE(value1, value2, ..., valueN)

Beispielsweise gibt COALESCE(NULL, 1, 2) den Wert 1 zurück, da 1 das erste Nicht-NULL-Argument ist.

Beispielabfragen

Lassen Sie uns einige Beispielabfragen untersuchen, um zu verstehen, wie COALESCE in Data Explorer-Abfragen verwendet wird.

Erstellte Beiträge, erhaltene Likes und erhaltene Lesezeichen

Schwierigkeitsgrad: Anfänger

Diese Abfrage ermittelt die Gesamtzahl der erstellten Beiträge, der erhaltenen Likes und der erhaltenen Lesezeichen für jeden Benutzer auf einer Website. Wenn ein Benutzer keine Beiträge, Likes oder Lesezeichen hat, gibt die Funktion COALESCE 0 statt NULL zurück.

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

Beispielergebnisse:

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

In dieser Abfrage verbinden wir die Tabelle users mit der Tabelle posts über das Feld user_id. Anschließend verwenden wir die Funktion COALESCE, um sicherzustellen, dass, falls ein Benutzer keine Beiträge, erhaltenen Likes oder Lesezeichen hat, 0 statt NULL zurückgegeben wird. Die Ergebnisse werden nach Benutzer-ID und Benutzernamen gruppiert und nach der Anzahl der Beiträge, Likes und Lesezeichen in absteigender Reihenfolge sortiert.

Themen und Antworten pro Benutzer

Schwierigkeitsgrad: Fortgeschritten

Diese Abfrage ermittelt die Anzahl der Themen und Antworten, die von jedem Benutzer zwischen zwei Daten erstellt wurden. Wenn ein Benutzer keine Themen oder Antworten hat, gibt COALESCE 0 statt NULL zurück.

-- [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)||'%'

Beispielergebnisse:

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

In dieser Abfrage wird COALESCE in der Common Table Expression (CTE) total verwendet. Sie stellt sicher, dass, falls die user_id in entweder qtt_topics oder qtt_replies NULL ist, der andere Wert verwendet wird. Dies ist wichtig, da ein FULL JOIN verwendet wird, um qtt_topics und qtt_replies zu kombinieren, und wenn ein Benutzer nur Themen, aber keine Antworten hat (oder umgekehrt), wäre seine user_id in einer der Tabellen NULL. COALESCE verhindert dies.

Detaillierte Erklärung mit Inline-Kommentaren 
-- [params]
-- date :start_date 
-- date :end_date
-- string NULL:username

-- Definiere eine CTE (Common Table Expression) zum Zählen der Themen pro Benutzer
WITH qtt_topics AS (
    SELECT 
        t.user_id,  -- Benutzer-ID
        COUNT(*) AS topics  -- Anzahl der Themen
    FROM topics t  -- aus der Tabelle topics
    WHERE    
        t.user_id > 0  -- nur Benutzer-IDs ungleich Null berücksichtigen
        AND t.deleted_at ISNULL  -- nur nicht gelöschte Themen berücksichtigen
        AND t.archetype = 'regular'  -- nur reguläre Themen berücksichtigen
        AND t.created_at::date BETWEEN :start_date AND :end_date  -- nur Themen berücksichtigen, die zwischen start_date und end_date erstellt wurden
    GROUP BY t.user_id  -- nach Benutzer-ID gruppieren, um die Anzahl der Themen pro Benutzer zu erhalten
),
    
-- Definiere eine CTE zum Zählen der Antworten pro Benutzer
qtt_replies AS (
    SELECT 
        p.user_id,  -- Benutzer-ID
        COUNT(*) AS replies  -- Anzahl der Antworten
    FROM posts p  -- aus der Tabelle posts
    WHERE    
        p.user_id > 0  -- nur Benutzer-IDs ungleich Null berücksichtigen
        AND p.deleted_at ISNULL  -- nur nicht gelöschte Beiträge berücksichtigen
        AND p.post_number != 1  -- nur Beiträge berücksichtigen, die nicht der erste Beitrag in einem Thema sind (d. h. Antworten)
        AND p.created_at::date BETWEEN :start_date AND :end_date  -- nur Beiträge berücksichtigen, die zwischen start_date und end_date erstellt wurden
    GROUP BY p.user_id  -- nach Benutzer-ID gruppieren, um die Anzahl der Antworten pro Benutzer zu erhalten
),

-- Definiere eine CTE, um die Anzahl der Themen und Antworten pro Benutzer zu kombinieren
total AS (
    SELECT
        COALESCE(qr.user_id, qt.user_id) user_id,  -- Benutzer-ID (entweder aus qtt_replies oder qtt_topics)
        COALESCE(topics,0) qtt_topics,  -- Anzahl der Themen (falls NULL, gib 0 zurück)
        COALESCE(replies,0) qtt_replies  -- Anzahl der Antworten (falls NULL, gib 0 zurück)
    FROM qtt_topics qt  -- aus der CTE qtt_topics
    FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id  -- mit der CTE qtt_replies über die Benutzer-ID verbinden
    ORDER BY user_id  -- nach Benutzer-ID sortieren
)

-- Hauptabfrage, um das endgültige Ergebnisset zu erhalten
SELECT 
    username,  -- Benutzername
    qtt_topics,  -- Anzahl der Themen
    qtt_replies  -- Anzahl der Antworten
FROM total  -- aus der CTE total
INNER JOIN users u ON u.id = user_id  -- mit der Tabelle users über die Benutzer-ID verbinden
WHERE :username ISNULL OR LOWER(username) LIKE '%'||LOWER(:username)||'%'  -- nach Benutzername filtern (falls angegeben)

Gelöste Fragen-Daten

Schwierigkeitsgrad: Experte / Erfordert das Plugin Discourse Solved

Diese Abfrage wird verwendet, um Details über Themen zu erhalten, einschließlich ob sie gelöst sind oder nicht, die Zeit bis zur ersten Antwort, die Zeit bis zur Lösung und andere verwandte Statistiken.

Diese Abfrage geht davon aus, dass alle Themen auf einer Website gelöst werden können.

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

Beispielergebnisse:

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

In dieser Abfrage wird COALESCE in den folgenden Zeilen verwendet:

  • COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create: Diese Zeile konvertiert das created_at-Datum der ersten Antwort in einen String. Wenn die erste Antwort nicht existiert (d. h. fr.created_at ist null), wird eine leere Zeichenkette (‘’) zurückgegeben.
  • COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create: Ähnlich wie oben konvertiert diese Zeile das created_at-Datum der Lösung in einen String. Wenn die Lösung nicht existiert (d. h. st.created_at ist null), wird eine leere Zeichenkette (‘’) zurückgegeben.
  • COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)": Diese Zeile berechnet die Zeitdifferenz in Tagen zwischen der Erstellung des Themas und der ersten Antwort. Wenn die erste Antwort nicht existiert (d. h. fr.created_at ist null), wird 0 zurückgegeben.
  • COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)": Diese Zeile berechnet die Zeitdifferenz in Stunden zwischen der Erstellung des Themas und der ersten Antwort. Wenn die erste Antwort nicht existiert (d. h. fr.created_at ist null), wird 0 zurückgegeben.
  • COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)": Diese Zeile berechnet die Zeitdifferenz in Tagen zwischen der Erstellung des Themas und der Lösung. Wenn die Lösung nicht existiert (d. h. st.created_at ist null), wird 0 zurückgegeben.
  • COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)": Diese Zeile berechnet die Zeitdifferenz in Stunden zwischen der Erstellung des Themas und der Lösung. Wenn die Lösung nicht existiert (d. h. st.created_at ist null), wird 0 zurückgegeben.

In all diesen Fällen wird COALESCE verwendet, um zu verhindern, dass NULL-Werte in den endgültigen Ergebnissen erscheinen, was die Lesbarkeit der resultierenden Abfrage verbessert und für spätere Datenverarbeitung oder -analyse hilfreich sein könnte.

Detaillierte Erklärung mit Inline-Kommentaren 
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all

-- Definiere eine CTE für gültige Themen
WITH valid_topics AS (
    -- Wähle notwendige Felder aus
    SELECT 
        t.id,  -- Themen-ID
        t.user_id,  -- Benutzer-ID
        t.title,  -- Thementitel
        t.views,  -- Anzahl der Aufrufe
        posts_count-1 AS "posts_count",  -- Anzahl der Beiträge im Thema
        t.created_at,  -- Erstellungsdatum des Themas
        (CURRENT_DATE::date-t.created_at::date) AS "total_days",  -- Gesamttage seit Erstellung des Themas
        string_agg(tags.name, ', ') AS tag_names  -- Aggregiere alle mit dem Thema verbundenen Tags
    FROM topics t  -- aus der Tabelle topics
    -- Verbinde notwendige Tabellen, um Tag-Namen zu erhalten
    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
    -- Subabfrage, um das Datum der ersten Antwort für jedes Thema zu erhalten
    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  -- nur nicht gelöschte Themen berücksichtigen
        AND t.created_at::date BETWEEN :start_date AND :end_date  -- nur Themen berücksichtigen, die zwischen start_date und end_date erstellt wurden
        AND t.archetype = 'regular'  -- nur reguläre Themen berücksichtigen
    GROUP BY t.id, t.user_id, t.title, t.views, posts_count, t.created_at  -- nach notwendigen Feldern gruppieren, um die korrekte Anzahl zu erhalten
),

-- Definiere eine CTE für gelöste Themen
solved_topics AS (
    -- Wähle Themen-ID und Erstellungsdatum der Lösung aus
    SELECT 
        vt.id,
        tcf.created_at
    FROM topic_custom_fields tcf  -- aus der Tabelle topic_custom_fields
    INNER JOIN valid_topics vt ON vt.id = tcf.topic_id  -- mit der CTE valid_topics verbinden
    WHERE tcf.name = 'accepted_answer_post_id'  -- nur Themen mit einer akzeptierten Antwort berücksichtigen
),

-- Definiere eine CTE für die letzte Antwort jedes Themas
last_reply AS (
    -- Wähle Themen-ID und Benutzer-ID der letzten Antwort aus
    SELECT p.topic_id, p.user_id FROM posts p
    INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p  -- Subabfrage, um die ID des letzten Beitrags für jedes Thema zu erhalten
                WHERE deleted_at ISNULL
                AND post_type = 1
                GROUP BY topic_id) x ON x.post = p.id  -- mit der Tabelle posts verbinden, um die Benutzer-ID der letzten Antwort zu erhalten
),

-- Definiere eine CTE für die erste Antwort jedes Themas
first_reply AS (
    -- Wähle Themen-ID, Benutzer-ID und Erstellungsdatum der ersten Antwort aus
    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  -- Subabfrage, um die ID der ersten Antwort für jedes Thema zu erhalten
                WHERE deleted_at ISNULL
                AND post_type = 1
                AND post_number > 1
                GROUP BY topic_id) x ON x.post = p.id  -- mit der Tabelle posts verbinden, um die Benutzer-ID und das Erstellungsdatum der ersten Antwort zu erhalten
)

-- Hauptabfrage, um das endgültige Ergebnisset zu erhalten
SELECT
    CASE 
        WHEN st.id IS NOT NULL THEN 'solved'  -- wenn die Themen-ID in der CTE solved_topics enthalten ist, dann ist der Status 'solved'
        ELSE 'unsolved'  -- sonst ist der Status 'unsolved'
    END AS status,
    vt.tag_names,  -- Tag-Namen
    vt.id AS topic_id,  -- Themen-ID
    vt.user_id topic_user_id,  -- Benutzer-ID
    ue.email,  -- Benutzer-E-Mail
    vt.title,  -- Thementitel
    vt.views,  -- Anzahl der Aufrufe
    lr.user_id AS last_reply_user_id,  -- Benutzer-ID der letzten Antwort
    ue2.email AS last_reply_user_email,  -- E-Mail des Benutzers, der die letzte Antwort gegeben hat
    vt.created_at::date topic_create,  -- Erstellungsdatum des Themas
    COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create,  -- Erstellungsdatum der ersten Antwort, falls vorhanden, sonst leere Zeichenkette
    COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create,  -- Erstellungsdatum der Lösung, falls vorhanden, sonst leere Zeichenkette
    COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)",  -- Zeit bis zur ersten Antwort in Tagen
    COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)",  -- Zeit bis zur ersten Antwort in Stunden
    COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)",  -- Zeit bis zur Lösung in Tagen
    COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)",  -- Zeit bis zur Lösung in Stunden
    posts_count AS number_of_replies,  -- Anzahl der Antworten
    total_days AS total_days_without_solution  -- Gesamttage ohne Lösung
FROM valid_topics vt  -- aus der CTE valid_topics
LEFT JOIN last_reply lr ON lr.topic_id = vt.id  -- mit der CTE last_reply verbinden
LEFT JOIN first_reply fr ON fr.topic_id = vt.id  -- mit der CTE first_reply verbinden
LEFT JOIN solved_topics st ON st.id = vt.id  -- mit der CTE solved_topics verbinden
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true  -- mit der Tabelle user_emails verbinden, um die E-Mail des Benutzers zu erhalten
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true  -- mit der Tabelle user_emails verbinden, um die E-Mail des Benutzers zu erhalten, der die letzte Antwort gegeben hat
WHERE (:tag_name =  'all' OR LOWER(vt.tag_names) LIKE '%' || LOWER(:tag_name) || '%')  -- nach Tag-Namen filtern
ORDER BY tag_names, total_days DESC  -- nach Tag-Namen und Gesamttagen in absteigender Reihenfolge sortieren

Wenn Sie Fragen haben oder Beispiele dafür teilen möchten, wie Sie COALESCE in Ihren Data Explorer-Abfragen verwendet haben, teilen Sie diese gerne unten mit. :slightly_smiling_face:

5 „Gefällt mir“