postgresql Postgres：检测活动和非活动时段

0pizxfdo 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(1)|浏览(133)

我有一个包含事件的表。每个事件都有一个定义的开始和结束时间戳：
| end_ts| end_ts |
| --| ------------ |
| 2019 -07-27 01：05：00| 2023-07-27 01:05:00 |
| 2019 -07-27 01：07：00| 2023-07-27 01:07:00 |
| 2019 -07-27 01：11：00| 2023-07-27 01:11:00 |
| 2023-07-27 01：15：00| 2023-07-27 01:15:00 |
| 2019 -07-27 01：35：00| 2023-07-27 01:35:00 |
| 2019 -07-27 01：42：00| 2023-07-27 01:42:00 |
| 2023-07-27 01：50：00| 2023-07-27 01:50:00 |
从这个结构中，我想找出活跃期和不活跃期。活动期间由事件发生的时间范围定义。这样的活动时段可以由多个连续事件组成。在非活动期间，没有事件发生。因此，预期输出如下所示：
| 期末|地位| status |
| --|--| ------------ |
| 2023-07-27 01：15：00|主动的| active |
| 2019 -07-27 01：30：00|不活动的| inactive |
| 2019 -07-27 01：42：00|主动的| active |
| 2019 -07-27 01：45：00|不活动的| inactive |
| 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');

字符串

postgresql

来源：https://stackoverflow.com/questions/76780313/postgres-detect-active-and-inactive-periods

1条答案

按热度按时间

frebpwbc1#

关于gaps and islands的其他任务。此外，还增加了输出间隙。仅适用于情况The events cannot overlap each other请参见此示例。

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

字符串
测试结果
| end_ts| GR|地位| status |
| --|--|--| ------------ |
| 2023-07-27 01：15：00| 0个|主动的| active |
| 2019 -07-27 01：30：00| 0个|不活动的| inactive |
| 2019 -07-27 01：42：00|一个|主动的| active |
| 2019 -07-27 01：45：00|一个|不活动的| inactive |
| 2023-07-27 01：50：00|二个|主动的| active |

赞(0）回复(0）举报 2023-08-04

我来回答

postgresql Postgres：检测活动和非活动时段

1条答案

相关问题

热门标签

最新问答