sql入门刷题

今天完成非技术快速入门篇剩余的两道题。主要学习了窗口函数。

13.窗口函数

题目如下:需要找到每个学校最低的gpa。

首先想到需要按university分组,最小的gpa可以用min(gap)得到,但由于要取出每个分组里的最小值,考虑使用子查询。由于子查询相对窗口函数性能较差,因此再尝试使用窗口函数解决该问题。

select device_id,university,
       min(gpa) over (partition by university) as gpa
from user_profile
order by university desc;

产生的结果与预期输出有误差,这是因为select device_id,university将全部行都取了出来,我们需要的只有存在min(gpa)的行,因此需要得到这些行所在的行号,使用窗口函数得到min(gap)所在行号后再select device_id,university即可。

# 方法 1  窗口函数
# 使用 row_number() over 完成 行号的取出  
select device_id,university, gpa
from (select *,row_number() over (partition by university order by gpa) as r
      from user_profile) as tmp
where r=1
order by university

窗口函数学习笔记:

窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。

用途:生成一个新变量,比如排名问题,topN问题。本题为top1问题。

排名问题:每个部门按业绩来排名topN问题:找出每个部门排名前N的员工进行奖励

基本语法:

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

<窗口函数>包括专用窗口函数,包括rank,dense_rank,row_number等;聚合函数,如sum,avg,count,max,min 等。

因为窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中

14.复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况,请取出相应明细数据,对于在8月份没有练习过的用户,答题数结果返回0

select 
      u.device_id, 
      u.university,
      count(q.question_id) as question_cnt,
      sum(case when result = 'right' then 1 else 0 end) as right_question_cnt
from user_profile u left join question_practice_detail q
on u.device_id=q.device_id
where university='复旦大学'and month(date)=8
group by u.device_id,u.university;

上述代码输出不了没有练习过的用户,是由于count(q.question_id) as question_cnt只取出了练习过的id,调整后的代码如下:

select 
      u.device_id, 
      u.university,
      sum(case when result is null then 0 else 1 end) as question_cnt,
      sum(result='right') as right_question_cnt
from user_profile u  join question_practice_detail q
on u.device_id=q.device_id
where university='复旦大学' and (month(date)=8 or month(date) is null)
group by u.device_id,u.university;

全部评论
说好入门的,窗口函数都出来了
点赞 回复 分享
发布于 2024-02-21 18:19 北京

相关推荐

我昨晚和朋友喝酒。听她讲了她上段感情的经历,四年前她读大一认识了前任,从刚开始聊天,到后来前任每天开几十公里的车去找她见面。他俩谈了大概三年吧,前任比她年纪大点,带她吃饭喝酒到处玩,还去见她爸妈,前任妈妈还总来给他们做饭,我朋友当时觉得一切都是那么美好,直到她在前任的手机里发现和别人暧昧的聊天记录,可尽管如此她还是选择原谅。我听到这就觉得不可思议,问她为啥不在当时就分手,她说她也提过分手,但前任的道歉态度太诚恳了,他们谈了这么久,她舍不得前任对她的好。可大家都清楚的,出轨只有零次和无数次,不久就又在前任手机里发现了他和别人聊骚,甚至去外地见那个人,过年时有天晚上我朋友问他在哪,他说在家,我朋友凌晨四点打车一百多公里从老家去郑州他家敲门,前任又说在公司,我朋友就跑去他公司找他,果不其然当时他正在外地见其他人。我朋友郑重说分手,前任死活不同意,给她下跪苦苦哀求,在她面前痛哭流涕做保证,甚至还和她签了协议说如果再出现这种情况就给我朋友三十万,俩人甚至还去了律师所公证盖章。就这样拖拉了半年多,可我朋友始终没办法忘却这些事,最后还是分开了。经常有人问我,对象出轨该不该原谅,我总回答看你能接受怎样的结果再做决定,但其实心里还是希望他们能果断离开,因为原谅和宽容都是以折磨自己为代价的,这太痛苦了。哪怕过去了,但你永远不会忘记这件事,就像童话中两个贪心人挖地下的财宝,结果挖出一个人的骸骨,虽然迅速埋上了,甚至在上面种了树,栽了花,但都清楚地知道底下埋的是什么。看见树,看见花,想的却是地下的那具骸骨。记得《飘》里的白瑞德说过一句话:我从来不是那样的人,不能耐心地拾起一片碎片,把它们凑合在一起,然后对自己说这个修补好了的东西跟新的完全一样。一样东西破碎了就是破碎了——我宁愿记住它最好时的模样,而不想把它修补好,然后终生看着那些碎了的地方。如果可以,希望我们永远保有果断离开的勇气。
秋招白月光
点赞 评论 收藏
分享
评论
5
3
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务