车联网案例，轨迹清洗 - 阿里云RDS PostgreSQL最佳实践 - 窗口查询

标签

PostgreSQL , 窗口函数 , 车联网 , 轨迹 , 轨迹清洗 , lag , lead

背景

车联网中一个非常典型的场景是采集车辆的行驶轨迹，通常来说车辆的轨迹并不会实时上报，可能会堆积若干条轨迹记录，或者间隔多少时间上报一次。

一个典型的数据结构如下

(car_id, pos geometry, crt_time timestamp)  

车辆在行驶，行驶过程中会遇到堵车，红绿灯，那么上报的轨迹记录可能是这样的

1, 位置1, '2017-01-01 12:00:00'  
1, 位置1, '2017-01-01 12:00:05'  
1, 位置1, '2017-01-01 12:00:10'  
1, 位置1, '2017-01-01 12:00:15'  
1, 位置1, '2017-01-01 12:00:20'  
1, 位置2, '2017-01-01 12:00:30'  

也就是说，在同一个位置，因为堵车、等红灯，可能会导致上传多条记录。

那么就涉及到在数据库中清洗不必要的等待记录的需求，在一个点，我们最多保留2条记录，表示到达这个位置和离开这个位置。

这个操作可以使用窗口函数实现。

当然从最佳效率角度来分析，轨迹清洗这个事情，在终端做是更合理的，一个位置的起始点，只留两条。

例子

1、设计表结构

create table car_trace (cid int, pos point, crt_time timestamp);  

2、生成1000万测试数据，假设有1000量车，（为了让数据更容易出现重复，为了测试看效果，位置使用25个点）

insert into car_trace select random()*999, point((random()*5)::int, (random()*5)::int), clock_timestamp() from generate_series(1,10000000);  

3、创建索引

create index idx_car on car_trace (cid, crt_time);  

4、查询数据layout

select * from car_trace where cid=1 order by crt_time limit 1000;  
  
   1 | (3,1) | 2017-07-22 21:30:09.84984  
   1 | (1,4) | 2017-07-22 21:30:09.850297  
   1 | (1,4) | 2017-07-22 21:30:09.852586  
   1 | (1,4) | 2017-07-22 21:30:09.854155  
   1 | (1,4) | 2017-07-22 21:30:09.854425  
   1 | (3,1) | 2017-07-22 21:30:09.854493  
  
观察到了几个重复。  

5、使用窗口过滤单一位置记录，最多仅保留到达这个位置和离开这个位置的两条记录。

这里用到两个窗口函数：

lag，表示当前记录的前面一条记录。

lead，表示当前记录的下一条记录。

判断到达点、离去点的方法如下：

• 当前pos 不等于 前一条pos，说明这条记录是当前位置的到达点。

• 当前pos 不等于 下一条pos，说明这条记录是当前位置的离去点。

• 前一条pos 为空，说明这条记录是第一条记录。

• 下一条pos 为空，说明这条记录是最后一条记录。

select * from   
(  
select   
  *,   
  lag(pos) over (partition by cid order by crt_time) as lag,   
  lead(pos) over (partition by cid order by crt_time) as lead   
from car_trace   
  where cid=1   
  and crt_time between '2017-07-22 21:30:09.83994' and '2017-07-22 21:30:09.859735'  
) t  
  where pos <> lag  
  or pos <> lead  
  or lag is null  
  or lead is null;  
  
 cid |  pos  |          crt_time          |  lag  | lead    
-----+-------+----------------------------+-------+-------  
   1 | (2,1) | 2017-07-22 21:30:09.83994  |       | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.839953 | (2,1) | (5,2)  
   1 | (5,2) | 2017-07-22 21:30:09.840704 | (3,1) | (4,4)  
   1 | (4,4) | 2017-07-22 21:30:09.84179  | (5,2) | (5,2)  
   1 | (5,2) | 2017-07-22 21:30:09.843787 | (4,4) | (1,5)  
   1 | (1,5) | 2017-07-22 21:30:09.844165 | (5,2) | (0,5)  
   1 | (0,5) | 2017-07-22 21:30:09.84536  | (1,5) | (4,1)  
   1 | (4,1) | 2017-07-22 21:30:09.845896 | (0,5) | (3,3)  
   1 | (3,3) | 2017-07-22 21:30:09.846958 | (4,1) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.84984  | (3,3) | (1,4)  
   1 | (1,4) | 2017-07-22 21:30:09.850297 | (3,1) | (1,4)  
   1 | (1,4) | 2017-07-22 21:30:09.854425 | (1,4) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.854493 | (1,4) | (3,2)  
   1 | (3,2) | 2017-07-22 21:30:09.854541 | (3,1) | (2,0)  
   1 | (2,0) | 2017-07-22 21:30:09.855297 | (3,2) | (4,1)  
   1 | (4,1) | 2017-07-22 21:30:09.857592 | (2,0) | (4,1)  
   1 | (4,1) | 2017-07-22 21:30:09.857595 | (4,1) | (0,4)  
   1 | (0,4) | 2017-07-22 21:30:09.857597 | (4,1) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.858996 | (0,4) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.859735 | (3,1) |   
