92
車聯網案例，軌跡清洗 - 阿裏雲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 證券行業數據庫需求分析與應用》的方法類似，有興趣的朋友可以參考。
最後更新：2017-07-23 21:32:38