Manejo de Valores Nulos con COALESCE

SaraDev · 13 Septiembre, 2023 01:15

En este tutorial, exploraremos el uso de la función COALESCE en consultas SQL de Data Explorer.

COALESCE te permite manejar valores NULL en los resultados de tus consultas. Si tienes valores NULL en tus datos, puedes usar COALESCE para proporcionar un valor predeterminado (como 0) para estos valores NULL.

COALESCE es particularmente útil cuando podrías estar realizando cálculos posteriores o análisis de datos en los resultados de tu consulta, donde los valores NULL podrían causar problemas o interpretaciones erróneas.

Sintaxis

La función COALESCE toma dos o más argumentos y devuelve el primer valor no NULL que encuentra de izquierda a derecha en la lista. Si todos los argumentos son NULL, COALESCE devuelve NULL.

La sintaxis básica para COALESCE es la siguiente:

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

Por ejemplo, COALESCE(NULL, 1, 2) devolverá 1 porque 1 es el primer argumento no NULL.

Consultas de Ejemplo

Examinemos algunas consultas de ejemplo para entender cómo se usa COALESCE en las consultas de Data Explorer.

Publicaciones Creadas, Me Gusta Recibidos y Marcadores Recibidos

Nivel de complejidad: Principiante

Esta consulta obtendrá el número total de publicaciones creadas, me gusta recibidos y marcadores recibidos en publicaciones para cada usuario en un sitio. Si un usuario no tiene publicaciones, me gusta o marcadores recibidos, la función COALESCE devolverá 0 en lugar 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 ejemplo:

usuario	nombre de usuario	conteo de publicaciones	me gusta recibidos	marcadores recibidos
1	alice	345	6	9
2	bella	278	5	6
3	charlie	37	3	3
4	dave	0	0	0

En esta consulta, unimos la tabla users con la tabla posts en el campo user_id. Luego usamos la función COALESCE para asegurar que, si un usuario no tiene publicaciones, me gusta recibidos o marcadores recibidos, devolvamos 0 en lugar de NULL. Los resultados se agrupan por ID de usuario y nombre de usuario, y se ordenan por el número de publicaciones, me gusta y marcadores en orden descendente.

Temas y Respuestas por Usuario

Nivel de complejidad: Intermedio

Esta consulta obtiene el conteo de temas y respuestas realizados por cada usuario entre dos fechas. Si un usuario no tiene temas o respuestas, COALESCE devolverá 0 en lugar de NULL.

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

WITH qtt_topics AS (
    SELECT 
        t.user_id,
        COUNT(*) AS temas
    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 respuestas
    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(temas,0) qtt_topics,
        COALESCE(respuestas,0) qtt_replies
    FROM qtt_topics qt
    FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id
    ORDER BY user_id)

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

Resultados de ejemplo:

nombre de usuario	qtt_topics	qtt_replies
Alice	10	50
Bella	15	45
Charlie	12	30

En esta consulta, COALESCE se usa en la expresión de tabla común (CTE) total. Asegura que si el user_id es NULL en qtt_topics o qtt_replies, se use el otro valor. Esto es importante porque se usa un FULL JOIN para combinar qtt_topics y qtt_replies, y si un usuario solo tiene temas pero no respuestas (o viceversa), su user_id sería NULL en una de las tablas. COALESCE evita que esto suceda.

Explicación detallada con comentarios en línea

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

-- Definir una CTE (Expresión de Tabla Común) para contar temas por usuario
WITH qtt_topics AS (
    SELECT 
        t.user_id,  -- id de usuario
        COUNT(*) AS temas  -- conteo de temas
    FROM topics t  -- desde la tabla de temas
    WHERE    
        t.user_id > 0  -- solo considerar ids de usuario distintos de cero
        AND t.deleted_at ISNULL  -- solo considerar temas que no han sido eliminados
        AND t.archetype = 'regular'  -- solo considerar temas regulares
        AND t.created_at::date BETWEEN :start_date AND :end_date  -- solo considerar temas creados entre start_date y end_date
    GROUP BY t.user_id  -- agrupar por id de usuario para obtener el conteo de temas por usuario
),
    
