Tratando valores nulos com COALESCE

Comunidade Dados e relatórios
1

Neste tutorial, exploraremos o uso da função COALESCE em consultas SQL do Data Explorer.

O COALESCE permite lidar com valores NULL nos resultados da sua consulta. Se houver valores NULL nos seus dados, você pode usar o COALESCE para fornecer um valor padrão (como 0) para esses valores NULL.

O COALESCE é particularmente útil quando você pode estar realizando cálculos ou análises de dados posteriores nos resultados da sua consulta, onde valores NULL poderiam causar problemas ou interpretações equivocadas.

Sintaxe

A função COALESCE aceita dois ou mais argumentos e retorna o primeiro valor não NULL que encontra da esquerda para a direita na lista. Se todos os argumentos forem NULL, o COALESCE retorna NULL.

A sintaxe básica para o COALESCE é a seguinte:

COALESCE(valor1, valor2, ..., valorN)

Por exemplo, COALESCE(NULL, 1, 2) retornará 1, pois 1 é o primeiro argumento não NULL.

Consultas de Exemplo

Vamos examinar algumas consultas de exemplo para entender como o COALESCE é usado em consultas do Data Explorer.

Posts Criados, Curtidas Recebidas e Marcadores Recebidos

Nível de Complexidade: Iniciante

Esta consulta obterá o número total de posts criados, curtidas recebidas e marcadores recebidos em posts para cada usuário em um site. Se um usuário não tiver posts, curtidas ou marcadores recebidos, a função COALESCE retornará 0 em vez 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

Resultados de Exemplo:

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

Nesta consulta, estamos unindo a tabela users com a tabela posts no campo user_id. Em seguida, usamos a função COALESCE para garantir que, se um usuário não tiver posts, curtidas recebidas ou marcadores recebidos, retornemos 0 em vez de NULL. Os resultados são agrupados por ID do usuário e nome de usuário, e ordenados pelo número de posts, curtidas e marcadores em ordem decrescente.

Tópicos e Respostas por Usuários

Nível de Complexidade: Intermediário

Esta consulta obtém a contagem de tópicos e respostas feitos por cada usuário entre duas datas. Se um usuário não tiver tópicos ou respostas, o COALESCE retornará 0 em vez 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)||'%'

Resultados de Exemplo:

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

Nesta consulta, o COALESCE é usado na Expressão de Tabela Comum (CTE) total. Ele garante que, se o user_id for NULL em qtt_topics ou qtt_replies, o outro valor seja usado. Isso é importante porque um FULL JOIN é usado para combinar qtt_topics e qtt_replies, e se um usuário tiver apenas tópicos, mas nenhuma resposta (ou vice-versa), seu user_id seria NULL em uma das tabelas. O COALESCE evita que isso aconteça.

Explicação Detalhada com Comentários Inline 
-- [params]
-- date :start_date 
-- date :end_date
-- string NULL:username

-- Define uma CTE (Expressão de Tabela Comum) para contar tópicos por usuário
WITH qtt_topics AS (
    SELECT 
        t.user_id,  -- id do usuário
        COUNT(*) AS topics  -- contagem de tópicos
    FROM topics t  -- da tabela topics
    WHERE    
        t.user_id > 0  -- considerar apenas IDs de usuário não zero
        AND t.deleted_at ISNULL  -- considerar apenas tópicos que não foram excluídos
        AND t.archetype = 'regular'  -- considerar apenas tópicos regulares
        AND t.created_at::date BETWEEN :start_date AND :end_date  -- considerar apenas tópicos criados entre start_date e end_date
    GROUP BY t.user_id  -- agrupar por id do usuário para obter a contagem de tópicos por usuário
),
    
-- Define uma CTE para contar respostas por usuário
qtt_replies AS (
    SELECT 
        p.user_id,  -- id do usuário
        COUNT(*) AS replies  -- contagem de respostas
    FROM posts p  -- da tabela posts
    WHERE    
        p.user_id > 0  -- considerar apenas IDs de usuário não zero
        AND p.deleted_at ISNULL  -- considerar apenas posts que não foram excluídos
        AND p.post_number != 1  -- considerar apenas posts que não são o primeiro post de um tópico (ou seja, respostas)
        AND p.created_at::date BETWEEN :start_date AND :end_date  -- considerar apenas posts criados entre start_date e end_date
    GROUP BY p.user_id  -- agrupar por id do usuário para obter a contagem de respostas por usuário
),

