04.Hive必刷50题--16-20题

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

SELECT s.*,sc.s_score
FROM student s
JOIN score sc ON s.s_id=sc.s_id AND sc.c_id='01' AND sc.s_score<60
ORDER BY sc.s_score DESC

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

最开始的sql(获取的数据格式不好,无法清晰的知道各科的成绩)

SELECT s.*,tmp1.avg,sc.s_score
FROM student s
LEFT JOIN (SELECT s_id,AVG(s_score) avg
FROM score 
GROUP BY s_id
)tmp1 ON tmp1.s_id=s.s_id
JOIN score sc ON s.s_id=sc.s_id
ORDER BY tmp1.avg DESC

修改后

SELECT s.*,tmp.avg,tmp1.s_score '语文',tmp2.s_score '数学',tmp3.s_score '英语'
FROM student s
LEFT JOIN (SELECT s_id,AVG(s_score) avg
FROM score 
GROUP BY s_id
)tmp ON tmp.s_id=s.s_id
LEFT JOIN (SELECT s_id,s_score FROM score WHERE c_id='01')tmp1 ON tmp1.s_id=s.s_id
LEFT JOIN (SELECT s_id,s_score FROM score WHERE c_id='02')tmp2 ON tmp2.s_id=s.s_id
LEFT JOIN (SELECT s_id,s_score FROM score WHERE c_id='03')tmp3 ON tmp3.s_id=s.s_id
ORDER BY tmp.avg DESC

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

SELECT * 
FROM course c
JOIN
(SELECT c_id,MAX(s_score) max,MIN(s_score) min,ROUND(AVG(s_score),2) avg,
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) modeRate,
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
)tmp1 
ON tmp1.c_id=c.c_id

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

SELECT sc1.*,ROW_NUMBER() over(ORDER BY sc1.s_score DESC) ranking
FROM score sc1 
WHERE sc1.c_id='01'
UNION 
SELECT sc2.*,ROW_NUMBER() over(ORDER BY sc2.s_score DESC) ranking
FROM score sc2 
WHERE sc2.c_id='02'
UNION
SELECT sc3.*,ROW_NUMBER() over(ORDER BY sc3.s_score DESC) ranking
FROM score sc3 
WHERE sc3.c_id='03'

窗口函数

结构

分析函数sum()、row_number()、count()

窗口函数 over()

分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

 

over()函数中三个函数

包括分区partition by 列名、排序order by 列名、指定窗口范围rows between 开始位置 and 结束位置

我们在使用over()窗口函数时,over()函数中的这三个函数可组合使用也可以不使用。

 

partition by

可理解为group by 分组。分析函数按照每一组每一组的数据进行计算的。

 

order by

排序的意思,主要实现累加的效果。比如:统计一年中当前月和之前月的支付总额

 

rows between 开始位置 and 结束位置

指定窗口范围,分析函数按照这个范围进行计算的。

 

分析函数

 

聚合类

avg()、sum()、max()、min()

 

排名类

row_number()按照值排序时产生一个自增编号,不会重复(如:1、2、3、4、5、6)

rank() 按照值排序时产生一个自增编号,值相等时会重复,会产生空位(如:1、2、3、3、3、6)

dense_rank() 按照值排序时产生一个自增编号,值相等时会重复,不会产生空位(如:1、2、3、3、3、4)

 

其他类

lag(列名,往前的行数,[行数为null时的默认值,不指定为null]),以计算用户上次购买时间,或者用户下次购买时间。

lead(列名,往后的行数,[行数为null时的默认值,不指定为null])

ntile(n) 把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,ntile返回此行所属的组的编号

 

over什么时候使用??

窗口函数应用场景:

(1)用于分区排序

(2)动态Group By

(3)Top N

(4)累计计算

(5)层次查询

 

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

SELECT sc.s_id,s.s_name,SUM(sc.s_score) sum
FROM score sc 
JOIN student s ON sc.s_id=s.s_id
GROUP BY sc.s_id,s.s_name
ORDER BY sum DESC

后续部分参见:

00.Hive必刷50题--建表,插入数据

01.Hive必刷50题--1-5题

02.Hive必刷50题--6-10题

03.Hive必刷50题--11-15题

04.Hive必刷50题--16-20题

05.Hive必刷50题--21-25题

06.Hive必刷50题--26-30题

07.Hive必刷50题--31-35题

08.Hive必刷50题--36-40题

09.Hive必刷50题--41-45题

010.Hive必刷50题--46-50题

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
1.上传tar包 2.解压 tar -zxvf hive-1.2.1.tar.gz 3.安装mysql数据库 推荐yum 在线安装 4.配置hive (a)配置HIVE_HOME环境变量 vi conf/hive-env.sh 配置其中的$hadoop_home (b)配置元数据库信息 vi hive-site.xml 添加如下内容: javax.jdo.option.ConnectionURL jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true JDBC connect string for a JDBC metastore javax.jdo.option.ConnectionDriverName com.mysql.jdbc.Driver Driver class name for a JDBC metastore javax.jdo.option.ConnectionUserName root username to use against metastore database javax.jdo.option.ConnectionPassword hadoop password to use against metastore database 5.安装hive和mysq完成后,将mysql的连接jar包拷贝到$HIVE_HOME/lib目录下 如果出现没有权限的问,在mysql授权(在安装mysql的机器上执行) mysql -uroot -p #(执行下面的语句 *.*:所有库下的所有表 %:任何IP地址或主机都可以连接) GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; FLUSH PRIVILEGES; 6. Jline包版本不一致的问,需要拷贝hive的lib目录中jline.2.12.jar的jar包替换掉hadoop中的 /home/hadoop/app/hadoop-2.6.4/share/hadoop/yarn/lib/jline-0.9.94.jar 启动hive bin/hive ---------------------------------------------------------------------------------------------------- Hive几种使用方式: 1.Hive交互shell bin/hive 2.Hive JDBC服务(参考java jdbc连接mysql) 3.hive启动为一个服务器,来对外提供服务 bin/hiveserver2 nohup bin/hiveserver2 1>/var/log/hiveserver.log 2>/var/log/hiveserver.err & 启动成功后,可以在别的节点上用beeline去连接 bin/beeline -u jdbc:hive2://mini1:10000 -n root 或者 bin/beeline ! connect jdbc:hive2://mini1:10000 4.Hive命令 hive -e ‘sql’ bin/hive -e 'select * from t_test'
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值