hive学习（二）：练习题——求访问次数

前言：

以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;