hive练习

学生课程成绩

use myhive;
CREATE TABLE `course` (
  `id` int,
  `sid` int ,
  `course` string,
  `score` int 
) ;

// 插入数据
// 字段解释：id, 学号， 课程， 成绩
INSERT INTO `course` VALUES (1, 1, 'yuwen', 43);
INSERT INTO `course` VALUES (2, 1, 'shuxue', 55);
INSERT INTO `course` VALUES (3, 2, 'yuwen', 77);
INSERT INTO `course` VALUES (4, 2, 'shuxue', 88);
INSERT INTO `course` VALUES (5, 3, 'yuwen', 98);
INSERT INTO `course` VALUES (6, 3, 'shuxue', 65);

需求 ：所有数学课程成绩 大于 语文课程成绩的学生的学号

//所有数学课程成绩 大于 语文课程成绩的学生的学号
//思路一    用join 把语文和数学放到一行里面

select sid ,course,score
from course   t1

select sid ,course,score
from course   t2


select
*
from  
(
select sid ,course,score
from course
)t1 
inner join 
(
select sid ,course,score
from course
)t2
on 
t1.sid = t2.sid 
and t1.course = "yuwen"
and t2.course = "shuxue"
where t1.score < t2.score

//思路二  
用case when 然后处理下 把数学和语文放到一行

select 
sid,
course,
(case course when  "shuxue" then score else 0 end) as  shuxue,
(case course when  "yuwen" then score else 0 end) as yuwen
from course  ;t1

sid	course	shuxue	yuwen
1	yuwen	0	43
1	shuxue	55	0
2	yuwen	0	77
2	shuxue	88	0
3	yuwen	0	98
3	shuxue	65	0

select
sid,
max(shuxue)  s,
max(yuwen) y
from (
select 
sid,
course,
(case course when  "shuxue" then score else 0 end) as  shuxue,
(case course when  "yuwen" then score else 0 end) as yuwen
from course
)t1
group by  sid
having s > y

计算每一年出现过的最大气温的日期+温度。

2014010216
2014010410
2012010609
2012010812
2012011023
2001010212
2001010411
2013010619
2013010812
2013011023
2008010216
2008010414
2007010619
2007010812
2007011023
2010010216
2010010410
2015010649
2015010812
2015011023

select
substr(data,1,8) date1,
substr(data,1,4) year1,
substr (data,9) degree
from weather    ;t1


select 
year1,
max(degree) max_degree
from  t1
group by year1   ; t2

select 
t1.date1,
t1.year1,
t1.degree
from 
t1 
inner join
t2
on t1.year1 = t2.year1
and t1.degree = t2.max_degree

注意 ： substr(s,start,len)   起始使从1开始，后面并不是跟结束的，是长度