如何获得一组postgres-hstore记录中每个键的最后一个值

Question

我有一个传感器读数表，作为postgreshstore key-value pairs。每条记录都有一个时间戳，时间间隔为1秒。并非所有传感器都会每秒记录一次。

该表本质上是：

create table readings (
timer timestamp primary key,
readings hstore
);

hstore包括用于在时间戳中指定的时间进行读取的(<sensor_id>(键/值对。

例如："；67〃&quot-45.67436〃"68〃"176.5424〃；可以是表示纬度&经度，计时器列中有时间戳。

在给定的一分钟内会有几个lat/lon-hstore对，我想要的查询会返回该分钟时间序列中的最后一个(对于每个键(。

我想每隔1分钟检索hstore中每个传感器的最新值。

本质上：获取一组时间戳&hstores。把钥匙从那套里拿出来。从具有集合中最新时间戳的键值对中获取该键的值返回1分钟的时间戳&生成的hs矿石在下一分钟重复

如果pl/pgsql函数是最好的方法，我会对它感到满意。

Answer 1

对于

"我想要的查询将返回该时间序列中的最后一个分钟(对于每个键("；

你可以使用windows功能，我猜你有不同的传感器

select * from 
( 
select * , row_number() over (partition by sensorid, to_char(timer, 'YYYY-MM-DD HH:MM') order by timer desc) rn
) t
where rn = 1

Answer 2

with t(t, h) as (values
('2000-01-01 01:01:01'::timestamp, 'a=>1,b=>2'::hstore),
('2000-01-01 01:01:02', 'a=>3'),
('2000-01-01 01:02:03', 'b=>4'),
('2000-01-01 01:02:05', 'a=>2,b=>3'))
select
date_trunc('minute', t),
jsonb_object_agg(k, v order by t)
from t, each(h) as h(k,v)
group by date_trunc('minute', t);
┌─────────────────────┬──────────────────────┐
│     date_trunc      │   jsonb_object_agg   │
├─────────────────────┼──────────────────────┤
│ 2000-01-01 01:01:00 │ {"a": "3", "b": "2"} │
│ 2000-01-01 01:02:00 │ {"a": "2", "b": "3"} │
└─────────────────────┴──────────────────────┘

演示