什麼是有序計算

使用過 SQL 的朋友對計算欄位都不會陌生，比如 firstname+lastname，year(birthday)，這些計算欄位屬於 行內計算，不管表示式裡用到的是單個欄位，還是多個欄位，使用的資料都在當前記錄行內。有行內計算，對應的也就有 跨行計算，如：第一名和第二名的差距；從 1 月到當前月份累計的銷售額。按照成績有序，才會有第一名、第二名的說法，累計操作同樣基於有序資料，從第幾個累加到第幾個，這些基於有序集合的計算，就屬於 有序計算。行內計算關心的是每條資料自身的情況，而跨行的有序計算則關心有序資料的變化情況。

相鄰記錄引用

簡單常見的有序計算是相鄰記錄引用，也就是在計算中要引用某種次序下的相鄰記錄。比如下面這些問題：

1、 股價每天的漲幅是多少（比上期）

按日期排序時，引用上一天的股價。

2、 前一天 + 當天 + 後一天的平均股價是多少（移動平均）

按日期排序時，引用前後兩天的股價。

3、 多支股票資料，計算每支股票內的每日漲幅（分組內的比上期）

按股票分組，組內按日期排序，引用上一天股價。

接下來通過這幾個例子研究下 SQL 如何實現這類有序計算。

早期 SQL 的解決方案

早期的 SQL 沒有視窗函式，引用相鄰記錄的方法是用 JOIN 把相鄰記錄拼到同一行。

問題 1 寫出來是這樣的：

SELECT day, curr.price/pre.price rate

FROM (

SELECT day, price, rownum row1

FROM tbl ORDER BY day ASC) curr

LEFT JOIN (

SELECT day, price, rownum row2

FROM tbl ORDER BY day ASC) pre

ON curr.row1=pre.row2+1

即將本表和本表做 JOIN，把前一天和當天作為連線條件，這樣即可將前一天的股價和當天股價連線到同一行中，再用行內計算得到漲幅。一個很簡單的問題必須使用子查詢才能解決。

再看問題 2，計算股價的移動平均，(前一天 + 當天 + 後一天)/3，同樣是使用 JOIN 實現：

SELECT day, (curr.price+pre.price+after.price)/3 movingAvg

FROM (

SELECT day, price, rownum row1

FROM tbl ORDER BY day ASC) curr

LEFT JOIN (

SELECT day, price, rownum row2

FROM tbl ORDER BY day ASC) pre

ON curr.row1=pre.row2+1

LEFT JOIN (

SELECT day, price, rownum row3

FROM tbl ORDER BY day ASC) after

ON curr.row1=after.row3-1

多取一天，就多 JOIN 一個子查詢，試想，如果要計算前 10 天 ~ 後 10 天的移動平均，那需要寫 20 個 JOIN，這種語句能寫死人。

再看更復雜一些的問題 3，股價表裡有多支股票時，增加 code 欄位區分不同的股票，那它的漲幅就要限定在某支股票的分組內：

SELECT code, day ,currPrice/prePrice rate

FROM(

SELECT code, day, curr.price currPrice, pre.price prePrice

FROM (

SELECT code, day, price, rownum row1

FROM tbl ORDER BY code, day ASC) curr

LEFT JOIN (

SELECT code, day, price, rownum row2

FROM tbl ORDER BY code, day ASC) pre

ON curr.row1=pre.row2+1 AND curr.code=pre.code

)

這裡著重看兩個地方：單表排序時，一定要增加股票程式碼，形成組合排序 code,day，code 還必須要在前面，這不難理解，先把一支股票的資料放在一起，然後這支股票組內資料再按照日期排序；資料排序好了還不算完，連線條件裡也要加上股票程式碼相等，否則兩個相鄰的不同股票資料挨著，也會計算漲幅，但這是沒意義的髒資料。

引入視窗函式

從 2003 年起，SQL 標準中引入了視窗函式，帶來了序的概念。有序計算變得容易了許多。上面的三個例子寫起來就簡單多了。

問題 1，比上期。為了看清楚，把視窗函式拆成多行縮排，方便理解：

SELECT day, price /

LAG(price,1)

OVER (

ORDER BY day ASC

) rate

FROM tbl

LAG 函式實現引用前面的記錄。函式裡的參數列示找前面第 1 條的 price，OVER 是視窗函式 LAG 的子句（每個視窗函式都有 OVER 子句），它的作用是定義待分析的有序集合，這個例子很簡單，待分析集合按照日期有序。

