Добавление параметра даты

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

Часто возникает необходимость извлечь данные за определённый период времени. Это можно сделать, зафиксировав временной диапазон непосредственно в запросе, но чтобы сделать отчёт более устойчивым к изменениям, лучше добавить параметр даты:

Этот запрос предназначен для подсчёта общего количества постов в каждой категории (исключая удалённые посты и темы, личные сообщения и темы без категории), созданных в определённом диапазоне дат, а затем для сортировки категорий по убыванию общего числа постов.

-- [params]
-- date :start_date -- Параметр начальной даты диапазона
-- date :end_date -- Параметр конечной даты диапазона

SELECT
   t.category_id,
   COUNT(p.id) AS "Total Posts" -- Подсчёт количества постов в каждой категории
FROM posts p 
  JOIN topics t ON t.id = p.topic_id 
WHERE p.created_at::date BETWEEN :start_date AND :end_date -- Фильтрация постов, созданных между начальной и конечной датой (::date приводит временную метку created_at к типу date)
  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

Использование стиля параметра date вместо string делает ввод значения параметра гораздо более интуитивным, особенно при совместном использовании с другими.

Ещё один полезный совет для запросов такого типа — приводить поле created_at к типу date. Поскольку значение хранится в базе данных как временная метка, если не выполнить приведение к типу date, запрос не будет включать результаты за саму дату параметра :end_date.

Вы можете проверить, какие значения хранятся как временные метки, используя дерево Explorer:

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

Этот запрос в основном служит примером для демонстрации принципов. Надеемся, он окажется вам полезным, но если у вас возникнут вопросы, задавайте их ниже. :slight_smile:

2 
СТРОКА 16: WHERE p.created_at::date BETWEEN :start_date AND :end_date -…

:tired_face:

3

Ага, верно, оно не любит эти встроенные комментарии рядом с параметрами.

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

SELECT
   t.category_id,
   COUNT(p.id) AS "Total Posts" -- Подсчитать количество постов в каждой категории
FROM posts p 
  JOIN topics t ON t.id = p.topic_id 
WHERE p.created_at::date BETWEEN :start_date AND :end_date -- Отфильтровать посты, созданные между датой начала и датой окончания (::date приводит временную метку created_at к типу date)
  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

Этот вариант, кажется, работает лучше. :+1: (Возможно, вам потребуется обновить страницу в первый раз, чтобы появились поля ввода параметров)

Спасибо за помощь в отладке руководств. :slight_smile: :trophy: