25、查询各科成绩前三名的记录三个语句 select s.* from ( select s_id,coure_id,score, rank()over(partition by coure_id order by score desc) a, row_number()over(partition by coure_id order by score desc) b, dense_rank()over(partition by coure_id order by score desc) c from score) s where s.a<4 and s.b<4 and s.c<4;
了解哪些窗口函数，都是什么功能？找一个在某个业务中的应 用？ 排名函数 rank()over(), row_number()over() dense_rank()over()
聚合函数 sum()over() 求和 count()over() 统计个数 max()over() 最大值 min()over() 最小值 avg()over() 平均值
first_value()over() 取分组内排序后，第一个值 last_value()over()取分组内排序后，最后一个值 lag()over()取出前n行数据 lead()over()取出后n行数据
案例 查询各科成绩最高分、最低分和平均分：以如下形式显示： 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 select sc.coure_id,c.course, max(sc.score), min(sc.score), avg(nvl(sc.score,0)), sum(case when sc.score>=60 then 1 else 0 end) dd, sum(case when sc.score>70 and sc.score<=80 then 1 else 0 end) cc, sum(case when sc.score>80 and sc.score<=90 then 1 else 0 end) bb, sum(case when sc.score>90 and sc.score<=100 then 1 else 0 end) aa from score sc join course c on sc.coure_id=c.s_id group by sc.coure_id,c.course;
编写sql实现每个用户截止到每月为止的最大单月访问次数和累 计到该月的总访问次数 userid,month,visits A,2015-01,5 A,2015-01,15 B,2015-01,5 A,2015-01,8 B,2015-01,25 A,2015-01,5 A,2015-02,4 A,2015-02,6 B,2015-02,10 B,2015-02,5 A,2015-03,16 A,2015-03,22 B,2015-03,23 B,2015-03,10 B,2015-03,1 create table if not exists auser( userid string, month string, visits int ) row format delimited fields terminated by ‘,’; load data local inpath ‘./auser’ into table auser; select*from auser;
select aa.userid,aa.month,aa.sum, max(aa.sum)over(partition by aa.userid order by aa.month), sum(aa.sum)over(partition by aa.userid order by aa.month) from (select userid,month,sum(visits) sum from auser group by userid,month) aa;
select userid from select userid,count(day), row_number()over(partition by userid order by day) from logs
26、查询每门课程被选修的学生数: select sc.coure_id,count(s.id) from score group by sc.coure_id
27、查询出只有两门课程的全部学生的学号和姓名: select s.id,s.name from student s left join score sc on s.id=sc.id group by sc.coure_id, having count(score)=2; 28、查询男生、女生人数: select sex,count() from student where group by sex 29、查询名字中含有"风"字的学生信息: select * from student where name like ‘%风%’ 30、查询同名同性学生名单，并统计同名人数: select student.,count() from student group by name,sex hive count()>1 求出每个栏目的被观看次数及累计观看时长 create table video ( uid int, channel string, min int ) row format delimited fields terminated by ’ ';
load data local inpath ‘./vedio’ into table video; select *from video;
select channel, count(1) num, sum(min) total from video group by channel ; 编写连续7天登录的总人数 create table t1( uid int, dt string, login_status int ) row format delimited fields terminated by ’ '; load data local inpath ‘./t1’ into table t1; select*from t1;
select uid, dt from (select t1.uid uid, date_sub(t1.dt,t1.rm) dt from (select uid, dt, row_number() over(distribute by uid sort by dt) rm from t1 where login_status=1) t1)t2 group by uid,dt having count(uid) >7 ; 31、查询1990年出生的学生名单: select * from student where brithday like ‘%1990%’;
32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列: select coure_id, avg(score) aa from score group by coure_id order by aa desc,coure_id asc; 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩: select s.id,s.name,avg(sc.score) avg from student s join score sc on s.id=sc.s_id group by s.id,s.name having avg>=85; 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数: select s.,sc.score from student s join score sc on s.id=sc.s_id join course c on sc.coure_id=c.s_id and c.course=‘数学’ where sc.score<60; 35、查询所有学生的课程及分数情况: select s.,c.course,sc.score from student s join score sc on s.id=sc.s_id join course c on sc.coure_id=c.s_id;
思路： 设置子查询，先查出每个店铺每月销售额， 通过子查询，使用sum()over()求出累计销售总额 36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:
select s.name,c.course,sc.score from student s join score sc on s.id=sc.s_id join course c on sc.coure_id=c.s_id where sc.score>70;
37、查询课程不及格的学生: select s.name,sc.score from student s join score sc on s.id=sc.s_id where sc.score<60;
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名: select s.id,s.name from student s join score sc on s.id=sc.s_id join course c on sc.coure_id=c.s_id and c.s_id=‘01’ where sc.score>80;
39、求每门课程的学生人数: select c.course,count(sc.coure_id) from course c join score sc on c.s_id=sc.coure_id group by c.course;
40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩: select s.id,s.name, max(sc.score), row_number()over(order by sc.score desc) aa from student s join score sc on s.id=sc.s_id join course c on sc.coure_id=c.s_id join teach t on c.teach_id=t.teach_id and t.teach_name=‘张三’ group by s.id,s.name,sc.score order by;
select s.* from (select s.id,s.name, row_number()over(order by sc.score desc) aa, sc.score from student s join score sc on s.id=sc.s_id join course c on sc.coure_id=c.s_id join teach t on c.teach_id=t.teach_id and t.teach_name=‘王五’ group by s.id,s.name,sc.score) s where s.aa<2;
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩: select sc.s_id, sc.coure_id, s.coure_id, sc.score from score sc join (select
from score )s on s.s_id=sc.s_id where s.coure_id<sc.coure_id and s.score=sc.score;42、查询每门课程成绩最好的前三名: select ss.* from (select s.*, sc.coure_id, sc.score, row_number()over(distribute by sc.coure_id sort by sc.score desc) aa from student s join score sc on s.id=sc.s_id) ss where ss.aa<4;
43、统计每门课程的学生选修人数（超过5人的课程才统计）: – 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 select coure_id, count(score) aa from score group by coure_id having aa>5 order by aa desc,coure_id asc
44、检索至少选修两门课程的学生学号: select s_id, count(score) cnt from score group by s_id having cnt>=2;
45、查询选修了全部课程的学生信息: select s.id,s.name from student s join course c left join score sc on c.s_id=sc.coure_id and s.id=sc.s_id group by s.id,s.name having sum(case when score is null then 1 else 0 end)=0; 46、查询各学生的年龄(周岁): – 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一 select s., (year(current_date())-year(brithday))age from student s; select s., year(brithday) from student s; SELECT brithday,(year(current_date())-year(brithday) - (CASE WHEN month(current_date())>month(brithday) THEN 0 when month(current_date())= month(brithday) and day(current_date())>=day(brithday) then 0 ELSE 1 END)) AS age FROM student ;
47、查询本周过生日的学生: selectfrom student where weekofyear(brithday)=weekofyear(current_date()); 48、查询下周过生日的学生: select from student where weekofyear(brithday)=weekofyear(current_date()) + 1; 49、查询本月过生日的学生: selectfrom student where month(current_date())=month(brithday); 50、查询12月份过生日的学生: selectfrom student where month(brithday)=12;
