leetcode569 SQL中位数问题

最新推荐文章于 2024-07-23 10:23:44 发布

上进的菜鸟

最新推荐文章于 2024-07-23 10:23:44 发布

阅读量705

点赞数

CC 4.0 BY-SA版权

分类专栏： Datawhale之MySQL

本文链接：https://blog.youkuaiyun.com/kylin_learn/article/details/90473470

Datawhale之MySQL 专栏收录该内容

43 篇文章

订阅专栏

博客探讨了在SQL中如何解决查找中位数的问题。原始方法使用rank函数但遇到问题，因为相同值的排名相同，而中位数的定义不同。解决方案是利用row_number函数，但MySQL不支持。通过模拟row_number功能，最终实现了找到中位数的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

drop table employees;
Create table employees(Id int,Company varchar(2),salary int);
insert into employees values(1,'A',2341);
insert into employees values(2,'A',341);
insert into employees values(3,'A',15);
insert into employees values(4,'A',15314);
insert into employees values(5,'A',451);
insert into employees values(6,'A',513);
insert into employees values(7,'B',15);
insert into employees values(8,'B',13);
insert into employees values(9,'B',1154);
insert into employees values(10,'B',1345);
insert into employees values(11,'B',1221);
insert into employees values(12,'B',234);
insert into employees values(13,'C',2345);
insert into employees values(14,'C',2645);
insert into employees values(15,'C',2645);
insert into employees values(16,'C',2652);
insert into employees values(17,'C',65);
select * from employees;

一开始是这样写的

SELECT * FROM
(SELECT e1.* ,(SELECT COUNT(salary)+1 FROM employees e2 WHERE e2.Company = e1.Company AND e2.Salary < e1.Salary) As 'rank'
FROM employees e1 ORDER BY e1.Company,e1.Salary) AS t1
JOIN (select Company,COUNT(*) as cnt
     from employees 
     group by Company) AS t2
ON t1.Company = t2.Company
where t1.rank in (ceiling(t2.cnt/2.0),t2.cnt/2+1);
-- 6/2=3,6/2+1=4 偶数
-- 5/2=2.5,5/2+1=3.5,奇数都不满足 
-- ceiling(5/2) = 3 奇数

但是返回是这样

因为我们rank当有一样的值的时候是一样的排序，可是中位数不是这样取的啊

所以row_number可以实现这样的


with cte as(

select Id,Company,salary 

,ROW_NUMBER() over(partition by Company order by salary) as rnk

,count(*) over(partition by Company) as cnt

from employees

)select Id,Company,salary from cte

where rnk in (CEILING(cnt/2.0),cnt/2+1)

但是emmm，mysql不支持row_number

这样实现row_number

set @row=0;
set @mid='';
SELECT * FROM
(SELECT *,
       case when @mid = Company then @row:=@row+1 else @row:=1 end 'rank',
       @mid:=Company
FROM employees
order by Company,salary) AS t1
JOIN (select Company,COUNT(*) as cnt
     from employees 
     group by Company) AS t2
ON t1.Company = t2.Company
where t1.rank in (ceiling(t2.cnt/2.0),t2.cnt/2+1);

参考链接：https://blog.youkuaiyun.com/yige9394/article/details/80506689

所以真正的可改成上面那样