使用sql的having求中位数

最新推荐文章于 2024-07-16 14:19:57 发布

linminx

最新推荐文章于 2024-07-16 14:19:57 发布

阅读量149

点赞数

文章标签： sql 服务器数据库

本文链接：https://blog.csdn.net/linminx/article/details/131489010

版权

一、建表

create table `department` (
	`id` int (10),
	`name` varchar (150),
	`member` int (10)
);

二、初始化数据

insert into `department` (`id`, `name`, `member`) values('1','梦工厂','20');
insert into `department` (`id`, `name`, `member`) values('2','螺丝厂','20');
insert into `department` (`id`, `name`, `member`) values('3','梦工厂','50');
insert into `department` (`id`, `name`, `member`) values('4','梦工厂','30');

三、求中位数

![此图为类比](https://img-blog.csdnimg.cn/a3025d1d2019478ba1edfcfd808d3c1a.png

1、最里层查询

select t1.member
from demo.`department` t1, demo.`department` t2 
group by t1.member
having 
-- S1的条件
sum(case when t1.member <= t2.member then 1 else 0 end) >= count(*)/2
and 
-- S2的条件
sum(case when t1.member >= t2.member then 1 else 0 end) >= count(*)/2

理解上方查询需要知道几点：
1、关键字执行顺序：groupby先分组，再通过having按组筛选
2、having后的聚合函数sum()使用了自连接查询，所以会从自连接的笛卡尔积中选出符合的组，
3、要求sum结果>=count(*)/2，便是指定s1和s2内的元素数至少为一半取上（等号的作用），进而得到上图的s1、s2，最后and取交集

2、外层查询

里层查询出的交集后，可能不止一个数，所以在外层取平均值即可

3、总实现

SELECT AVG(DISTINCT member)
FROM
(
SELECT t1.member
FROM demo.`department` t1, demo.`department` t2
GROUP BY t1.member
HAVING SUM(CASE WHEN t1.member <= t2.member THEN 1 ELSE 0 END) >= COUNT(*)/2
AND SUM(CASE WHEN t1.member >= t2.member THEN 1 ELSE 0 END) >= COUNT(*)/2
) tmp;