SQL练习14-牛客网72-76题-查询分组的前2名/中位数

yantingting
792 阅读5分钟

链接：www.nowcoder.com/ta/sql?page…

第74题\color{red}{第74题} 每个岗位分数排名前2的用户
第75题\color{red}{第75题} 每个分组的中位数位置
第76题\color{red}{第76题} 每个分组的中位数

  1. 牛客每次考试完，都会有一个成绩表(grade)，如下:

第1行表示用户id为1的用户选择了C++岗位并且考了11001分
...
第8行表示用户id为8的用户选择了前端岗位并且考了9999分

请你写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入):

(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)

select job, round(avg(score),3)
from grade
group by job
order by avg(score)desc
  1. 还是上题中grade表，请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序，如下: 
select g.*
from grade g
left join
    (select job, round(avg(score),3) s
    from grade
    group by job
    order by avg(score)) a
on g.job=a.job
where g.score > a.s
order by id

在通过的代码区看到下面这种解法，更简单：

SELECT *
FROM grade g1
WHERE score > (SELECT AVG(score)
              FROM grade g2
              WHERE g2.job = g1.job
              GROUP BY job)
ORDER BY id;
  1. 牛客每次举办企业笔试的时候，企业一般都会有不同的语言岗位，比如C++工程师，JAVA工程师，Python工程师，每个用户笔试完有不同的分数，现在有一个分数(grade)表简化如下:

第1行表示用户id为1的选择了language_id为1岗位的最后考试完的分数为12000，
....
第7行表示用户id为7的选择了language_id为2岗位的最后考试完的分数为11000，
不同的语言岗位(language)表简化如下:

请你找出每个岗位分数排名前2的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序，得到结果如下: 

select g1.id, l.name,g1.score
from grade g1
join language l on g1.language_id=l.id
where (select count(distinct g2.score)
      from grade g2
      where g2.score>=g1.score and g1.language_id=g2.language_id)<=2
order by l.name, g1.score desc, g1.id

窗口函数：

select id,name,score
from (
    select g.id,l.name,g.score,
    dense_rank() over(partition by g.language_id order by score desc) rn 
    from grade g 
    join language l on g.language_id=l.id) t
where rn<=2
order by name,score desc,id;
  1. 牛客每次考试完，都会有一个成绩表(grade)，如下:

第1行表示用户id为1的用户选择了C++岗位并且考了11001分
...
第8行表示用户id为8的用户选择了前端岗位并且考了9999分

请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序，结果如下:

解释:
第1行表示C++岗位的中位数位置范围为[2,2]，也就是2。因为C++岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的10000是中位数)

第2行表示Java岗位的中位数位置范围为[1,2]。因为Java岗位总共2个人，是偶数，所以要知道中位数，需要知道2个位置的数字，而因为只有2个人，所以中位数位置为[1,2]是正确的(即需要知道位置为1的12000与位置为2的13000才能计算出中位数为12500)

第3行表示前端岗位的中位数位置范围为[2,2]，也就是2。因为前端岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的11000是中位数)

(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round，sqlite不支持floor函数，支持cast(x as integer) 函数，不支持if函数，支持case when ...then ...else ..end函数)

select job,
case count(*)%2 
    when 0 then count(*)/2 
    else count(*)/2+1 end as start,
count(*)/2+1 as end
from grade
group by job
  1. 还是上题中的grade表，请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序，结果如下:

解释：
第1行表示C++岗位的中位数位置上的为用户id为2，分数为10000，在C++岗位里面排名是第2
第2，3行表示Java岗位的中位数位置上的为用户id为4,5，分数为12000,13000，在Java岗位里面排名是第2,1
第4行表示前端岗位的中位数位置上的为用户id为7，分数为11000，在前端岗位里面排名是第2

错误：

select id, job, score,
rank() over (partition by job order by score) as rank
from grade
where rank=max(rank)/2 or rank=max(rank)/2+1

错误原因：窗口函数不能嵌套聚合函数。

select b.id,b.job,b.score,b.rank
from (
    select *,
    row_number() over(partition by job order by score desc) as rank
    from grade) b
join (select job,count(*) number from grade group by job) a
on b.job=a.job
where b.rank=a.number/2+1 or b.rank=(a.number+1)/2
order by b.id;

思路： 表b计算分组排名，表a计算每个分组内有多少个得分num，然后两表join，用表b中排名与表a中num的数量关系作为筛选条件。

avatar
文章
阅读
粉丝