PostgreSQL | Сколько записей в таблице по каждому дню?

У нас в таблице в PostgreSQL есть много записей. Каждая запись использует столбец даты появления самой себя в этой таблице. Столбец с датами создания называется «datecreate«.

Проблема заключается в том, что даты имеют тип «timestamp with time zone«. То есть помимо года, месяца и дня, формат даты имеет часы, минуты, секунды, миллисекунды и часовой пояс. В этом случае каждая дата получается уникальной, потому что добавляется в свой временной отрезок. Эту уникальность нужно округлить до «ДНЯ«, чтобы правильно произвести расчёт.

Мы хотим получить результат вычислений в виде двух столбцов:

Столбец с днями в порядке возрастания
Столбец с количеством записей под каждый день

Команда SQL-запроса будет выглядеть так:

SELECT datecreate::date, count(datecreate) FROM t_scheme.t_name GROUP BY datecreate::date ORDER BY datecreate ASC;

Результат работы:

Получили количества созданных записей по каждому дню - PostgreSQL — Получили количества созданных записей по каждому дню — PostgreSQL

Мы отталкивались от того, что подсчитываем все существующие записи и именно по ним создаём список дней.

Важно обратить внимание на то, что в какой-то день может не быть записей. Это связано с тремя факторами:

Записи вообще не создавались в этот день
Записи за этот день были удалены
Записи были обновлены на другой день

В результате эта дата выпадает из итоговой таблицы. Нужно учитывать это в отчёте.

Как учитывать каждую дату, даже если записей за этот день не было?

В таком случае нужно отталкиваться с другой стороны. Нужно знать две даты из таблицы:

самую раннюю запись в таблице — (SELECT min(datecreate) FROM table)
самую позднюю запись в таблице — (SELECT max(datecreate) FROM table)

После этого можно будет построить список всех возможных дней. Сделать это можно функцией generate_series(), которая принимает три параметра:

начало
конец
интервал

Общая команда будет выглядеть так:

SELECT generate_series(   (SELECT min(datecreate) FROM table)::date,    (SELECT max(datecreate) FROM table),    '1D')::date;

На конце функции обязательно приводим к типу «date«, иначе получим результаты в формате «timestamp«.

Сгенерировали все возможные даты из двух граничных дат в PostgreSQL

В итоге мы получаем полный набор дат между двумя пограничными датами.

Теперь нужно пройтись по элемента данной таблицы и рассчитать под каждый день количества записей.

SELECT *, (SELECT count(datecreate::date) FROM table WHERE datecreate::date = generate_series)
FROM (SELECT generate_series((SELECT min(datecreate) FROM table)::date, (SELECT max(datecreate) FROM table)::date, '1D')::date) AS q;

Это выражение подсчитает все количества по дням, но будет делать это очень долго. Но ноль мы получим в нужной ячейке.

Подсчёт количества записей по всем датам подряд - PostgreSQL — Подсчёт количества записей по всем датам подряд — PostgreSQL

Можно ускорить результат через соединение двух таблиц (через левый джоин):

SELECT q.generate_series, w.count
FROM (SELECT generate_series((SELECT min(datecreate) FROM table)::date, (SELECT max(datecreate) FROM table)::date, '1D')::date) AS q
LEFT JOIN (SELECT datecreate::date, count(datecreate) FROM table GROUP BY datecreate::date) AS w
ON q.generate_series = w.datecreate
ORDER BY q.generate_series ASC;

Получаем «дырку» вместо «0». Результат вычисляется в 12 раз быстрее.