Hive50道挑战题(有运行结果)

HQL总结

一个完整的查询结构

select distinct....
from leftTable....
join rightTable.... on ....
where ....
group by ....
having ....
order by ....
limit ....
union [all]....

子句的执行顺序

1. 先执行 from 子句 
2. 再执行 on 关联条件
3. 然后再关联右表，也就是执行 join....
4. 再执行 where....
5. 执行 group by.....
6. 再执行 having....
7. 然后执行 select....
8. 如果有 distinct，则会对 select 选择的所有字段的值的组合去重
9. 再执行 order by....
10. 再执行 limit 进行限制条数查询(分页查询)
11. 最后执行两个 sql 的合并。

注意：列别名和表别名尽量正确使用，在hive中，非常严格，有的时候必须加别名。

1. hql什么时候不会被翻译成mr程序
	-- select * from tablename;
	-- select * from tablename limit [m,]n;
2. hql对子查询支持的不够友好
	(1) hql有子查询时，通常都会有表别名和列别名的应用
	(2) 子查询可以在from子句中，与mysql的用法一致
	(3) 子查询可以在where子句中，但是只能应用[not] in 或者 [not] exists
	(4) 子查询可以在having子句中，但是只能应用[not] in 或者 [not] exists
	(5) 子查询在having子句中时，如果用的是in，并且有聚合函数，那么聚合函数应该在select子句出现
	(6) 子查询不支持在select子句中。
--3. 查询原则
	（1）能不使用子查询，就不用子查询，通常子查询都可以使用join来替换
	（2）能不用join就别用join，但是通常避免不了
	（3）[not] in 通常要替换成 [not] exists
	（4）join时，一定要注意小表驱动大表
--4，聚合函数的count的优化
	#1. 执行效果上：
	- count(*)包括了所有的列，相当于行数，在统计结果的时候不会忽略null值
	- count(1)包括了所有列，用1代表行，在统计结果的时候也不会忽略null值
	- count(列名)只包括列名那一列，在统计结果时，会忽略null值

	#2. 执行效率上：
	- 列名为主键，count(列名)会比count(1)快，count(主键)效率是最高的
	- 列名不为主键，count(1)会比count(列名)快
	- 如果表中有多个列并且没有主键，count（1）的效率高于count(*)
	- 如果表中只有一个字段count(*)效率最高    

运行时如遇到系统打开文件数过多的解决办法

1）永久生效

1. vi /etc/pam.d/login 添加
		session required /lib/security/pam_limits.so
		
2. vi /etc/security/limits.conf 添加
		* soft nofile 65536
		* hard nofile 65536
		
3. vi /etc/sysctl.conf 添加
		fs.file-max = 6553600	

2）临时生效

	ulimit -n 65536
	
	或将该命令写入profile,每次登陆时执行.

3）检查是否生效

	ulimit -a

一、数据准备

---------------------表名和字段-----------------
--学生表
--Student(s_id,s_name,s_birth,s_sex) 学生编号,学生姓名, 出生年月,学生性别
--课程表
--Course(c_id,c_name,t_id) 课程编号, 课程名称, 教师编号
--教师表
--Teacher(t_id,t_name) 教师编号,教师姓名
--成绩表
--Score(s_id,c_id,s_score) 学生编号,课程编号,分数


create database if not exists exercise;
use exercise;

-- -------------------建表------------------
-- 学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` varchar(20),
`s_name` varchar(20),
`s_birth` varchar(20),
`s_sex` varchar(10)
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath '/root/data/student.csv' into table student;

-- 课程表
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c_id` varchar(20),
  `c_name` varchar(20),
  `t_id` varchar(20)
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath '/root/data/course.csv' into table course;

-- 教师表
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`(
`t_id` varchar(20),
`t_name` varchar(20)
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath '/root/data/teacher.csv' into table teacher;

-- 成绩表
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`(
`s_id` varchar(20),
`c_id` varchar(20),
`s_score` int
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath '/root/data/score.csv' into table score;

-- 学生表测试数据			vim /root/data/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,女
09,张飞,1990-9-25,男
10,刘备,1990-01-25,男
11,关羽,1990-01-25,男

-- 课程表测试数据			vim /root/data/course.csv
01,语文,02
02,数学,01
03,英语,03
04,物理,04


-- 教师表测试数据			vim /root/data/teacher.csv
01,张三
02,李四
03,王五
04,赵六


-- 成绩表测试数据			vim /root/data/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
09,01,85
09,02,80
09,04,99
10,01,80
10,02,56
10,03,30
10,04,90
11,04,90

-- 查询表语句
select * from student;
select * from course;
select * from teacher;
select * from score;

二、练习题

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

写法一：连接查询
select st.*,sc1.s_score as `01分数`,sc2.s_score as `02分数` 
from student st
join score sc1 on sc1.s_id=st.s_id and sc1.c_id='01'
join score sc2 on sc2.s_id=st.s_id and sc2.c_id='02'
where sc1.s_score>sc2.s_score;

写法二：子查询
select * 
from (
select st.s_id,st.s_name,st.s_birth,st.s_sex
    ,max(if(c_id='01',s_score,null)) as `01分数`
    ,max(if(c_id='02',s_score,null)) as `02分数`
from student st
join score sc on sc.s_id=st.s_id
group by st.s_id,st.s_name,st.s_birth,st.s_sex
) t
where `01分数`>`02分数`;

写法三：先行转列，然后再完成条件判断
-- 1) 先查询5个字段
select st.s_id,st.s_name
,(case when sc.c_id='01' then sc.s_score else null end) `01分数`
,(case when sc.c_id='02' then sc.s_score else null end) `02分数`
,(case when sc.c_id='03' then sc.s_score else null end) `03分数`
,(case when sc.c_id='04' then sc.s_score else null end) `04分数`
from student st join score sc on st.s_id=sc.s_id;

