全部产品
云市场

使用详解

更新时间:2019-04-29 10:27:59

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

示例数据

metrics表:

  1. select time,uid,speed,dev_type,ST_AsText(position) from metrics;
  2. time | uid | speed | dev_type | st_astext
  3. ------------------------+--------+-------+----------+------------------
  4. 2017-01-01 01:02:00+08 | abc123 | 72 | field | POINT(12.1 25.2)
  5. 2017-01-01 01:02:23+08 | def456 | 64 | roof | POINT(12.2 25.3)
  6. 2017-01-01 01:02:30+08 | ghi789 | 56 | roof | POINT(12.3 25.4)
  7. 2017-01-01 01:03:12+08 | abc123 | 82 | field | POINT(12.4 25.5)
  8. 2017-01-01 01:03:35+08 | def456 | 74 | roof | POINT(12.5 25.6)
  9. 2017-01-01 01:03:42+08 | ghi789 | 66 | roof | POINT(12.6 25.7)
  10. 2017-01-01 01:12:23+08 | def456 | 64 | roof | POINT(12.7 25.8)
  11. 2017-01-01 01:12:30+08 | ghi789 | 46 | roof | POINT(12.8 25.9)
  12. 2017-01-01 01:13:12+08 | abc123 | 72 | field | POINT(12.9 26)
  13. 2017-01-01 01:13:35+08 | def456 | 64 | roof | POINT(13 26.1)
  14. 2017-01-01 01:13:42+08 | ghi789 | 56 | roof | POINT(13.1 26.2)

示例一:普通值过滤

根据用户设置的数值限制条件,返回某时间段内速度大于55的骑手记录。比如: “>”, “<”, “=”, “<=”, “>=”, “!=”。ST_AsText的使用,参考:空间对象输出函数

  1. 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;
  2. time | uid | speed | dev_type | st_astext
  3. ------------------------+--------+-------+----------+------------------
  4. 2017-01-01 01:02:23+08 | def456 | 64 | roof | POINT(12.2 25.3)
  5. 2017-01-01 01:02:30+08 | ghi789 | 56 | roof | POINT(12.3 25.4)
  6. 2017-01-01 01:03:12+08 | abc123 | 82 | field | POINT(12.4 25.5)
  7. 2017-01-01 01:03:35+08 | def456 | 74 | roof | POINT(12.5 25.6)
  8. 2017-01-01 01:03:42+08 | ghi789 | 66 | roof | POINT(12.6 25.7)

示例二:圈定范围过滤

返回某个时间段内位于多边形范围内的骑手记录。空间范围函数比如: ST_Contains,ST_Distance等使用,参考:空间对象关系函数

  1. 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);
  2. time | uid | speed | dev_type | st_astext
  3. ------------------------+--------+-------+----------+------------------
  4. 2017-01-01 01:03:35+08 | def456 | 74 | roof | POINT(12.5 25.6)
  5. 2017-01-01 01:03:42+08 | ghi789 | 66 | roof | POINT(12.6 25.7)

示例三: 圈定范围聚合

返回某个时间段里,位于多边形范围内的骑手的聚合结果(如:sum、avg等)其中ST_MakeBox2D,ST_Point使用,参考:空间对象构造函数

  1. 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;
  2. uid | sum
  3. --------+-----
  4. abc123 | 72
  5. def456 | 138
  6. ghi789 | 112

示例四: 时间窗口聚合(一)

按照5分钟为一个聚合时间窗口,获取骑手最大速度;常见聚合函数如:sum,max,min,avg等

  1. 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;
  2. uid | interval | max
  3. --------+------------------------+-----
  4. def456 | 2017-01-01 01:10:00+08 | 64
  5. def456 | 2017-01-01 01:00:00+08 | 74

示例五: 时间窗口聚合(二)

按照5分钟为一个聚合时间窗口,分别获取该窗口内第一条和最后一条记录。其中time_bucket,last,first等函数使用,参考:时序分析函数

  1. 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;
  2. uid | interval | last | first
  3. --------+------------------------+------+-------
  4. def456 | 2017-01-01 01:10:00+08 | 64 | 64
  5. def456 | 2017-01-01 01:00:00+08 | 74 | 64

示例六:插值

按照5分钟为一个时间窗口,获取骑手时间窗口内最小速度,不存在的窗口插值0。

  1. 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;
  2. date | speed
  3. ---------------------+-------
  4. 2017-01-01 01:00:00 | 72
  5. 2017-01-01 01:05:00 | 0
  6. 2017-01-01 01:10:00 | 72