(20 rows)  

未加清洗轨迹，得到的结果如下：

select   
  *,   
  lag(pos) over (partition by cid order by crt_time) as lag,   
  lead(pos) over (partition by cid order by crt_time) as lead   
from car_trace   
  where cid=1   
  and crt_time between '2017-07-22 21:30:09.83994' and '2017-07-22 21:30:09.859735';  
  
 cid |  pos  |          crt_time          |  lag  | lead    
-----+-------+----------------------------+-------+-------  
   1 | (2,1) | 2017-07-22 21:30:09.83994  |       | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.839953 | (2,1) | (5,2)  
   1 | (5,2) | 2017-07-22 21:30:09.840704 | (3,1) | (4,4)  
   1 | (4,4) | 2017-07-22 21:30:09.84179  | (5,2) | (5,2)  
   1 | (5,2) | 2017-07-22 21:30:09.843787 | (4,4) | (1,5)  
   1 | (1,5) | 2017-07-22 21:30:09.844165 | (5,2) | (0,5)  
   1 | (0,5) | 2017-07-22 21:30:09.84536  | (1,5) | (4,1)  
   1 | (4,1) | 2017-07-22 21:30:09.845896 | (0,5) | (3,3)  
   1 | (3,3) | 2017-07-22 21:30:09.846958 | (4,1) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.84984  | (3,3) | (1,4)  
   1 | (1,4) | 2017-07-22 21:30:09.850297 | (3,1) | (1,4)  
   1 | (1,4) | 2017-07-22 21:30:09.852586 | (1,4) | (1,4)  
   1 | (1,4) | 2017-07-22 21:30:09.854155 | (1,4) | (1,4)  
   1 | (1,4) | 2017-07-22 21:30:09.854425 | (1,4) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.854493 | (1,4) | (3,2)  
   1 | (3,2) | 2017-07-22 21:30:09.854541 | (3,1) | (2,0)  
   1 | (2,0) | 2017-07-22 21:30:09.855297 | (3,2) | (4,1)  
   1 | (4,1) | 2017-07-22 21:30:09.857592 | (2,0) | (4,1)  
   1 | (4,1) | 2017-07-22 21:30:09.857595 | (4,1) | (0,4)  
   1 | (0,4) | 2017-07-22 21:30:09.857597 | (4,1) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.858996 | (0,4) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.859735 | (3,1) |   
(22 rows)  

使用lag, lead清洗掉了停留过程中的记录。

被跟踪对象散落导致的扫描IO放大的优化

因为业务中涉及的车辆ID可能较多，不同车辆汇聚的数据会往数据库中写入，如果不做任何优化，那么不同车辆的数据进入数据库后，可能是交错存放的，也就是说一个数据块中，可能有不同车辆的数据。

那么在查询单一车辆的轨迹时，会扫描很多数据块（扫描IO放大）。

优化思路有两种。

1、业务端汇聚分组排序后写入数据库。例如程序在接收到车辆终端提交的数据后，按车辆ID分组，按时间排序，写入数据库(insert into tbl values (),(),...();)。这样的话，同样车辆的数据，可能会尽可能的落在同一个数据块内。

2、数据库端使用分区，重组数据。例如，按车辆ID，每辆车、或者车辆HASH分区存放。

以上两种方法，都是要将数据按查询需求重组，从而达到降低扫描IO的目的。

这个方法与《PostgreSQL 证券行业数据库需求分析与应用》的方法类似，有兴趣的朋友可以参考。