歡迎大家掃碼關注我的微信公衆号：

Hive 之函數 02-常用查詢函數（二）六、視窗函數七、排名函數八、兩個小題

Hive 之函數 02-常用查詢函數（二）

六、視窗函數
- 6.1 函數說明
- 6.2 需求
- 6.3 實作
- - 6.3.1 查詢在 2017 年 4 月份購買過的顧客及總人數
  - 6.3.2 查詢顧客的購買明細及購買總額
  - 6.3.3 上述的場景, 要将 cost 按照日期進行逐個累加
  - 6.3.4 查詢顧客上次的購買時間
  - 6.3.5 查詢前 20% 時間的訂單資訊
  - 6.3.6 關于幾個時間參數的使用示例
  - 6.3.7 SQL 語句的書寫及執行順序
七、排名函數
- 7.1 函數說明
- 7.2 需求
- 7.3 實作
八、兩個小題
- 8.1 統計使用者累計通路次數
- 8.2 店鋪訪客數等

六、視窗函數

6.1 函數說明

OVER()

：指定分析函數工作的資料視窗大小，這個資料視窗大小可能會随着行的變化而變化；如果括号内為空，表示對整個資料集開窗；

over()

括号内可以寫的參數：

CURRENT ROW

：目前行；

n PRECEDING

：往前 n 行資料；

n FOLLOWING

：往後 n 行資料；

UNBOUNDED

：起點，

UNBOUNDED PRECEDING

表示從前面的起點，

UNBOUNDED FOLLOWING

表示到後面的終點；（如果想要計算某個時間段内整體的購買額，使用

distribute by

加

sort by

又隻能“累加”，此時就可使用“起點”到“終點”這個）

over()

前面可以寫的參數：

LAG(col,n)

：往前第 n 行資料；可以接受第三個參數，當為 Null 時充當預設值；

LEAD(col,n)

：往後第 n 行資料；可以接受第三個參數，當為 Null 時充當預設值；

NTILE(n)

：把有序分區中的行分發到指定資料的組中，各個組有編号，編号從 1 開始，對于每一行， NTILE 傳回此行所屬的組的編号。注意： n 必須為 int 類型。主要用于求百分率内資料資訊的情況。

【注】

over()

放在聚合函數的後面，它的作用是開窗，範圍僅僅是針對它前面的聚合函數有效；

6.2 需求

○ 查詢在 2017 年 4 月份購買過的顧客及總人數

○ 查詢顧客的購買明細及購買總額

○ 上述的場景, 要将 cost 按照日期進行逐個累加

○ 查詢顧客上次的購買時間

○ 查詢前 20% 時間的訂單資訊

6.3 實作

建表及導入資料：

hive (default)> create table business(
              > name string, 
              > orderdate string, 
              > cost int)
              > row format delimited fields terminated by ',';
OK
Time taken: 0.884 seconds

hive (default)> load data local inpath 
              > '/opt/module/data/business.txt'
              > into table business;
Loading data to table default.business
Table default.business stats: [numFiles=1, totalSize=171]
OK
Time taken: 0.419 seconds

查詢全部資料：

hive (default)> select * from business;
OK
business.name	business.orderdate	business.cost
jack	2017-01-01	10
tony	2017-01-02	15
jack	2017-02-03	23
tony	2017-01-04	29
jack	2017-01-05	46
jack	2017-04-06	42
tony	2017-01-07	50
jack	2017-01-08	55
mart	2017-04-08	62
mart	2017-04-09	68
neil	2017-05-10	12
mart	2017-04-11	75
neil	2017-06-12	80
mart	2017-04-13	94
Time taken: 2.0 seconds, Fetched: 14 row(s)

6.3.1 查詢在 2017 年 4 月份購買過的顧客及總人數

hive(default)> select name, count(1) over()
              > from business
              > where substring(orderdate, 1, 7) = '2017-04'
              > group by name;

... ...
OK
name	count_window_0
mart	2
jack	2
Time taken: 114.428 seconds, Fetched: 2 row(s)

【注】

