时间戳

首页 > 解决方案 > 统计 PostgreSQL 中不同时间段之间发生的事件

sql - 统计 PostgreSQL 中不同时间段之间发生的事件

问题描述

我正在尝试使用 PostgreSQL 11.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;

我认为必须有更好的方法来做到这一点......

标签： sqlpostgresql

解决方案

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

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    |
+-------------------+-----+

推荐阅读