hive求出场率和环比

1、有如下数据：（建表语句+sql查询）

id names

1 aa,bb,cc,dd,ee

2 aa,bb,ff,ww,qq

3 aa,cc,rr,yy

4 aa,bb,dd,oo,pp

求英雄的出场排名top3的出场次数及出场率

create table if not exists t_names(

id int,

names array

)

row format delimited

fields terminated by ‘\t’

collection items terminated by ‘,’

;

select *

from (

select name,cc,cc / (sum(cc) over()) as ccl,

rank() over(sort by cc desc) as rk

from (

select

name,

count(1) as cc

from t_names lateral view explode(names) tt as name

group by

name

) a

) aa

where aa.rk <= 3

;

2、有如下通话记录：

Zhangsan Wangwu 01:01:01

Zhangsan Zhaoliu 00:11:21

Zhangsan Yuqi 00:19:01

Zhangsan Jingba 00:21:01

Zhangsan Wuxi 01:31:17

Wangwu Zhaoliu 00:51:01

Wangwu Zhaoliu 01:11:19

Wangwu Yuqi 00:00:21

Wangwu Yuqi 00:23:01

Yuqi Zhaoliu 01:18:01

Yuqi Wuxi 00:18:00

Jingba Wangwu 00:01:01

Jingba Wangwu 00:00:06

Jingba Wangwu 00:02:04

Jingba Wangwu 00:02:54

Wangwu Yuqi 01:00:13

Wangwu Yuqi 00:01:01

Wangwu Zhangsan 00:01:01

统计两个人的通话总时长（用户之间互相通话的时长）

create table relations(

fromstr string,

tostr string,

time string

)

row format delimited

fields terminated by ’ ’

;

select

fromstr,

tostr,

sum(duration) as durations

from (

Select

Case when fromstr >= tostr then fromstr else tostr end fromstr,

Case when fromstr >= tostr then tostr else fromstr end tostr,

Split(time,’:’)[0] * 60 * 60 + Split(time,’:’)[1] * 60 + Split(time,’:’)[2] duration

from relations

) a

group by fromstr,tostr

;

5、有如下销售数据：（建表语句+sql查询）（20分）

店铺 月份 金额

a,01,150

a,01,200

b,01,1000

b,01,800

c,01,250

c,01,220

b,01,6000

a,02,2000

a,02,3000

b,02,1000

b,02,1500

c,02,350

c,02,280

a,03,350

a,03,250

3、编写Hive的HQL语句求出每个店铺的当月销售额和累计到当月的总销售额

create table t_store(

name string,

months int,

money int

)

row format delimited fields terminated by “,”;

select name,months,amoney,sum(amoney) over(distribute by name sort by months asc rows between unbounded preceding and current row) as totalmomey

from (

Select name,months,sum(money) as amoney

From t_store

Group by name,months

) a

；

4、[Hive SQL]统计amt连续3个月，环比增长>50%的user

user_id month amt
1,20170101,100
3,20170101,20
4,20170101,30
1,20170102,200
2,20170102,240
3,20170102,30
4,20170102,2
1,20170101,180
2,20170101,250
3,20170101,30
4,20170101,260
…
…

select user_id
from(
select
user_id,month,mon_amt,pre_mon_amt,
sum(case when ((mon_amt - pre_mon_amt) / pre_mon_amt * 100) > 50
and datediff(to_date(month,‘yyyymm’),to_date(pre2_month,‘yyyymm’),‘mm’) = 2
then 1
else 0 end) over(partition by user_id order by month asc rows between current row and 2 following) as flag
from (
select
user_id,
substr(month,0,6) as month,
sum(amt) as mon_amt,
lag(sum(amt),1,0.00001) over(partition by user_id order by substr(month,0,6) asc ) as pre_mon_amt,
substr(lag(substr(month,0,6),2,‘199001’) over(partition by user_id order by substr(month,0,6) asc),0,6) as pre_2_mon
from amt
group by user_id,substr(month,0,6)
) t1
) t2
where t2.flag >=3;

5、hive经典50题

数据

Student(Sid,Sname,Sage,Ssex)学生表
Sid：学号
Sname：学生姓名
Sbirth：学生生日
Ssex：学生性别

