二、Hive综合应用案例 — 学生成绩查询
第1关：计算每个班的语文总成绩和数学总成绩
任务描述
本关任务：计算每个班的语文总成绩和数学总成绩，要求有哪科低于60分，该名学生成绩不计入计算。
编程要求
根据提示，在右侧编辑器补充代码，计算每个班的语文总成绩和数学总成绩，要求有哪科低于60分，该名学生成绩不计入计算。
创建数据库：mydb
创建成绩表：score
字段名 类型 注释
name string 姓名
chinese string 语文成绩
maths string 数学成绩
部分数据如下：
banzhang,89,98
uanzhishu,99,89
数据切分方式：逗号
数据所在位置：/root/data/step1_files/score.txt
创建班级表：class
字段名 类型 注释
stuname string 姓名
classname string 所在班级
部分数据如下：
xiaoming,c1
xiaohei,c2
数据切分方式：逗号
数据所在位置：/root/data/step1_files/class.txt
测试说明
平台会对你编写的代码进行测试：
预期输出：
c1 88.0 132.0
c2 99.0 155.0
c3 177.0 197.0
开始你的任务吧，祝你成功！
---------- 禁止修改 ----------
drop database if exists mydb cascade;
set hive.auto.convert.join = false;
set hive.ignore.mapjoin.hint=false;
---------- 禁止修改 ----------
---------- begin ----------
---创建mydb数据库
create database if not exists mydb;
---使用mydb数据库
use mydb;
---创建表score
create table if not exists score(
name string comment '姓名',
chinese string comment '语文成绩',
maths string comment '数学成绩'
)
row format delimited fields terminated by ',' stored as textfile;
---导入数据：/root/data/step1_files/score.txt
load data local inpath '/root/data/step1_files/score.txt' into table score;
--创建表class
create table if not exists class(
stuname string comment '姓名',
classname string comment '所在班级'
)
row format delimited fields terminated by ',' stored as textfile;
---导入数据：/root/data/step1_files/class.txt
load data local inpath '/root/data/step1_files/class.txt' into table class;
--计算每个班的语文总成绩和数学总成绩，要求有哪科低于60分，该名学生成绩不计入计算
select t1.classname,t1.chinese,t2.maths from(
select c.classname classname,sum(s.chinese) chinese from class c,score s
where c.stuname=s.name and s.chinese>=60 group by c.classname) t1,(
select c.classname classname,sum(s.maths) maths from class c,score s
where c.stuname=s.name and s.maths>=60 group by c.classname) t2
where t1.classname=t2.classname;
---------- end ----------
第2关：查询选修了3门以上的课程的学生姓名
任务描述
本关任务：查询选修了3门以上的课程的学生姓名。
编程要求
在右侧编辑器补充代码，查询选修了3门以上的课程的学生姓名。
创建数据库：mydb
创建成绩表：my_stu
字段名 类型 注释
id string 学生id
name string 姓名
sex string 性别
age string 年龄
col string 所选的系
部分数据如下：
95001,李勇,男,20,CS
95002,刘晨,女,19,IS
数据切分方式：逗号
数据所在位置：/root/data/step2_files/my_student.txt
创建班级表：my_score
字段名 类型 注释
id string 学生id
courseid string 课程id
score string 成绩
部分数据如下：
95001,1,81
95001,2,85
数据切分方式：逗号
数据所在位置：/root/data/step2_files/my_score.txt
创建班级表：my_course
字段名 类型 注释
courseid string 课程id
coursename string 课程名称
部分数据如下：
1,数据库
2,数学
数据切分方式：逗号
数据所在位置：/root/data/step2_files/my_course.txt
测试说明
平台会对你编写的代码进行测试：
预期输出：
李勇 4
刘晨 4
王敏 3
张立 4
刘刚 4
孙庆 6
易思玲 4
李娜 3
梦圆圆 3
孔小涛 3
包小柏 4
孙花 4
冯伟 4
王小丽 3
王君 4
钱国 3
王风娟 3
王一 4
邢小丽 5
赵钱 3
周二 3
郑明 4
开始你的任务吧，祝你成功！
---------- 禁止修改 ----------
drop database if exists mydb cascade;
set hive.auto.convert.join = false;
set hive.ignore.mapjoin.hint=false;
---------- 禁止修改 ----------
---------- begin ----------
---创建mydb数据库
create database if not exists mydb;
---使用mydb数据库
use mydb;
---创建表my_stu
create table if not exists my_stu(
id string comment '学生id',
name string comment '学生姓名',
sex string comment '性别',
age string comment '年龄',
col string comment '所选的系'
)
row format delimited fields terminated by ',' stored as textfile;
---导入数据：/root/data/step2_files/my_student.txt
load data local inpath '/root/data/step2_files/my_student.txt' into table my_stu;
--创建表my_score
create table if not exists my_score(
id string comment '学生id',
courseid string comment '课程id',
score string comment '成绩'
)
row format delimited fields terminated by ',' stored as textfile;
---导入数据：/root/data/step2_files/my_score.txt
load data local inpath '/root/data/step2_files/my_score.txt' into table my_score;
--创建表my_course
create table if not exists my_course(
courseid string comment '课程id',
coursename string comment '课程名称'
)
row format delimited fields terminated by ',' stored as textfile;
---导入数据：/root/data/step2_files/my_course.txt
load data local inpath '/root/data/step2_files/my_course.txt' into table my_course;
---查询选修了3门以上的课程的学生姓名。
select stu.name,t.coursenum
from(
select id,count(courseid) coursenum from my_score
group by id) t,my_stu stu
where t.coursenum>=3 and stu.id=t.id;
---------- end ----------
第3关：课程选修人数
任务描述
本关任务：查询每个课程有多少人选修。
编程要求
在右侧编辑器补充代码，查询每个课程有多少人选修。
创建数据库：mydb
创建成绩表：my_stu
字段名 类型 注释
id string 学生id
name string 姓名
sex string 性别
age string 年龄
col string 所选的系
部分数据如下：
95001,李勇,男,20,CS
95002,刘晨,女,19,IS
数据切分方式：逗号
数据所在位置：/root/data/step2_files/my_student.txt
创建班级表：my_score
字段名 类型 注释
id string 学生id
courseid string 课程id
score string 成绩
部分数据如下：
95001,1,81
95001,2,85
数据切分方式：逗号
数据所在位置：/root/data/step2_files/my_score.txt
创建班级表：my_course
字段名 类型 注释
courseid string 课程id
coursename string 课程名称
部分数据如下：
1,数据库
2,数学
数据切分方式：逗号
数据所在位置：/root/data/step2_files/my_course.txt
测试说明
平台会对你编写的代码进行测试：
预期输出：
caozuoxitong 16
shujuchuli 11
shujujiegou 12
shujuku 15
shuxue 15
xinxixitong 13
开始你的任务吧，祝你成功！
---------- 禁止修改 ----------
drop database if exists mydb cascade;
set hive.auto.convert.join = false;
set hive.ignore.mapjoin.hint=false;
---------- 禁止修改 ----------
---------- begin ----------
---创建mydb数据库
create database if not exists mydb;
---使用mydb数据库
use mydb;
---创建表my_stu
create table if not exists my_stu(
id string comment '学生id',
name string comment '学生姓名',
sex string comment '性别',
age string comment '年龄',
col string comment '所选的系'
)
row format delimited fields terminated by ',' stored as textfile;
---导入数据：/root/data/step2_files/my_student.txt
load data local inpath '/root/data/step2_files/my_student.txt' into table my_stu;
--创建表my_score
create table if not exists my_score(
id string comment '学生id',
courseid string comment '课程id',
score string comment '成绩'
)
row format delimited fields terminated by ',' stored as textfile;
---导入数据：/root/data/step2_files/my_score.txt
load data local inpath '/root/data/step2_files/my_score.txt' into table my_score;
--创建表my_course
create table if not exists my_course(
courseid string comment '课程id',
coursename string comment '课程名称'
)
row format delimited fields terminated by ',' stored as textfile;
---导入数据：/root/data/step2_files/my_course.txt
load data local inpath '/root/data/step2_files/my_course.txt' into table my_course;
---查询每个课程有多少人选修
select t2.coursename,count(*) from(
select t1.name name,course.coursename coursename from(
select stu.name name,score.courseid courseid from my_score score,my_stu stu
where score.id=stu.id) t1,my_course course where t1.courseid=course.courseid) t2 group by t2.coursename;
---------- end ----------
第4关：shujuku课程的平均成绩
任务描述
本关任务：计算shujuku课程的平均成绩。
编程要求
在右侧编辑器补充代码，计算shujuku课程的平均成绩。
创建数据库：mydb
创建成绩表：my_stu
字段名 类型 注释
id string 学生id
name string 姓名
sex string 性别
age string 年龄
col string 所选的系
部分数据如下：
95001,李勇,男,20,CS
95002,刘晨,女,19,IS
数据切分方式：逗号
数据所在位置：/root/data/step2_files/my_student.txt
创建班级表：my_score
字段名 类型 注释
id string 学生id
courseid string 课程id
score string 成绩
部分数据如下：
95001,1,81
95001,2,85
数据切分方式：逗号
数据所在位置：/root/data/step2_files/my_score.txt
创建班级表：my_course
字段名 类型 注释
courseid string 课程id
coursename string 课程名称
部分数据如下：
1,数据库
2,数学
数据切分方式：逗号
数据所在位置：/root/data/step2_files/my_course.txt
测试说明
平台会对你编写的代码进行测试：
预期输出：shujuku 83.66666666666667
开始你的任务吧，祝你成功！
---------- 禁止修改 ----------
drop database if exists mydb cascade;
set hive.auto.convert.join = false;
set hive.ignore.mapjoin.hint=false;
---------- 禁止修改 ----------
---------- begin ----------
---创建mydb数据库
create database if not exists mydb;
---使用mydb数据库
use mydb;
---创建表my_stu
create table if not exists my_stu(
id string comment '学生id',
name string comment '学生姓名',
sex string comment '性别',
age string comment '年龄',
col string comment '所选的系'
)
row format delimited fields terminated by ',' stored as textfile;
---导入数据：/root/data/step2_files/my_student.txt
load data local inpath '/root/data/step2_files/my_student.txt' into table my_stu;
--创建表my_score
create table if not exists my_score(
id string comment '学生id',
courseid string comment '课程id',
score string comment '成绩'
)
row format delimited fields terminated by ',' stored as textfile;
---导入数据：/root/data/step2_files/my_score.txt
load data local inpath '/root/data/step2_files/my_score.txt' into table my_score;
--创建表my_course
create table if not exists my_course(
courseid string comment '课程id',
coursename string comment '课程名称'
)
row format delimited fields terminated by ',' stored as textfile;
---导入数据：/root/data/step2_files/my_course.txt
load data local inpath '/root/data/step2_files/my_course.txt' into table my_course;
---计算shujuku课程的平均成绩
select t3.coursename,t2.avg_score from (
select t1.courseid courseid,avg(score.score) avg_score from(
select courseid from my_course
where my_course.coursename='shujuku') t1,my_score score where t1.courseid=score.courseid group by t1.courseid) t2,my_course t3 where t2.courseid=t3.courseid;
---------- end ----------
三·、Hive综合应用案例——用户学历查询
第1关：查询每一个用户从出生到现在的总天数
任务描述
本关任务：查询出每一个用户从出生到现在的总天数
编程要求
在右侧编辑器补充hql语句，查询出每一个用户从出生到现在的总天数。
创建数据库：mydb
创建表：usertab
字段名 类型 注释
id int 用户id
sex string 性别,f:女性,m:男性
time string 出生日期
education string 学历
occupation string 职业
income string 收入
area string 出生地区
desired_area string 向往地区
city_countryside string 城市/农村
部分数据如下：
600,f,1994/02/04,本科,机械工程师,6k,北方,南方,城市
601,f,1993/04/09,本科,Java开发工程师,7k,北方,南方,城市
602,m,1991/05/13,本科,大数据开发工程师,9k,北方,南方,农村
603,m,1996/12/23,本科,大数据开发工程师,9k,南方,东方,城市
604,f,1996/11/24,本科,人工智能开发工程师,10k,南方,东方,农村
数据切分方式：逗号（,）
数据所在目录：/root/data.txt
测试说明
平台会对你编写的代码进行测试：
预期输出：
600 9257
601 9558
602 10255
603 8204
604 8233
605 9971
606 9406
607 9164
608 8485
609 9042
610 9160
开始你的任务吧，祝你成功！
---------- 禁止修改 ----------
drop database if exists mydb cascade;
---------- 禁止修改 ----------
---------- begin ----------
---创建mydb数据库
create database mydb;
---使用mydb数据库
use mydb;
---创建表user
create table usertab(
id int,
sex string,
time string,
education string,
occupation string,
income string,
area string,
desired_area string,
city_countryside string
)row format delimited fields terminated by ',';
---导入数据：/root/data.txt
load data local inpath '/root/data.txt' into table usertab;
--查询每一个用户从出生到2019-06-10的总天数
select id,datediff(cast('2019-06-10' as date),cast(regexp_replace(time, '/', '-') as date)) from usertab;
---------- end ----------
第2关：同一个地区相同的教育程度的最高收入
任务描述
本关任务：查询出同一个地区相同的教育程度的最高收入。
编程要求
在右侧编辑器补充hql语句，查询出同一个地区相同的教育程度的最高收入。
创建数据库：mydb
创建表：usertab1
字段名 类型 注释
Id Int 用户id
Sex String 性别,f:女性,m:男性
Time String 出生日期
Education String 学历
Occupation String 职业
Income int 收入
Area String 出生地区
Desired_area String 向往地区
City_Countryside String 城市/农村
部分数据如下：
600,f,1994/02/04,本科,机械工程师,6000,北方,南方,城市
601,f,1993/04/09,本科,Java开发工程师,7000,北方,南方,城市
602,m,1991/05/13,本科,大数据开发工程师,9000,北方,南方,农村
数据切分方式：逗号（,）
数据所在目录：/root/data1.txt
测试说明
平台会对你编写的代码进行测试：
预期输出：
东方 硕士 14000
北方 本科 9000
南方 专科 8000
南方 本科 9500
西方 专科 8000
开始你的任务吧，祝你成功！
---------- 禁止修改 ----------
drop database if exists mydb cascade;
---------- 禁止修改 ----------
---------- begin ----------
---创建mydb数据库
CREATE DATABASE mydb;
---使用mydb数据库
USE mydb;
---创建表user
CREATE TABLE usertab1 (
id INT,
sex STRING,
time STRING,
education STRING,
occupation STRING,
income STRING,
area STRING,
desired_area STRING,
city_countryside STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
---导入数据：/root/data.txt
LOAD DATA LOCAL INPATH '/root/data1.txt' INTO TABLE usertab1;
--同一个地区相同的教育程度的最高收入
SELECT
area, education, MAX(CAST(income AS INT)) AS max_income
FROM usertab1
GROUP BY area, education;
---------- end ----------
第3关：统计各级学历所占总人数百分比
任务描述
本关任务：查询出各级学历所占总人数百分比。
编程要求
在右侧编辑器中补充代码，查询出各级学历所占总人数百分比。（对结果保留两位小数，并对学历升序输出）
创建数据库：mydb
创建表：usertab2
字段名 类型 注释
id int 用户id
sex string 性别f：女性；m：男性
time string 出生日期
education string 学历
occupation string 职业
income string 收入
area string 出生地区
desired_area string 向往地区
city_countryside string 城市/农村
部分数据如下：
600,f,1994/02/04,本科,机械工程师,6k,北方,南方,城市
601,f,1993/04/09,本科,Java开发工程师,7k,北方,南方,城市
602,m,1991/05/13,本科,大数据开发工程师,9k,北方,南方,农村
603,m,1996/12/23,本科,大数据开发工程师,9k,南方,东方,城市
604,f,1996/11/24,本科,人工智能开发工程师,10k,南方,东方,农村
数据切分方式：逗号（,）
数据所在目录：/root/data.txt
测试说明
平台会对你编写的代码进行测试：
预期输出：
27.27% 专科
45.45% 本科
27.27% 硕士
开始你的任务吧，祝你成功！
---------- 禁止修改 ----------
drop database if exists mydb cascade;
set hive.mapred.mode=nonstrict;
---------- 禁止修改 ----------
---------- begin ----------
----创建mydb数据库
CREATE DATABASE mydb;
---使用mydb数据库
USE mydb;
---创建表user
CREATE TABLE usertab2 (
id INT,
sex STRING,
time STRING,
education STRING,
occupation STRING,
income STRING,
area STRING,
desired_area STRING,
city_countryside STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
---导入数据：/root/data.txt
LOAD DATA LOCAL INPATH '/root/data.txt' INTO TABLE usertab2;
--查询
SELECT
CONCAT(ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2), '%') AS percentage,
education
FROM usertab2
GROUP BY education
ORDER BY education;
---------- end ----------
有任何问题都可以随时关注私信！
扫一扫
447
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
