Este é um relatório de painel em versão SQL para Tópicos sem Resposta.
O relatório do painel foi projetado para contar o número de tópicos criados dentro de um intervalo de datas especificado que não receberam nenhuma resposta de outros usuários. Este relatório pode ser filtrado por uma categoria específica e pode incluir opcionalmente subcategorias.
-- [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
Parâmetros
Parâmetros de Data:
A consulta aceita dois parâmetros, :start_date e :end_date, que definem o intervalo de datas para o relatório. Ambos os parâmetros de data aceitam o formato de data AAAA-MM-DD.
Parâmetros de Categoria:
:category_id: Um parâmetro inteiro que pode ser definido para o ID de uma categoria específica para refinar a análise para postagens dentro dessa categoria. Se for definido como nulo ou não fornecido, tópicos de todas as categorias são considerados.
:include_subcategories: Um parâmetro booleano que controla se deve incluir postagens de subcategorias do :category_id especificado. Se definido como verdadeiro, o relatório incluirá links para postagens na categoria especificada e suas subcategorias; se falso, apenas a categoria especificada será considerada.
Explicação da Consulta SQL
A consulta começa com uma Expressão de Tabela Comum (CTE) chamada no_response_total. Esta CTE executa as seguintes etapas:
Seleção de Tópicos: Seleciona todos os tópicos (t.id) e suas datas de criação (t.created_at) da tabela topics.
JOIN à Esquerda com Posts: Executa um JOIN à esquerda com a tabela posts para encontrar a primeira resposta para cada tópico. As condições do JOIN garantem que a postagem não seja do criador do tópico (p.user_id != t.user_id), a postagem não foi excluída (p.deleted_at IS NULL) e a postagem é do tipo 1, que normalmente representa uma resposta padrão.
Filtragem de Tópicos: A consulta filtra tópicos que são mensagens privadas (t.archetype <> 'private_message') e tópicos que foram excluídos (t.deleted_at ISNULL).
Filtragem de Categoria: Se um :category_id for fornecido, a consulta filtrará os tópicos para incluir apenas aqueles na categoria especificada. Se :include_subcategories for verdadeiro, também incluirá tópicos de subcategorias da categoria especificada.
Agrupamento e Número Mínimo de Postagem: Os tópicos são agrupados por seu ID e o número mínimo de postagem (MIN(p.post_number)) é calculado para encontrar a primeira resposta.
Filtragem para Nenhuma Resposta: A subconsulta tt filtra os tópicos que têm uma primeira resposta com um número de postagem maior ou igual a 2, deixando apenas os tópicos sem respostas (tt.first_reply IS NULL) ou apenas a postagem original (tt.first_reply < 2).
Após a definição da CTE no_response_total, a consulta principal faz o seguinte:
Filtrar por Intervalo de Datas: Filtra os tópicos da CTE pelas datas de início e fim fornecidas (:start_date e :end_date).
Contagem de Tópicos Sem Resposta: Conta o número de tópicos sem resposta para cada data dentro do intervalo especificado.
Agrupamento por Data: Os resultados são agrupados pela data de criação do tópico (DATE(nrt.created_at)).
Ordenação: Os resultados são ordenados por data em ordem crescente.
Você consegue fazer uma versão que não contenha parâmetros? Quero criar uma versão que olhe para trás 7 dias para ser enviada por e-mail às pessoas e estou com dificuldades para usar este código, pois há parâmetros definidos nele.
Sim, aqui está uma versão atualizada da consulta que olha para trás 7 dias a partir da data atual sem usar parâmetros.
Esta versão não inclui nenhuma filtragem por categorias ou subcategorias.
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
Se você quisesse ajustar o quão longe a consulta olha para trás, você só precisaria mudar esta linha na consulta:
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND
Estou com muita dificuldade para transformar isso em algo que não seja baseado em datas, mas sim em mês e ano.
Já fiz várias coisas para tentar fazer funcionar, mas ele continua me dizendo que a coluna não existe (quando existe, pois acabei de criá-la dentro de uma instrução with e, em seguida, estou referenciando-a).
Como alguém poderia alterar este código para que, em vez de analisar tópicos sem resposta dia a dia, pudéssemos vê-lo ano a ano, mês a mês, etc.?
Para modificar a consulta de forma que ela possa agregar tópicos sem respostas por ano, mês ou outros intervalos de tempo, você pode adicionar um parâmetro para especificar o intervalo desejado na função date_trunc para alcançar isso.
Por exemplo:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = day -- Opções: 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
Se você quisesse remover os parâmetros, poderia alternativamente usar uma consulta como:
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
Obrigado, isso resolveu o como obter a parte do ano.
Estou preso novamente, pois as datas no Postgre parecem se comportar de maneira diferente
to_char(t.created_at, ‘MM-YY’) as Yearmonth,
Isso está me dando 10-22, que representa “Out-22”
Como posso mudar 10-22 para Out-22? Tentei procurar orientações no discourse, mas não consegui encontrar isso, a menos que eu não tenha certeza onde procurar?
Para alterar o formato da data de 10-22 para Oct-22 no PostgreSQL, você pode usar a função TO_CHAR. Essa função permite formatar datas de várias maneiras, por exemplo:
SELECT
TO_CHAR(TO_DATE('10-22', 'MM-YY'), 'Mon-YY') AS formatted_date
Nesta instrução SQL:
TO_DATE('10-22', 'MM-YY') converte a string 10-22 em um tipo de data usando o formato MM-YY.
TO_CHAR(..., 'Mon-YY') então formata essa data para exibir o nome abreviado do mês seguido pelo ano, resultando em Oct-22.
Aqui está outro exemplo disso baseado na versão da consulta Topics with No Response com o parâmetro interval compartilhado acima:
-- [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 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
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
Como referência, os resultados desta consulta seriam assim:
