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;