Обработка NULL-значений с помощью COALESCE

Создание сообщества Данные и отчетность
1

В этом руководстве мы рассмотрим использование функции COALESCE в SQL-запросах Data Explorer.

COALESCE позволяет обрабатывать значения NULL в результатах вашего запроса. Если в ваших данных есть значения NULL, вы можете использовать COALESCE, чтобы указать значение по умолчанию (например, 0) для этих NULL-значений.

COALESCE особенно полезен, когда вы планируете выполнять последующие вычисления или анализ данных на основе результатов запроса, где значения NULL могут вызвать проблемы или неверную интерпретацию.

Синтаксис

Функция COALESCE принимает два или более аргумента и возвращает первое значение, не равное NULL, которое она встречает слева направо в списке. Если все аргументы равны NULL, COALESCE возвращает NULL.

Базовый синтаксис для COALESCE выглядит следующим образом:

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

Например, COALESCE(NULL, 1, 2) вернет 1, так как 1 — это первый аргумент, не равный NULL.

Примеры запросов

Рассмотрим несколько примеров запросов, чтобы понять, как используется COALESCE в запросах Data Explorer.

Созданные посты, полученные лайки и полученные закладки

Уровень сложности: Начальный

Этот запрос получает общее количество созданных постов, полученных лайков и полученных закладок для каждого пользователя на сайте. Если у пользователя нет постов, лайков или закладок, функция COALESCE вернет 0 вместо 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

Пример результатов:

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

В этом запросе мы соединяем таблицу users с таблицей posts по полю user_id. Затем мы используем функцию COALESCE, чтобы гарантировать, что если у пользователя нет постов, полученных лайков или закладок, мы вернем 0 вместо NULL. Результаты группируются по ID пользователя и имени пользователя, а затем сортируются по количеству постов, лайков и закладок в порядке убывания.

Темы и ответы на них по пользователям

Уровень сложности: Средний

Этот запрос получает количество тем и ответов, созданных каждым пользователем между двумя датами. Если у пользователя нет тем или ответов, COALESCE вернет 0 вместо 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)||'%'

Пример результатов:

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

В этом запросе COALESCE используется в общем табличном выражении (CTE) total. Он гарантирует, что если user_id равен NULL в qtt_topics или qtt_replies, будет использовано другое значение. Это важно, потому что используется FULL JOIN для объединения qtt_topics и qtt_replies, и если у пользователя есть только темы, но нет ответов (или наоборот), его user_id будет NULL в одной из таблиц. COALESCE предотвращает это.

Подробное объяснение с комментариями в коде 
-- [params]
-- date :start_date 
-- date :end_date
-- string NULL:username

-- Определение CTE (общего табличного выражения) для подсчета тем на пользователя
WITH qtt_topics AS (
    SELECT 
        t.user_id,  -- id пользователя
        COUNT(*) AS topics  -- количество тем
    FROM topics t  -- из таблицы topics
    WHERE    
        t.user_id > 0  -- учитываем только ненулевые id пользователей
        AND t.deleted_at ISNULL  -- учитываем только темы, которые не удалены
        AND t.archetype = 'regular'  -- учитываем только обычные темы
        AND t.created_at::date BETWEEN :start_date AND :end_date  -- учитываем только темы, созданные между start_date и end_date
    GROUP BY t.user_id  -- группируем по id пользователя, чтобы получить количество тем на пользователя
),
    
-- Определение CTE для подсчета ответов на пользователя
qtt_replies AS (
    SELECT 
        p.user_id,  -- id пользователя
        COUNT(*) AS replies  -- количество ответов
    FROM posts p  -- из таблицы posts
    WHERE    
        p.user_id > 0  -- учитываем только ненулевые id пользователей
        AND p.deleted_at ISNULL  -- учитываем только посты, которые не удалены
        AND p.post_number != 1  -- учитываем только посты, которые не являются первым постом в теме (то есть ответы)
        AND p.created_at::date BETWEEN :start_date AND :end_date  -- учитываем только посты, созданные между start_date и end_date
    GROUP BY p.user_id  -- группируем по id пользователя, чтобы получить количество ответов на пользователя
),

-- Определение CTE для объединения подсчетов тем и ответов на пользователя
total AS (
    SELECT
        COALESCE(qr.user_id, qt.user_id) user_id,  -- id пользователя (из qtt_replies или qtt_topics)
        COALESCE(topics,0) qtt_topics,  -- количество тем (если NULL, возвращаем 0)
        COALESCE(replies,0) qtt_replies  -- количество ответов (если NULL, возвращаем 0)
    FROM qtt_topics qt  -- из CTE qtt_topics
    FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id  -- соединяем с CTE qtt_replies по id пользователя
    ORDER BY user_id  -- сортируем по id пользователя
)

-- Основной запрос для получения итогового набора результатов
SELECT 
    username,  -- имя пользователя
    qtt_topics,  -- количество тем
    qtt_replies  -- количество ответов
FROM total  -- из CTE total
INNER JOIN users u ON u.id = user_id  -- соединяем с таблицей users по id пользователя
WHERE :username ISNULL OR LOWER(username) LIKE '%'||LOWER(:username)||'%'  -- фильтруем по имени пользователя (если указано)

Данные о решенных вопросах

