在postgresql的时间段之间发生的事件计数

edqdpe6u 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(309)

我正在尝试使用postgresql11.10创建一个查询，以便检索发生在多个时间节点中的所有事件。
目前，我只想统计这些事件，然后在详细的报告中获得“谁”和“什么”。
我有下表和数据

create table events(
    id int primary key generated always as identity,
    who varchar,
    event_date date,
    start_time time,
    end_time time,
    event_type int
);

INSERT INTO events (who, event_date, start_time, end_time, event_type) values
('A', '2021-04-01', '06:00:00', '13:00:00', 1001),
('B', '2021-04-01', '08:00:00', '15:00:00', 1001),
('C', '2021-04-01', '06:00:00', '11:00:00', 1002),
('A', '2021-04-01', '13:30:00', '18:00:00', 1002);

我需要计算以下时间线中表示的事件：
事件时间线
最小值和最大值可以根据一天中发生的事件而变化。
这是我做的，但我不能避免计数事件时，领先间隔是06:00:00。

select
    start_time,
    end_time,
    intervals as current_interval,
    coalesce(lead(intervals, 1) over (order by start_time, end_time, intervals ), '21:00:00') as next_interval,
    case 
        when
            intervals between start_time and end_time - '1 min'::interval
            or
            coalesce(lead(intervals, 1) over (order by start_time, end_time, intervals ), '21:00:00')
            between start_time and end_time - '1 min'::interval 
        then
            intervals || '-' || coalesce(lead(intervals, 1) over (order by start_time, end_time, intervals ), '21:00:00')
        else
            null
    end as is_present_in
from events e,
(
    select start_time as intervals from events
    where event_date = '2021-04-01'
    and who in ('A','B','C') 
    union 
    select end_time as intervals from events
    where event_date = '2021-04-01'
    and who in ('A','B','C') 
) tt
order by start_time, end_time, intervals;

我想一定有更好的办法。。。

sql postgresql

来源：https://stackoverflow.com/questions/67272031/count-events-ocurred-between-time-periods-in-postgresql

2条答案

按热度按时间

kgsdhlau1#

也许这个查询解决了你的任务

select (start_time || ' - ' || end_time) as "interval",
       (select count(1)
        from events
        where (intervals.start_time::interval + intervals.end_time::interval) / 2
               between events.start_time and events.end_time)
from (
         select start_time, lead(start_time, 1) OVER (order by start_time) end_time
         from (select start_time
               from events
               union
               select end_time
               from events) t
     ) intervals
where end_time notnull

结果

+-------------------+-----+
|interval           |count|
+-------------------+-----+
|06:00:00 - 08:00:00|2    |
|08:00:00 - 11:00:00|3    |
|11:00:00 - 13:00:00|2    |
|13:00:00 - 13:30:00|1    |
|13:30:00 - 15:00:00|2    |
|15:00:00 - 18:00:00|1    |
+-------------------+-----+

赞(0）回复(0）举报 2021-07-26

vwoqyblh2#

使用范围类型：

with
    i(r) as (
        select tsrange(dt, lead(dt) over (order by dt), '[)')
        from (
            select distinct dt
            from events cross join lateral (values(event_date + start_time), (event_date + end_time)) as t(dt)) as t)
select r, count(*)
from i join events as e on (tsrange(e.event_date + e.start_time, e.event_date + e.end_time, '[)') && i.r)
group by i.r
order by i.r;
┌───────────────────────────────────────────────┬───────┐
│                       r                       │ count │
├───────────────────────────────────────────────┼───────┤
│ ["2021-04-01 06:00:00","2021-04-01 08:00:00") │     2 │
│ ["2021-04-01 08:00:00","2021-04-01 11:00:00") │     3 │
│ ["2021-04-01 11:00:00","2021-04-01 13:00:00") │     2 │
│ ["2021-04-01 13:00:00","2021-04-01 13:30:00") │     1 │
│ ["2021-04-01 13:30:00","2021-04-01 15:00:00") │     2 │
│ ["2021-04-01 15:00:00","2021-04-01 18:00:00") │     1 │
└───────────────────────────────────────────────┴───────┘

赞(0）回复(0）举报 2021-07-26

我来回答

在postgresql的时间段之间发生的事件计数

2条答案

相关问题

热门标签

最新问答