--我有带表的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
是否可以在一个查询中执行此操作，或者我应该定期执行一些计算。
最佳答案
您可以使用带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;
关于postgresql - 从PostgreSQL选择间隔/列组合的第一次和最后一次出现，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30840915/