SQL 如何在時間序列中根據欄位變化分組

將排序（一般按時間排）後的資料按某欄位變化分組統計，也就是分組欄位值與上一行的值比較，如果相同則分到與上一行同組，不同時則建立一個新組。

這個問題用SQL來做很難！

SQL的集合是無序的，早期SQL沒有相鄰行引用的方法。SQL2003標準中加入了視窗函式，能引用相鄰行，但分組仍然非常困難，需要用子查詢人為造出分組序號。

舉個例子：查詢人員某段時間所處城市，並列出起始和結束時間。現有資料庫表footmark資料如下：

NAME	FOOTDATE	CITY
Tom	2020-01-02 08:30:00	Beijing
Tom	2020-01-03 08:30:00	Beijing
Tom	2020-01-04 13:30:05	Beijing
Tom	2020-01-04 16:36:00	Chengdu
Tom	2020-01-05 08:30:00	Chengdu
Tom	2020-01-06 12:30:00	Chengdu
Tom	2020-01-06 17:30:25	Beijing
Tom	2020-01-07 09:30:05	Beijing
Tom	2020-01-09 16:30:00	Beijing
…	…	…

 

要求最終分組統計的結果如下：

NAME	CITY	STARTDATE	ENDDATE
Tom	Beijing	2020-01-02 08:30:00	2020-01-04 13:30:05
Tom	Chengdu	2020-01-04 16:36:00	2020-01-06 12:30:00
Tom	Beijing	2020-01-06 17:30:25	2020-01-09 16:30:00

 

以Oracle為例，用SQL寫出來是這樣：

WITH A AS

       ( SELECT NAME, FOOTDATE, CITY,

              CASE WHEN CITY=LAG(CITY) OVER (PARTITION BY NAME ORDER BY FOOTDATE) THEN 0 ELSE ROWNUM END FLAG

       FROM FOOTMARK ORDER BY NAME, FOOTDATE),

B AS

       ( SELECT NAME, FOOTDATE, CITY,

              MAX(FLAG) OVER (PARTITION BY NAME ORDER BY FOOTDATE) FLAG

       FROM A),

C AS

       ( SELECT NAME, CITY, FLAG,

MIN(FOOTDATE) STARTDATE,

MAX(FOOTDATE) ENDDATE

       FROM B

GROUP BY NAME, CITY, FLAG

ORDER BY NAME, FLAG )

SELECT NAME, CITY, STARTDATE, ENDDATE FROM C;

這裡的FLAG就是人為造出的分組序號，這種SQL既難寫又難懂。

 

對於這種按順序分組，如果使用集算器的SPL語言就會簡單很多，只需1行程式碼：

connect("mydb").query("SELECT * FROM FOOTMARK ORDER BY NAME,FOOTDATE").groups@o(NAME,CITY;min(FOOTDATE):STARTDATE,max(FOOTDATE):ENDDATE)

SPL基於有序集合實現，提供了按有序分組的選項@o，解決這個問題非常容易。

 

SPL提供了等值分組、有序分組、有序條件分組、序號分組、巢狀分組、大資料有序分組、大資料有序條件分組等多種分組方式，詳情參考 。

集算器 SPL 是解決 SQL 難題的專業指令碼語言，它語法簡單，符合自然思維，是天然分步、層次清晰的程式導向計算語言。它採用與資料庫無關的統一語法，編寫的演算法可在資料庫間無縫遷移。它是桌面級計算工具，即裝即用，配置簡單，除錯功能完善，可設定斷點、單步執行，每步執行結果都可檢視。請參閱

SPL也能很方便地嵌入到JAVA應用，可參考 。

具體使用方法可參考 。