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

示例数据

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