-- Definir una CTE para contar respuestas por usuario
qtt_replies AS (
    SELECT 
        p.user_id,  -- id de usuario
        COUNT(*) AS respuestas  -- conteo de respuestas
    FROM posts p  -- desde la tabla de publicaciones
    WHERE    
        p.user_id > 0  -- solo considerar ids de usuario distintos de cero
        AND p.deleted_at ISNULL  -- solo considerar publicaciones que no han sido eliminadas
        AND p.post_number != 1  -- solo considerar publicaciones que no son la primera en un tema (es decir, respuestas)
        AND p.created_at::date BETWEEN :start_date AND :end_date  -- solo considerar publicaciones creadas entre start_date y end_date
    GROUP BY p.user_id  -- agrupar por id de usuario para obtener el conteo de respuestas por usuario
),

-- Definir una CTE para combinar los conteos de temas y respuestas por usuario
total AS (
    SELECT
        COALESCE(qr.user_id, qt.user_id) user_id,  -- id de usuario (de qtt_replies o qtt_topics)
        COALESCE(temas,0) qtt_topics,  -- conteo de temas (si es NULL, devolver 0)
        COALESCE(respuestas,0) qtt_replies  -- conteo de respuestas (si es NULL, devolver 0)
    FROM qtt_topics qt  -- desde la CTE qtt_topics
    FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id  -- unir con la CTE qtt_replies en el id de usuario
    ORDER BY user_id  -- ordenar por id de usuario
)

-- Consulta principal para obtener el conjunto de resultados final
SELECT 
    nombre de usuario,  -- nombre de usuario
    qtt_topics,  -- conteo de temas
    qtt_replies  -- conteo de respuestas
FROM total  -- desde la CTE total
INNER JOIN users u ON u.id = user_id  -- unir con la tabla de usuarios en el id de usuario
WHERE :username ISNULL OR LOWER(nombre de usuario) LIKE '%'||LOWER(:username)||'%'  -- filtrar por nombre de usuario (si se proporciona)

Datos de Preguntas Resueltas

Nivel de complejidad: Avanzado / Requiere el plugin Discourse Solved

Esta consulta se usa para obtener detalles sobre temas, incluyendo si están resueltos o no, el tiempo tomado para la primera respuesta, el tiempo tomado para la solución y otras estadísticas relacionadas.

Esta consulta asume que todos los temas en un sitio pueden resolverse.

