前言：

以sql為基礎(chǔ)，利用題目進行hive的語句練習(xí)，逐步體會sql與hive的不同之處。
本次練習(xí)題來源：https://www.cnblogs.com/qingyunzong/p/8747656.html

題目說明：

（1）存在數(shù)據(jù)：

用戶名,月份,訪問次數(shù)
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）結(jié)果需求：

現(xiàn)要求出：
每個用戶截止到每月為止的最大單月訪問次數(shù)和累計到該月的總訪問次數(shù)，結(jié)果數(shù)據(jù)格式如下

image

hive解題步驟：

(1)創(chuàng)建表

create external table if not exists t_access(
uname string comment '用戶名',
umonth string comment '月份',
ucount int comment '訪問次數(shù)'
) comment '用戶訪問表' 
row format delimited fields terminated by "," 
location "/hive/t_access"; 

(2)導(dǎo)入數(shù)據(jù)

load data local inpath "/home/jiafeng/access.txt" into table t_access;

提示：首先在 cd /home/jiafeng目錄下 創(chuàng)建access.txt文件 代碼如下:

cd /home/jiafeng #進入目錄
vi access.txt

進入編輯模式，a進入當(dāng)前光標(biāo)后輸入，復(fù)制粘貼數(shù)據(jù)源

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)驗證數(shù)據(jù)

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)根據(jù)名字和月份進行分組，求出每組每月的訪問次數(shù)

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)將分組后的語句創(chuàng)建表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自連接，根據(jù)name連接，篩選a.umonth>=b.umonth，利用max(b.num),sum(b.num)獲得月最大訪問次數(shù)及用戶累計訪問次數(shù)

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;  #創(chuàng)建數(shù)據(jù)庫hive

use hive; #使用hive數(shù)據(jù)庫

create table t_access (
uname varchar(20),
umonth varchar(20),
ucount int); #創(chuàng)建表

#drop table t_access;

load data infile "D:/access.txt" into table t_access fields terminated by ","; #導(dǎo)入數(shù)據(jù)

select * from t_access; #驗證數(shù)據(jù)

drop table if exists tmp_access;

create table tmp_access as (select uname,umonth,sum(ucount) num  from t_access group by uname,umonth); #創(chuàng)建分組后的表

select a.uname,a.umonth,max(b.num) as "最大訪問次數(shù)",sum(b.num) as "總訪問次數(shù)",a.num as "當(dāng)月訪問次數(shù)" 
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; #利用表自連

總結(jié)

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;