over()

是開窗函數，針對【每一條】資料進行開窗，如果括号内沒内容，就會将所有符合 where 子句及後續限定語句的資料作為開窗的資料給前面的聚合函數進行聚合計算；本例中， mart 購買過 4 次， jack 購買過 1 次，符合 where 子句的共有 5 條資料，加上

group by

的限制，就變成了兩條（因為相同的 name 就隻輸出了一條資料，總共兩條），是以第二列的 count 函數就會輸出 2。

如果本例中去掉

group by

的語句，那麼就會将符合 where 子句的所有資料進行開窗（5 條），那麼結果的第一列會出現一個 jack 和四個 mart，而第二列就應該都是 5。

hive (default)> select name, count(*) over()
              > from business
              > where substring(orderdate, 1, 7) = '2017-04';

... ...
OK
name	count_window_0
mart	5
mart	5
mart	5
mart	5
jack	5
Time taken: 23.664 seconds, Fetched: 5 row(s)

如果不加開窗函數

over()

，那麼結果将是 2017 年 4 月份購買過的人，及購買過的次數：

hive (default)> select name, count(*)
              > from business
              > where substring(orderdate, 1, 7) = '2017-04'
              > group by name;

... ...
OK
name	_c1
jack	1
mart	4
Time taken: 19.944 seconds, Fetched: 2 row(s)

6.3.2 查詢顧客的購買明細及購買總額

hive (default)> select name, orderdate, cost, sum(cost) over()
              > from business;

... ...
OK
name	orderdate	cost	sum_window_0
mart	2017-04-13	94	661
neil	2017-06-12	80	661
mart	2017-04-11	75	661
neil	2017-05-10	12	661
mart	2017-04-09	68	661
mart	2017-04-08	62	661
jack	2017-01-08	55	661
tony	2017-01-07	50	661
jack	2017-04-06	42	661
jack	2017-01-05	46	661
tony	2017-01-04	29	661
jack	2017-02-03	23	661
tony	2017-01-02	15	661
jack	2017-01-01	10	661
Time taken: 18.86 seconds, Fetched: 14 row(s)

6.3.3 上述的場景, 要将 cost 按照日期進行逐個累加

hive (default)> select orderdate, cost, sum(cost) over(order by orderdate)
              > from business;

... ...
OK
orderdate	cost	sum_window_0
2017-01-01	10	10
2017-01-02	15	25
2017-01-04	29	54
2017-01-05	46	100
2017-01-07	50	150
2017-01-08	55	205
2017-02-03	23	228
2017-04-06	42	270
2017-04-08	62	332
2017-04-09	68	400
2017-04-11	75	475
2017-04-13	94	569
2017-05-10	12	581
2017-06-12	80	661
Time taken: 22.812 seconds, Fetched: 14 row(s)

【注】在

over()

函數中進行

order by

的時候，第一次的資料隻有日期最小的，結果就為 10，第二次，包含了最小和倒數第二小的日期的資料，是以結果就是 10+15=25，以此類推。

假如我現在想得到每個人的明細，及每個人各自的總消費額，則有：

hive (default)> select name, orderdate, cost, sum(cost) over(distribute by name)
              > from business;

... ...
OK
name	orderdate	cost	sum_window_0
jack	2017-01-05	46	176
jack	2017-01-08	55	176
jack	2017-01-01	10	176
jack	2017-04-06	42	176
jack	2017-02-03	23	176
mart	2017-04-13	94	299
mart	2017-04-11	75	299
mart	2017-04-09	68	299
mart	2017-04-08	62	299
neil	2017-05-10	12	92
neil	2017-06-12	80	92
tony	2017-01-04	29	94
tony	2017-01-02	15	94
tony	2017-01-07	50	94
Time taken: 19.979 seconds, Fetched: 14 row(s)

【注】視窗函數中的條件是按照 name 進行分區，那麼結果就是計算每個分區後單獨分區的 cost 總和了。不能在

over()

函數中使用

group by

，會報錯！

