postgresql - 从 PostgreSQL 中选择列组合的间隔/第一次和最后一次出现

Question

-- 我有带表的 PostgreSQL 数据库

CREATE TABLE timer
(
  id character varying(12),
  date timestamp without time zone,
  id1 character varying(10),
  id2 character varying(10)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE timer
  OWNER TO postgres;

-- 和数据

insert into timer values ('001', '2015-01-01 12:00:00', 100, null);
insert into timer values ('002', '2015-01-01 12:00:00', 200, null);
insert into timer values ('003', '2015-01-01 12:00:10', 100, null);
insert into timer values ('004', '2015-01-01 12:00:10', 200, null);
insert into timer values ('005', '2015-01-01 12:00:20', 100, 'aaaa');
insert into timer values ('006', '2015-01-01 12:00:20', 200, null);
insert into timer values ('007', '2015-01-01 12:00:30', 100, 'aaaa');
insert into timer values ('008', '2015-01-01 12:00:30', 200, null);
insert into timer values ('009', '2015-01-01 12:00:40', 100, 'aaaa');
insert into timer values ('010', '2015-01-01 12:00:40', 200, 'bbbb');
insert into timer values ('011', '2015-01-01 12:00:50', 100, 'aaaa');
insert into timer values ('012', '2015-01-01 12:00:50', 200, 'bbbb');
insert into timer values ('013', '2015-01-01 12:01:00', 100, 'aaaa');
insert into timer values ('014', '2015-01-01 12:01:00', 200, 'aaaa');
insert into timer values ('015', '2015-01-01 12:01:10', 100, 'aaaa');
insert into timer values ('016', '2015-01-01 12:01:10', 200, 'aaaa');
insert into timer values ('017', '2015-01-01 12:01:20', 100, null);
insert into timer values ('018', '2015-01-01 12:01:20', 200, 'aaaa');
insert into timer values ('019', '2015-01-01 12:01:30', 100, null);
insert into timer values ('020', '2015-01-01 12:01:30', 200, 'aaaa');
insert into timer values ('021', '2015-01-01 12:01:40', 100, null);
insert into timer values ('022', '2015-01-01 12:01:40', 200, 'aaaa');
insert into timer values ('023', '2015-01-01 12:01:50', 100, 'bbbb');
insert into timer values ('024', '2015-01-01 12:01:50', 200, 'aaaa');
insert into timer values ('025', '2015-01-01 12:02:00', 100, 'bbbb');
insert into timer values ('026', '2015-01-01 12:02:00', 200, 'aaaa');

select * from timer;
id  |         date        |  id1 |   id2  |
----+---------------------+------+--- ----+
001 | 2015-01-01 12:00:00 | 100  |  null  |
002 | 2015-01-01 12:00:00 | 200  |  null  |
003 | 2015-01-01 12:00:10 | 100  |  null  |
004 | 2015-01-01 12:00:10 | 200  |  null  |
005 | 2015-01-01 12:00:20 | 100  |  aaaa  |
006 | 2015-01-01 12:00:20 | 200  |  null  |
007 | 2015-01-01 12:00:30 | 100  |  aaaa  |
008 | 2015-01-01 12:00:30 | 200  |  null  |
009 | 2015-01-01 12:00:40 | 100  |  aaaa  |
010 | 2015-01-01 12:00:40 | 200  |  bbbb  |
011 | 2015-01-01 12:00:50 | 100  |  aaaa  |
012 | 2015-01-01 12:00:50 | 200  |  bbbb  |
013 | 2015-01-01 12:01:00 | 100  |  aaaa  |
014 | 2015-01-01 12:01:00 | 200  |  aaaa  |
015 | 2015-01-01 12:01:10 | 100  |  aaaa  |
016 | 2015-01-01 12:01:10 | 200  |  aaaa  |
017 | 2015-01-01 12:01:20 | 100  |  null  |
018 | 2015-01-01 12:01:20 | 200  |  aaaa  |
019 | 2015-01-01 12:01:30 | 100  |  null  |
020 | 2015-01-01 12:01:30 | 200  |  aaaa  |
021 | 2015-01-01 12:01:40 | 100  |  null  |
022 | 2015-01-01 12:01:40 | 200  |  aaaa  |
023 | 2015-01-01 12:01:50 | 100  |  bbbb  |
024 | 2015-01-01 12:01:50 | 200  |  aaaa  |
025 | 2015-01-01 12:02:00 | 100  |  bbbb  |
026 | 2015-01-01 12:02:00 | 200  |  aaaa  |

每 10 秒服务器接收到带有两个 id-s 的新数据。 id1 和 id2我必须以以下格式显示结果:

|     date_start      |      date_end       | id1 | id2  |
+---------------------+---------------------+-----+------+
| 2015-01-01 12:00:00 | 2015-01-01 12:00:10 | 100 | null |
| 2015-01-01 12:00:00 | 2015-01-01 12:00:30 | 200 | null |
| 2015-01-01 12:00:20 | 2015-01-01 12:01:10 | 100 | aaaa |
| 2015-01-01 12:00:40 | 2015-01-01 12:00:50 | 200 | bbbb |
| 2015-01-01 12:01:10 | 2015-01-01 12:02:00 | 200 | aaaa |
| 2015-01-01 12:01:20 | 2015-01-01 12:01:40 | 100 | null |
| 2015-01-01 12:01:50 | 2015-01-01 12:02:00 | 100 | bbbb |

结果格式为
组合 id1/id2 的第一次出现和 id2 更改前的最后一次出现。
因此，例如 id:001 的行以值 date:'2015-01-01 12:00:00' id1:100 id2:null
此行的第一个更改是行 id:005，其中 id2 变为 aaaa
是否可以在一个查询中执行此操作，或者我应该定期进行一些计算。

Answer 1

您可以使用简单的 max 和 min 聚合函数以及 GROUP BY 来接收您需要的结果:

select min(date) date_start, max(date) date_end, id1, id2
from timer
group by id1, id2
order by date_start, id1 nulls first, id2 nulls first;