SQL 和 SPL 的有序運算對比

【摘要】
有序運算是指按照一定的次序對有序集合的成員進行計算。SQL 和 SPL 是大家比較熟悉的程式語言，本文將探討對於有序運算問題，這兩種語言的解決方案和基本原理。如何簡便快捷的處理有序運算，這裡為你全程解析，並提供 SQL 和 SPL 示例程式碼。

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

一．相鄰記錄引用

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

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

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

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

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

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

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

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

1. 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 做法是對資料做組合排序，雖然實際上也是分組的效果，但不容易理解到位。

2. SPL的解決方案

問題前面解讀過了，先看下問題 1 的 SPL 指令碼：

A1: 從資料檔案匯入股市資料表

A2: 使用函式 sort 按日期排序後，計算每天股價與前日的漲幅。其中 price[-1] 表示前日的價格。函式 derive 用於為序表增加計算列。

SPL 同樣也支援從資料庫中讀取資料表，比如 A1 可以改為：

A1 的計算結果是一個序表，SPL中的序表是 有序集合，集合的成員是有次序的，這一點與Java等高階語言中的陣列類似。可以通過序號來訪問成員，是有序集合的基本功能。所以在處理有序運算問題時，有序集合具有天然的優勢。訪問前一個交易日的資料，對於 SQL 來說比較複雜，需要子查詢或者視窗函式的幫助。但是對於有序集合來說，只要訪問當前序號 -1 的成員就可以了。不但運算效率更高，理解起來也更加簡單。

我們繼續看一下問題 2 和問題 3 的解決方案。問題 2：

匯入股市資料表，並按日期排序。計算當日、前一日和後一日的股價平均值。其中 price[-1:1] 表示從前一日到後一日的股價。

問題 3：

匯入股市資料表，並按日期排序。使用函式 group 根據股票程式碼分組。每支股票分別計算當日與前一日股價的漲幅。其中符號 ~ 用來表示當前成員。

使用 SPL 來解決問題 2 和問題 3 時，仍然是通過相對的位置序號來訪問相鄰成員。解題思路與問題 1 類似，問題 2 只是從訪問前 1 天變成訪問連續 3 天，問題 3 引入了多支股票所以先按股票程式碼進行了分組。

二．序號定位

1. SQL的解決方案

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

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)

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）。

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

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

2. SPL的解決方案

問題 4 要取出股價的中位數 ，SPL 指令碼如下：

匯入股市資料表，並按股價排序。根據股票程式碼分組後，每支股票分別取出股價中位數。

解題思路是，股票程式碼相同的股票分為一組並按股價排序，接下來每支股票的分組子集按照中間位置的序號訪問成員就可以了。

我們再來看一下問題 5，每支股票最高價格日與前一天相比漲幅是多少。SPL 指令碼如下：

匯入股市資料表，並按日期排序。根據股票程式碼分組。使用函式 pmax 定位股價最大值所在位置，再使用函式 calc 在指定位置上進行計算。

SPL 處理定位問題通常來說分為兩步：首先獲取成員或者滿足條件的位置（序號），然後我們就可以根據序號進行訪問或計算了。我們並不需要自己來實現定位，SPL 提供了很多定位函式，用於查詢成員或表示式在序表中的位置。

三．有序分組

1. SQL的解決方案

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

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 已經很難看懂了。

2. SPL的解決方案

我們先看一下問題 6， 一支股票最多連續上漲過幾天。SPL 指令碼如下：

匯入股市資料表，並按日期排序。使用函式 group 的選項 @o，根據股價是否上漲進行分組。分組時只和相鄰的對比，當股價是否上漲發生變化時產生新組。最後統計連續上漲的天數。

解題思路是，統計出股票每一次連續上漲的天數，再從中選出最長的一組。SPL 不僅語法簡單，更重要的是邏輯清晰，只要按思路順序編寫就可以了。SQL 和 SPL 處理有序分組問題的差別如此巨大，本質上因為 SQL 以無序集合為基礎，而 SPL 的序表是有序集合，更擅長於有序計算。除此以外，SPL 還提供了大量的函式，從而更加降低了使用難度。

問題 7，連續上漲超過三天的股票有哪些。SPL指令碼如下：

匯入股市資料表，並按日期排序。先按照股票程式碼分組，再按照問題 6 的方法，計算出每支股票連續上漲的最大天數，最後選出連續上漲超過 3 天的。

這個問題的 SQL 解決方案已經很難看懂了，但是 SPL 指令碼還是很簡單的。與問題 6 相比，僅僅是多了一個按股票程式碼分組的過程。SPL 的分組與 SQL 的分組有著本質上的區別。SQL 的分組除了只能得到分組彙總的結果，查詢時也只能選出分組時使用的欄位和聚合結果。而 SPL 使用直觀的記錄分組，比如本例中，將相同股票程式碼值的記錄分在一組，分組子集中保留了資料的全部資訊。正因為如此，我們才可以對這些分組子集進行下一步的計算。例如在本例中，我們可以對著每個分組子集再次進行有序分組。

總結

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

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

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

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

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