文档

使用详解

更新时间:
一键部署

时空数据库,天然支持时序和空间混合查询与分析,下面是一些参考示例。

示例数据

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
  • 本页导读 (1)
文档反馈