問題 2，移動平均。可以用取前邊函式 LAG+ 取後面函式 LEAD 實現，但這裡用 AVG 函式更可取，它能支援一個範圍內（比如前後十條）的平均，LAG/LEAD 每次只能取到一個值:

SELECT price,

AVG(price) OVER (

ORDER BY day ASC

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

) movingAvg

FROM tbl;

這樣取前後 n 條也容易了，只要改變 RANGE BETWEEN 裡的範圍。

問題 3，分組內的有序計算。每支股票的所有股價是一個分組，視窗函式對它也做了支援：

SELECT code, day, price /

LAG(price,1)

OVER (

PARTITION BY code

ORDER BY day ASC

) rate

FROM tbl

OVER 下的 PARTITION BY 子句描述了怎麼劃分分組，LAG 操作會限定在每個組內。這比之前的 JOIN 做法好了很多，描述分組很直觀。；而 JOIN 做法是對資料做組合排序，雖然實際上也是分組的效果，但不容易理解到位。

序號定位

有序集合裡找相鄰記錄，屬於相對位置定位，有時我們還會找絕對位置的記錄，比如計算每天股價與第一天上市價的漲跌差距：

SELECT day, price- FIRST_VALUE(price) OVER (ORDER BY day ASC) FROM tbl

或者，已經知道第 10 天是最高股價，計算出每天和它的差距：

SELECT day, price- NTH_VALUE(price, 10)OVER (ORDER BY day ASC) FROM tbl

再看複雜點的情況，要定位的序號事先未知，需要根據資料計算出來：

4、 股票按照股價排序，取出中間位置的股價（中位數）

先看簡單的單支股票的解法，按照股價排序後，中間位置還不知道在哪，這時得根據實際股票資料的數量算出中間位置：

SELECT *

FROM

SELECT day, price, ROW_NUMBER()OVER (ORDER BY day ASC) seq FROM tbl

WHERE seq=(

SELECT TRUNC((COUNT(*)+1)/2) middleSeq FROM tbl)

FROM 裡的子查詢用 ROW_NUMBER() 給每行生成序號，WHERE 裡的子查詢計算出中間序號。這個 SQL 裡有兩個注意事項，一是不能直接針對第一個子查詢內部過濾，因為 WHERE 裡不能使用同級 SELECT 中的計算欄位，這是 SQL 執行順序導致的；二是 WHERE 裡的子查詢結果一定是一行一列的單個值，這時能直接把它看成單個值和 seq 做等值比較。

計算多支股票中位數的 SQL 如下：

SELECT *

FROM

(SELECT code, day, price,

ROW_NUMBER() OVER ( PARTITION BY code ORDER BY day ASC)seq FROM tbl) t1

WHERE seq=(

SELECT TRUNC((COUNT(*)+1)/2) middleSeq

FROM tbl t2

WHERE t1.code=t2.code)

除了增加視窗函式裡的 PARTITION BY，還要注意計算中間位置時，查詢條件也要限定在一支股票內。

5、 每支股票最高價格日與前一天相比漲幅是多少

這個問題需要兩種排序方式組合起來定位，還是先看單支股票：

SELECT day, price, seq, rate

FROM (

SELECT day, price, seq,

price/LAG(price,1) OVER (ORDER BY day ASC) rate

FROM (

SELECT day, price,

ROW_NUMBER ()OVER (ORDER BY price DESC) seq

FROM tbl)

)

WHERE seq=1

連續兩層子查詢都通過視窗函式給原始資料增加有用資訊，ROW_NUMBER 把價格從高到低標上序號，通過 LAG 計算出每天的漲幅，最後過濾出價格最高的一天就可以了（seq=1）。

注意過濾出最高價格不能先於漲幅的計算，最高價格的前一天還不知道在哪裡，先過濾掉，後面就算不出來漲幅了。

前面已經有幾個針對分組做有序計算的例子了，這個題就不給出最終答案了，讀者有興趣可以自己嘗試寫寫怎麼得出多支股票最高價時的漲幅。

有序分組

有序資訊還可以用於分組。看這個例子：

6、 一支股票最多連續上漲過幾天。

這個問題有點難想了。基本的思路是把按日期有序的股票記錄分成若干組，連續上漲的記錄分成同一組，也就是說，某天的股價比上一天是上漲的，則和上一天記錄分到同一組，如果下跌了，則開始一個新組。最後看所有分組中最大的成員數量，也就是最多連續上漲的天數。

這種分組比較特殊，和記錄的次序有關，而 SQL 裡只支援等值分組，就需要把這種有序分組轉換成常規的等值分組來實現。過程是這樣：