--[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 'resuelto'
        ELSE 'no resuelto'
    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 "tiempo_primera_respuesta(días)",
    COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "tiempo_primera_respuesta(horas)",
    COALESCE(st.created_at::date - vt.created_at::date,0) AS "tiempo_solución(días)",
    COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "tiempo_solución(horas)",
    posts_count AS numero_de_respuestas,
    total_days AS total_dias_sin_solucion
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 ejemplo:

estado	tag_names	tema	usuario del tema	correo electrónico	título	vistas	usuario de la última respuesta	correo electrónico del usuario de la última respuesta	fecha_creación_tema	fecha_creación_primera_respuesta	fecha_creación_solución	tiempo_primera_respuesta(días)	tiempo_primera_respuesta(horas)	tiempo_solución(días)	tiempo_solución(horas)	numero_de_respuestas	total_dias_sin_solucion
resuelto	a, c, b	Un Título de Tema (7)	alice	alice@example.com	Un Título de Tema	58	bella	bella@example.com	2023-08-25	2023-08-25	2023-08-29	0	1	1	24	9	4
no resuelto	tag1	Bienvenido al Salón (3)	system	no_email	Bienvenido al Salón	3	system	no_email	2023-05-01			0	0	0	0	2	134

En esta consulta, COALESCE se usa en las siguientes líneas:

COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create: Esta línea convierte la fecha created_at de la primera respuesta a una cadena. Si la primera respuesta no existe (es decir, fr.created_at es null), devolverá una cadena vacía (``).
COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create: Similar a lo anterior, esta línea convierte la fecha created_at de la solución a una cadena. Si la solución no existe (es decir, st.created_at es null), devolverá una cadena vacía (``).
COALESCE(fr.created_at::date - vt.created_at::date,0) AS "tiempo_primera_respuesta(días)": Esta línea calcula la diferencia de tiempo en días entre la creación del tema y la primera respuesta. Si la primera respuesta no existe (es decir, fr.created_at es null), devolverá 0.
COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "tiempo_primera_respuesta(horas)": Esta línea calcula la diferencia de tiempo en horas entre la creación del tema y la primera respuesta. Si la primera respuesta no existe (es decir, fr.created_at es null), devolverá 0.
COALESCE(st.created_at::date - vt.created_at::date,0) AS "tiempo_solución(días)": Esta línea calcula la diferencia de tiempo en días entre la creación del tema y la solución. Si la solución no existe (es decir, st.created_at es null), devolverá 0.
COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "tiempo_solución(horas)": Esta línea calcula la diferencia de tiempo en horas entre la creación del tema y la solución. Si la solución no existe (es decir, st.created_at es null), devolverá 0.

En todos estos casos, COALESCE se usa para evitar que aparezcan valores NULL en los resultados finales, lo que mejora la legibilidad de la consulta resultante y podría ser útil para un posterior procesamiento o análisis de datos.

Explicación detallada con comentarios en línea

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

-- Definir CTE para temas válidos
WITH valid_topics AS (
    -- Seleccionar campos necesarios
    SELECT 
        t.id,  -- id del tema
        t.user_id,  -- id del usuario
        t.title,  -- título del tema
        t.views,  -- número de vistas
        posts_count-1 AS "posts_count",  -- número de publicaciones en el tema
        t.created_at,  -- fecha de creación del tema
        (CURRENT_DATE::date-t.created_at::date) AS "total_days",  -- total de días desde que se creó el tema
        string_agg(tags.name, ', ') AS tag_names  -- agregar todas las etiquetas asociadas con el tema
    FROM topics t  -- desde la tabla de temas
    -- Unir tablas necesarias para obtener los nombres de las etiquetas
    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 obtener la fecha de la primera respuesta para cada tema
    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  -- solo considerar temas que no han sido eliminados
        AND t.created_at::date BETWEEN :start_date AND :end_date  -- solo considerar temas creados entre start_date y end_date
        AND t.archetype = 'regular'  -- solo considerar temas regulares
    GROUP BY t.id, t.user_id, t.title, t.views, posts_count, t.created_at  -- agrupar por campos necesarios para obtener el conteo correcto
),

-- Definir CTE para temas resueltos
solved_topics AS (
    -- Seleccionar id del tema y fecha de creación de la solución
    SELECT 
        vt.id,
        tcf.created_at
    FROM topic_custom_fields tcf  -- desde la tabla topic_custom_fields
    INNER JOIN valid_topics vt ON vt.id = tcf.topic_id  -- unir con la CTE valid_topics
    WHERE tcf.name = 'accepted_answer_post_id'  -- solo considerar temas con una respuesta aceptada
),

-- Definir CTE para la última respuesta de cada tema
last_reply AS (
    -- Seleccionar id del tema e id del usuario de la última respuesta
    SELECT p.topic_id, p.user_id FROM posts p
    INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p  -- subconsulta para obtener el id de la última publicación para cada tema
                WHERE deleted_at ISNULL
                AND post_type = 1
                GROUP BY topic_id) x ON x.post = p.id  -- unir con la tabla de publicaciones para obtener el id del usuario de la última respuesta
),