假如現在想要每個人的購買明細，并且按照購買日期排序，同時還要把每個人的購買額逐一累加結果展示出來：

hive (default)> select name, orderdate, cost, sum(cost) over(distribute by name sort by orderdate)
              > from business;

... ...
OK
name	orderdate	cost	sum_window_0
jack	2017-01-01	10	10
jack	2017-01-05	46	56
jack	2017-01-08	55	111
jack	2017-02-03	23	134
jack	2017-04-06	42	176
mart	2017-04-08	62	62
mart	2017-04-09	68	130
mart	2017-04-11	75	205
mart	2017-04-13	94	299
neil	2017-05-10	12	12
neil	2017-06-12	80	92
tony	2017-01-02	15	15
tony	2017-01-04	29	44
tony	2017-01-07	50	94
Time taken: 30.149 seconds, Fetched: 14 row(s)

6.3.4 查詢顧客上次的購買時間

hive (default)> select name, orderdate, cost, 
              > lag(orderdate, 1) over(distribute by name sort by orderdate)
              > from business;

... ...
OK
name	orderdate	cost	lag_window_0
jack	2017-01-01	10	NULL
jack	2017-01-05	46	2017-01-01
jack	2017-01-08	55	2017-01-05
jack	2017-02-03	23	2017-01-08
jack	2017-04-06	42	2017-02-03
mart	2017-04-08	62	NULL
mart	2017-04-09	68	2017-04-08
mart	2017-04-11	75	2017-04-09
mart	2017-04-13	94	2017-04-11
neil	2017-05-10	12	NULL
neil	2017-06-12	80	2017-05-10
tony	2017-01-02	15	NULL
tony	2017-01-04	29	2017-01-02
tony	2017-01-07	50	2017-01-04
Time taken: 14.933 seconds, Fetched: 14 row(s)

【注】要想不出現 Null，可以在 lag 函數中傳第三個參數，可作為一個預設值，如果遇到 Null，則會以預設值替代；

如果需求變為 “查詢顧客下次的購買時間” 就将 lag 函數換成 lead 即可；

6.3.5 查詢前 20% 時間的訂單資訊

首先，将資料分成五 “組”，使用

ntile()

函數即可：

hive (default)> select name, orderdate, cost, 
              > ntile(5) over(order by orderdate)
              > from business;

... ...
OK
name	orderdate	cost	ntile_window_0
jack	2017-01-01	10	1
tony	2017-01-02	15	1
tony	2017-01-04	29	1
jack	2017-01-05	46	2
tony	2017-01-07	50	2
jack	2017-01-08	55	2
jack	2017-02-03	23	3
jack	2017-04-06	42	3
mart	2017-04-08	62	3
mart	2017-04-09	68	4
mart	2017-04-11	75	4
mart	2017-04-13	94	4
neil	2017-05-10	12	5
neil	2017-06-12	80	5
Time taken: 54.086 seconds, Fetched: 14 row(s)

從五組裡面取出第一組的資料，即是 “前 20%” 時間的資料：

hive (default)> select name, orderdate, cost
              > from 
              > (
              > select name, orderdate, cost, 
              > ntile(5) over(order by orderdate) ntile_5
              > from business
              > ) t1
              > where t1.ntile_5 = 1;

... ...
OK
name	orderdate	cost
jack	2017-01-01	10
tony	2017-01-02	15
tony	2017-01-04	29
Time taken: 24.67 seconds, Fetched: 3 row(s)

6.3.6 關于幾個時間參數的使用示例

select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按 name 分組，組内資料相加
sum(cost) over(partition by name order by orderdate) as sample3,--按 name 分組，組内資料累加
sum(cost) over(partition by name order by orderdate rows between
UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一樣,由起點到目前行的聚合
sum(cost) over(partition by name order by orderdate rows between
1 PRECEDING and current row) as sample5, --目前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 
1 PRECEDING AND 1 FOLLOWING ) as sample6,--目前行和前邊一行及後面一行
sum(cost) over(partition by name order by orderdate rows between
current row and UNBOUNDED FOLLOWING ) as sample7 --目前行及後面所有行
from business;