1) 按日期排序，用視窗函式取出每天的前一天股價；

2）對比，如果上漲了的標記為 0，下跌的標記為 1；

3）累加當前行以前的標記，累加的結果類似 0,0,1,1,1,1,2,2,3,3,3…，這些就是我們需要的組號了；

4）現在可以用 SQL 常規的等值分組了。

完整的 SQL 寫出來是這樣：

SELECT MAX(ContinuousDays)

FROM (

SELECT COUNT(*) ContinuousDays

FROM (

SELECT SUM(RisingFlag) OVER (ORDER BY day) NoRisingDays

FROM (

SELECT day, CASE WHEN price>

LAG(price) OVER (ORDER BY day) THEN 0 ELSE 1 END RisingFlag FROM tbl

)

) GROUP BY NoRisingDays

)

這個題已經不簡單了，巢狀了四層的子查詢。細追究下解題思路，就得說 SQL 語言與 JAVA/C 語言的不同特點，SQL 是集合化語言，提供的計算直接針對集合，沒有顯式可精細控制的迴圈操作，更沒有過程中的臨時變數可利用，這導致解決問題的思路和人的自然思路差異比較大，得變換思路，通過幾個規整的集合計算實現出等價效果；用非集合化的語言 JAVA 或 C，比較貼合自然思路，迴圈處理每個資料，過程中產生新組或加入舊組很直觀。當然 JAVA 等語言基本上沒有提供集合運算，也是各有特長。

合理的查詢需求在複雜程度上不會止步：

7、 連續上漲超過三天的股票有哪些？

這個問題題是有序分組 + 分組子集，最後再加個常規的分組、聚合值過濾（HAVING）。通過上個查詢的思路得到每支股票的所有上漲組，最外面套上分組運算得出每支股票的最大上漲天數，並用聚合後的條件運算 HAVING 過濾出上漲大於三天的：

SELECT code, MAX(ContinuousDays)

FROM (

SELECT code, NoRisingDays, COUNT(*) ContinuousDays

FROM (

SELECT code,

SUM(RisingFlag) OVER (PARTITION BY code ORDER BY day) NoRisingDays

FROM (

SELECT code, day,

CASE WHEN price>

LAG(price) OVER (PARTITION BY code ORDER BY day)

THEN 0 ELSE 1 END RisingFlag

FROM tbl

)

) GROUP BY NoRisingDays

)

GROUP BY code

HAVING MAX(ContinuousDays)>=3

這個 SQL 已經很難看懂了。

總結

從上面的討論可以看出。沒有視窗函式 SQL 對有序運算極端不適應（目前還有些資料庫不支援視窗函式），理論上可以寫，但實際的麻煩程度基本上等同於不能用。在引入視窗函式後，有序計算得到了很好的改善，不過對於稍複雜情況還是相當麻煩。

這個原因在於 SQL 的理論基礎，也就是關係代數，是以無序集合作為基礎的，僅靠視窗函式這種打補丁的辦法並不能從根本上解決問題。

其實，計算機語言中的陣列（即集合）是天然有序的（有序號），在 Java/C++ 這些高階語言的思路下很容易理解和實現有序計算，但是這類語言的集合計算能力又比較弱，實現上面這些問題的程式碼也不短（雖然有序計算的解題思路難度並不大）。

esProc 的 SPL 可以很好地解決這一問題。esProc 是專業的資料計算引擎，基於有序集合設計，同時提供了完善的集合運算，相當於 Java 和 SQL 優勢的結合。在 SPL 的支援下，有序集合計算會非常容易，比如上面的問題用 SPL 寫出來是這樣：

1、 T.sort(day).derive(price/price[-1]:rate)

2、 T.sort(day).derive(avg(price[-1:1]):movingAvg)

3、 T.sort(day).group(code).(~.derive(price/price[-1]:rate)).conj()

4、 T.sort(price).group(code).(~((~.len()+1)\2))

5、 T.sort(day).group(code).((p=~.pmax(price),~.calc(p,price/price[-1])))

6、 T.sort(day).group@o(price >price[-1]).max(~.len()))

7、 T.sort(day).group(code).select(~.group@o(price>price[-1]).max(~.len())>3).(code)

SPL 中提供了跨行引用的語法，也支援有序分組等運算，有了這些後，上面那些問題只要按自然思維去組織計算邏輯，一行程式碼就能優雅地寫出來。

SQL 有序計算

什麼是有序計算

相鄰記錄引用

早期 SQL 的解決方案

引入視窗函式

序號定位

有序分組

總結

相關文章