使用详解
时空数据库,天然支持时序和空间混合查询与分析,下面是一些参考示例。
示例数据
metrics表:
select time,uid,speed,dev_type,ST_AsText(position) from metrics;
time | uid | speed | dev_type | st_astext
------------------------+--------+-------+----------+------------------
2017-01-01 01:02:00+08 | abc123 | 72 | field | POINT(12.1 25.2)
2017-01-01 01:02:23+08 | def456 | 64 | roof | POINT(12.2 25.3)
2017-01-01 01:02:30+08 | ghi789 | 56 | roof | POINT(12.3 25.4)
2017-01-01 01:03:12+08 | abc123 | 82 | field | POINT(12.4 25.5)
2017-01-01 01:03:35+08 | def456 | 74 | roof | POINT(12.5 25.6)
2017-01-01 01:03:42+08 | ghi789 | 66 | roof | POINT(12.6 25.7)
2017-01-01 01:12:23+08 | def456 | 64 | roof | POINT(12.7 25.8)
2017-01-01 01:12:30+08 | ghi789 | 46 | roof | POINT(12.8 25.9)
2017-01-01 01:13:12+08 | abc123 | 72 | field | POINT(12.9 26)
2017-01-01 01:13:35+08 | def456 | 64 | roof | POINT(13 26.1)
2017-01-01 01:13:42+08 | ghi789 | 56 | roof | POINT(13.1 26.2)
示例一:普通值过滤
根据用户设置的数值限制条件,返回某时间段内速度大于55的骑手记录。比如: “>”, “<”, “=”, “<=”, “>=”, “!=”。ST_AsText的使用,参考:空间对象输出函数
select time,uid,speed,dev_type,ST_AsText(position) from metrics where time >'2017-01-01 01:02:00' and time < '2017-01-01 01:11:02' and speed > 55;
time | uid | speed | dev_type | st_astext
------------------------+--------+-------+----------+------------------
2017-01-01 01:02:23+08 | def456 | 64 | roof | POINT(12.2 25.3)
2017-01-01 01:02:30+08 | ghi789 | 56 | roof | POINT(12.3 25.4)
2017-01-01 01:03:12+08 | abc123 | 82 | field | POINT(12.4 25.5)
2017-01-01 01:03:35+08 | def456 | 74 | roof | POINT(12.5 25.6)
2017-01-01 01:03:42+08 | ghi789 | 66 | roof | POINT(12.6 25.7)
示例二:圈定范围过滤
返回某个时间段内位于多边形范围内的骑手记录。空间范围函数比如: ST_Contains,ST_Distance等使用,参考:空间对象关系函数。
select time,uid,speed,dev_type,ST_AsText(position) from metrics where time >'2017-01-01 01:02:00' and time < '2017-01-01 01:11:02' and ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(12.4, 25.5),ST_Point(13.0,26.1)),4326),position);
time | uid | speed | dev_type | st_astext
------------------------+--------+-------+----------+------------------
2017-01-01 01:03:35+08 | def456 | 74 | roof | POINT(12.5 25.6)
2017-01-01 01:03:42+08 | ghi789 | 66 | roof | POINT(12.6 25.7)
示例三: 圈定范围聚合
返回某个时间段里,位于多边形范围内的骑手的聚合结果(如:sum、avg等)其中ST_MakeBox2D,ST_Point使用,参考:空间对象构造函数
SELECT uid, sum(speed) FROM metrics WHERE time < '2017-01-01 01:13:42' and ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(12.4, 25.5),ST_Point(13.0,26.1)),4326),position) GROUP BY uid;
uid | sum
--------+-----
abc123 | 72
def456 | 138
ghi789 | 112
示例四: 时间窗口聚合(一)
按照5分钟为一个聚合时间窗口,获取骑手最大速度;常见聚合函数如:sum,max,min,avg等
SELECT uid,time_bucket('5 minutes', time) AS interval, max(speed) FROM metrics WHERE uid='def456' and time < '2017-01-01 01:13:42' GROUP BY uid, interval ORDER BY interval DESC;
uid | interval | max
--------+------------------------+-----
def456 | 2017-01-01 01:10:00+08 | 64
def456 | 2017-01-01 01:00:00+08 | 74
示例五: 时间窗口聚合(二)
按照5分钟为一个聚合时间窗口,分别获取该窗口内第一条和最后一条记录。其中time_bucket,last,first等函数使用,参考:时序分析函数
SELECT uid,time_bucket('5 minutes', time) AS interval, last(speed, time), first(speed, time) FROM metrics WHERE uid='def456' and time < '2017-01-01 01:13:42' GROUP BY uid, interval ORDER BY interval DESC;
uid | interval | last | first
--------+------------------------+------+-------
def456 | 2017-01-01 01:10:00+08 | 64 | 64
def456 | 2017-01-01 01:00:00+08 | 74 | 64
示例六:插值
按照5分钟为一个时间窗口,获取骑手时间窗口内最小速度,不存在的窗口插值0。
SELECT period AS date, coalesce(speed,0) AS speed FROM generate_series('2017-01-01 01:00:00'::timestamp,'2017-01-01 01:13:42',interval '5 minutes') AS period LEFT JOIN (SELECT time_bucket('5 minutes',time)::timestamp AS date, min(speed) as speed FROM metrics WHERE uid = 'abc123' and time < '2017-01-01 01:13:42' GROUP BY 1) t ON t.date = period;
date | speed
---------------------+-------
2017-01-01 01:00:00 | 72
2017-01-01 01:05:00 | 0
2017-01-01 01:10:00 | 72
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论