Categories: PostgreSQL

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

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

  1. самую раннюю запись в таблице — (SELECT min(datecreate) FROM table)
  2. самую позднюю запись в таблице — (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

 

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

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 раз быстрее.

Подсчёт количества записей через соединение и ЛЕВЫЙ ДЖОИН с появлением NULL — PostgreSQL

 

Информационные ссылки

PostgreSQL | Как отобрать сегодняшние записи из таблицы?

PostgreSQL | Как отобрать вчерашние записи из таблицы?

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Команды SQL — https://postgrespro.ru/docs/postgresql/15/sql-commands

Функции для работы с метками времени — https://postgrespro.ru/docs/postgresql/15/functions-datetime

Функция date_trunc() — https://postgrespro.ru/docs/postgresql/15/functions-datetime#FUNCTIONS-DATETIME-TRUNC

admin

Share
Published by
admin

Recent Posts

PostgreSQL | Как получить первый элемент из ячейки типа record?

При помощи функции "row()" мы можем создавать записи - данные с типом record. -- Создаём…

1 год ago

PostgreSQL | Как отфильтровать JSONB-массив объектов по логическому значению ключа?

-- массив JSONB с объектами select '[{"id":1, "q":true}, {"id":2, "q":false}, {"id":3, "q":true}, {"id":4, "q":false}]'::jsonb; --…

1 год ago

PostgreSQL | Как отбросить нули в типе numeric?

Функция "trim_scale()" умеет отбрасывать лишние нули в числах с типом numeric. select trim_scale(37.000000000); -- 37…

1 год ago

JavaScript | Как получить следующий день?

Следующий день относительно текущего т. е. "завтра" var tomorrow = new Date(); tomorrow.setDate(tomorrow.getDate() + 1);…

1 год ago

PostgreSQL | Агрегация JSONB-объектов в один объект

У нас есть столбец из JSONB объектов. Нам нужно соединить все объекты этого столбца в…

1 год ago

PostgreSQL | Группировка по 10 записей

Не всегда нужно группировать записи таблиц в рамках какого-то повторяющегося значения столбца. Иногда нужно просто…

1 год ago