HiveSQL案例——分析需求(查询和学生期末考试的一些信息)

目录

前期准备：

一、环境准备

二、数据准备

三、表格准备

具体的HiveQL需求分析

1.查询2019级2班ETL技术期末考试成绩排名前6的学生信息。

2.统计2019级3班ETL技术期末综合成绩80分以上的学生人数（含80分）。

3.统计2019级3班ETL技术期末考试成绩及格的学生人数。

4.统计2019级4班参加ETL技术考试的学生总人数。

5.统计2019级3班参加ETL技术考试的学生总人数。

6.查询19数据科学与大数据技术1班吉苇同学的学号、以及所有成绩。

7.统计2019级1班ETL技术的期末综合成绩不及格的学生人数。

8.查询2019级1班ETL技术综合成绩排名前5的学生学号和姓名。

9.统计19数据科学与大数据技术全年级的学生总人数。

10.查询2019级3班ETL技术期末考试成绩排名前7的学生信息。

11.查询2019级全年级ETL技术期末考试成绩排名前8的学生信息。

12.查询2019级全年级ETL技术的综合成绩85分及以上的学生信息。

13.分析出2019级全年级ETL技术的期末考试成绩分布在80-92分之间的学生信息。

前期准备：

一、环境准备

启动集群：start-all.sh

启动MySQL服务器  登录MySQL数据库

登录hive ，设置hive自动选择本地执行模式

二、数据准备

1.Excel数据处理并另存为.txt文件

Excel中：

另存为.txt文件 

2.在.txt文件中将空格替换(Ctrl+h)为|，另存使编码为UTF-8

另存使编码为UTF-8 

 4.将.txt文件上传至虚拟机software目录下

再上传(hdfs dfs -put或者hadoop fs -put)至hdfs

三、表格准备

创建表gznu19_c1、gznu19_c2、gznu19_c3、gznu19_c4

#gznu19_c1为表1，存储19级1班的数据，date01是默认的存储位置
create table gznu19_c1(
  xuhao String,  #序号
  xuehao String,  #学号
  name String,  #姓名
  class String,  #班级
  kaoqing String.  #考勤
  psgrade String,  #平时成绩
  qmgrade String,  #期末成绩
  zhgrade String  #综合成绩
)
row format delimited fields terminated by '|'
stored as textfile
location '/date01';

建表语句如下：

具体的HiveQL需求分析

1.查询2019级2班ETL技术期末考试成绩排名前6的学生信息。

select * from gznu19_c2 order by qmgrade desc limit 6;

2.统计2019级3班ETL技术期末综合成绩80分以上的学生人数（含80分）。

select count(*) from gznu19_c3 where zhgrade>=80;

3.统计2019级3班ETL技术期末考试成绩及格的学生人数。

select count(*) from gznu19_c3 where qmgrade >= 60;

4.统计2019级4班参加ETL技术考试的学生总人数。

select count(*) from gznu19_c4 where zhgrade!='缓考' and zhgrade!='缺考';

5.统计2019级3班参加ETL技术考试的学生总人数。

select count(*) from gznu19_c3 where class="19数据科学与大数据技术3班";

6.查询19数据科学与大数据技术1班吉苇同学的学号、以及所有成绩。

select xuehao,kaoqing,psgrade,qmgrade,zhgrade from gznu19_c1 where name='吉苇';

7.统计2019级1班ETL技术的期末综合成绩不及格的学生人数。

select count(*) from gznu19_c1 where qmgrade < 60;

8.查询2019级1班ETL技术综合成绩排名前5的学生学号和姓名。

select xuehao,name from gznu19_c1 order by zhgrade desc limit 5;

9.统计19数据科学与大数据技术全年级的学生总人数。

SELECT COUNT(*) AS total_students
FROM (
  SELECT xuehao FROM gznu19_c1
  UNION ALL
  SELECT xuehao FROM gznu19_c2
  UNION ALL
  SELECT xuehao FROM gznu19_c3
  UNION ALL
  SELECT xuehao FROM gznu19_c4
) S;

10.查询2019级3班ETL技术期末考试成绩排名前7的学生信息。

select * from gznu19_c3 order by qmgrade desc limit 7;

11.查询2019级全年级ETL技术期末考试成绩排名前8的学生信息。

SELECT * 
FROM(
  SELECT * FROM gznu19_c1 
  UNION ALL
  SELECT * FROM gznu19_c2 
  UNION ALL
  SELECT * FROM gznu19_c3 
  UNION ALL
  SELECT * FROM gznu19_c4 
)T
order by qmgrade desc limit 8;

12.查询2019级全年级ETL技术的综合成绩85分及以上的学生信息。

SELECT * 
FROM (
  SELECT * FROM gznu19_c1 
  UNION ALL
  SELECT * FROM gznu19_c2 
  UNION ALL
  SELECT * FROM gznu19_c3 
  UNION ALL
  SELECT * FROM gznu19_c4 
) t
where zhgrade >= 85;

 

13.分析出2019级全年级ETL技术的期末考试成绩分布在80-92分之间的学生信息。

SELECT * 
FROM (
  SELECT * FROM gznu19_c1 
  UNION ALL
  SELECT * FROM gznu19_c2 
  UNION ALL
  SELECT * FROM gznu19_c3 
  UNION ALL
  SELECT * FROM gznu19_c4 
) t
where qmgrade > 80 and qmgrade < 92;