sql - 计算PostgreSQL中匹配条件的行的时间差
问题描述
我有一个 PostgreSQL 表,我需要在其中计算名称处于状态 1 的时间间隔。一个名称可以多次处于这种状态,我需要每个单独间隔的时间。我正在使用一个临时表,其中我保存了一个间隔的开始和结束点,然后计算它的时间差。但我不喜欢这种解决方案,并认为必须有更好的方法。我希望在这里找到一些 SQL 专业人士,他们可以向我展示一些神奇而简单的解决方案。
表格如下所示:
|name |state |time |
|-------|------|--------------------------|
|one | 1 |'2020-11-11 01:00:02.5+01'| (start of first interval)
|one | 1 |'2020-11-11 01:00:04.5+01'| (end of first interval) = 2 seconds
|one | 0 |'2020-11-11 01:00:05.0+01'|
|one | 0 |'2020-11-11 01:00:05.5+01'|
|one | 1 |'2020-11-11 01:00:10.5+01'| (start of second interval)
|one | 1 |'2020-11-11 01:00:11.5+01'|
|one | 1 |'2020-11-11 01:00:12.5+01'| (end of second interval) = 2 seconds
|two | 0 |'2020-11-11 01:00:13.0+01'|
|two | 0 |'2020-11-11 01:00:14.5+01'|
|two | 1 |'2020-11-11 01:00:15.0+01'| (start of third interval)
|two | 1 |'2020-11-11 01:00:15.5+01'| (end of third interval) = 0.5 seconds
|two | 0 |'2020-11-11 01:00:16.5+01'|
示例表的 SQL 脚本:
CREATE TABLE intervals(
name char(10),
state integer,
time timestamptz
);
INSERT INTO intervals(name, state, time) VALUES
('one', 1, '2020-11-11 01:00:02.5+01'),
('one', 1, '2020-11-11 01:00:04.5+01'),
('one', 0, '2020-11-11 01:00:05.0+01'),
('one', 0, '2020-11-11 01:00:05.5+01'),
('one', 1, '2020-11-11 01:00:10.5+01'),
('one', 1, '2020-11-11 01:00:11.5+01'),
('one', 1, '2020-11-11 01:00:12.5+01'),
('two', 0, '2020-11-11 01:00:13.0+01'),
('two', 0, '2020-11-11 01:00:14.5+01'),
('two', 1, '2020-11-11 01:00:15.0+01'),
('two', 1, '2020-11-11 01:00:15.5+01'),
('two', 0, '2020-11-11 01:00:16.5+01');
解决方案
这是一种差距和孤岛问题。在这种情况下,行号的差异应该做你想要的:
select name, min(time), max(time),
max(time) - min(time) as duration
from (select i.*,
row_number() over (partition by name order by time) as seqnum,
row_number() over (partition by name, state order by time) as seqnum_2
from intervals i
) i
where state = 1
group by name, (seqnum - seqnum_2), state;
这是一个 db<>fiddle。
行号差异的逻辑有点难以解释。如果您运行子查询,您将看到具有相同名称和相邻值的行上的行号差异是如何state
保持不变的。聚合只是按差异聚合,这对它们来说是恒定的。
推荐阅读
- javascript - 获取逗号后出现最多的文本字符串,用于任何目的
- python - 在 Python 中绘制键值对
- python - 在python中创建一个空白日期
- swift - 将音频播放速度提高 3 倍 - Swift
- python - cdk 代码创建调用 dynamodb 以放置项目的步骤函数的问题
- amazon-web-services - 在执行 aws s3 同步时删除中间文件夹
- python - Python:从一月到本月动态选择列
- python - 如何在 Linux 中有效地降级 python?
- java - 在几个小时的范围内从昨天获取纪元时间
- reactjs - 反应原生地图不直接更新自定义样式