-- Define uma CTE para combinar as contagens de tópicos e respostas por usuário
total AS (
    SELECT
        COALESCE(qr.user_id, qt.user_id) user_id,  -- id do usuário (de qtt_replies ou qtt_topics)
        COALESCE(topics,0) qtt_topics,  -- contagem de tópicos (se NULL, retorna 0)
        COALESCE(replies,0) qtt_replies  -- contagem de respostas (se NULL, retorna 0)
    FROM qtt_topics qt  -- da CTE qtt_topics
    FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id  -- unir com a CTE qtt_replies no id do usuário
    ORDER BY user_id  -- ordenar por id do usuário
)

-- Consulta principal para obter o conjunto final de resultados
SELECT 
    username,  -- nome de usuário
    qtt_topics,  -- contagem de tópicos
    qtt_replies  -- contagem de respostas
FROM total  -- da CTE total
INNER JOIN users u ON u.id = user_id  -- unir com a tabela users no id do usuário
WHERE :username ISNULL OR LOWER(username) LIKE '%'||LOWER(:username)||'%'  -- filtrar por nome de usuário (se fornecido)

Dados de Perguntas Resolvidas

Nível de Complexidade: Avançado / Requer o Plugin Discourse Solved

Esta consulta é usada para obter detalhes sobre tópicos, incluindo se foram resolvidos ou não, o tempo gasto para a primeira resposta, o tempo gasto para a solução e outras estatísticas relacionadas.

Esta consulta assume que todos os tópicos em um site podem ser resolvidos.

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

Resultados de Exemplo:

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

Nesta consulta, o COALESCE é usado nas seguintes linhas:

  • COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create: Esta linha converte a data created_at da primeira resposta para uma string. Se a primeira resposta não existir (ou seja, fr.created_at for nulo), retornará uma string vazia (``).
  • COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create: Similar ao anterior, esta linha converte a data created_at da solução para uma string. Se a solução não existir (ou seja, st.created_at for nulo), retornará uma string vazia (``).
  • COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)": Esta linha calcula a diferença de tempo em dias entre a criação do tópico e a primeira resposta. Se a primeira resposta não existir (ou seja, fr.created_at for nulo), retornará 0.
  • COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)": Esta linha calcula a diferença de tempo em horas entre a criação do tópico e a primeira resposta. Se a primeira resposta não existir (ou seja, fr.created_at for nulo), retornará 0.
  • COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)": Esta linha calcula a diferença de tempo em dias entre a criação do tópico e a solução. Se a solução não existir (ou seja, st.created_at for nulo), retornará 0.
  • COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)": Esta linha calcula a diferença de tempo em horas entre a criação do tópico e a solução. Se a solução não existir (ou seja, st.created_at for nulo), retornará 0.

Em todos esses casos, o COALESCE é usado para evitar que valores NULL apareçam nos resultados finais, o que melhora a legibilidade da consulta resultante e pode ser útil para processamento ou análise de dados posteriores.

Explicação Detalhada com Comentários Inline 
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all

-- Define CTE para tópicos válidos
WITH valid_topics AS (
    -- Selecionar campos necessários
    SELECT 
        t.id,  -- id do tópico
        t.user_id,  -- id do usuário
        t.title,  -- título do tópico
        t.views,  -- número de visualizações
        posts_count-1 AS "posts_count",  -- número de posts no tópico
        t.created_at,  -- data de criação do tópico
        (CURRENT_DATE::date-t.created_at::date) AS "total_days",  -- total de dias desde a criação do tópico
        string_agg(tags.name, ', ') AS tag_names  -- agregar todas as tags associadas ao tópico
    FROM topics t  -- da tabela topics
    -- Unir tabelas necessárias para obter os nomes das tags
    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
    -- Subconsulta para obter a data da primeira resposta para cada tópico
    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  -- considerar apenas tópicos que não foram excluídos
        AND t.created_at::date BETWEEN :start_date AND :end_date  -- considerar apenas tópicos criados entre start_date e end_date
        AND t.archetype = 'regular'  -- considerar apenas tópicos regulares
    GROUP BY t.id, t.user_id, t.title, t.views, posts_count, t.created_at  -- agrupar por campos necessários para obter a contagem correta
),