Уровень сложности: Продвинутый / Требуется плагин Discourse Solved

Этот запрос используется для получения информации о темах, включая то, решены они или нет, время до первого ответа, время до решения и другие связанные статистические данные.

Этот запрос предполагает, что все темы на сайте могут быть решены.

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

Пример результатов:

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

В этом запросе COALESCE используется в следующих строках:

  • COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create: Эта строка преобразует дату created_at первого ответа в строку. Если первого ответа нет (то есть fr.created_at равен NULL), будет возвращена пустая строка (``).
  • COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create: Аналогично предыдущему, эта строка преобразует дату created_at решения в строку. Если решения нет (то есть st.created_at равен NULL), будет возвращена пустая строка (``).
  • COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)": Эта строка вычисляет разницу во времени в днях между созданием темы и первым ответом. Если первого ответа нет (то есть fr.created_at равен NULL), будет возвращено 0.
  • COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)": Эта строка вычисляет разницу во времени в часах между созданием темы и первым ответом. Если первого ответа нет (то есть fr.created_at равен NULL), будет возвращено 0.
  • COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)": Эта строка вычисляет разницу во времени в днях между созданием темы и решением. Если решения нет (то есть st.created_at равен NULL), будет возвращено 0.
  • COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)": Эта строка вычисляет разницу во времени в часах между созданием темы и решением. Если решения нет (то есть st.created_at равен NULL), будет возвращено 0.

Во всех этих случаях COALESCE используется для предотвращения появления значений NULL в итоговых результатах, что улучшает читаемость результирующего запроса и может быть полезно для последующей обработки или анализа данных.

Подробное объяснение с комментариями в коде 
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all

-- Определение CTE для валидных тем
WITH valid_topics AS (
    -- Выбор необходимых полей
    SELECT 
        t.id,  -- id темы
        t.user_id,  -- 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  -- из таблицы topics
    -- Соединение необходимых таблиц для получения имен тегов
    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  -- учитываем только темы, созданные между start_date и end_date
        AND t.archetype = 'regular'  -- учитываем только обычные темы
    GROUP BY t.id, t.user_id, t.title, t.views, posts_count, t.created_at  -- группируем по необходимым полям для получения правильного подсчета
),

-- Определение CTE для решенных тем
solved_topics AS (
    -- Выбор id темы и даты создания решения
    SELECT 
        vt.id,
        tcf.created_at
    FROM topic_custom_fields tcf  -- из таблицы topic_custom_fields
    INNER JOIN valid_topics vt ON vt.id = tcf.topic_id  -- соединяем с CTE valid_topics
    WHERE tcf.name = 'accepted_answer_post_id'  -- учитываем только темы с принятым ответом
),

-- Определение CTE для последнего ответа каждой темы
last_reply AS (
    -- Выбор id темы и id пользователя последнего ответа
    SELECT p.topic_id, p.user_id FROM posts p
    INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p  -- подзапрос для получения id последнего поста для каждой темы
                WHERE deleted_at ISNULL
                AND post_type = 1
                GROUP BY topic_id) x ON x.post = p.id  -- соединяем с таблицей posts для получения id пользователя последнего ответа
),

-- Определение CTE для первого ответа каждой темы
first_reply AS (
    -- Выбор id темы, id пользователя и даты создания первого ответа
    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  -- подзапрос для получения id первого ответа для каждой темы
                WHERE deleted_at ISNULL
                AND post_type = 1
                AND post_number > 1
                GROUP BY topic_id) x ON x.post = p.id  -- соединяем с таблицей posts для получения id пользователя и даты создания первого ответа
)

-- Основной запрос для получения итогового набора результатов
SELECT
    CASE 
        WHEN st.id IS NOT NULL THEN 'solved'  -- если id темы есть в CTE solved_topics, то статус «решено»
        ELSE 'unsolved'  -- иначе статус «нерешено»
    END AS status,
    vt.tag_names,  -- имена тегов
    vt.id AS topic_id,  -- id темы
    vt.user_id topic_user_id,  -- id пользователя
    ue.email,  -- email пользователя
    vt.title,  -- заголовок темы
    vt.views,  -- количество просмотров
    lr.user_id AS last_reply_user_id,  -- id пользователя последнего ответа
    ue2.email AS last_reply_user_email,  -- 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  -- из CTE valid_topics
LEFT JOIN last_reply lr ON lr.topic_id = vt.id  -- соединяем с CTE last_reply
LEFT JOIN first_reply fr ON fr.topic_id = vt.id  -- соединяем с CTE first_reply
LEFT JOIN solved_topics st ON st.id = vt.id  -- соединяем с CTE solved_topics
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true  -- соединяем с таблицей user_emails для получения email пользователя
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true  -- соединяем с таблицей user_emails для получения email пользователя, сделавшего последний ответ
WHERE (:tag_name =  'all' OR LOWER(vt.tag_names) LIKE '%' || LOWER(:tag_name) || '%')  -- фильтрация по имени тега
ORDER BY tag_names, total_days DESC  -- сортировка по именам тегов и общему количеству дней в порядке убывания

Если у вас есть вопросы или примеры того, как вы использовали COALESCE в своих запросах Data Explorer, пожалуйста, делитесь ими ниже. :slightly_smiling_face:

5 лайков