众数的概念

众数作为统计学中重要的概念,它指在群体出现次数最多的值,在下面这张表中,众数是10000和20000这两个值。

nameincome
辛普森400000
迈克30000
怀特20000
阿诺德20000
史密斯20000
劳伦斯15000
哈德逊15000
肯特10000
贝克10000
贝克特10000

利用sql求取众数-方法1

有些DBMS已经提供了用来求取众数的函数,但其实用标准SQL也很简单。思路是将收入相同的汇入一个集合,然后从汇总后的各个集合中,找出元素个数最多的集合。用SQL 这么操作很简单。


SELECT income,Count(*) as cnt
FROM
Graduates
Group by 
income
HAVING
count(*) >=ALL(SELECT count(*) from Graduates GROUP BY income);

执行结果:

incomecnt
100003
200003

简要解析

Group by 子句的作用是根据最初的集合生成若干个子集。因此,将收入(income)作为GROUP BY列时,将得到S1~S5这样5个子集。如下图所示:
在这里插入图片描述
这里元素最多的就是S3和S5,二者都是三个元素,因此查询结果也是这两个结果。

利用sql求取众数-方法2

当然采用ALL谓词用于NULL或空集时,可能会出现问题,可以用极致函数来代替,这里要求求取元素最多的集合,因此可以采用max函数。

--使用极致函数求取众数
SELECT income,count(*) as cnt
FROM Graduates
group by income
having count(*) >=(select max(cnt) From

 (select count(*) as cnt 
from
Graduates
group by
income
)TMP
)

中位数

与众数一样,在统计学中,中位数一样被经常用到的指标。它指的是将集合中的元素按升序排列后恰好位于正中间的元素。如果集合的元素个数为偶数,则取中间两个元素的平均值作为中位数。前面的表 Graduates 里有 10 行数据,所以我们取“史密斯 , 20 000”和“劳伦斯 , 15 000”的平均值 17 500 作为中位数。
如果用 SQL,该如何求中位数呢?像面向过程语言的处理方法那样排完序逐行比较,显然是不合理的。所以我们来思考一下如何用面向集合的方式,来查询位于集合正中间的元素。SQL的做法是,将集合里的元素按照大小分为上半部分和下半部分两个子集,同时让这 2 个子集共同拥有集合正中间的元素。这样,共同部分的元素的平均值就是中位数,思路如下图所示。
在这里插入图片描述

sql实现中位数求取

select avg(distinct income)
from
(select T1.income from graduates T1,Graduates T2 Group by T1.income
--条件1
having 
sum(case when T2.income>=T1.income Then 1 else 0 end)>=count(*)/2
--条件2
and
sum(case when T2.income<=T1.income then 1 else 0 end) >=count(*)/2
)

这条 SQL 语句的要点在于比较条>= COUNT(*)/2 里的等号,这个等号是有意地加上的。加上等号并不是为了清晰地分开子集 S1 和S2,而是为了让这 2 个子集拥有共同部分。如果去掉等号,将条件改成“> COUNT(*)/2”,那么当元素个数为偶数时,S1 和 S2 就没有共同的元素了,也就无法求出中位数了。如果事先知道集合的元素个数是奇数,那么因为 FROM 子句里的子查询结果只有一条数据,所以外层的 AVG 函数可以去掉。但是,如果要写出更通用的 SQL 语句(即适用于元素个数为偶数这种情况),AVG 函数还是需要的。这道例题的解法运用了 CASE 表达式、自连接以及 HAVING 子句等 SQL 的各种利器,笔者觉得还是很棒的。

Logo

华为云1024程序员节送福利,参与活动赢单人4000元礼包,更有热门技术干货免费学习

更多推荐