sql中排序序号_SQL 和 SPL 的有序运算对比
【摘要】
有序運算是指按照一定的次序對有序集合的成員進行計算。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 的有序运算对比的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 给plt.axvline设置图例(lab
- 下一篇: python计算precision,re