6.3.7 SQL 語句的書寫及執行順序

書寫順序：（gohl）

select
from 
join on
where
group by 
order by
having
limit

執行順序：（gshol）

from
join on
where
group by
select
having
order by
limit

七、排名函數

首先要說明的是下面介紹的排名函數也是視窗函數中的，隻不過為了凸顯它們的排名功能，是以給單列出來了。

7.1 函數說明

RANK()

：排序相同時會重複，總數不會變；（有并列第一時是 1， 1， 3， 4， … …）

DENSE_RANK()

：排序相同時會重複，總數會減少；（有并列第一時是 1， 1， 2， 3， … …）

ROW_NUMBER()

：會根據順序計算；（有并列第一時也是 1， 2， 3， 4， … …）

7.2 需求

計算每門學科成績排名。

7.3 實作

建表并導入資料：

hive (default)> create table score
              > (
              > name string, 
              > subject string, 
              > score int
              > )
              > row format delimited fields terminated by '\t';
OK
Time taken: 1.336 seconds

hive (default)> load data local inpath 
              > '/opt/module/data/subject.txt'
              > into table score;
Loading data to table default.score
Table default.score stats: [numFiles=1, totalSize=213]
OK
Time taken: 0.52 seconds

查詢資料：

hive (default)> select * from score;
OK
score.name	score.subject	score.score
孫悟空	國文	87
孫悟空	數學	95
孫悟空	英語	68
大海	國文	94
大海	數學	56
大海	英語	84
宋宋	國文	64
宋宋	數學	86
宋宋	英語	84
婷婷	國文	65
婷婷	數學	85
婷婷	英語	78
Time taken: 0.094 seconds, Fetched: 12 row(s)

按需求查詢資料：

hive (default)> select name, subject, score, 
              > rank() over(partition by subject order by score desc) rank_f, 
              > dense_rank() over(distribute by subject sort by score desc) dense_rank_f, 
              > row_number() over(partition by subject order by score desc) row_number_f
              > from score;

... ...
OK
name	subject	score	rank_f	dense_rank_f	row_number_f
孫悟空	數學	95	1	1	1
宋宋	數學	86	2	2	2
婷婷	數學	85	3	3	3
大海	數學	56	4	4	4
宋宋	英語	84	1	1	1
大海	英語	84	1	1	2
婷婷	英語	78	3	2	3
孫悟空	英語	68	4	3	4
大海	國文	94	1	1	1
孫悟空	國文	87	2	2	2
婷婷	國文	65	3	3	3
宋宋	國文	64	4	4	4
Time taken: 30.053 seconds, Fetched: 12 row(s)

八、兩個小題

8.1 統計使用者累計通路次數

建表及導入資料：

hive (default)> create table action
              > (
              > userid string, 
              > visitdate string,
              > visitcount int
              > )
              > row format delimited fields terminated by '\t';
OK
Time taken: 0.079 seconds
hive (default)> load data local inpath 
              > '/opt/module/data/visit.txt'
              > into table action;
Loading data to table default.action
Table default.action stats: [numFiles=1, totalSize=128]
OK
Time taken: 0.206 seconds

查詢資料：

hive (default)> select * from action;
OK
action.userid	action.visitdate	action.visitcount
u01	2017/1/21	5
u02	2017/1/23	6
u03	2017/1/22	8
u04	2017/1/20	3
u01	2017/1/23	6
u01	2017/2/21	8
u02	2017/1/23	6
u01	2017/2/22	4
Time taken: 0.054 seconds, Fetched: 8 row(s)

需求：統計出每個使用者、每個月的累計通路次數：

首先，将日期修改為最終需要的格式：

hive (default)> select userid, 
              > date_format(regexp_replace(visitdate, '/', '-'), 'yyyy-MM') mn, 
              > visitcount
              > from action;
OK
userid	mn	visitcount
u01	2017-01	5
u02	2017-01	6
u03	2017-01	8
u04	2017-01	3
u01	2017-01	6
u01	2017-02	8
u02	2017-01	6
u01	2017-02	4
Time taken: 0.065 seconds, Fetched: 8 row(s)