-- Definir CTE para la primera respuesta de cada tema
first_reply AS (
    -- Seleccionar id del tema, id del usuario y fecha de creación de la primera respuesta
    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 obtener el id de la primera respuesta para cada tema
                WHERE deleted_at ISNULL
                AND post_type = 1
                AND post_number > 1
                GROUP BY topic_id) x ON x.post = p.id  -- unir con la tabla de publicaciones para obtener el id del usuario y la fecha de creación de la primera respuesta
)

-- Consulta principal para obtener el conjunto de resultados final
SELECT
    CASE 
        WHEN st.id IS NOT NULL THEN 'resuelto'  -- si el id del tema está en la CTE solved_topics, entonces el estado es 'resuelto'
        ELSE 'no resuelto'  -- de lo contrario, el estado es 'no resuelto'
    END AS status,
    vt.tag_names,  -- nombres de las etiquetas
    vt.id AS topic_id,  -- id del tema
    vt.user_id topic_user_id,  -- id del usuario
    ue.email,  -- correo electrónico del usuario
    vt.title,  -- título del tema
    vt.views,  -- número de vistas
    lr.user_id AS last_reply_user_id,  -- id del usuario de la última respuesta
    ue2.email AS last_reply_user_email,  -- correo electrónico del usuario que hizo la última respuesta
    vt.created_at::date topic_create,  -- fecha de creación del tema
    COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create,  -- fecha de creación de la primera respuesta, si existe, de lo contrario cadena vacía
    COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create,  -- fecha de creación de la solución, si existe, de lo contrario cadena vacía
    COALESCE(fr.created_at::date - vt.created_at::date,0) AS "tiempo_primera_respuesta(días)",  -- tiempo tomado para la primera respuesta en días
    COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "tiempo_primera_respuesta(horas)",  -- tiempo tomado para la primera respuesta en horas
    COALESCE(st.created_at::date - vt.created_at::date,0) AS "tiempo_solución(días)",  -- tiempo tomado para la solución en días
    COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "tiempo_solución(horas)",  -- tiempo tomado para la solución en horas
    posts_count AS numero_de_respuestas,  -- número de respuestas
    total_days AS total_dias_sin_solucion  -- total de días sin solución
FROM valid_topics vt  -- desde la CTE valid_topics
LEFT JOIN last_reply lr ON lr.topic_id = vt.id  -- unir con la CTE last_reply
LEFT JOIN first_reply fr ON fr.topic_id = vt.id  -- unir con la CTE first_reply
LEFT JOIN solved_topics st ON st.id = vt.id  -- unir con la CTE solved_topics
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true  -- unir con la tabla user_emails para obtener el correo electrónico del usuario
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true  -- unir con la tabla user_emails para obtener el correo electrónico del usuario que hizo la última respuesta
WHERE (:tag_name =  'all' OR LOWER(vt.tag_names) LIKE '%' || LOWER(:tag_name) || '%')  -- filtrar por nombre de etiqueta
ORDER BY tag_names, total_days DESC  -- ordenar por nombres de etiquetas y total de días en orden descendente

Si tienes alguna pregunta o ejemplos de cómo has usado COALESCE en tus consultas de Data Explorer, siéntete libre de compartirlos a continuación.

Tema		Respuestas	Vistas
Data explorer doesn't show email address with a where clause Bug	5	804	20 Septiembre 2017
Topics stats of Solved/Unsolved topics, inc. 'time to solution' Data & reporting sql-query , solved	0	190	22 Abril 2023
SQL query in Data Explorer to pull latest topics? Data & reporting sql-query	11	2877	21 Noviembre 2022
Attempting to create Monthly Leaderboard query - head exploding Data & reporting sql-query	19	1793	8 Junio 2024
Find posts "solved" in specific month Data & reporting solved , sql-query	4	1575	29 Marzo 2019

Manejo de Valores Nulos con COALESCE

Sintaxis

Consultas de Ejemplo

Publicaciones Creadas, Me Gusta Recibidos y Marcadores Recibidos

Temas y Respuestas por Usuario

Datos de Preguntas Resueltas

Temas relacionados