结果如下：
OK
s_id    s_name  01分数  02分数  03分数  04分数
01      赵雷    80      NULL    NULL    NULL
01      赵雷    NULL    90      NULL    NULL
01      赵雷    NULL    NULL    99      NULL
02      钱电    70      NULL    NULL    NULL
02      钱电    NULL    60      NULL    NULL
02      钱电    NULL    NULL    80      NULL
03      孙风    80      NULL    NULL    NULL
03      孙风    NULL    80      NULL    NULL
03      孙风    NULL    NULL    80      NULL
04      李云    50      NULL    NULL    NULL
04      李云    NULL    30      NULL    NULL
04      李云    NULL    NULL    20      NULL
05      周梅    76      NULL    NULL    NULL
05      周梅    NULL    87      NULL    NULL
06      吴兰    31      NULL    NULL    NULL
06      吴兰    NULL    NULL    34      NULL
07      郑竹    NULL    89      NULL    NULL
07      郑竹    NULL    NULL    98      NULL
09      张飞    85      NULL    NULL    NULL
09      张飞    NULL    80      NULL    NULL
09      张飞    NULL    NULL    NULL    99
10      刘备    80      NULL    NULL    NULL
10      刘备    NULL    56      NULL    NULL
10      刘备    NULL    NULL    30      NULL
10      刘备    NULL    NULL    NULL    90
11      关羽    NULL    NULL    NULL    90
Time taken: 12.029 seconds, Fetched: 26 row(s)

-- 2) 在通过分组查询，进行聚合，完成行转列
select st.s_id,st.s_name
,max((case when sc.c_id='01' then sc.s_score else null end)) `01分数`
,max((case when sc.c_id='02' then sc.s_score else null end)) `02分数`
,max((case when sc.c_id='03' then sc.s_score else null end)) `03分数`
,max((case when sc.c_id='04' then sc.s_score else null end)) `04分数`
from student st join score sc on st.s_id=sc.s_id
group by st.s_id,st.s_name;

结果如下：
OK
s_id    s_name  01分数  02分数  03分数  04分数
01      赵雷    80      90      99      NULL
02      钱电    70      60      80      NULL
03      孙风    80      80      80      NULL
04      李云    50      30      20      NULL
05      周梅    76      87      NULL    NULL
06      吴兰    31      NULL    34      NULL
07      郑竹    NULL    89      98      NULL
09      张飞    85      80      NULL    99
10      刘备    80      56      30      90
11      关羽    NULL    NULL    NULL    90
Time taken: 12.853 seconds, Fetched: 10 row(s)

-- 3) 在 2 基础上完成题意
select * from
(select st.s_id,st.s_name
,max((case when sc.c_id='01' then sc.s_score else null end)) `01分数`
,max((case when sc.c_id='02' then sc.s_score else null end)) `02分数`
,max((case when sc.c_id='03' then sc.s_score else null end)) `03分数`
,max((case when sc.c_id='04' then sc.s_score else null end)) `04分数`
from student st join score sc on st.s_id=sc.s_id
group by st.s_id,st.s_name) A
where A.`01分数`>A.`02分数`;


-- 扩展：
case  when.. then...[when...then...][when then...] else...end;

当条件中是范围取值时，when 后面要写具体条件，带上字段名
如果是等值条件，一种写法，就是在when后面写完整条件，比如age=40
             另一种写法，就是可以将字段名放在case后，when后只需要写具体值。
例如：
select st.s_id,st.s_name
,(case when sc.c_id='01' then sc.s_score else null end) `01分数`
,(case when sc.c_id='02' then sc.s_score else null end) `02分数`
from student st join score sc on st.s_id=sc.s_id;

也可以写成

select st.s_id,st.s_name
,(case sc.c_id when '01' then sc.s_score else null end) `01分数`
,(case sc.c_id when '02' then sc.s_score else null end) `02分数`
from student st join score sc on st.s_id=sc.s_id;

运行结果

OK
s_id    s_name  s_birth s_sex   01分数  02分数
02      钱电    1990-12-21      男      70      60
04      李云    1990-08-06      男      50      30
09      张飞    1990-9-25       男      85      80
10      刘备    1990-01-25      男      80      56
Time taken: 11.994 seconds, Fetched: 4 row(s)

-- 写法三
OK
s_id    s_name  01分数  02分数  03分数  04分数
02      钱电    70      60      80      NULL
04      李云    50      30      20      NULL
09      张飞    85      80      NULL    99