Course(Cid,Cname,T#)课程表
Cid：课程编号
Cname：课程名称
Tid：教师编号

SC(Sid,Cid,score)成绩表
Sid：学号
Cid：课程编号
score：成绩

Teacher(Tid,Tname)教师表
Tid：教师编号：
Tname：教师名字

01 赵雷 1990-01-01 男
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男
05 周梅 1991-12-01 女
06 吴兰 1992-03-01 女
07 郑竹 1989-07-01 女
08 王菊 1990-01-20 女

01 语文 02
02 数学 01
03 英语 03

01 张三
02 李四
03 王五

01 01 80
01 02 90
01 03 99
02 01 70
02 02 60
02 03 80
03 01 80
03 02 80
03 03 80
04 01 50
04 02 30
04 03 20
05 01 76
05 02 87
06 01 31
06 03 34
07 02 89
07 03 98

题目+答案

–1、查询01课程比02课程成绩高的所有学生的学号
select sc1.sid,sc1.score score1,sc2.score score2
from sc sc1
join sc sc2 on sc1.sid=sc2.sid
where sc1.cid = 1 and sc2.cid = 2 and sc1.score>sc2.score

–2、查询平均成绩大于60分的同学的学号和平均成绩
select sid,avg(score) avgscore
from sc
group by sid
having avgscore>60;

–3、查询所有同学的学号、姓名、选课数、总成绩
select stu.sid,stu.sname,
count(sc.cid) countcourse,
case when sum(sc.score) is null then 0 else sum(sc.score) end sumscore
from student stu
left join sc
on sc.sid=stu.sid
group by stu.sid,stu.sname;

–4、查询姓‘李’的老师的个数：
select count(*) from teacher where tname like ‘李%’;

–5、查询没有学过“张三”老师课程的同学的学号、姓名：
select stu.sid,sname
from student stu
join course cs
join teacher t
left join sc on stu.sid=sc.sid and cs.cid=sc.cid and t.tid=cs.tid
where tname=‘张三’
group by stu.sid,sname
having sum(case when sc.sid is null then 0 else 1 end)=0;

–6、查询学过“张三”老师所教的所有课的同学的学号、姓名：
select stu.sid,sname
from student stu
join course cs
join teacher t
left join sc on stu.sid=sc.sid and cs.cid=sc.cid and t.tid=cs.tid
where tname=‘张三’
group by stu.sid,sname
having sum(case when sc.sid is null then 1 else 0 end)=0;

–7、查询学过01并且也学过编号02课程的同学的学号、姓名：
select stu.sid,stu.sname
from student stu
join sc sc1 on sc1.sid=stu.sid
join sc sc2 on sc2.sid=sc1.sid
where sc1.cid=01 and sc2.cid=02;

–8、查询课程编号02的成绩比课程编号01课程成绩低的所有同学的学号、姓名：
select stu.sid,stu.sname
from student stu
join sc sc1 on sc1.sid=stu.sid and sc1.cid=01
left join sc sc2 on sc2.sid=sc1.sid and sc2.cid=02
where sc1.score>sc2.score or sc2.score is null;

–9、查询所有课程成绩小于60的同学的学号、姓名：
select stu.sid,stu.sname
from student stu
left join sc
on sc.sid=stu.sid and sc.score>=60
group by stu.sid,stu.sname
having sum(case when sc.sid is null then 0 else 1 end)=0;

–10、查询没有学全所有课的同学的学号、姓名：
select stu.sid,stu.sname
from student stu
left join course cs
left join sc on sc.sid=stu.sid and cs.cid=sc.cid
group by stu.sid,stu.sname
having sum(case when sc.cid is null then 1 else 0 end)>0;

–11、查询至少有一门课与学号为01同学所学相同的同学的学号和姓名：
select distinct st.sid,st.sname
from student st
join sc sc1
on st.sid=sc1.sid
join sc sc2
on sc1.cid=sc2.cid
where sc2.sid=1;

–12、查询至少学过学号为01同学所有一门课的其他同学学号和姓名；
select distinct st.sid,st.sname
from student st
join sc sc1
on st.sid=sc1.sid
join sc sc2
on sc1.cid=sc2.cid
where sc2.sid=1 and sc1.sid!=1;

–13、查询张三老师教的课的平均成绩：
select avg(score) avgscore
from sc
join course co
on sc.cid=co.cid
join teacher t
on t.tid=co.tid
where tname=‘张三’;

–14、查询和02号的同学学习的课程完全相同的其他同学学号和姓名：
select stu.sid,stu.sname
from student stu
join
(
select stu.sid as stid,sc1.sid as scid,case when stu.sid is null then sc1.sid else stu.sid end as all_id
from student stu
join (
select sc.cid
from sc
where sc.sid = 2
) aa
full outer join sc sc1 on sc1.cid = aa.cid and sc1.sid = stu.sid
) a on a.all_id = stu.sid
group by stu.sid,stu.sname
having sum(case when stid is null or scid is null then 1 else 0 end) = 0 and st.sid!=2
;
–15、查询学习“张三”老师课的成绩表记录：
select sc.*
from sc
join course c
on sc.cid=c.cid
join teacher t
on c.tid=t.tid
where t.tname=‘张三’;

–16、查询没有上过编号03课程的同学学号的02号课的成绩：
select sc.*
from sc
left join
(select * from sc where sc.cid = ‘3’) sc2
on sc.sid =sc2.sid
where sc2.cid is null and sc.cid=2
;

–17、按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，
–按如下形式显示：学生ID，数据库，企业管理，英语，有效课程数，有效平均分
select sc.sid,
max(case course.cname when ‘语文’ then sc.score else 0 end) yuwen,
max(case course.cname when ‘数学’ then sc.score else 0 end) shuxue,
max(case course.cname when ‘英语’ then sc.score else 0 end) yingyu,
count(sc.cid) kechengshu,
avg(sc.score) pingjunfen
from sc join course
on sc.cid=course.cid
group by sc.sid
order by pingjunfen;

–18、查询各科成绩最高和最低的分：以如下的形式显示：课程ID，最高分，最低分
select cid,max(score) maxscore,min(case when score is null then 0 else score end) minscore
from sc
group by cid;
–19、按各科平均成绩从低到高和及格率的百分数从高到低顺序：
select avg(score) avgscore,concat(cast(sum(case when score >= 60 then 1 else 0 end)/count(sc.sid) as string),’%’) jigelv
from sc
group by cid
order by avgscore asc,jigelv desc;

–20、查询如下课程平均成绩和及格率的百分数(用”1行”显示): 语文（01），数学（02），英语（03）
select
max(case t1.cid when 1 then concat(t1.avgscore,’:’,jigelv) else 0 end) as yuwen,
max(case t1.cid when 2 then concat(t1.avgscore,’:’,jigelv) else 0 end) as shuxue,
max(case t1.cid when 3 then concat(t1.avgscore,’:’,jigelv) else 0 end) as yingyu
from
(select sc.cid,avg(score) avgscore,
concat(cast(sum(case when score >= 60 then 1 else 0 end)*100/count(sc.sid) as string),’%’) jigelv
from sc
join course cs
on sc.cid=cs.cid
group by sc.cid,cs.cname having cs.cname=‘语文’ or cs.cname=‘数学’ or cs.cname=‘英语’) t1;

–21、查询不同老师所教不同课程平均分从高到低显示：
select cs.tid,avg(score) avgscore
from sc
join course cs
on sc.cid=cs.cid
join teacher t
on t.tid=cs.tid
group by cs.tid,cs.cid
order by avgscore desc;

–22、查询如下课程成绩第3名到第6名的学生成绩单：语文(01)，数学(02)，英语(03)
select a.*
from
(
select sc.*,
rank() over(distribute by sc.cid sort by sc.score desc) rk
from sc) a
where a.rk between 3 and 6;

–23、统计下列各科成绩，各分数段人数：课程ID，课程名称，[100-85],[85-70],[70-60],[ 小于60] ：
select a.cid,a.cname,a.px,
count(a.px)
from
(
select cs.cid,cs.cname,
(case when score<60 then ‘[小于60]’
when score<70 then ‘[70-60]’
when score<85 then ‘[85-70]’
else ‘[100-85]’ end) as px
from sc
join course cs
on sc.cid=cs.cid
) a
group by a.cid,a.cname,a.px
;

–24、查询学生平均成绩及其名次：
select a.*,
rank() over(distribute by 1 sort by a.avgscore) rk
from
(
select sc.sid,
avg(sc.score) avgscore
from sc
group by sc.sid
) a;

–25、查询各科成绩前三名的记录（不考虑成绩并列情况）：
select a.,cs.cname
from
(
select sc.,
row_number() over(distribute by sc.cid sort by sc.score desc) rk
from sc
) a
join course cs
on cs.cid=a.cid
where a.rk<4;

–26、查询每门课程被选修的学生数：
select cs.cid,cs.cname,sum(case when sc.sid is null then 0 else 1 end) cd
from sc
right join course cs
on sc.cid=cs.cid
group by cs.cid,cs.cname;

–27、查询出只选修一门课程的全部学生的学号和姓名：
select stu.sid,stu.sname
from student stu
join sc
on sc.sid=stu.sid
group by stu.sid,stu.sname
having count(stu.sid)=1;

–28、查询男生、女生人数：
select sum(if(ssex=‘男’,1,0)) male,
sum(if(ssex=‘女’,1,0)) female
from student;

–29、查询姓“张”的学生名单：
select * from student
where sname like ‘张%’;

–30、查询同名同姓的学生名单，并统计同名人数：
select stu.*,
count(sid) over(distribute by stu.sname) stucount
from student stu;

–31、1981年出生的学生名单
select stu.*
from student stu
where substring(stu.sage,1,4)=‘1990’;

–32、查询平均成绩大于80的所有学生的学号、姓名和平均成绩：
select stu.sid,stu.sname,
avg(score) avgscore
from student stu
join sc
on sc.sid=stu.sid
group by stu.sid,stu.sname
having avgscore>80;

–33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列：
select sc.cid,cs.cname,avg(score) avgscore
from sc
join course cs
on cs.cid=sc.cid
group by sc.cid,cs.cname
order by avgscore asc,sc.cid desc;

–34、查询课程名称为“数学”，且分数低于60的学生名字和分数：
select sname,score
from sc
join student stu
on stu.sid=sc.sid
join course cs
on sc.cid=cs.cid
where cs.cname=‘数学’ and score<60;

–35、查询所有学生的选课情况：
select stu.sid,stu.sname,cs.cname
from sc
join course cs
on cs.cid=sc.cid
join student stu
on
stu.sid=sc.sid;

–36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数：
select sname,cname,score
from student stu
join sc
on sc.sid=stu.sid
join course cs
on cs.cid=sc.cid
where sc.score>70;

–37、查询不及格的课程，并按课程号从大到小的排列：
select sc.cid,cname
from sc
join course cs
on cs.cid=sc.cid
where sc.score<60
group by sc.cid,cname
order by sc.cid desc;

–38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名：
select stu.sid,stu.sname
from student stu
join sc
on sc.sid=stu.sid
where sc.cid=3 and score>=80;

–39、求选了课程的学生人数：
select count(aa.sid) from
(select sid
from sc
group by sid) aa;

–40、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select
first_value(sname)
over(distribute by tname sort by score desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) name,
first_value(score)
over(distribute by tname sort by score desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) score
from sc
join student stu
on stu.sid=sc.sid
join course cs
on cs.cid=sc.cid
join teacher t
on t.tid=cs.tid
where tname=‘张三’
limit 1
;

select sname,score
from sc
join student stu
on stu.sid=sc.sid
join course cs
on cs.cid=sc.cid
join teacher t
on t.tid=cs.tid
where tname=‘张三’
order by score desc
limit 1;

–41、查询各个课程及相应的选修人数：
select sc.cid,cs.cname,count(sc.sid) cnt
from sc
join course cs
on sc.cid=cs.cid
group by sc.cid,cs.cname;

–42、查询不同课程成绩相同的学生和学号、课程号、学生成绩：
select stu.sname,stu.sid,sc.cid,sc.score
from student stu
join sc
on sc.sid=stu.sid
join course cs
on cs.cid=sc.cid
order by sc.score

select sc.sid,sc.cid,sc.score
from sc
join
(
select sc.score,count(sc.score) cntscore
from sc
group by sc.score
) a on a.score=sc.score
join course cs
on cs.cid=sc.cid
where a.cntscore>1;

–43、查询每门课程成绩最好的前两名：
select sid,cid,score
from
(
select sc.*,
rank() over(distribute by cid sort by score) rk
from sc
) aa
where aa.rk<3;

–44、统计每门课程的学生选修人数(超过5人的课程才统计)。
–要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序：
select cid,count(sid) cntsid
from sc
group by cid having cntsid>5
order by cntsid desc,cid asc;

–45、检索至少选修两门课程的学生学号：
select sid
from sc
group by sid
having count(cid)>=2
;

–46、查询全部学生选修的课程的课程号和课程名：
select cs.cid,cs.cname
from student stu
join course cs
left join sc
on sc.cid=cs.cid and stu.sid=sc.sid
group by cs.cid,cs.cname
Having sum(case when sc.score is null then 1 else 0 end)=0;

select cid,cname,sum1 from (
select sc.cid,cs.cname,sum(case when score is null then 0 else 1 end) sum1
from student stu
join course cs
left join sc
on sc.cid=cs.cid and stu.sid=sc.sid
group by sc.cid,cs.cname
) aa
join (select count(*) c from student) bb
where sum1=bb.c;

–47、查询没学过”张三”老师讲授的任一门课程的学生姓名：
select stu.sname
from student stu
join course cs
left join teacher t
on t.tid=cs.tid
left join sc
on sc.sid=stu.sid and cs.cid=sc.cid
where tname = ‘张三’
group by sname
having sum(case when score is null then 0 else 1 end)=0
;

–48、查询两门以上不及格课程的同学的学号以及其平均成绩：
select sid,count(cid) cd
from sc
where score<60
group by sid
having cd>=2;

–49、检索02课程分数小于60，按分数降序排列的同学学号
select sc.sid,score
from sc
where sc.score<60 and sc.cid=2
order by score desc;

–50、查询任意一门课程成绩在70分以上的姓名、课程名称和分数：
select sname,cname,score
from student stu
join sc
on sc.sid=stu.sid
join course cs
on cs.cid=sc.cid
where score>70;