Блог

Я пытаюсь подсчитать количество идентификаторов, активных в течение данного часового окна и дня. У меня есть три столбца: start_time, end_time и идентификатор.

Большинство групп являются стандартными группами в один и тот же день, т. е. с 9 утра до 11 утра, с 11 утра до 1 часа дня и т. Д.

Одна из группировок перекрывает два дня (11 вечера-1 час ночи). То есть 16 мая в 11:01 вечера и 17 мая в 12:01 утра должны быть в одной группе. Но при составлении простого запроса вы в конечном итоге сгруппируете 17 мая в 12:01 утра в пределах 17 мая в 11:00 вечера.

Я попробовал несколько запросов с несколькими вариантами операторов и подзапросов, но не могу понять суть.

Эта попытка запроса ниже дает мне 0 для столбца 11pm_1am. Я подумал, что мог бы просто перенести время 12:30 утра на предыдущую дату и посчитать его там, но не повезло.

 select date_trunc('day', start_time_1) as date_active
, count(distinct(case when CAST(start_time_1 AS TIME) <= '06:00:00' and CAST(end_time_1 AS TIME) >= '01:00:00' then id end)) as early_morning
, count(distinct(case when CAST(start_time_1 AS TIME) <= '00:00:00' and CAST(end_time_1 AS TIME) >= '23:00:00' then id end)) as overnight_11pm_1am
from 
(select id
, case 
when cast(start_time as time) between '00:00:00' and '01:00:00' 
then start_time - INTERVAL '01:00' HOUR TO MINUTE 
else start_time 
end as start_time_1
,case 
when cast(end_timeas time) between '00:00:00' and '01:00:00' 
then end_time - INTERVAL '01:00' HOUR TO MINUTE 
else end_time 
end as end_time_1
from table
where start_time >= '2021-01-01'
and  start_time < '2021-01-04'
)
group by date_active

 

Кончились идеи.