車連網案例，曲目清洗 - 阿裡雲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清洗掉了停留程序中的記錄。

因為商務中涉及的車輛ID可能較多，不同車輛彙聚的資料會往資料庫中寫入，如果不做任何優化，那麼不同車輛的資料進入資料庫後，可能是交錯存放的，也就是說一個資料區塊中，可能有不同車輛的資料。

那麼在查詢單一車輛的曲目時，會掃描很多資料區塊（掃描IO放大）。

優化思路有兩種。

1、商務端彙聚分組排序後寫入資料庫。例如程式在接收車輛終端提交的資料後，按車輛ID分組，按時間排序，寫入資料庫(insert into tblvalues (),(),...();)。這樣的話，同樣車輛的資料，可能會盡可能的落在同一個資料區塊內。

2、資料庫端使用分區，重組資料。例如，按車輛ID，每輛車、或者車輛HASH分區存放。

以上兩種方法，都是要將資料按查詢需求重組，從而達到降低掃描IO的目的。

這個方法與《PostgreSQL證券產業資料庫需求剖析與套用》的方法類似，有興趣的朋友可以參考。

相關產品：

1.  雲資料庫RDS

2.  安全管家

3.  物聯網套件

4.  雲端服務器ECS