SQL求处于中位数上的成绩

最新推荐文章于 2023-03-22 09:24:29 发布

鸡康

最新推荐文章于 2023-03-22 09:24:29 发布

阅读量222

点赞数

分类专栏： SQL需求文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/qq_45451226/article/details/121076756

版权

SQL需求专栏收录该内容

13 篇文章 1 订阅

订阅专栏

数据

drop table if exists grade;
CREATE TABLE  grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO grade VALUES
(1,'C++',11001),
(2,'C++',10000),
(3,'C++',9000),
(4,'Java',12000),
(5,'Java',13000),
(6,'B',12000),
(7,'B',11000),
(8,'B',9999),
(9,'B',10002);

在这里插入图片描述

利用窗口函数增加三列

select *,
       count(score) over(partition by job) as total,
       row_number() over(partition by job order by score) as ascend, #升序序号
       row_number() over(partition by job order by score desc) as descend #逆序序号
    from grade

在这里插入图片描述

利用这三列数求出处于中位数的数据(完整代码)

with t_rank as
(
    select *,
       count(score) over(partition by job) as total,
       row_number() over(partition by job order by score) as ascend, #升序序号
       row_number() over(partition by job order by score desc) as descend #逆序序号
    from grade
)
 
select id,job,score,descend
from t_rank
where ascend >= total/2 and descend >= total/2
order by id