题目:
写一个SQL查询,找出每个公司的工资中位数,以任意顺序返回结果表。查询结果个数如下所示。
输出结果如下:
解题思路:
中位数:位于集合正中间的元素。当数据总书为奇数时,最中间的数就是中位数,当数据个数为偶数时,中间两个数的平均值为中位数。
方法一:使用窗口函数row_number() 和floor()向下取整函数
①先以公司分组按工资升序排序作为 rnk 列,以公司为组统计出公司的人数作为 cnt 列;
1 select id, company,salary, 2 row_number() over(partition by company order by salary asc) as rk, 3 count(Salary) over(partition by company) as cnt 4 from Employee;
②使用floor()函数统计出中位数id号
where t.rk in (floor((cnt+1)/2),(floor(cnt+2)/2));
假如总数为5个(1,2,3,4,5即中位数就为3)floor((5+1)/2 )= 3 ,floor((5+2)/2)=3;
假如总数为6个(1,2,3,4,5,6即中位数就为3,4)floor((6+1)/2 )= 3 ,floor((6+2)/2)=4;
③以第②步的结果为条件,进行筛选
1 select id,company,salary 2 from 3 ( select id, company,salary, 4 row_number() over(partition by company order by salary asc) as rk, 5 count(Salary) over(partition by company) as cnt 6 from Employee 7 ) t 8 where t.rk in (floor((cnt+1)/2),(floor(cnt+2)/2));
方法二:使用having()
这个有点难理解 :)
看得博客题解,但是答案有点不对,我暂时也解不出来呜呜~,看到这篇博客的朋友能不能解释一下,谢谢~
SELECT id, Company, Salary FROM Employee WHERE Id IN ( SELECT e1.Id FROM Employee e1 JOIN Employee e2 ON e1.Company = e2.Company GROUP BY e1.Id HAVING sum( CASE WHEN e2.Salary >= e1.Salary THEN 1 ELSE 0 END ) >= count( * ) / 2 and sum( CASE WHEN e1.Salary >= e2.Salary THEN 1 ELSE 0 END ) >= count( * ) / 2 ) GROUP BY id, Company, Salary ORDER BY Company;
小知识:
来源《SQL进阶教程》书籍
使用HAVING 子句进行自连接:求中位数
1 -- 求中位数的 SQL 语句 :在 HAVING 子句中使用非等值自连接 2 SELECT AVG(DISTINCT income) 3 FROM (SELECT T1.income 4 FROM Graduates T1, Graduates T2 5 GROUP BY T1.income 6 --S1 的条件 7 HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END) 8 >= COUNT(*) / 2 9 --S2 的条件 10 AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END) 11 >= COUNT(*) / 2 ) TMP;
注意:
标签:Salary,569,cnt,floor,company,中位数,力扣,MySQL,id From: https://www.cnblogs.com/liu-myu/p/17246777.html