-- Define CTE para tópicos resolvidos
solved_topics AS (
    -- Selecionar id do tópico e data de criação da solução
    SELECT 
        vt.id,
        tcf.created_at
    FROM topic_custom_fields tcf  -- da tabela topic_custom_fields
    INNER JOIN valid_topics vt ON vt.id = tcf.topic_id  -- unir com a CTE valid_topics
    WHERE tcf.name = 'accepted_answer_post_id'  -- considerar apenas tópicos com uma resposta aceita
),

-- Define CTE para a última resposta de cada tópico
last_reply AS (
    -- Selecionar id do tópico e id do usuário da última resposta
    SELECT p.topic_id, p.user_id FROM posts p
    INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p  -- subconsulta para obter o id do último post de cada tópico
                WHERE deleted_at ISNULL
                AND post_type = 1
                GROUP BY topic_id) x ON x.post = p.id  -- unir com a tabela posts para obter o id do usuário da última resposta
),

-- Define CTE para a primeira resposta de cada tópico
first_reply AS (
    -- Selecionar id do tópico, id do usuário e data de criação da primeira resposta
    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  -- subconsulta para obter o id da primeira resposta de cada tópico
                WHERE deleted_at ISNULL
                AND post_type = 1
                AND post_number > 1
                GROUP BY topic_id) x ON x.post = p.id  -- unir com a tabela posts para obter o id do usuário e a data de criação da primeira resposta
)

-- Consulta principal para obter o conjunto final de resultados
SELECT
    CASE 
        WHEN st.id IS NOT NULL THEN 'solved'  -- se o id do tópico estiver na CTE solved_topics, o status é 'solved'
        ELSE 'unsolved'  -- caso contrário, o status é 'unsolved'
    END AS status,
    vt.tag_names,  -- nomes das tags
    vt.id AS topic_id,  -- id do tópico
    vt.user_id topic_user_id,  -- id do usuário
    ue.email,  -- email do usuário
    vt.title,  -- título do tópico
    vt.views,  -- número de visualizações
    lr.user_id AS last_reply_user_id,  -- id do usuário da última resposta
    ue2.email AS last_reply_user_email,  -- email do usuário que fez a última resposta
    vt.created_at::date topic_create,  -- data de criação do tópico
    COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create,  -- data de criação da primeira resposta, se existir, caso contrário string vazia
    COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create,  -- data de criação da solução, se existir, caso contrário string vazia
    COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)",  -- tempo gasto para a primeira resposta em dias
    COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)",  -- tempo gasto para a primeira resposta em horas
    COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)",  -- tempo gasto para a solução em dias
    COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)",  -- tempo gasto para a solução em horas
    posts_count AS number_of_replies,  -- número de respostas
    total_days AS total_days_without_solution  -- total de dias sem solução
FROM valid_topics vt  -- da CTE valid_topics
LEFT JOIN last_reply lr ON lr.topic_id = vt.id  -- unir com a CTE last_reply
LEFT JOIN first_reply fr ON fr.topic_id = vt.id  -- unir com a CTE first_reply
LEFT JOIN solved_topics st ON st.id = vt.id  -- unir com a CTE solved_topics
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true  -- unir com a tabela user_emails para obter o email do usuário
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true  -- unir com a tabela user_emails para obter o email do usuário que fez a última resposta
WHERE (:tag_name =  'all' OR LOWER(vt.tag_names) LIKE '%' || LOWER(:tag_name) || '%')  -- filtrar por nome da tag
ORDER BY tag_names, total_days DESC  -- ordenar por nomes das tags e total de dias em ordem decrescente

Se você tiver dúvidas ou exemplos de como usou o COALESCE em suas consultas do Data Explorer, sinta-se à vontade para compartilhá-los abaixo. :slightly_smiling_face:

5 curtidas