【摘要】
有序運算是指按照一定的次序對有序集合的成員進行計算。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 中提供了跨行引用的語法，也支持有序分組等運算，有了這些后，上面那些問題只要按自然思維去組織計算邏輯，一行代碼就能優雅地寫出來。

總結

以上是生活随笔為你收集整理的sql中排序序号_SQL 和 SPL 的有序运算对比的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。