车联网案例，轨迹清洗 - 阿里云RDS PostgreSQL最佳实践 - 窗口函数
背景
车联网中一个非常典型的场景是采集车辆的行驶轨迹，通常来说车辆的轨迹并不会实时上报，可能会堆积若干条轨迹记录，或者间隔多少时间上报一次。
一个典型的数据结构如下
(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 证券行业数据库需求分析与应用》的方法类似，有兴趣的朋友可以参考。