精华内容
下载资源
问答
  • hivesql练习题
    千次阅读
    2020-12-26 22:11:09

    建表

    create table student(s_id string,s_name string,s_birth string,s_sex string) row format delimited fields terminated by '\t';
    create table course(c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';
    create table teacher(t_id string,t_name string) row format delimited fields terminated by '\t';
    create table score(s_id string,c_id string,s_score int) row format delimited fields terminated by '\t';

    生成数据

    vi /export/data/hivedatas/student.csv

    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 女

    vi /export/data/hivedatas/course.csv

    01 语文 02

    02 数学 01

    03 英语 03

    vi /export/data/hivedatas/teacher.csv

    01 张三

    02 李四

    03 王五

    vi /export/data/hivedatas/score.csv

    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

    导数据到hive

    load data local inpath '/export/data/hivedatas/student.csv' into table student;
    load data local inpath '/export/data/hivedatas/course.csv' into table course;
    load data local inpath '/export/data/hivedatas/teacher.csv' into table teacher;
    load data local inpath '/export/data/hivedatas/score.csv' into table score;

    –注:–hive查询语法

    SELECT [ALL | DISTINCT] select_expr, select_expr, ...    FROM table_reference    [WHERE where_condition]    [GROUP BY col_list [HAVING condition]]    [CLUSTER BY col_list      | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]    ]    [LIMIT number]

    – 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:

    select student.*,a.s_score as 01_score,b.s_score as 02_score

    from student  

    join score a on student.s_id=a.s_id and a.c_id='01'  

    left join score b on student.s_id=b.s_id and b.c_id='02'

    where  a.s_score>b.s_score;

    –答案2

    select student.*,a.s_score as 01_score,b.s_score as 02_score

    from student

    join score a on  a.c_id='01'

    join score b on  b.c_id='02'

    where  a.s_id=student.s_id and b.s_id=student.s_id and a.s_score>b.s_score;

    – 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数:

    select student.*,a.s_score as 01_score,b.s_score as 02_score

    from student

    join score a on student.s_id=a.s_id and a.c_id='01'

    left join score b on student.s_id=b.s_id and b.c_id='02'

    where a.s_score<b.s_score;

    –答案2

    select student.*,a.s_score as 01_score,b.s_score as 02_score

    from student

    join score a on  a.c_id='01'

    join score b on  b.c_id='02'

    where  a.s_id=student.s_id and b.s_id=student.s_id and a.s_score<b.s_score;

    – 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩:

    select  student.s_id,student.s_name,tmp.平均成绩

    from

    student  

    join (   select score.s_id,round(avg(score.s_score),1) as 平均成绩   from score group by s_id) as tmp  

    on tmp.平均成绩>=60

    where student.s_id = tmp.s_id

    –答案2

    select  student.s_id,student.s_name,round(avg (score.s_score),1) as 平均成绩

    from student

    join score

    on student.s_id = score.s_id

    group by student.s_id,student.s_name

    having avg (score.s_score) >= 60;

    – 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:
    – (包括有成绩的和无成绩的)

    select  student.s_id,student.s_name,tmp.avgScore

    from student

    join (select score.s_id,round(avg(score.s_score),1) as avgScore from score group by s_id) as tmp

    on tmp.avgScore < 60 where student.s_id=tmp.s_id

    union all

    select  s2.s_id,s2.s_name,0 as avgScore from student s2

    where s2.s_id not in   (select distinct sc2.s_id from score sc2);

    –答案2

    select  score.s_id,student.s_name,round(avg (score.s_score),1) as avgScore

    from studentinner

    join score

    on student.s_id=score.s_id

    group by score.s_id,student.s_name

    having avg (score.s_score) < 60

    union all

    select  s2.s_id,s2.s_name,0 as avgScore

    from student s2

    where s2.s_id not in   (select distinct sc2.s_id from score sc2);

    – 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:

    select student.s_id,student.s_name,(count(score.c_id) )as total_count,sum(score.s_score)as total_score

    from student left join score

    on student.s_id=score.s_id 

    group by student.s_id,student.s_name ;

    – 6、查询"李"姓老师的数量:

    select t_name,count(1) from teacher where t_name like '李%' group by t_name;

    – 7、查询学过"张三"老师授课的同学的信息:

    select student.*

    from student 

    join score on student.s_id =score.s_id 

    join course on course.c_id=score.c_id

    join teacher on course.t_id=teacher.t_id and t_name='张三';

    – 8、查询没学过"张三"老师授课的同学的信息:

    select student.*

    from student

    left join (select s_id from score join course on course.c_id=score.c_id join teacher on course.t_id=teacher.t_id and t_name='张三')tmp

    on student.s_id =tmp.s_id

    where tmp.s_id is null;

    – 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息:

    select *

    from student

    join (select s_id from score where c_id =1 )tmp1

    on student.s_id=tmp1.s_id

    join (select s_id from score where c_id =2 )tmp2

    on student.s_id=tmp2.s_id;

    – 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息:

    select student.*

    from student

    join (select s_id from score where c_id =1 )tmp1

    on student.s_id=tmp1.s_idleft

    join (select s_id from score where c_id =2 )tmp2

    on student.s_id =tmp2.s_id

    where tmp2.s_id is null;

    – 11、查询没有学全所有课程的同学的信息:

    –先查询出课程的总数量

    select count(1) from course;

    –再查询所需结果

    select student.*

    from studentleft join( select s_id from score group by s_id having count(c_id)=3)tmp

    on student.s_id=tmp.s_id

    where tmp.s_id is null;

    –方法二(一步到位):

    select student.*

    from student

    join (select count(c_id)num1 from course)tmp1

    left join( select s_id,count(c_id)num2 from score group by s_id) tmp2

    on student.s_id=tmp2.s_id and tmp1.num1=tmp2.num2

    where tmp2.s_id is null;

    – 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息:

    select student.*

    from student

    join (select c_id from score where score.s_id=01)tmp1

    join (select s_id,c_id from score)tmp2

    on tmp1.c_id =tmp2.c_id and student.s_id =tmp2.s_id

    where student.s_id not in('01')

    group by student.s_id,s_name,s_birth,s_sex;

    – 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息:
    –备注:hive不支持group_concat方法,可用 concat_ws(’|’, collect_set(str)) 实现

    select student.*,tmp1.course_id

    from student 

    join (select s_id ,concat_ws('|', collect_set(c_id)) course_id from score group by s_id having s_id not in (1))tmp1

    on student.s_id = tmp1.s_id

    join (select concat_ws('|', collect_set(c_id)) course_id2 from score where s_id=1)tmp2

    on tmp1.course_id = tmp2.course_id2;

    – 14、查询没学过"张三"老师讲授的任一门课程的学生姓名:

    select student.*

    from student

    left join (select s_id from score join (select c_id from course join teacher on course.t_id=teacher.t_id and t_name='张三')tmp2 on score.c_id=tmp2.c_id )tmp

    on student.s_id = tmp.s_id 

    where tmp.s_id is null;

    – 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩:

    select student.s_id,student.s_name,tmp.avg_score

    from studentinner

    join (select s_id from score where s_score<60 group by score.s_id having count(s_id)>1)tmp2on student.s_id = tmp2.s_id

    left join ( select s_id,round(AVG (score.s_score)) avg_score from score group by s_id)tmp

    on tmp.s_id=student.s_id;

    – 16、检索"01"课程分数小于60,按分数降序排列的学生信息:

    select student.*,s_score

    from student,score

    where student.s_id=score.s_id and s_score<60 and c_id='01'

    order by s_score desc;

    – 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩:

    select a.s_id,tmp1.s_score as chinese,tmp2.s_score as math,tmp3.s_score as english, round(avg (a.s_score),2) as avgScore

    from score a

    left join (select s_id,s_score from score s1 where c_id='01')tmp1

    on tmp1.s_id=a.s_id

    left join (select s_id,s_score from score s2 where c_id='02')tmp2 on tmp2.s_id=a.s_idleft join (select s_id,s_score from score s3 where c_id='03')tmp3

    on tmp3.s_id=a.s_id

    group by a.s_id,tmp1.s_score,tmp2.s_score,tmp3.s_score

    order by avgScore desc;

    – 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率:
    –及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90

    select course.c_id,course.c_name,tmp.maxScore,tmp.minScore,tmp.avgScore,tmp.passRate,tmp.moderate,tmp.goodRate,tmp.excellentRates

    from course

    join(

    select c_id,max(s_score) as maxScore,min(s_score)as minScore,

    round(avg(s_score),2) avgScore,

    round(sum(case when s_score>=60 then 1 else 0 end)/count(c_id),2)passRate,

    round(sum(case when s_score>=60 and s_score<70 then 1 else 0 end)/count(c_id),2) moderate,

    round(sum(case when s_score>=70 and s_score<80 then 1 else 0 end)/count(c_id),2) goodRate,

    round(sum(case when s_score>=80 and s_score<90 then 1 else 0 end)/count(c_id),2) excellentRates

    from score group by c_id) tmp

    on tmp.c_id=course.c_id;

    – 19、按各科成绩进行排序,并显示排名:
    – row_number() over()分组排序功能(mysql没有该方法)

    select s1.*,row_number()over(order by s1.s_score desc) Ranking

    from score s1

    where s1.c_id='01'

    order by noRanking asc

    union all

    select s2.*,row_number()over(order by s2.s_score desc) Ranking

    from score s2

    where s2.c_id='02'

    order by noRanking asc

    union all

    select s3.*,row_number()over(order by s3.s_score desc) Ranking

    from score s3

    where s3.c_id='03'

    order by noRanking asc;

    – 20、查询学生的总成绩并进行排名:

    select score.s_id,s_name,sum(s_score) sumscore,row_number()over(order by sum(s_score) desc) Ranking

    from score ,student

    where score.s_id=student.s_id

    group by score.s_id,s_name

    order by sumscore desc;

    – 21、查询不同老师所教不同课程平均分从高到低显示:
    – 方法1

    select course.c_id,course.t_id,t_name,round(avg(s_score),2)as avgscore

    from course

    join teacher

    on teacher.t_id=course.t_id

    join score

    on course.c_id=score.c_id  

    group by course.c_id,course.t_id,t_name 

    order by avgscore desc;

    – 方法2

    select course.c_id,course.t_id,t_name,round(avg(s_score),2)as avgscore

    from course,teacher,score

    where teacher.t_id=course.t_id and course.c_id=score.c_id    

    group by course.c_id,course.t_id,t_name 

    order by avgscore desc;

    – 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩:

    select tmp1.*

    from (select * from score where c_id='01' order by s_score desc limit 3) tmp1 order by s_score asc limit 2

    union all

    select tmp2.* from (select * from score where c_id='02' order by s_score desc limit 3) tmp2 order by s_score asc limit 2

    union all

    select tmp3.* from (select * from score where c_id='03' order by s_score desc limit 3)tmp3    order by s_score asc limit 2;

    – 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

    select c.c_id,c.c_name,tmp1.s0_60, tmp1.percentum,tmp2.s60_70, tmp2.percentum,tmp3.s70_85, tmp3.percentum,tmp4.s85_100, tmp4.percentum

    from course c

    join(

    select c_id,sum(case when s_score<60 then 1 else 0 end )as s0_60, round(100*sum(case when s_score<60 then 1 else 0 end )/count(c_id),2)as percentum

    from score group by c_id)tmp1 on tmp1.c_id =c.c_id

    left join (

    select c_id,sum(case when s_score<70 and s_score>=60 then 1 else 0 end )as s60_70, round(100*sum(case when s_score<70 and s_score>=60 then 1 else 0 end )/count(c_id),2)as percentum

    from score group by c_id) tmp2 on tmp2.c_id =c.c_idleft

    join(

    select c_id,sum(case when s_score<85 and s_score>=70 then 1 else 0 end )as s70_85, round(100*sum(case when s_score<85 and s_score>=70 then 1 else 0 end )/count(c_id),2)as percentum

    from score group by c_id) tmp3

    on tmp3.c_id =c.c_idleft

    join (

    select c_id,sum(case when s_score>=85 then 1 else 0 end )as s85_100, round(100*sum(case when s_score>=85 then 1 else 0 end )/count(c_id),2)as percentum  

    from score group by c_id)  tmp4 

     

    on tmp4.c_id =c.c_id;

    – 24、查询学生平均成绩及其名次:

    select tmp.*,row_number()over(order by tmp.avgScore desc) Ranking

    from (

    select student.s_id, student.s_name, round(avg(score.s_score),2) as avgScore

    from student join score on student.s_id=score.s_id group by student.s_id,student.s_name) tmp

    order by avgScore desc;

    – 25、查询各科成绩前三名的记录

    –课程id为01的前三名

    select score.c_id,course.c_name,student.s_name,s_score

    from score

    join student on student.s_id=score.s_id

    join course on score.c_id='01' and course.c_id=score.c_id

    order by s_score desc limit 3;  

    –课程id为02的前三名

    select score.c_id,course.c_name,student.s_name,s_score

    from score

    join student on student.s_id=score.s_id

    join course on score.c_id='02' and course.c_id=score.c_id

    order by s_score desc limit 3; 

    –课程id为03的前三名

    select score.c_id,course.c_name,student.s_name,s_score

    from scorejoin student on student.s_id=score.s_id

    join course on score.c_id='03' and course.c_id=score.c_id

    order by s_score desc limit 3;

    – 26、查询每门课程被选修的学生数:

    select c.c_id,c.c_name,tmp.number

    from course c

    join (

    select c_id,count(1) as number from score where score.s_score<60 group by score.c_id

    )tmp    

    on tmp.c_id=c.c_id;

    – 27、查询出只有两门课程的全部学生的学号和姓名:

    select st.s_id,st.s_name

    from student st

    join (select s_id from score group by s_id having count(c_id) =2) tmp    

    on st.s_id=tmp.s_id;

    – 28、查询男生、女生人数:

    select tmp1.man,tmp2.women

    from

    (select count(1) as man from student where s_sex='男')tmp1,   

    (select count(1) as women from student where s_sex='女')tmp2;

    – 29、查询名字中含有"风"字的学生信息:

    select * from student where s_name like '%风%';

    – 30、查询同名同性学生名单,并统计同名人数:

    select s1.s_id,s1.s_name,s1.s_sex,count(*) as sameName

    from student s1,student s2

    where s1.s_name=s2.s_name and s1.s_id<>s2.s_id and s1.s_sex=s2.s_sex

    group by s1.s_id,s1.s_name,s1.s_sex;

    – 31、查询1990年出生的学生名单:

    select * from student where s_birth like '1990%';

    – 32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列:

    select score.c_id,c_name,round(avg(s_score),2) as avgScore

    from score

    join course

    on score.c_id=course.c_id  

    group by score.c_id,c_name 

    order by avgScore desc,score.c_id asc;

    – 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:

    select score.s_id,s_name,round(avg(s_score),2)as avgScore

    from score

    join student

    on student.s_id=score.s_id    

    group by score.s_id,s_name 

    having avg(s_score) >= 85;

    – 34、查询课程名称为"数学",且分数低于60的学生姓名和分数:

    select s_name,s_score as mathScore

    from student

    join (select s_id,s_score from score,course where score.c_id=course.c_id and c_name='数学')tmp    

    on tmp.s_score < 60 and student.s_id=tmp.s_id;

    – 35、查询所有学生的课程及分数情况:

    select a.s_name,

    SUM(case c.c_name when '语文' then b.s_score else 0 end ) as chainese,

    SUM(case c.c_name when '数学' then b.s_score else 0 end ) as math,

    SUM(case c.c_name when '英语' then b.s_score else 0 end ) as english,

    SUM(b.s_score) as sumScore

    from student a

    join score b on a.s_id=b.s_id

    join course c on b.c_id=c.c_id    

    group by s_name,a.s_id;

    – 36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:

    select student.s_id,s_name,c_name,s_score

    from student

    join (

    select sc.*

    from score sc

    left join(select s_id from score where s_score < 70 group by s_id)tmp

    on sc.s_id=tmp.s_id

    where tmp.s_id is null

    )tmp2

    on student.s_id=tmp2.s_id

    join course

    on tmp2.c_id=course.c_id

    order by s_id;

    **-- 查询全部及格的信息**

    select sc.*

    from score sc

    left join(select s_id from score where s_score < 60 group by s_id)tmp

    on sc.s_id=tmp.s_id

    where tmp.s_id is null;

    **-- 或(效率低)**

    select sc.*

    from score sc

    where sc.s_id not in (select s_id from score where s_score < 60 group by s_id);

    – 37、查询课程不及格的学生:

    select s_name,c_name as courseName,tmp.s_score

    from student

    join (select s_id,s_score,c_name from score,course where score.c_id=course.c_id and s_score < 60)tmp

    on student.s_id=tmp.s_id;

    –38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名:

    select student.s_id,s_name,s_score as score_01

    from student

    join score

    on student.s_id=score.s_id

    where c_id='01' and s_score >= 80;

    – 39、求每门课程的学生人数:

    select course.c_id,course.c_name,count(1)as selectNum

    from course

    join score

    on course.c_id=score.c_id

    group by course.c_id,course.c_name;

    – 40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩:

    select student.*,tmp3.c_name,tmp3.maxScore

    from (

    select s_id,c_name,max(s_score)as maxScore

    from score

    join (select course.c_id,c_name from course join (select t_id,t_name from teacher where t_name='张三')tmp on course.t_id=tmp.t_id) tmp2

    on score.c_id=tmp2.c_id

    group by score.s_id,c_name

    order by maxScore desc limit 1

    )tmp3

    join student on student.s_id=tmp3.s_id;

    – 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩:

    select distinct a.s_id,a.c_id,a.s_score

    from score a,score b    

    where a.c_id <> b.c_id and a.s_score=b.s_score;

    – 42、查询每门课程成绩最好的前三名:

    select tmp1.*

    from (select *,row_number()over(order by s_score desc) ranking from score where c_id ='01') tmp1

    where tmp1.ranking <= 3

    union all

    select tmp2.* from (select *,row_number()over(order by s_score desc) ranking from score where c_id ='02') tmp2 

    where tmp2.ranking <= 3

    union all

    select tmp3.* from (select *,row_number()over(order by s_score desc) ranking from score where c_id ='03') tmp3

    where tmp3.ranking <= 3;

    – 43、统计每门课程的学生选修人数(超过5人的课程才统计):
    – 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

    select distinct course.c_id,tmp.num

    from course

    join (select c_id,count(1) as num from score group by c_id) tmp    

    where tmp.num>=5 

    order by tmp.num desc ,course.c_id asc;

    – 44、检索至少选修两门课程的学生学号:

    select s_id,count(c_id) as totalCourse

    from score

    group by s_id

    having count(c_id) >= 2;

    – 45、查询选修了全部课程的学生信息:

    select student.* from student, (select s_id,count(c_id) as totalCourse from score group by s_id)tmp

    where student.s_id=tmp.s_id and totalCourse=3;

    –46、查询各学生的年龄(周岁):
    – 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
    方法一

    select

    s_name,

    s_birth,

    (

    year(CURRENT_DATE)-

    year(s_birth)-

    (

    case when

    month(CURRENT_DATE) < month(s_birth) then 1

    when

    month(CURRENT_DATE) = month(s_birth) and day(CURRENT_DATE) < day(s_birth) then 1

    else 0 end)

    ) as age    

    from student;

    方法二:

    select s_name,s_birth,

    floor((datediff(current_date,s_birth) - floor((year(current_date) - year(s_birth))/4))/365) as age

    from student;

    – 47、查询本周过生日的学生:
    –方法1

    select * from student where weekofyear(CURRENT_DATE)+1 =weekofyear(s_birth);

    –方法2

    select s_name,s_sex,s_birth from student where substring(s_birth,6,2)='10' and substring(s_birth,9,2)=14;

    – 48、查询下周过生日的学生:
    –方法1

    select * from student where weekofyear(CURRENT_DATE)+1 =weekofyear(s_birth);

    –方法2

    select s_name,s_sex,s_birth

    from student

    where substring(s_birth,6,2)='10' and substring(s_birth,9,2)>=15  and substring(s_birth,9,2)<=21;

    – 49、查询本月过生日的学生:
    –方法1

    select * from student where MONTH(CURRENT_DATE) =MONTH(s_birth);

    –方法2

    select s_name,s_sex,s_birth from student where substring(s_birth,6,2)='10';

    – 50、查询12月份过生日的学生:

    select s_name,s_sex,s_birth from student where substring(s_birth,6,2)='12';

    更多相关内容
  • Hive SQL练习题

    2020-07-10 10:01:36
  • HiveSQL练习题(一)

    2022-08-09 22:41:24
    hivesql基础练习

    已知:

    (1) 有数个京东店铺,每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志,

    (2) 访问日志存储的表名为test2,访客的用户id为user_id,被访问的店铺名称为shop,数据如下:

    user_idshop
    u1a
    u2b
    u1b
    u1b
    u3c
    u4b
    u1a
    u2c
    u5b
    u4b
    u6c
    u2c
    u1b
    u2

    a

    u2a
    u3a
    u5a
    u5a
    u5a

    需求:

    (1)每个店铺的UV(访客数)

     (2)每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

    //建表语句
    CREATE DATABASE hive_learn;  //创建hive_learn数据库 
    CREATE TABLE hive_learn.test2 ( 
     user_id string, 
     shop string)
    ROW format delimited FIELDS TERMINATED BY '\t'; 
    
    //数据准备
    INSERT INTO TABLE hive_learn.test2 VALUES
    ( 'u1', 'a' ),
    ( 'u2', 'b' ),
    ( 'u1', 'b' ),
    ( 'u1', 'a' ),
    ( 'u3', 'c' ),
    ( 'u4', 'b' ),
    ( 'u1', 'a' ),
    ( 'u2', 'c' ),
    ( 'u5', 'b' ),
    ( 'u4', 'b' ),
    ( 'u6', 'c' ),
    ( 'u2', 'c' ),
    ( 'u1', 'b' ),
    ( 'u2', 'a' ),
    ( 'u2', 'a' ),
    ( 'u3', 'a' ),
    ( 'u5', 'a' ),
    ( 'u5', 'a' ),
    ( 'u5', 'a' );   
    
    需求实现
    (1):
    SELECT 
        shop
        ,count(DISTINCT user_id)
    FROM hive_learn.test2
    GROUP BY shop
    
    (2):
    SELECT 
       t2.shop,
       t2.user_id,
       t2.cnt
    FROM
    (
        SELECT 
            t1.*,
            row_number() over(partition BY t1.shop ORDER BY t1.cnt DESC) rank
        FROM
        (
            SELECT 
                user_id,
                shop,
                count(*) AS cnt
            FROM hive_learn.test2
            GROUP BY user_id,shop
        ) t1
    )t2
    WHERE rank <= 3;                  
    

     

     

    展开全文
  • hivesql语句练习

    2018-12-06 18:42:32
    1.上传tar包 2.解压 tar -zxvf hive-1.2.1.tar.gz 3.安装mysql数据库 推荐yum 在线安装 4.配置hive (a)配置HIVE_HOME环境变量 vi conf/hive-env.sh ... hive -e ‘sql’ bin/hive -e 'select * from t_test'
  • 文章Hive面试题SQL测试题目所需数据,包含建表语句 测试数据等等...................
  • hive sql 50道练习题

    2021-10-09 16:13:16
    查询"01"课程比"02"课程成绩低的学生的信息及课程分数:(和上面的第1一样) select student.*, s1.s_score score01, s2.s_score score02 from student join score s1 on student.s_id = s1.s_id and s1.c_id='01' ...

    建库建表

    --建库
    create database test;
    
    --建表
    create table student(s_id string,s_name string,s_birth string,s_sex string) row format delimited fields terminated by '\t';
    create table course(c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';
    create table teacher(t_id string,t_name string) row format delimited fields terminated by '\t';
    create table score(s_id string,c_id string,s_score int) row format delimited fields terminated by '\t';
    

    生成数据
    vim /opt/module/test/course.txt

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

    vim /opt/module/test/score.txt

    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
    

    vim /opt/module/test/student.txt

    01	赵雷	1990-01-0102	钱电	1990-12-2103	孙风	1990-05-2004	李云	1990-08-0605	周梅	1991-12-0106	吴兰	1992-03-0107	郑竹	1989-07-0108	王菊	1990-01-20

    vim /opt/module/test/teacher.txt

    01	张三
    02	李四
    03	王五
    

    导入数据

    --导入数据
    load data local inpath '/opt/module/test/student.txt' into table student;
    load data local inpath '/opt/module/test/course.txt' into table course;
    load data local inpath '/opt/module/test/teacher.txt' into table teacher;
    load data local inpath '/opt/module/test/score.txt' into table score;
    
    1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数:
    --方式1:直接连接sc1和sc2
    select
        sc1.s_id,
        sc1.s_score score01,
        sc2.s_score score02
    from score sc1
    join score sc2 on sc1.s_id=sc2.s_id
    where sc1.c_id='01' and sc2.c_id='02' and sc1.s_score>sc2.s_score;
    
    --方式2
    select
        student.*,
        s1.s_score score01,
        s2.s_score score02
    from student
    join score s1 on student.s_id = s1.s_id and s1.c_id='01'
    left join score s2 on student.s_id = s2.s_id and s2.c_id='02'
    where s1.s_score>s2.s_score;
    
    --方式3
    select
        student.*,
        s1.s_score score01,
        s2.s_score score02
    from student
    join score s1 on student.s_id = s1.s_id
    left join score s2 on student.s_id = s2.s_id
    where s1.c_id='01' and s2.c_id='02' and s1.s_score>s2.s_score;
    
    --方式4
    select
        student.*,
        s1.s_score score01,
        s2.s_score score02
    from student
    join score s1 on s1.c_id='01'
    left join score s2 on s2.c_id='02'
    where student.s_id = s1.s_id and student.s_id = s2.s_id and s1.s_score>s2.s_score;
    
    1. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数:(和上面的第1题一样)
    select
        student.*,
        s1.s_score score01,
        s2.s_score score02
    from student
    join score s1 on student.s_id = s1.s_id and s1.c_id='01'
    left join score s2 on s1.s_id = s2.s_id and s2.c_id='02'
    where s1.s_score<s2.s_score;
    
    1. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩:
    --方式1:round函数可以去掉null值
    select
        t1.sid,
        t1.sname,
        t1.avg_score
    from
    (
        select
            student.s_id sid,
            student.s_name sname,
            round(avg(s.s_score),1)  avg_score
        from student
        join score s on student.s_id = s.s_id
        group by student.s_id,student.s_name
    )t1
    where avg_score>=60
    order by t1.sid;
    
    --方式2:这个比较好
    --第一步:查询出每个sid对应的平均成绩(通过round函数去掉null值,并且取小数点2位),作为临时表tmp
    --第二步:将student表和tmp表进行关联,筛选出平均成绩avg_score大大于60的信息。
    select
        student.s_id,
        student.s_name,
        tmp.avg_score
    from student
    join
    (
        select
            s_id,
            round(avg(s_score),2) avg_score
        from score
        group by s_id
    )tmp
    on student.s_id=tmp.s_id
    where tmp.avg_score>=60;
    
    --方式3:将student表和score表进行关联,通过s_id和s_name进行分组,在分组的基础上查询每个分组的平均成绩大于等于60的学生信息和平均成绩
    --这个比较简单,而且容易想到
    select
        student.s_id,
        student.s_name,
        round(avg(s.s_score),2)
    from student
    join score s on student.s_id = s.s_id
    group by student.s_id, student.s_name
    having avg(s.s_score)>=60;
    
    1. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:(包括有成绩的和无成绩的)
    --方式1:将成绩小于60以及没有成绩的查询结果union all
    --提示:union all去重;union不去重
    --有成绩大于60的
    select
        student.s_id,
        student.s_name,
        avg_score
    from student
    join
    (
    select
        tmp.s_id,
        tmp.avg_score
     from (
              select s_id,
                     round(avg(s_score), 2) avg_score
              from score
              group by s_id
          ) tmp
     where tmp.avg_score < 60
    )t1
    on student.s_id=t1.s_id
    union all
    (
    --没有成绩的
        select
            student.s_id,
            student.s_name,
            0 avg_score
        from student
        left join score s on student.s_id = s.s_id
        where student.s_id not in
        (
            select s_id
            from score
            group by s_id
        )
    );
    
    --方式1
    select
        student.s_id,
        student.s_name,
        tmp.avgScore from student
    join
    (
        select
            score.s_id,
            round(avg(score.s_score),1)as avgScore
        from score group by s_id
    )as tmp
    on tmp.avgScore < 60
    where student.s_id=tmp.s_id
    union all
    select
        s2.s_id,
        s2.s_name,
        0 as avgScore
    from student s2
    where s2.s_id not in
    (
        select
            distinct sc2.s_id
        from score sc2
    );
    
    --方式2:用having
    select
        student.s_id,
        student.s_name,
        round(avg(s.s_score),2)
    from student
    join score s on student.s_id = s.s_id
    group by student.s_id, student.s_name
    having avg(s.s_score)<60
    union all
    --没有成绩的
        select
            student.s_id,
            student.s_name,
            0 avg_score
        from student
        left join score s on student.s_id = s.s_id
        where student.s_id not in
        (
            select s_id
            from score
            group by s_id
        );
    
    1. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:
    --方式1:
    --1.通过查询score查询有成绩的选课总数和所有课程的总成绩
    --2.处理选课总数和所有课程总成绩为null的数据,并设置为0
    select
        student.s_id,
        student.s_name,
        case when tmp.count_course is null then 0 else tmp.count_course end,
        case when tmp.sum_score is null then 0 else tmp.sum_score end
    from student
    left join
    (
        select
            s_id,
            count(c_id) count_course,
            sum(score.s_score) sum_score
        from score
        group by s_id
    )tmp
    on student.s_id=tmp.s_id;
    
    --方式2:直接group by
    select
        student.s_id,
        student.s_name,
        count(s.c_id) total_course,
        sum(s.s_score) total_score
    from student
    left join score s on student.s_id = s.s_id
    group by student.s_id,student.s_name;
    
    1. 查询"李"姓老师的数量
    select
        t_name,
        count(1)
    from teacher
    where t_name like '李%'
    group by t_name;
    
    1. 查询学过"张三"老师授课的同学的信息:
    select *
    from student
    join score s on student.s_id = s.s_id
    join course c on s.c_id = c.c_id
    join teacher t on c.t_id = t.t_id and t.t_name='张三';
    
    1. 查询没学过"张三"老师授课的同学的信息:
    select
        student.*
    from student
    left join
    (
        select
            s_id
        from score
        join course c on score.c_id = c.c_id
        join teacher t on c.t_id = t.t_id
    )tmp
    on student.s_id=tmp.s_id
    where tmp.s_id is null--这个思想比较好,反过来求张三老师教过的;
    
    1. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息:
    --方式1,不能使用‘01’,而需要使用1
    select student.*
    from student
    where s_id in
    (
        select
            t1.s_id
        from
        (
            select
                s_id
            from score
            where c_id=1
            group by s_id
        )t1
        join
        (
            select
                s_id
            from score
            where c_id=2
            group by s_id
        )t2
        on t1.s_id=t2.s_id
    );
    --方式2
    select student.*
    from student
    join
    (
        select
            s_id
        from score
        where c_id=1
    ) tmp1
    on student.s_id = tmp1.s_id
    join
    (
        select
            s_id
        from score
        where c_id=2
    )tmp2
    on student.s_id = tmp2.s_id;
    
    1. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
    select
        student.s_id,
        s_name,
        s_sex,
        s_birth
    from student
    join
    (
    select
        s_id
    from score
    where c_id='01'
    )t1
    on student.s_id=t1.s_id
    left join
    (
        select
            s_id
        from score
        where c_id='02'
    )t2
    on student.s_id=t2.s_id
    where t2.s_id is null;
    
    1. 查询没有学全所有课程的同学的信息:
    select
        student.s_id,
        student.s_name,
        student.s_sex,
        student.s_birth
    from student
    join
    (
        select
            s_id
        from score
        group by s_id
        having count(c_id)<3
    ) t1
    on student.s_id=t1.s_id;
    
    --方法1:不知道这个行不行
    select
        student.*
    from student
    where s_id in 
    (
        select
            s_id
        from score
        where c_id in 
        (
            select
                c_id
            from score
            where s_id='01'
        )t1
    )t2
    
    --方法2:先使用join
    select 
        s_id,
        s_name,
        s_sex
    from student
    join
    (
        select 
            c_id
        from score 
        where s_id='01'
    )t1
    join
    (
        select
            s_id,
            c_id
        from score
    )t2
    on t1.c_id=t2.c_id and student.s_id=t2.s_id
    where student.s_id is not '01'
    group by s_id,s_name,s_sex;
    
    1. 查询和"01"号的同学学习的课程完全相同的其他同学的信息
    --on条件一起写和分开写有什么区别???
    --备注:hive不支持group_concat方法,可用 concat_ws(’|’, collect_set(str)) 实现
    select
        student.*
    from student
    join
    (
        select
            s_id,
            concat_ws('|',collect_set(c_id)) concat_cid
        from score
        group by s_id
        where s_id in not '01'
    )t1
    on student.s_id=t1.s_id
    join
    (
        select 
            concat_ws('|',collect_set(c_id)) concat_cid
        from score 
        group by s_id
        where s_id='01'
    )t2
    on t1.concat_cid=t2.concat_cid ;
    
    1. 查询没学过"张三"老师讲授的任一门课程的学生姓名:
    --这个思想很牛逼!!!
    --先查询学过张三教授的课程的学生信息,反过来求没有学过张三老师课程的学生
    select
        student.s_id,
        student.s_name
    from student
    left join
    (
        select
            s_id
        from score 
        join
        (
            select 
                c_id
            from course
            join teacher
            on course.t_id=teacher.t_id
            where teacher.t_name='张三'
        )t1
        on score.c_id=t1.c_id
    )t2
    on student.s_id=t2.s_id
    where t2.s_id is null;--注意这里!!!
    
    
    1. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩:
    select
        s_id,
        s_name,
       avg(s_score)
    from 
    (
    select
        s_id,
        c_id,
        case when s_score<60 then '不及格' else '及格' s_score_jige
    from score 
    );
    
    1. 检索"01"课程分数小于60,按分数降序排列的学生信息:
    select
        student.s_id,
        student.s_name,
        student.s_sex,
        student.s_birth,
        t1.s_score
    from student
    join
    (
        select
            s_id,
            s_score
        from score
        where c_id='01' and s_score<60
        order by s_score desc
    )t1
    on student.s_id=t1.s_id;
    
    1. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩:
    --类似列转行!!
    --由学生id,课程id和成绩转为--学生id 数学 语文 英语 平均成绩
    --第一步:按平均成绩从高到低显示所有学生的id和平均成绩 --注意08王菊没有成绩
    --第二步:通过左连接将三个课程转为列
    --注意:因为需要查询课程的字段,在进行group by的时候需要将该字段加上
    select
        score.s_id,
        t1.s_score as Chinese,
        t2.s_score as Math,
        t3.s_score as English,
        round(avg(score.s_score),2) avg_score
    from score
    left join
    (
        select
            s_id,
            s_score
        from score
        where c_id='01'
    )t1
    on score.s_id=t1.s_id
    left join
    (
        select
            s_id,
            s_score
        from score
        where c_id='02'
    )t2
    on score.s_id=t2.s_id
    left join
    (
        select
            s_id,
            s_score
        from score
        where c_id='03'
    )t3
    on score.s_id=t3.s_id
    group by score.s_id,t1.s_score,t2.s_score,t3.s_score
    order by avg_score desc;
    
    
    1. 查询各科成绩最高分、最低分和平均分:
    -- 以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
    -- 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
    select
        c.c_id,
        c.c_name,
        max_score,
        min_score,
        avg_score,
        passRate,
        middleRate,
        goodRate,
        excellentRate
    from course c
    join
    (
        select
            c_id,
            max(s_score) max_score,
            min(s_score) min_score,
            round(avg(s_score),2) avg_score,
            round(sum(case when s_score>=60 then 1 else 0 end)/count(c_id),2) passRate,
            round(sum(case when s_score>=70 and s_score<80 then 1 else 0 end)/count(c_id),2) middleRate,
            round(sum(case when s_score>=80 and s_score<90 then 1 else 0 end)/count(c_id),2) goodRate,
            round(sum(case when s_score>=90 then 1 else 0 end)/count(c_id),2) excellentRate
        from score
        group by c_id
    )t1
    on c.c_id=t1.c_id;
    
    
    1. 按各科成绩进行排序,并显示排名
    -- 开窗函数
    select
        score.*,
        row_number() over (order by s_score desc) Ranking
    from score
    where c_id='01'
    union all
    (
    select
        score.*,
        row_number() over (order by s_score desc) Ranking
    from score
    where c_id='02'
    )union all
    (
    select
        score.*,
        row_number() over (order by s_score desc) Ranking
    from score
    where c_id='03'
    );
    
    1. 查询学生的总成绩并进行排名
    select
        s_id,
        sum(s_score) sum_score,
        row_number() over (order by sum(s_score) desc ) rk
    from score
    group by s_id;
    
    1. 查询不同老师所教不同课程平均分从高到低显示:不同课程平均分从高到低显示
    --方法1
    select
        course.t_id,
        course.c_id,
        t_name,
        round(avg(s_score),2) avg_score
    from course
    join score s on course.c_id = s.c_id
    join teacher t on course.t_id = t.t_id
    group by course.c_id,course.t_id,t_name
    order by avg_score desc ;
    
    --方法2
    select
        course.c_id,
        course.t_id
        t_name,
        round(avg(s_score),2)as avgscore
    from course,teacher,score
    where teacher.t_id=course.t_id and course.c_id=score.c_id
    group by course.c_id,course.t_id,t_name
    order by avgscore desc;
    
    1. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩:
    select
        t1.*
    from
    (
        select
            *,
            row_number() over (order by s_score desc )rk
        from score
        where c_id='01'
        order by s_score desc
    )t1
    where rk=2 or rk=3
    union all
    select
        t1.*
    from
    (
        select
            *,
            row_number() over (order by s_score desc )rk
        from score
        where c_id='02'
        order by s_score desc
    )t1
    where rk=2 or rk=3
    union all
    select
        t1.*
    from
    (
        select
            *,
            row_number() over (order by s_score desc )rk
        from score
        where c_id='03'
        order by s_score desc
    )t1
    where rk=2 or rk=3;
    
    1. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
    select
        course.c_id,
        course.c_name,
        first,
        firstRate,
        second,
        secondRate,
        third,
        thirdRate,
        fourth,
        fourthRate
    from course
    join
    (
        select
            c_id,
            sum(case when s_score<=100 and s_score>=85 then 1 else 0 end) first,
            round(sum(case when s_score<=100 and s_score>=85 then 1 else 0 end)/sum(c_id),2) as firstRate,
            sum(case when s_score<=100 and s_score>=85 then 1 else 0 end) second,
            round(sum(case when s_score<=85 and s_score>=70 then 1 else 0 end)/sum(c_id),2) as secondRate,
            sum(case when s_score<=100 and s_score>=85 then 1 else 0 end) third,
            round(sum(case when s_score<=70 and s_score>=60 then 1 else 0 end)/sum(c_id),2) as thirdRate,
            sum(case when s_score<=100 and s_score>=85 then 1 else 0 end) fourth,
            round(sum(case when s_score<=60 and s_score>=0 then 1 else 0 end)/sum(c_id),2) as fourthRate
        from score
        group by c_id
    )tmp
    on course.c_id=tmp.c_id;
    
    1. 查询学生平均成绩及其名次:
    select
        s_id,
        round(avg(s_score) ,2) avg_score,
        row_number() over (order by avg(s_score) desc ) rk
    from score
    group by s_id;
    
    1. 查询各科成绩前三名的记录
    (select
        s_id,
        c_id,
        s_score
    from score
    where c_id='01'
    order by s_score desc
    limit 3)
    union all
    (
        select
            s_id,
            c_id,
            s_score
        from score
        where c_id='02'
        order by s_score desc
        limit 3
    )
    union all
    (
        select
            s_id,
            c_id,
            s_score
        from score
        where c_id='03'
        order by s_score desc
        limit 3
    );
    
    1. 查询每门课程被选修的学生数:
    select
        c_id,
        count(s_id)
    from score
    group by c_id;
    
    1. 查询出只有两门课程的全部学生的学号和姓名
    --方法1:在最外层筛选
    select
        student.s_id,
        s_name
    from student
    join
    (
        select
            s_id,
            count(c_id) count_cid
        from score
        group by s_id
    )tmp
    on student.s_id=tmp.s_id
    where tmp.count_cid=2;
    --方法2:用having筛选
    select
        student.s_id,
        s_name
    from student
    join
    (
        select
            s_id,
            count(c_id) count_cid
        from score
        group by s_id
        having count_cid=2
    )tmp
    on student.s_id=tmp.s_id;
    --或者简单点
    select
        student.s_id,
        s_name
    from student
    join
    (
        select
            s_id
        from score
        group by s_id
        having count(s_score)=2
    )tmp
    on student.s_id=tmp.s_id;
    
    1. 查询男生、女生人数:
    select
        sum(case when s_sex='男' then 1 else 0 end) men,
        sum(case when s_sex='女' then 1 else 0 end) women
    from student;
    
    1. 查询名字中含有"风"字的学生信息:
    select
        *
    from student
    where s_name like '%风%';
    
    1. 查询同名同性学生名单,并统计同名人数:
    select
        s1.s_id,
        s1.s_name,
        count(*) as sameStu
    from student s1,test.student s2
    where s1.s_name=s2.s_name and s1.s_id<>s2.s_id and s1.s_sex=s2.s_sex
    group by s1.s_id,s1.s_name,s1.s_sex;
    
    1. 查询1990年出生的学生名单:
    select
        *
    from student
    where s_birth like '1990%';
    
    1. 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列:
    select
        c_id,
        round(avg(s_score),2) avg_score
    from score
    group by c_id
    order by avg_score desc,c_id;
    
    1. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:
    select
        student.s_id,
        s_name,
        avg_score
    from student
    join
    (
        select
            s_id,
            round(avg(s_score),2) avg_score
        from score
        group by s_id
        having avg(s_score)>=85--这里不能写别名
    )tmp
    on student.s_id=tmp.s_id;
    
    1. 查询课程名称为"数学",且分数低于60的学生姓名和分数:
    --先看有几张表合在一起,然后用where筛选条件避免笛卡尔积
    select
        s_name,
        s_score
    from student,score,course
    where student.s_id=score.s_id and score.c_id=course.c_id and score.s_score<60 and c_name='数学';
    
    1. 查询所有学生的课程及分数情况:
    --姓名 语文 数学 英语 总分
    select
        s.s_id,
        s_name,
        sum(case when c_name='语文' then s_score else 0 end) as Chinese,
        sum(case when c_name='数学' then s_score else 0 end) as Math,
        sum(case when c_name='英语' then s_score else 0 end) as English
    from score
    join course c on score.c_id = c.c_id
    join student s on score.s_id = s.s_id
    group by s.s_id,s_name
    order by s_id;
    
    1. 查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:
    --先查询语文的
    --后面union all
    --union需要去重,但效率低;union all不去重,但效率高
    select
        s_name,
        c_name,
        tmp.s_score
    from
    (
        (
            select
                s_id,
                c_id,
                s_score
            from score
            where c_id='01' and s_score>70
        )
        union all
        (
            select
                s_id,
                c_id,
                s_score
            from score
            where c_id='02' and s_score>70
        )
        union all
        (
            select
                s_id,
                c_id,
                s_score
            from score
            where c_id='03' and s_score>70
        )
    )tmp
    left join course on course.c_id=tmp.c_id
    left join student on student.s_id = tmp.s_id;
    
    1. 查询课程不及格的学生
    select *
    from score
    where s_score<60;
    
    1. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名:
    select
        student.s_id,
        s_name
    from student
    join
    (
        select
            s_id
        from score
        where c_id='01' and s_score>=80
    )tmp
    on student.s_id=tmp.s_id;
    
    1. 求每门课程的学生人数:
    select
        c_id,
        count(1)
    from score
    group by c_id;
    
    1. 查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
    select
        s_id,
        s_score
    from score
    join
    (
        select
            course.c_id,
            course.c_name,
            course.t_id,
            t1.t_name
        from course
        join
        (
            select
                t_id,
                t_name
            from teacher
            where t_name='张三'
        )t1
        on course.t_id=t1.t_id
    )t2
    on score.c_id=t2.c_id
    order by s_score desc
    limit 1;
    
    1. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩:
    select distinct--去重
        s1.s_id,
        s1.c_id,
        s1.s_score
    from score s1,score s2
    where s1.c_id<>s2.c_id and s1.s_score=s2.s_score;
    
    1. 查询每门课程成绩最好的前三名:
    select
        t1.*
    from
    (
        select
            *,
            row_number() over (order by s_score desc ) rk
        from score
        where c_id='01'
    )t1
    where rk<=3
    union all
    select
        t1.*
    from
    (
        select
            *,
            row_number() over (order by s_score desc ) rk
        from score
        where c_id='02'
    )t1
    where rk<=3
    union all
    select
        t1.*
    from
    (
        select
            *,
            row_number() over (order by s_score desc ) rk
        from score
        where c_id='03'
    )t1
    where rk<=3;
    
    1. 统计每门课程的学生选修人数(超过5人的课程才统计):要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
    select
        c_id,
        count(1)
    from score
    group by c_id
    having count(1) > 5
    order by count(1) desc,c_id;
    
    1. 检索至少选修两门课程的学生学号
    select
        s_id,
        count(c_id)
    from score
    group by s_id
    having count(c_id)>=2;
    
    1. 查询选修了全部课程的学生信息:
    select
        s_id,
        count(c_id)
    from score
    group by s_id
    having count(c_id)=3;
    
    1. 查询各学生的年龄(周岁):
      –按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
    select
        s_id,
        case
            when month(s_birth)<month(`current_date`()) or (month(s_birth)=month(`current_date`()) and day(s_birth)<day(`current_date`()))
            then (year(`current_date`())-year(s_birth)-1)
            else (year(`current_date`())-year(s_birth))
        end age
    from student;
    
    --调整一下
    select
        s_id,
        year(`current_date`())-year(s_birth) -
        (
            case
                when month(s_birth)<month(`current_date`()) then 1
                when month(s_birth)=month(`current_date`()) and day(s_birth)<day(`current_date`()) then 1
                else 0
            end
        )as age
    from student;
    
    1. 查询本周过生日的学生
    select
        *
    from student
    where weekofyear(`current_date`())=weekofyear(s_birth);
    
    1. 查询下周过生日的学生
    select
        *
    from student
    where weekofyear(`current_date`())+1=weekofyear(s_birth);
    
    1. 查询本月过生日的学生
    select
        *
    from student
    where month(`current_date`())=month(s_birth);
    
    1. 查询12月份过生日的学生
    select
        *
    from student
    where month(s_birth)='12';
    
    展开全文
  • Hive SQL 练习

    2020-09-04 16:08:19
    的情况,则两人一定认识 需求: 该城市上网用户中两人一定认识的组合数 该可以选用自己擅长的任何技术来解决,可以是JAVA、Python、C、C++编程语言,也可以是Hadoop,Spark大数据工具 sql实现: select friend_id, ...
  • Hive SQL面试(附答案)

    千次阅读 2021-12-30 00:37:58
    点击上方 "大数据肌肉猿"关注,星标一起成长点击下方链接,进入高质量学习交流群今日更新| 950个转型案例分享-大数据交流群本文目录:一、行列转换二、排名中取他值三、累计求值四、窗口大小...
  • 一定要学习的Hive SQL的50道练习题

    千次阅读 热门讨论 2020-07-05 19:58:06
    文章目录写在前面建表准备建表生成数据导入数据到hive需求1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号(重点):2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数(重点):3、查询...
  • 大数据自学之hive通关宝典秘籍 超详细的练习过程,赶紧收藏吧! 》》》》》》》》持续跟新中 现有这样几张表及对应数据,请建好表并插入数据 Student(Sid,Sname,Sage,Ssex)学生表 Sid:学号 Sname:学生姓名 Sbirth...
  • Hive SQL - 50道练习题

    2021-07-01 14:19:09
    目录建表准备建表生成数据导入数据到hive需求1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号(重点):2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数(重点):3、查询平均成绩大于...
  • Hive练习题 第1题 我们有如下的用户访问数据 userId visitDate visitCount u01 2017/1/21 5 u02 2017/1/23 6 ...
  • 第1 我们有如下的用户访问数据 userId visitDate visitCount u01 2017/1/21 5 u02 2017/1/23 6 u03 ...
  • Hive SQL50道练习题.lnk

    2022-02-19 12:43:04
    Hive SQL50道练习题.lnk
  • Hivesql10

    2022-03-16 16:02:52
    手写HQL10
  • 01、Hive数据仓库——Hive SQL练习

    千次阅读 2022-02-18 13:35:57
    Hive数据仓库——Hive SQL练习
  • 找出连续两个或者两个以上是空座位的seat_id. 表:cinema seat_id free ...seat_id 字段是一个自增的整数,free 字段值(‘1’ 表示空余, ‘0’ 表示已被占据)。...sql: select seat_id from ( selec
  • hive sql练习

    2019-07-27 22:42:40
    具体练习数据可从Hive sql数据获取 #0.创建数据库 create database traindb; use database triandb; #1.建表 student表 create table student(sid varchar(10),sname varchar(10),sbirth date,ssex varchar(10)...
  • Hive SQL练习成长之路

    2021-01-22 10:05:10
    本篇主要分享hive sql的一个练习题,主要是练习hive sql在具体场景中的使用,相信对于刚接触hive sql的小伙伴在练习完成之后一定会有帮助。 2.数据准备 在hive中首先准备4张表,分别为student、score、course和...
  • 说起 Hive 大家首先自然会想到的就是 SQL ,所以...SQL 语法相关的面试。另外,光会写 SQL 还是不够的,这还只能算是基本技能,要知道 Hive 的调优才能拿到高阶工程师的入场资格。 本篇面试内容划重点:Hive 调...
  • hive初级练习50(详细过程)

    千次阅读 2020-12-12 14:40:35
    hive初级练习50一.建库和建表1.表信息(1)课程表(course.txt)(2)成绩表(score.txt)(3)学生表(student.txt)(4)教师表(teacher.txt)2.建库建表二.练习50(详细过程)1.查询"01"课程比"02"课程成绩高...
  • 两种sql 3.1 select tt2.banjiID ,tt2.banjiName ,tt2.banjiFZR ,t4.baijiXZ from (select tt1.banjiID,tt1.banjiName,t3.banjiFZR from (select t1.banjiID ,t2.banjiName from table_1 t1 ...
  • HIVE实验练习题

    2020-11-08 19:08:06
    高阶练习
  • Hive Sql题

    2022-02-21 21:00:03
    第一: 了解哪些窗口函数,都是什么功能?找一个在某个业务中的应用? 手写窗口函数及功能意义,同时随便写一个带窗口函数的sql,并说明其sql的含义。 ​ 2、求出每个栏目的被观看次数及累计观看时长? 数据:...
  • Hive SQL50道练习题

    2020-12-23 08:52:40
    – 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息: 面试|不可不知的十大Hive调优技巧最佳实践 select * from studentjoin (select s_id from score where c_id =1 )tmp1 on student.s_id=tmp1.s_...
  • 15道Hive SQL经典训练提升

    千次阅读 热门讨论 2021-08-01 14:53:05
    15道经典hive sql基础面试
  • hive基础 & hive sql练习

    2019-04-17 13:05:20
    hadoop@vm2:~/apache-hive-0.14.0-bin$ ll total 400 drwxrwxr-x 8 hadoop hadoop 4096 Apr 16 04:45 ./ drwxr-xr-x 28 hadoop hadoop 4096 Apr 16 07:04 ../ drwxrwxr-x 3 hadoop hadoop 4096 Apr 16 04:4...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,025
精华内容 810
关键字:

hivesql练习题