前言：
以sql为基础，利用题目进行hive的语句练习，逐步体会sql与hive的不同之处。
本次练习题来源：https://www.cnblogs.com/qingyunzong/p/8747656.html
题目说明：
（1）存在数据：
用户名,月份,访问次数
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,1
（2）结果需求：
现要求出：
每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数，结果数据格式如下
image
hive解题步骤：
(1)创建表
create external table if not exists t_access(
uname string comment '用户名',
umonth string comment '月份',
ucount int comment '访问次数'
) comment '用户访问表'
row format delimited fields terminated by ","
location "/hive/t_access";
(2)导入数据
load data local inpath "/home/jiafeng/access.txt" into table t_access;
提示：首先在 cd /home/jiafeng目录下 创建access.txt文件 代码如下:
cd /home/jiafeng #进入目录
vi access.txt
进入编辑模式，a进入当前光标后输入，复制粘贴数据源
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,1
进入末行模式退出并保存 ：wq
(3)验证数据
hive> select * from t_access;
OK
t_access.uname t_access.umonth t_access.ucount
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 1
Time taken: 3.474 seconds, Fetched: 15 row(s)
(4)根据名字和月份进行分组，求出每组每月的访问次数
hive> select uname,umonth,sum(ucount) as num from t_access group by uname,umonth;
uname umonth num
A 2015-01 33
A 2015-02 10
A 2015-03 38
B 2015-01 30
B 2015-02 15
B 2015-03 34
(5)将分组后的语句创建表tmp_access
hive> create table tmp_access as select uname,umonth,sum(ucount) as num from t_access group by uname,umonth;
Time taken: 4.445 seconds
hive> select * from tmp_access;
OK
tmp_access.uname tmp_access.umonth tmp_access.num
A 2015-01 33
A 2015-02 10
A 2015-03 38
B 2015-01 30
B 2015-02 15
B 2015-03 34
Time taken: 0.454 seconds, Fetched: 6 row(s)
(6)利用tmp_access自连接，根据name连接，筛选a.umonth>=b.umonth，利用max(b.num),sum(b.num)获得月最大访问次数及用户累计访问次数
hive> select a.uname,a.umonth,max(b.num) as max_access,sum(b.num) as sum_access,a.num from tmp_access a join tmp_access b on a.uname=b.uname where a.umonth>=b.umonth group by a.uname,a.umonth,a.num;
Total MapReduce CPU Time Spent: 0 msec
OK
a.uname a.umonth max_access sum_access a.num
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 34 79 34
Time taken: 14.372 seconds, Fetched: 6 row(s)
SQL语句解法
create database hive; #创建数据库hive
use hive; #使用hive数据库
create table t_access (
uname varchar(20),
umonth varchar(20),
ucount int); #创建表
#drop table t_access;
load data infile "D:/access.txt" into table t_access fields terminated by ","; #导入数据
select * from t_access; #验证数据
drop table if exists tmp_access;
create table tmp_access as (select uname,umonth,sum(ucount) num from t_access group by uname,umonth); #创建分组后的表
select a.uname,a.umonth,max(b.num) as "最大访问次数",sum(b.num) as "总访问次数",a.num as "当月访问次数"
from tmp_access as a
join tmp_access as b
on a.uname=b.uname where a.umonth>=b.umonth group by a.uname,a.umonth; #利用表自连
总结
hive与sql的不同之处：
1.hive中 as 后面的别名不能用引号
select a.uname,a.umonth,max(b.num) as max_access,sum(b.num) as sum_access
2.hive中自连接的分组groupby要求更高
group by a.uname,a.umonth,a.num;
