英文:
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 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论