其次，求出第三列：

hive (default)> select userid, mn, sum(visitcount)
              > from 
              > (
              > select userid, 
              > date_format(regexp_replace(visitdate, '/', '-'),'yyyy-MM') mn,
              > visitcount
              > from action
              > ) t1
              > group by userid, mn;

... ...
OK
userid	mn	_c2
u01	2017-01	11
u01	2017-02	12
u02	2017-01	12
u03	2017-01	8
u04	2017-01	3
Time taken: 17.183 seconds, Fetched: 5 row(s)

最後，求出第四列：

hive (default)> select userid, mn, sum_c, sum(sum_c) over(distribute by userid sort by mn)
              > from 
              > (
              > select userid, mn, sum(visitcount) sum_c
              > from (
              > select userid,
              > date_format(regexp_replace(visitdate, '/', '-'),'yyyy-MM') mn,
              > visitcount
              > from action) t1
              > group by userid, mn
              > ) t2;

... ...
OK
userid	mn	sum_c	sum_window_0
u01	2017-01	11	11
u01	2017-02	12	23
u02	2017-01	12	12
u03	2017-01	8	8
u04	2017-01	3	3
Time taken: 15.256 seconds, Fetched: 5 row(s)

8.2 店鋪訪客數等

有 50W個店鋪，每個顧客通路任何一個店鋪的任何一個商品時都會産生一條通路日志，通路日志存儲的表名為 jd，訪客的使用者 id 為 user_id，被通路的店鋪名稱為 shop，請統計：

每個店鋪的UV（訪客數）
每個店鋪通路次數top3的訪客資訊。輸出店鋪名稱、訪客id、通路次數

建表及導入資料：

hive (default)> create table jd(
              > usr_id string, 
              > shop string
              > )
              > row format delimited fields terminated by '\t';
OK
Time taken: 0.679 seconds

hive (default)> load data local inpath
              > '/opt/module/data/jd.txt'
              > into table jd;
Loading data to table default.jd
Table default.jd stats: [numFiles=1, totalSize=95]
OK
Time taken: 0.34 seconds

查詢資料：

hive (default)> select * from jd;
OK
jd.usr_id	jd.shop
u1	a
u2	b
u1	b
u1	a
u3	c
u4	b
u1	a
u2	c
u5	b
u4	b
u6	c
u2	c
u1	b
u2	a
u2	a
u3	a
u5	a
u5	a
u5	a
Time taken: 0.079 seconds, Fetched: 19 row(s)

需求一：每個店鋪的UV（訪客數）

先按照使用者、商鋪去重：

hive (default)> select usr_id, shop 
              > from jd
              > group by usr_id, shop;

... ...
OK
usr_id	shop
u1	a
u1	b
u2	a
u2	b
u2	c
u3	a
u3	c
u4	b
u5	a
u5	b
u6	c
Time taken: 15.159 seconds, Fetched: 11 row(s)

然後計數：

hive (default)> select shop, count(1) uv from 
              > (
              > select usr_id, shop 
              > from jd
              > group by usr_id, shop
              > ) t1
              > group by shop;

... ...
OK
shop	uv
a	4
b	4
c	3
Time taken: 64.018 seconds, Fetched: 3 row(s)

需求二：每個店鋪通路次數 top3 的訪客資訊。輸出店鋪名稱、訪客id、通路次數

先計算每個商鋪中每個賬戶的通路次數：

hive (default)> select shop, usr_id, count(*)
              > from jd
              > group by shop, usr_id;

... ...
OK
shop	usr_id	_c2
a	u1	3
a	u2	2
a	u3	1
a	u5	3
b	u1	2
b	u2	1
b	u4	2
b	u5	1
c	u2	2
c	u3	1
c	u6	1
Time taken: 44.766 seconds, Fetched: 11 row(s)

針對同一店鋪，對通路次數進行逆序排序，并新增一個 rank 排序列：

