Postgres:检测活跃和非活跃时段

huangapple go评论57阅读模式
英文:

Postgres: Detect active and inactive periods

问题

我有一个包含事件的表格。每个事件都有定义好的开始和结束时间戳:

start_ts end_ts
2023-07-27 01:02:00 2023-07-27 01:05:00
2023-07-27 01:05:00 2023-07-27 01:07:00
2023-07-27 01:07:00 2023-07-27 01:11:00
2023-07-27 01:11:00 2023-07-27 01:15:00
2023-07-27 01:30:00 2023-07-27 01:35:00
2023-07-27 01:35:00 2023-07-27 01:42:00
2023-07-27 01:45:00 2023-07-27 01:50:00

从这个结构中,我想找出活动和非活动时段。活动时段由事件发生的时间范围定义。这样的活动时段可以由多个连续的事件组成。在非活动时段中没有事件发生。因此,预期的输出如下:

period_start period_end status
2023-07-27 01:02:00 2023-07-27 01:15:00 active
2023-07-27 01:15:00 2023-07-27 01:30:00 inactive
2023-07-27 01:30:00 2023-07-27 01:42:00 active
2023-07-27 01:42:00 2023-07-27 01:45:00 inactive
2023-07-27 01:45:00 2023-07-27 01:50:00 active

您知道如何使用 PostgreSQL 来实现这个目标吗?

CREATE TABLE IF NOT EXISTS events(
   id INT PRIMARY KEY,   
   start_ts timestamp NOT NULL,
   end_ts timestamp NOT NULL
);

INSERT INTO events(id,start_ts,end_ts)
VALUES
(1,'2023-07-27 01:02:00','2023-07-27 01:05:00'),
(2,'2023-07-27 01:05:00','2023-07-27 01:07:00'),
(3,'2023-07-27 01:07:00','2023-07-27 01:11:00'),
(4,'2023-07-27 01:11:00','2023-07-27 01:15:00'),
(5,'2023-07-27 01:30:00','2023-07-27 01:35:00'),
(6,'2023-07-27 01:35:00','2023-07-27 01:42:00'),
(7,'2023-07-27 01:45:00','2023-07-27 01:50:00');
英文:

I have a table containing events. Each event has a defined starting and ending timestamp:

start_ts end_ts
2023-07-27 01:02:00 2023-07-27 01:05:00
2023-07-27 01:05:00 2023-07-27 01:07:00
2023-07-27 01:07:00 2023-07-27 01:11:00
2023-07-27 01:11:00 2023-07-27 01:15:00
2023-07-27 01:30:00 2023-07-27 01:35:00
2023-07-27 01:35:00 2023-07-27 01:42:00
2023-07-27 01:45:00 2023-07-27 01:50:00

From this structure, I want to find out, active and inactive periods. Active periods are defined by the time range in which an event takes place. Such an active period can be composed of multiple consecutive events.
In inactive periods no event is taking place. So the expected output would be like:

period_start period_end status
2023-07-27 01:02:00 2023-07-27 01:15:00 active
2023-07-27 01:15:00 2023-07-27 01:30:00 inactive
2023-07-27 01:30:00 2023-07-27 01:42:00 active
2023-07-27 01:42:00 2023-07-27 01:45:00 inactive
2023-07-27 01:45:00 2023-07-27 01:50:00 active

Any idea how I can achieve this by using PostgreSQL?

CREATE TABLE IF NOT EXISTS events(
   id INT PRIMARY KEY,   
   start_ts timestamp NOT NULL,
   end_ts timestamp NOT NULL
);

INSERT INTO events(id,start_ts,end_ts)
VALUES
(1,'2023-07-27 01:02:00','2023-07-27 01:05:00'),
(2,'2023-07-27 01:05:00','2023-07-27 01:07:00'),
(3,'2023-07-27 01:07:00','2023-07-27 01:11:00'),
(4,'2023-07-27 01:11:00','2023-07-27 01:15:00'),
(5,'2023-07-27 01:30:00','2023-07-27 01:35:00'),
(6,'2023-07-27 01:35:00','2023-07-27 01:42:00'),
(7,'2023-07-27 01:45:00','2023-07-27 01:50:00');

答案1

得分: 1

|start_ts|	end_ts|	gr|	status|
|:---------|:--------|--:|:--|
|2023-07-27 01:02:00|	2023-07-27 01:15:00|	0|	活跃|
|2023-07-27 01:15:00|	2023-07-27 01:30:00|	0|	非活跃|
|2023-07-27 01:30:00|	2023-07-27 01:42:00|	1|	活跃|
|2023-07-27 01:42:00|	2023-07-27 01:45:00|	1|	非活跃|
|2023-07-27 01:45:00|	2023-07-27 01:50:00|	2|	活跃|
英文:

Another task about gaps and islands. Additionally, with adding gaps to output. Only for case The events cannot overlap each other
See this example.

with t1 as(-- gaps
  select *
  ,case when lag(end_ts,1,start_ts)over(order by start_ts)=start_ts then 0 else 1 end gap
from events
)
,t2 as(--count islands
  select *
    ,sum(gap)over(order by start_ts)gr
  from t1
)
,t3 as(-- compact islands and take time for gap
  select min(start_ts)start_ts,max(end_ts)end_ts,gr
    ,lead(min(start_ts))over(order by gr) next_active
  from t2
  group by gr
)
select start_ts,end_ts,gr,'active' as status
from t3
union all
select end_ts start_ts,next_active end_ts,gr,'inactive' as status
from t3
where next_active is not null
order by gr,start_ts

Result

start_ts end_ts gr status
2023-07-27 01:02:00 2023-07-27 01:15:00 0 active
2023-07-27 01:15:00 2023-07-27 01:30:00 0 inactive
2023-07-27 01:30:00 2023-07-27 01:42:00 1 active
2023-07-27 01:42:00 2023-07-27 01:45:00 1 inactive
2023-07-27 01:45:00 2023-07-27 01:50:00 2 active

huangapple
  • 本文由 发表于 2023年7月27日 21:35:12
  • 转载请务必保留本文链接:https://go.coder-hub.com/76780313.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定