Это SQL-версия отчета панели управления по темам без ответов.
Отчет панели управления предназначен для подсчета количества тем, созданных в указанном диапазоне дат, которые не получили ни одного ответа от других пользователей. Этот отчет можно отфильтровать по конкретной категории и при желании включить подкатегории.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
-- null int :category_id
-- boolean :include_subcategories = false
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
DATE(nrt.created_at) AS date,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY date
ORDER BY date ASC
Параметры
Параметры даты:
Запрос принимает два параметра: :start_date и :end_date, которые определяют диапазон дат для отчета. Оба параметра даты принимают формат ГГГГ-ММ-ДД.
Параметры категории:
:category_id: Целочисленный параметр, который можно установить в ID конкретной категории, чтобы сузить анализ до постов в этой категории. Если он установлен в null или не предоставлен, рассматриваются темы из всех категорий.
:include_subcategories: Булевый параметр, который контролирует, включать ли посты из подкатегорий указанной :category_id. Если установлено значение true, отчет будет включать ссылки на посты как в указанной категории, так и в её подкатегориях; если false, учитывается только указанная категория.
Объяснение SQL-запроса
Запрос начинается с общего табличного выражения (CTE) под названием no_response_total. Это CTE выполняет следующие шаги:
Выборка тем: Выбираются все темы (t.id) и даты их создания (t.created_at) из таблицы topics.
Левое соединение с постами: Выполняется левое соединение с таблицей posts для поиска первого ответа на каждую тему. Условия соединения гарантируют, что пост не является автором темы (p.user_id != t.user_id), пост не удален (p.deleted_at IS NULL), и пост имеет тип 1, что обычно означает стандартный ответ.
Фильтрация тем: Запрос исключает темы, которые являются личными сообщениями (t.archetype <> 'private_message'), и темы, которые были удалены (t.deleted_at ISNULL).
Фильтрация по категории: Если предоставлен :category_id, запрос отфильтрует темы, включив только те, которые находятся в указанной категории. Если :include_subcategories установлено в true, будут также включены темы из подкатегорий указанной категории.
Группировка и минимальный номер поста: Темы группируются по их ID, и вычисляется минимальный номер поста (MIN(p.post_number)), чтобы найти первый ответ.
Фильтрация тем без ответа: Подзапрос tt исключает темы, у которых первый ответ имеет номер поста, больший или равный 2, оставляя только темы без ответов (tt.first_reply IS NULL) или только исходный пост (tt.first_reply < 2).
После определения CTE no_response_total основной запрос выполняет следующее:
Фильтрация по диапазону дат: Фильтруются темы из CTE по указанным начальной и конечной датам (:start_date и :end_date).
Подсчет тем без ответа: Подсчитывается количество тем без ответа для каждой даты в указанном диапазоне.
Группировка по дате: Результаты группируются по дате создания темы (DATE(nrt.created_at)).
Сортировка: Результаты сортируются по дате в порядке возрастания.
Можете ли вы создать версию без параметров? Я хочу создать версию, которая смотрит на последние 7 дней, чтобы затем отправлять её по электронной почте людям, и у меня возникают трудности с использованием этого кода, так как в нём установлены параметры.
Да, вот обновлённая версия запроса, которая смотрит на 7 дней назад от текущей даты без использования параметров.
Эта версия не включает фильтрацию по категориям или подкатегориям.
WITH no_response_total AS (
SELECT *
FROM (
SELECT
t.id,
t.created_at,
MIN(p.post_number) AS first_reply
FROM
topics t
LEFT JOIN
posts p
ON
p.topic_id = t.id
AND p.user_id != t.user_id
AND p.deleted_at IS NULL
AND p.post_type = 1
WHERE
t.archetype <> 'private_message'
AND t.deleted_at IS NULL
AND (
t.category_id = :category_id
OR t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
GROUP BY
t.id
) tt
WHERE
tt.first_reply IS NULL
OR tt.first_reply < 2
)
SELECT
DATE(nrt.created_at) AS date,
COUNT(nrt.id) AS topics_without_response
FROM
no_response_total nrt
WHERE
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND CURRENT_DATE
GROUP BY
date
ORDER BY
date ASC
Если вы захотите изменить период, на который смотрит запрос, вам нужно будет изменить только эту строку в запросе:
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND
Спасибо за ответ. Я вернусь к этому, когда мне это понадобится в следующий раз, так как сейчас внимание переключилось на что-то другое, и у меня не было времени вернуться к этому.
У меня возникают серьезные трудности с тем, чтобы сделать вывод не по датам, а по месяцу и году.
Я пробовал множество способов заставить это работать, но система постоянно сообщает, что столбец не существует (хотя он есть, так как я только что создал его в операторе WITH, а затем ссылаюсь на него).
Как можно изменить этот код, чтобы вместо просмотра тем без ответов день за днем мы могли видеть их год за годом, месяц за месяцем и так далее?
Чтобы изменить запрос так, чтобы он мог агрегировать темы без ответов по годам, месяцам или другим временным интервалам, вы можете добавить параметр для указания желаемого интервала в функции date_trunc.
Например:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = day -- Варианты: day, week, month, year
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc(:interval, nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period
ORDER BY period ASC
Если вы хотите убрать параметры, вы также можете использовать запрос следующего вида:
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc('year', nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN '2024-01-01' AND '2025-01-01'
GROUP BY period
ORDER BY period ASC
Теперь я снова застрял, так как даты в PostgreSQL ведут себя иначе.
to_char(t.created_at, 'MM-YY') as Yearmonth
Это выдаёт 10-22, что означает «октябрь-22».
Как мне изменить 10-22 на Oct-22? Я пытался найти подсказки в документации Discourse, но не смог этого сделать, если только я не знаю, где именно искать?
Чтобы изменить формат даты с 10-22 на Oct-22 в PostgreSQL, можно использовать функцию TO_CHAR. Эта функция позволяет форматировать даты различными способами, например:
SELECT
TO_CHAR(TO_DATE('10-22', 'MM-YY'), 'Mon-YY') AS formatted_date
В этом SQL-запросе:
TO_DATE('10-22', 'MM-YY') преобразует строку 10-22 в тип даты, используя формат MM-YY.
TO_CHAR(..., 'Mon-YY') затем форматирует эту дату так, чтобы отображалось сокращённое название месяца, за которым следует год, в результате получается Oct-22.
Вот ещё один пример, основанный на версии запроса Topics with No Response с параметром interval, который был приведён выше:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = month
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at IS NULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
TO_CHAR(date_trunc(:interval, nrt.created_at)::date, 'Mon-YY') AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period, date_trunc(:interval, nrt.created_at)::date
ORDER BY date_trunc(:interval, nrt.created_at)::date ASC
Для справки, результаты этого запроса будут выглядеть следующим образом:
