集算器如何最佳化SQL計算（3）序運算

標籤：

跨行引用

早期SQL不直接支援跨行引用，要產生序號後再JOIN，極其繁瑣困難。引入視窗函數後的SQL能夠較方便地引用其它行資料，但寫法仍不簡潔，有多個跨行引用項時代碼會很長。而且如前所述，視窗函數在其它運算結果集基礎上再實施，對視窗Function Compute值的再引用就要寫成子查詢的形式，仍然繁瑣。

MySQL不支援視窗函數，但支援在SQL中使用變數，可以引用到前面的行，但無法引用到後面的行。

集算器提供了方便自然的跨行引用文法。

 

各產品月銷售表結構為：產品、月份、銷量；現要找出銷量比上月多10%的記錄。

	A
1	=db.query("select * from 銷售表 order by 產品,月份")
2	=A1.select(if(產品==產品[-1],銷量/銷量[-1])>1.1)

排序後可以簡單用[-1]就可以引用前一月的資料，且可以直接基於跨行計算值過濾。使用SQL視窗函數則要用子查詢，MySQL則要定義兩個臨時變數。

 

再計算上表中各月前後一個月的銷量移動平均值：

	A
1	=db.query("select * from 銷售表 order by 產品,月份")
2	=A1.derive(if(產品==產品[-1]&&產品==產品[1],銷量{-1:1}.avg()):移動平均)

計算移動平均涉及到反向參考和集合引用，用[1]可引用下一行資料，{-1:1}可引用從上一行到下一行的欄位值集合。類似地，SQL視窗函數也需要子查詢先把相應行計算出來再做移動平均；而MySQL的變數不能後向引用，就很難直接計算了。

 

再看一例，簡化的事件表結構為：序號，時刻，…；時刻應當和序號同步遞增，但可能有錯誤，需要找出時刻沒有和序號同步遞增的記錄。

	A
1	=db.query("select * from 事件表 order by 序號")
2	=A1.select(時刻!=max(時刻{:0})||時刻!=min(時刻{0:}))	和前後所有記錄對比

取集合時還可以從頭取後或取到尾。SQL視窗函數也支援類似的寫法，但兩次比較要做兩個不同方向的排序，當然了必須要用子查詢。

 

有序分組

SQL只提供與次序無關的等值分組，但有時分組的索引值並不能在每條記錄中找到，而是和記錄的次序有關，這種情況，用SQL又需要使用視窗函數（或其它更麻煩的手段）製造出序號才能實現。

集算器提供了與次序相關的分組機制，方便用於與連續區間相關的計算。

 

收支表結構為：月份、收入、支出；找出連續虧損達三月或以上的那些月份的記錄。

	A
1	=db.query("select * from 收支表 order by 月份")
2	[email protected](收入>支出).select(~.收入<~.支出 && ~.len()>=3).conj()

[email protected]表示在分組時只比較相鄰記錄，如果相鄰值發生變化則會分出一個新組。這樣就可以根據收入支出的比較把收支記錄分成贏利、虧損、贏利、…這樣的組，然後取出其中虧損且成員不少於3的組再合并起來。

 

還是這個表，希望計算收入最長連續增長了幾個月。可以設計這樣的分組機制：收入增長時和上月分作一個組，收入下降時則分出一個新組，最後統計群組成員的最大值。

	A
1	=db.query("select * from 收支表 order by 月份")
2	[email protected](收入<收入[-1]).max(~.len())

[email protected]將在條件變化時分出一個新組，即收入降低時。

在視窗函數的支援下，SQL也能實現本例和上例的思路，但寫法非常難懂。

 

區間合并也是常見的有序分組運算。設有事件發生區間表T有欄位：S（開始時刻）、E（結束時刻）；現在要將這些區間中重疊部分去除後再計算該事件實際發生的總時間長度。

	A
1	$select S,E from T order by S
2	=A1.select(E>max(E{:-1}))	去除被包含的條目
3	=A2.run(max(S,E[-1]):S)	去除重疊時間段
4	=A2.sum([email protected](max(S,E[-1]),E))	計算總時間長度
5	=A2.run(if(S<E[-1],S[-1],S):S)[email protected](S;~.m(-1).E:E)	合并有重疊的時間段

這裡給了多種目標的處理方法，充分利用了跨行運算和有序分組的特點。SQL要實現這種運算簡單用視窗函數已經做不到了，需要用到很難理解的遞迴查詢。

 

位置訪問

對於有序的集合，有時我們需要直接用序號訪問成員。SQL延用了數學上的無序集合概念，要產生序號再用條件過濾才能訪問指定位置的成員，這對許多運算造成很大的麻煩。

集算器採用了有序集合機制，允許直接用序號訪問成員，這類運算要方便得多。

比如經濟統計中常用到的在眾多價格中找出中位元：

	A
1	[email protected]("select 價格 from T order by 價格")
2	=A1([(A1.len()+1)\2,A1.len()\2+1]).avg()

 

位置還可以用於分組。事件表結構為：序號、時刻、動作，動作有開始、結束兩種，現在要統計事件持續的總時間長度，即每一對開始和結束之間的時間之和。

	A
1	[email protected]("select 時刻 from 事件表 order by 時刻")
2	=A1.group((#-1)\2).sum([email protected](~(1),~(2))

#表示記錄序號，group((#-1)\2)即將資料每兩個分成一組，然後針對每組計算時間長度再合計即可。

        

根據位置還能進行相鄰跨行引用。設有股價表結構為：交易日、收盤價；現列出計算出股價超過100元的交易日及當日漲幅。

	A
1	=db.query("select * from 股價表 order by 交易日")
2	[email protected](收盤價>100).select(~>1)
3	=A2.new(A1(~).交易日:交易日,A1(~).收盤價-A1(~-1).收盤價:漲幅)

pselect函數將返回滿足條件的成員位置，使用這些位置就可以方便地計算漲幅，而不必象使用視窗函數時事先計算出所有漲幅再過濾。

---恢複內容結束---

集算器如何最佳化SQL計算（3）序運算