Adicionando um parâmetro de data

Dados e relatórios
1

Frequentemente, você vai querer extrair dados para um período de tempo específico. Você pode fazer isso codificando seu período de tempo diretamente na consulta, mas para tornar seu relatório mais à prova de futuro, você pode adicionar um parâmetro de data:

Esta consulta foi projetada para contar o número total de postagens em cada categoria (excluindo postagens e tópicos excluídos, mensagens privadas e tópicos sem categoria) que foram criadas dentro de um determinado intervalo de datas e, em seguida, ordenar as categorias pelo número total de postagens em ordem decrescente.

-- [params]
-- date :start_date -- Este é um parâmetro para a data de início do intervalo
-- date :end_date -- Este é um parâmetro para a data de término do intervalo

SELECT
   t.category_id,
   COUNT(p.id) AS "Total Posts" -- Conta o número de postagens em cada categoria
FROM posts p
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date -- Filtra postagens criadas entre a data de início e a data de término (::date converte o timestamp created_at para data)
  AND t.deleted_at IS NULL
  AND p.deleted_at IS NULL
  AND t.archetype <> 'private message'
  AND p.user_id > 0
  AND t.category_id IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC

Usar o estilo de parâmetro date em vez de string torna a inserção do valor do parâmetro muito mais intuitiva, especialmente quando compartilhado com outras pessoas.

Outra dica notável para este tipo de consulta é converter o created_at para data. Como o valor é armazenado no banco de dados como um timestamp, se você não o converter para data, a consulta não capturará os resultados da data do próprio :end_date.

Você pode verificar quais valores são armazenados como timestamps usando a árvore do Explorer:

explorer tree - value type
explorer tree - value type278×281 3.18 KB

Esta consulta é principalmente um exemplo para demonstrar os princípios. Espero que você a considere útil, mas se tiver alguma dúvida, por favor, pergunte abaixo. :slight_smile:

9 curtidas
2 
PG::SyntaxError: ERROR:  syntax error at or near ":"
LINE 16: WHERE p.created_at::date BETWEEN :start_date AND :end_date -...

:tired_face:

4 curtidas
3

Ah sim, ele não gosta desses comentários inline ao lado dos parâmetros.

-- [params]
-- date :start_date 
-- date :end_date 

SELECT
   t.category_id,
   COUNT(p.id) AS "Total Posts" -- Conta o número de posts em cada categoria
FROM posts p 
  JOIN topics t ON t.id = p.topic_id 
WHERE p.created_at::date BETWEEN :start_date AND :end_date -- Filtra posts criados entre a data de início e a data de fim (::date converte o timestamp created_at para data)
  AND t.deleted_at IS NULL 
  AND p.deleted_at IS NULL 
  AND t.archetype <> 'private message' 
  AND p.user_id > 0
  AND t.category_id IS NOT NULL 
GROUP BY 1 
ORDER BY 2 DESC

Este parece funcionar melhor. :+1: (Pode ser necessário atualizar a página na primeira vez para que as caixas de entrada de parâmetros sejam exibidas)

Obrigado por me ajudar a depurar os guias. :slight_smile: :trophy:

7 curtidas