hive視窗分析函式使用詳解系列一

1.綜述

Hive的聚合函式衍生的視窗函式在我們進行資料處理和資料分析過程中起到了很大的作用

在Hive中，視窗函式允許你在結果集的行上進行計算，這些計算不會影響你查詢的結果集的行數。

Hive提供的視窗和分析函式可以分為聚合函式類視窗函式，分組排序類視窗函式，偏移量計算類視窗函式。

本節主要介紹聚合函式類視窗函式的常見使用場景。

1.1.常見聚合類開窗函式

count() over();
sum() over();
max() over();
min() over();
avg() over();

1.2.分析函式語法

分析函式 over(partition by 列名 order by 列名 rows between 開始位置 and 結束位置)

具體解析

over()括號內為空時，是直接進行聚合計算。

其中partition by 列名 是按指定列進行分組，進而進行聚合計算。

最後的order by 列名 是按照指定列進行排序，進而進行聚合計算。

1.3.基礎資料準備

create table if not exists temp.user_info (
  `id` bigint comment '使用者id',
  `client` string comment '客戶端',
  `gender` int comment '性別，0女1男',
  `constellation` string comment '星座',
  `age` int comment '年齡',
  `pv` bigint comment '訪問量',
  `chat_num` bigint comment '聊天次數'
) comment '使用者資訊測試臨時表' 

資料預覽

id	client	gender	constellation	age	pv	chat_num
1	ios	0	處女座	29	174	3
2	ios	1	雙魚座	26	263	2
3	android	1	雙魚座	35	232	39
4	ios	1	水瓶座	32	57	3
5	ios	1	射手座	33	67	6
6	ios	1	雙子座	36	81	5
7	ios	1	獅子座	29	68	4
8	ios	1	獅子座	28	19	3
9	ios	0	射手座	32	479	2
10	ios	1	白羊座	26	255	36

2.over視窗為空時的計算

over()括號內為空的計算比較簡單，主要應用場景為保留資料明細的同時，增加額外的列進行資料聚合計算。

1.1.sum求解總訪問量總和及使用者明細列表。

-- over()括號內為空時，是直接進行聚合計算
select id,client,gender,age,pv,sum(pv) over() as total_pv from temp.user_info where  id <= 10
order by id

資料結果

id	client	gender	age	pv	total_pv
1	ios	0	29	174	1695
2	ios	1	26	263	1695
3	android	1	35	232	1695
4	ios	1	32	57	1695
5	ios	1	33	67	1695
6	ios	1	36	81	1695
7	ios	1	29	68	1695
8	ios	1	28	19	1695
9	ios	0	32	479	1695
10	ios	1	26	255	1695

可以看到給出了資料明細，並且在每行明細後增加了累積求和值。

1.2.count查詢使用者總量及使用者明細列表。

select id,client,gender,age,pv,count(id) over() as total_count from temp.user_info where  id <= 10
order by id

資料結果

id	client	gender	age	pv	total_count
1	ios	0	29	174	10
2	ios	1	26	263	10
3	android	1	35	232	10
4	ios	1	32	57	10
5	ios	1	33	67	10
6	ios	1	36	81	10
7	ios	1	29	68	10
8	ios	1	28	19	10
9	ios	0	32	479	10
10	ios	1	26	255	10

給出了資料明細，並且在明細後增加了當前總使用者數。

1.3.max查詢使用者最大訪問量及使用者明細

-- max()查詢使用者最大訪問量及使用者明細
select id,client,gender,age,pv,max(pv) over() as max_pv from temp.user_info where  id <= 10
order by id

資料結果

id	client	gender	age	pv	max_pv
1	ios	0	29	174	479
2	ios	1	26	263	479
3	android	1	35	232	479
4	ios	1	32	57	479
5	ios	1	33	67	479
6	ios	1	36	81	479
7	ios	1	29	68	479
8	ios	1	28	19	479
9	ios	0	32	479	479
10	ios	1	26	255	479

給出了資料明細，並在最後列增加了使用者最大訪問量資料

min() 同理

1.4.avg查詢使用者平均訪問量及使用者明細

select id,client,gender,age,pv,avg(pv) over() as avg_pv from temp.user_info where  id <= 10
order by id

資料結果

id	client	gender	age	pv	avg_pv
1	ios	0	29	174	169.5
2	ios	1	26	263	169.5
3	android	1	35	232	169.5
4	ios	1	32	57	169.5
5	ios	1	33	67	169.5
6	ios	1	36	81	169.5
7	ios	1	29	68	169.5
8	ios	1	28	19	169.5
9	ios	0	32	479	169.5
10	ios	1	26	255	169.5