hive (default)> select shop, usr_id, uv, row_number() over(distribute by shop sort by uv desc)
              > from (
              > select shop, usr_id, count(*) uv
              > from jd
              > group by shop, usr_id
              > ) t1;

... ...
OK
shop	usr_id	uv	row_number_window_0
a	u5	3	1
a	u1	3	2
a	u2	2	3
a	u3	1	4
b	u4	2	1
b	u1	2	2
b	u5	1	3
b	u2	1	4
c	u2	2	1
c	u6	1	2
c	u3	1	3
Time taken: 47.539 seconds, Fetched: 11 row(s)

從上一步的資料中，拿出 top3：

hive (default)> select shop, usr_id, uv
              > from 
              > (
              > select shop, usr_id, uv, row_number() over(distribute by shop sort by uv desc) rk
              > from (
              > select shop, usr_id, count(*) uv
              > from jd
              > group by shop, usr_id) t1
              > ) t2
              > where rk < 4;

... ...
OK
shop	usr_id	uv
a	u5	3
a	u1	3
a	u2	2
b	u4	2
b	u1	2
b	u5	1
c	u2	2
c	u6	1
c	u3	1
Time taken: 73.195 seconds, Fetched: 9 row(s)

Hive 之函數 02-常用查詢函數（二）六、視窗函數七、排名函數八、兩個小題

Hive 之函數 02-常用查詢函數（二）

六、視窗函數

6.1 函數說明

6.2 需求

6.3 實作

6.3.1 查詢在 2017 年 4 月份購買過的顧客及總人數

6.3.2 查詢顧客的購買明細及購買總額

6.3.3 上述的場景, 要将 cost 按照日期進行逐個累加

6.3.4 查詢顧客上次的購買時間

6.3.5 查詢前 20% 時間的訂單資訊

6.3.6 關于幾個時間參數的使用示例

6.3.7 SQL 語句的書寫及執行順序

七、排名函數

7.1 函數說明

7.2 需求

7.3 實作

八、兩個小題

8.1 統計使用者累計通路次數

8.2 店鋪訪客數等

繼續閱讀

Hadoop離線_Hive的基本操作

Hive中内部表、外部表、分區、分桶以及SQL的執行順序

Hive中的内部表外部表及分區表

Hive---外部分區表的建立

Hive學習筆記 3 Hive的資料模型：内部表、分區表、外部表、桶表、視圖

HiveQL(二):分區表

Hive的分區表入門

Hive的分區表

Hive（二）--分區分桶，内部表外部表

大資料高頻面試題之Hive的小檔案合并

世界因大資料而改變

hive sql通過具體位址解析出行政區劃(省＞市＞區＞縣＞鄉＞鎮＞村)

SQL：視窗函數（排序統計）

Hive最全常見錯誤及解決方案hive --service metastore &

《Hive權威指南》第八章：HiveQL索引8 HiveQL：索引

HiveQl語句應用執行個體：WordCount具體步驟如下：

Hive 之 函數 02-常用查詢函數（二）六、 視窗函數七、 排名函數八、 兩個小題

Hive 之 函數 02-常用查詢函數（二）

六、 視窗函數

6.1 函數說明

6.2 需求

6.3 實作

6.3.1 查詢在 2017 年 4 月份購買過的顧客及總人數

6.3.2 查詢顧客的購買明細及購買總額

6.3.3 上述的場景, 要将 cost 按照日期進行逐個累加

6.3.4 查詢顧客上次的購買時間

6.3.5 查詢前 20% 時間的訂單資訊

6.3.6 關于幾個時間參數的使用示例

6.3.7 SQL 語句的書寫及執行順序

七、 排名函數

7.1 函數說明

7.2 需求

7.3 實作

八、 兩個小題

8.1 統計使用者累計通路次數

8.2 店鋪訪客數等

繼續閱讀

Hive 之函數 02-常用查詢函數（二）六、視窗函數七、排名函數八、兩個小題

Hive 之函數 02-常用查詢函數（二）

六、視窗函數

七、排名函數

八、兩個小題