hive的典型應用場景

案例一：

需求：現有這么一批數據，現要求出：每個用戶截止到每月為止的最大單月訪問次數和累計到該月的總訪問次數。
數據：

用戶名，月份，訪問次數
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,11

最終結果：

用戶  月份      最大訪問次數  總訪問次數       當月訪問次數
A     2015-01          33              33               33
A     2015-02          33              43               10
A     2015-03          38              81               38
B     2015-01          30              30               30
B     2015-02          30              45               15
B     2015-03          44              89                44

解決：

#step01 統計每個用戶每月的總訪問次數
create view view_step01 as select name,month,sum(visitCount) total from t_user  group by name,month;
#step02 （自連接，連接條件為name）
create view view_step02 as
    select t1.name aname,t1.month amonth,t1.total atotal,t2.name bname,t2.month bmonth,t2.total btotal
    from view_step01 t1 join view_step01  t2 on t1.name =t2.name 
#step03 去除無用數據，每組找到小于等于自己月份的數據
select bname,bmonth,max(btotal),sum(btotal),btotal
from view_step02
where unix_timestamp(amonth,'yyyy-MM')>=unix_timestamp(bmoth,'yyyy-MM')
group by aname,amonth,atotal;

案例二：

#建表語句：

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `sid` int(11) DEFAULT NULL,
  `course` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#插入數據
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);

需求：所有數學課程成績 大于 語文課程成績的學生的學號
解決：（行列轉換）

SELECT
    t1.sid 
FROM
    (
SELECT
    sid,
    max( CASE `course` WHEN "yuwen" THEN score ELSE 0 END ) AS "yuwen",
    max( CASE `course` WHEN "shuxue" THEN score ELSE 0 END ) AS "shuxue" 
FROM
    `course` 
GROUP BY
    sid 
    ) t1 
WHERE
    t1.yuwen < t1.shuxue;

案例三：

需求：比如：2010012325表示在2010年01月23日的氣溫為25度。現在要求使用hive，計算每一年出現過的最大氣溫的日期+溫度。
數據：
年 溫度
20140101 14
20140102 16
20140103 17
20140104 10
20140105 06
20120106 09
20120107 32
20120108 12
20120109 19
20120110 23
20010101 16
20010102 12
20010103 10
20010104 11
20010105 29
20130106 19
20130107 22
20130108 12
20130109 29
20130110 23
20080101 05

現在需要根據年月進行group by 但是最終的結果需要是20080101 05，也就是說，分組字段和最后保留的字段不相同，這時怎么辦？

解決：

#Step1:
CREATE VIEW view_step1 AS SELECT
substr( tmp, 1, 4 ) AS YEAR,
max( substr( tmp, 9, 2 ) ) AS tmp 
FROM
    tmp 
GROUP BY
    substr( tmp, 1, 4 );

#Step2:
SELECT
    b.tmp,
    a.tmp 
FROM
    view_step1 a
    JOIN tmp b ON a.YEAR = substr( b.tmp, 1, 4 ) 
    AND a.tmp = substr( b.tmp, 9, 2 );

案例四：

數據

#表示有id為1,2,3的學生選修了課程a,b,c,d,e,f中其中幾門：
id course 
1,a 
1,b 
1,c 
1,e 
2,a 
2,c 
2,d 
2,f 
3,a 
3,b 
3,c 
3,e

需求：編寫Hive的HQL語句來實現以下結果：表中的1表示選修，表中的0表示未選修。

解決（方案1）：

#行列轉換
select id 
max(case when course='a' then 1 else 0 and ) as a ,
max(case when course='b' then 1 else 0 and ) as b ,
max(case when course='c' then 1 else 0 and ) as c ,
max(case when course='d' then 1 else 0 and ) as d ,
max(case when course='e' then 1 else 0 and ) as e ,
max(case when course='f' then 1 else 0 and ) as f
from course  group by id;

解決（方案2）：

#collect_set函數
#step01
create view id_courses as 
select a.course acourse,b.course bcourse,b.id id
(select collect_set(course) as course from course) a 
    join 
(selecet id ,colect_set(course) as course from course group by id) b

#step02
select id,
case when array_contains(bcourse,acourse[0]) then 1 else 0 end as a ,
case when array_contains(bcourse,acourse[1]) then 1 else 0 end as b ,
case when array_contains(bcourse,acourse[2]) then 1 else 0 end as c ,
case when array_contains(bcourse,acourse[3]) then 1 else 0 end as d ,
case when array_contains(bcourse,acourse[4]) then 1 else 0 end as e ,
case when array_contains(bcourse,acourse[5]) then 1 else 0 end as f
from id_courses;