中位数是指一组数据排序以后,位于中间位置的数据值。如果数据个数是奇数,中位数就是最中间位置那个值;如果是偶数,则是中间位置那两个数的平均值。
怎么查询出数据分组以后每个组的中位数呢?
用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 擅长解决这类分组子集和组内有序计算,请阅计算各组前 N 行、组内运算、行间计算
集算器 SPL 是解决 SQL 难题的专业脚本语言,它语法简单,符合自然思维,是天然分步、层次清晰的面向过程计算语言。它采用与数据库无关的统一语法,编写的算法可在数据库间无缝迁移。它是桌面级计算工具,即装即用,配置简单,调试功能完善,可设置断点、单步执行,每步执行结果都可查看。请参阅SQL 解题手
SPL也能很方便地嵌入到JAVA应用,可参考Java 如何调用 SPL 脚本。
具体使用方法可参考 如何使用集算器。