我有一个表,看起来像:
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
ts TIMESTAMP,
expected_departure TIMESTAMP,
aimed_departure TIMESTAMP,
stop TEXT,
service TEXT,
vehicle_ref TEXT
)
我想查找标识符(stop, service, vehicle_ref)
出现的最后一条记录,以及第一条具有相同ts
的行均未返回相同(stop, service, vehicle_ref)
的记录。
我试过很多不同的方法,但都没能弄清楚。
如果可以简化查询,则可以重构数据,甚至切换到时间序列数据库。
编辑:下面是一个示例,为了简单起见,已提交日期:
// First API call
id: 1,
ts: 10:00:00,
aimed_departure: 10:30:00
expected_departure: 10:31:00,
stop: 1,
service: 2,
vehicle_ref: 1000
id: 2,
ts: 10:00:00,
aimed_departure: 10:50:00,
expected_departure: 10:50:00,
stop: 1,
service: 3,
vehicle_ref: 2000
// Second API call
id: 3,
ts: 10:32:00,
aimed_departure: 10:50:00,
expected_departure: 10:50:00,
stop: 1,
service: 3,
vehicle_ref: 2000
我想识别id=1,3
并从中选择,因为有一个服务(stop: 1, service: 2, vehicle_ref: 1000
(显示在第一个API调用(ts:10:00:00(中,但不显示在第二个调用(ts: 10:32:00
(中。
服务出现的地方可能有很多调用,但我只对它最后一次出现和第一次没有出现感兴趣,只要ts
之间的差异小于某个间隔。
只需使用max和min,并对所有进行子查询和联合
select id,stop, service, vehicle_ref from
(
select stop, service, vehicle_ref,T1.id from mytable T1 inner join
(select max(id) as id from mytable ) as T2
on T1.id=T2.id
)TT1
union all
select TT4.id,stop, service, vehicle_ref from mytable TT2
inner join
(
select min(id) as id from
(
select stop, service, vehicle_ref,min(id) as id from mytable
group by stop, service, vehicle_ref
) TT3
) TT4 on TT2.id=TT4.id
这是输出
id stop service vehicle_ref
3 1 3 2000
1 1 2 1000