SQL 如何計算每個分組的中位數

中位數是指一組資料排序以後，位於中間位置的資料值。如果資料個數是奇數，中位數就是最中間位置那個值；如果是偶數，則是中間位置那兩個數的平均值。

怎麼查詢出資料分組以後每個組的中位數呢？

用SQL來解決這個問題是很有難度的！

SQL的集合是無序的，沒有資料位置的概念，需要人為地造出行號，但是要對各分組獨立編行號也困難。後來在SQL2003標準中加入了視窗函式，可以對分組編行號了，但是求各組中位數依然繁瑣。

舉個例子：現有成績表SCORES資料如下，要求查出每科成績的中位數。

COURSE	SCORE
History	68.5
History	79.0
History	82.5
History	88.0
History	93.5
Maths	75.5
Maths	83.0
Maths	85.0
Maths	95.5

查詢出來的各科成績中位數應該是：

COURSE	SCORE
History	82.5
Maths	84.0

 

以Oracle為例，用SQL寫出來是這樣：

WITH  A  AS 

       ( SELECT COURSE, SCORE,

              ROW_NUMBER()OVER ( PARTITION BY COURSE ORDER BY SCORE) AS RN,

              COUNT(*) OVER (PARTITION BY COURSE) AS CNT

       FROM SCORES ),

B  AS

       (SELECT * FROM A WHERE RN>(CNT-0.5)/2 AND RN<(CNT+2.5)/2 )

SELECT COURSE, AVG(SCORE) AS SCORE FROM B

GROUP BY COURSE

ORDER BY COURSE;

這裡的A為每組資料加上組內行號並統計每組記錄數，B查出位於每組中間位置的記錄，最後從B裡算出每組平均值，即為中位數。解題步驟比較多，這種SQL不好寫。另外還有不用視窗函式的辦法，語句就更加複雜了，這裡不再列出。

 

集算器的SPL語言支援組內運算，也提供了中位數函式，解決這個問題就會簡單很多，只需1行程式碼：

connect("mydb").query("select * from scores order by course, score").group(COURSE).new(~.COURSE,~.(SCORE).median():SCORE)

 

SPL 擅長解決這類分組子集和組內有序計算，請閱 、 、

集算器 SPL 是解決 SQL 難題的專業指令碼語言，它語法簡單，符合自然思維，是天然分步、層次清晰的程式導向計算語言。它採用與資料庫無關的統一語法，編寫的演算法可在資料庫間無縫遷移。它是桌面級計算工具，即裝即用，配置簡單，除錯功能完善，可設定斷點、單步執行，每步執行結果都可檢視。請參閱

SPL也能很方便地嵌入到JAVA應用，可參考 。

具體使用方法可參考 。