Relatório do Painel - Sistema

Dados e relatórios
, ,
1

Esta é uma versão SQL do Relatório de Painel para o Sistema.

Este relatório de painel fornece uma contagem diária do número de mensagens pessoais enviadas automaticamente pelo sistema.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

SELECT
  DATE(created_at) AS day,
  COUNT(*) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND deleted_at IS NULL
  AND user_id > 0
GROUP BY DATE(created_at)
ORDER BY day

Explicação da Consulta SQL

A consulta funciona extraindo dados da tabela topics – especificamente, aqueles que se qualificam como mensagens privadas para usuários com um subtipo de system_message dentro de um determinado período. Vamos detalhar:

  • 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.
  • SELECT: A consulta seleciona dois campos:
    • DATE(created_at) AS day: Isso extrai a parte da data do timestamp created_at, agrupando efetivamente os registros pelo dia em que foram criados.
    • COUNT(*) AS notifications_count: Isso conta o número total de MPs geradas pelo sistema para cada dia.
  • FROM: Especifica a tabela topics como a fonte de dados, que contém registros de todos os tópicos, incluindo mensagens privadas.
  • WHERE: Contém vários filtros para refinar o conjunto de dados:
    • archetype = 'private_message': Inclui apenas entradas que são mensagens privadas.
    • subtype = 'system_message': Restringe ainda mais a seleção apenas a mensagens geradas pelo sistema.
    • created_at BETWEEN :start_date AND :end_date: Filtra as MPs para aquelas criadas dentro do intervalo especificado pelos parâmetros.
    • deleted_at IS NULL: Exclui mensagens que foram excluídas.
    • user_id > 0: Garante que as mensagens estejam associadas a contas de usuário reais, em vez de contas do sistema ou anônimas.
  • GROUP BY: Agrupa os resultados com base no dia em que foram criados.
  • ORDER BY: Ordena o conjunto de resultados final pelo dia em ordem crescente, garantindo uma sequência cronológica de contagens diárias.

Exemplo de Resultados

day notifications_count
2024-01-01 5
2024-01-02 7
2024-01-03 11
2024-01-04 14
2024-01-05 8
3 curtidas
2

Isso pode ser vinculado a um PM específico, como o PM de boas-vindas?

Gostaria de saber quantos deles são enviados diariamente.

Opiniões?

1 curtida
3

Sim, a melhor maneira de adicionar isso seria adicionar uma seção à instrução WHERE na consulta, filtrando pelo title do tópico.

Por exemplo:

WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND title = 'Greetings!'

Encontraria todas as mensagens Greetings!.

Observe que algumas das mensagens do sistema não incluem um usuário real, portanto, remover a linha AND user_id > 0 pode ser necessário com esse tipo de consulta.

Você pode querer remover AND deleted_at IS NULL para ainda contar as mensagens de boas-vindas que os usuários podem excluir.

Você pode usar regex para corresponder a tópicos com um título semelhante.

Para corresponder a um tópico por título usando uma expressão regular (regex) no PostgreSQL, você pode usar o operador ~, que corresponde a uma expressão regular contra uma string. A estrutura da consulta seria assim:

SELECT *
FROM topics
WHERE title ~ 'YourRegexPatternHere'

Substitua 'YourRegexPatternHere' pelo padrão de regex real que você deseja corresponder ao campo title.

Por exemplo, se você estiver procurando por tópicos com títulos que contenham a palavra “Welcome” (ignorando maiúsculas e minúsculas), você poderia usar:

SELECT *
FROM topics
WHERE title ~* 'Welcome'

O operador ~* é usado para correspondência que ignora maiúsculas e minúsculas.

3 curtidas
4

Ah, isso é incrível. Muito obrigado! Vou tentar isso!

1 curtida