Оптимизировать SQL запрос с подсчетом по нескольким периодам

Question

Есть postgresql и таблица с идентификатором факта Fact_id и датой его создания Created_at типа:

Fact_id	Created_at
5770	2024-12-20 15:55:54
5775	2024-12-20 10:52:22
6122	2024-12-25 10:10:09
6235	2024-12-21 12:23:58
5775	2024-12-25 18:17:47
6122	2024-12-23 18:53:15
5770	2024-12-22 14:22:16
6122	2024-12-26 14:16:38

Мне нужно посчитать, сколько Fact_id было создано за последниt сутки, за сутки до этого, и за двое суток до. Но в результирующей таблице отразить значения только в том случае, если Fact_id был создан за последние сутки.

Меня хватило на вложенный запрос:

select fact_id, int_1, int_2, int_3
from (select fact_id,
     COUNT(case when created_at >= (now() - interval '1 day') then 1 end) as int_1,
     COUNT(case when created_at between (now() - interval '1 day') and (now() - interval '2 day') then 1 end) as int_2,
     COUNT(case when created_at between (now() - interval '2 day') and (now() - interval '3 day') then 1 end) as int_3
     from table
     where created_at >= (now() - interval '3 day')
     group by fact_id) as d
where int_1 > 0

В итоге я получаю таблицу:

Fact_id	int_1	int_2	int_3
6122	1	1	0

Можно ли как-то оптимизировать, сократить код, чтобы получить такой же результат? Отдельно смущает заданные практически вручную периоды в условиях для COUNT.

Answer 1

Посмотрите пример с CROSSTAB

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

Сперва вычислим день(dd) от сегодня (назад) для каждой строки. Это значение будем использовать для группировки (подсчета количества) строк.

select *
  ,extract('day' from now()-created_at)dd
from logs

fact_id	created_at	dd
5770	2024-12-21 15:55:54	6
5775	2024-12-21 10:52:22	6
6122	2024-12-26 10:10:09	1
6235	2024-12-22 12:23:58	5
5775	2024-12-26 18:17:47	0
6122	2024-12-24 18:53:15	2
5770	2024-12-23 14:22:16	4
6122	2024-12-27 14:16:38	0
9990	2024-12-25 14:16:38	2

После группировки мы получим данные(3 строки в данном случае) для каждого fact_id (dd,cnt).
После этого развернем (PIVOT) эту таблицу по одной строке для fact_id. Поскольку не все даты могут быть заполнены, мы воспользуемся вторым параметром crosstab, которая определяет все значения (dd), какие должны быть в источнике. В данном случае значения от 0 до 2.

CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM CROSSTAB($$
  select fact_id
    ,extract('day' from now()-created_at)dd 
    ,count(*) cnt
  from logs
  where created_at >= (now() - interval '3 day')
  group by fact_id,dd
  order by fact_id,dd
  $$,
   $$ select n from generate_series(0,2)n
  $$
)
AS ct (fact_id int, "int_1" bigint, "int_2" bigint, "int_3" bigint)
where int_1>0

fact_id	int_1	int_2	int_3
5775	1	null	null
6122	1	1	1

Для интереса, без фильтра результат будет такой

fact_id	int_1	int_2	int_3
5775	1	null	null
6122	1	1	1
9990	null	null	1

Исходная таблица для CROSSTAB после группировки

fact_id	cnt	dd
5775	1	0
6122	1	1
9990	1	2
6122	1	0
6122	1	2

Здесь пример, где вместо now() используется константа (или параметр).
fiddle

БЛОГ НА HUSL

Оптимизировать SQL запрос с подсчетом по нескольким периодам

Ответы (1 шт):