給出了資料明細，並在最後列增加了使用者平均訪問量。

2.指定列進行分組的聚合計算

2.1.sum求解不同年齡段總訪問量總和及使用者明細

select *,sum(pv) over(partition by age) as total_pv from temp.user_info where  id <= 10
order by age

資料結果

id	client	gender	age	pv	avg_pv
2	ios	1	26	263	518
10	ios	1	26	255	518
8	ios	1	28	19	19
1	ios	0	29	174	242
7	ios	1	29	68	242
4	ios	1	32	57	536
9	ios	0	32	479	536
5	ios	1	33	67	67
3	android	1	35	232	232
6	ios	1	36	81	81

可以看到最後的total_pv 是按照年齡段分組進行累加的

2.2.count求解不同客戶端總使用者數及使用者明細列表

select id,client,gender,age,pv,count(id) over(partition by client) as count_total from temp.user_info where  id <= 10
order by id

資料結果

id	client	gender	age	pv	count_total
1	ios	0	29	174	9
2	ios	1	26	263	9
3	android	1	35	232	1
4	ios	1	32	57	9
5	ios	1	33	67	9
6	ios	1	36	81	9
7	ios	1	29	68	9
8	ios	1	28	19	9
9	ios	0	32	479	9
10	ios	1	26	255	9

可以看到最後count_total 是按client分組進行計數的

2.3.max求解不同年齡段最大訪問量及使用者明細列表

select id,client,gender,age,pv,max(pv) over(partition by age) as count_total from temp.user_info where  id <= 10
order by age

資料結果

id	client	gender	age	pv	count_total
10	ios	1	26	255	263
2	ios	1	26	263	263
8	ios	1	28	19	19
7	ios	1	29	68	174
1	ios	0	29	174	174
4	ios	1	32	57	479
9	ios	0	32	479	479
5	ios	1	33	67	67
3	android	1	35	232	232
6	ios	1	36	81	81

可以看到進行了分組求最大值。

min以及avg同理，不再舉例。

3.指定列進行分組和排序的聚合計算

3.1.sum按性別分組截止當前年齡總訪問量及使用者明細列表

select id,client,gender,age,pv,sum(pv) over(partition by gender order by age) as total_pv from temp.user_info where  id <= 10
order by gender

資料結果

id	client	gender	age	pv	total_pv
1	ios	0	29	174	174
9	ios	0	32	479	653
2	ios	1	26	263	518
10	ios	1	26	255	518
8	ios	1	28	19	537
7	ios	1	29	68	605
4	ios	1	32	57	662
5	ios	1	33	67	729
3	android	1	35	232	961
6	ios	1	36	81	1042

資料解釋：可以看到上述資料，性別為女的29歲及之前年齡段訪問pv為174次，女性32歲及之前年齡訪問總和pv為653次。

同理男性，不同年齡段及之前年齡的累加資料如上表，且相同年齡的累加值是一致的。

3.2.按性別分組截止當前年齡最大使用者訪問量及使用者明細列表

select id,client,gender,age,pv,max(pv) over(partition by gender order by age) as max_pv from temp.user_info where  id <= 10
order by gender

資料明細

id	client	gender	age	pv	max_pv
1	ios	0	29	174	174
9	ios	0	32	479	479
2	ios	1	26	263	263
10	ios	1	26	255	263
8	ios	1	28	19	263
7	ios	1	29	68	263
4	ios	1	32	57	263
5	ios	1	33	67	263
3	android	1	35	232	263
6	ios	1	36	81	263

可以看到男性最大訪問量為263

min以及avg同理，不再舉例。

3.3.按性別分組截止當前年齡使用者總數

select id,client,gender,age,pv,count(id) over(partition by gender order by age) as count_uv from temp.user_info where  id <= 10
order by gender,age

資料結果

id	client	gender	age	pv	count_uv
1	ios	0	29	174	1
9	ios	0	32	479	2
2	ios	1	26	263	2
10	ios	1	26	255	2
8	ios	1	28	19	3
7	ios	1	29	68	4
4	ios	1	32	57	5
5	ios	1	33	67	6
3	android	1	35	232	7
6	ios	1	36	81	8

分組累加求和

綜合以上內容，第一部分和第二部分可以透過聚合函式+join的形式實現，但第三部分排序累加計數，實現起來比較困難，而這部分在一些需要分組累加彙總的場景使用很方便。

下一期：hive視窗分析函式使用攻略之二-分組排序視窗函式

按例，歡迎點選此處關注我的個人公眾號，交流更多知識。