hive窗口分析函数使用详解系列一

文章目录

1.综述

Hive的聚合函数衍生的窗口函数在我们进行数据处理和数据分析过程中起到了很大的作用

在Hive中，窗口函数允许你在结果集的行上进行计算，这些计算不会影响你查询的结果集的行数。

Hive提供的窗口和分析函数可以分为聚合函数类窗口函数，分组排序类窗口函数，偏移量计算类窗口函数。

本节主要介绍聚合函数类窗口函数的常见使用场景。

1.1.常见聚合类开窗函数

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

1.2.分析函数语法

分析函数 over(partitionby 列名 orderby 列名 rowsbetween 开始位置 and 结束位置)

具体解析

over()

括号内为空时，是直接进行聚合计算。

其中

partition by 列名 

是按指定列进行分组，进而进行聚合计算。

最后的

order by 列名

是按照指定列进行排序，进而进行聚合计算。

1.3.基础数据准备

createtableifnotexiststemp.user_info (`id`bigintcomment'用户id',`client` string comment'客户端',`gender`intcomment'性别，0女1男',`constellation` string comment'星座',`age`intcomment'年龄',`pv`bigintcomment'访问量',`chat_num`bigintcomment'聊天次数')comment'用户信息测试临时表'

数据预览
idclientgenderconstellationagepvchat_num1ios0处女座2917432ios1双鱼座2626323android1双鱼座35232394ios1水瓶座325735ios1射手座336766ios1双子座368157ios1狮子座296848ios1狮子座281939ios0射手座32479210ios1白羊座2625536

2.over窗口为空时的计算

over()括号内为空的计算比较简单，主要应用场景为保留数据明细的同时，增加额外的列进行数据聚合计算。

1.1.sum求解总访问量总和及用户明细列表。

-- over()括号内为空时，是直接进行聚合计算select id,client,gender,age,pv,sum(pv)over()as total_pv fromtemp.user_info where  id <=10orderby id

数据结果
idclientgenderagepvtotal_pv1ios02917416952ios12626316953android13523216954ios1325716955ios1336716956ios1368116957ios1296816958ios1281916959ios032479169510ios1262551695
可以看到给出了数据明细，并且在每行明细后增加了累积求和值。

1.2.count查询用户总量及用户明细列表。

select id,client,gender,age,pv,count(id)over()as total_count fromtemp.user_info where  id <=10orderby id

数据结果
idclientgenderagepvtotal_count1ios029174102ios126263103android135232104ios13257105ios13367106ios13681107ios12968108ios12819109ios0324791010ios12625510
给出了数据明细，并且在明细后增加了当前总用户数。

1.3.max查询用户最大访问量及用户明细

-- max()查询用户最大访问量及用户明细select id,client,gender,age,pv,max(pv)over()as max_pv fromtemp.user_info where  id <=10orderby id

数据结果
idclientgenderagepvmax_pv1ios0291744792ios1262634793android1352324794ios132574795ios133674796ios136814797ios129684798ios128194799ios03247947910ios126255479
给出了数据明细，并在最后列增加了用户最大访问量数据

min() 同理

1.4.avg查询用户平均访问量及用户明细

select id,client,gender,age,pv,avg(pv)over()as avg_pv fromtemp.user_info where  id <=10orderby id

数据结果
idclientgenderagepvavg_pv1ios029174169.52ios126263169.53android135232169.54ios13257169.55ios13367169.56ios13681169.57ios12968169.58ios12819169.59ios032479169.510ios126255169.5
给出了数据明细，并在最后列增加了用户平均访问量。

2.指定列进行分组的聚合计算

2.1.sum求解不同年龄段总访问量总和及用户明细

select*,sum(pv)over(partitionby age)as total_pv fromtemp.user_info where  id <=10orderby age

数据结果
idclientgenderagepvavg_pv2ios12626351810ios1262555188ios12819191ios0291742427ios129682424ios132575369ios0324795365ios13367673android1352322326ios1368181
可以看到最后的total_pv 是按照年龄段分组进行累加的

2.2.count求解不同客户端总用户数及用户明细列表

select id,client,gender,age,pv,count(id)over(partitionby client)as count_total fromtemp.user_info where  id <=10orderby id

数据结果
idclientgenderagepvcount_total1ios02917492ios12626393android13523214ios1325795ios1336796ios1368197ios1296898ios1281999ios032479910ios1262559
可以看到最后count_total 是按client分组进行计数的

2.3.max求解不同年龄段最大访问量及用户明细列表

select id,client,gender,age,pv,max(pv)over(partitionby age)as count_total fromtemp.user_info where  id <=10orderby age

数据结果
idclientgenderagepvcount_total10ios1262552632ios1262632638ios12819197ios129681741ios0291741744ios132574799ios0324794795ios13367673android1352322326ios1368181
可以看到进行了分组求最大值。

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

3.指定列进行分组和排序的聚合计算

3.1.sum按性别分组截止当前年龄总访问量及用户明细列表

select id,client,gender,age,pv,sum(pv)over(partitionby gender orderby age)as total_pv fromtemp.user_info where  id <=10orderby gender

数据结果
idclientgenderagepvtotal_pv1ios0291741749ios0324796532ios12626351810ios1262555188ios128195377ios129686054ios132576625ios133677293android1352329616ios136811042
数据解释：可以看到上述数据，性别为女的29岁及之前年龄段访问pv为174次，女性32岁及之前年龄访问总和pv为653次。

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

3.2.按性别分组截止当前年龄最大用户访问量及用户明细列表

select id,client,gender,age,pv,max(pv)over(partitionby gender orderby age)as max_pv fromtemp.user_info where  id <=10orderby gender

数据明细
idclientgenderagepvmax_pv1ios0291741749ios0324794792ios12626326310ios1262552638ios128192637ios129682634ios132572635ios133672633android1352322636ios13681263
可以看到男性最大访问量为263

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

3.3.按性别分组截止当前年龄用户总数

select id,client,gender,age,pv,count(id)over(partitionby gender orderby age)as count_uv fromtemp.user_info where  id <=10orderby gender,age

数据结果
idclientgenderagepvcount_uv1ios02917419ios03247922ios126263210ios12625528ios1281937ios1296844ios1325755ios1336763android13523276ios136818
分组累加求和

综合以上内容，第一部分和第二部分可以通过聚合函数+join的形式实现，但第三部分排序累加计数，实现起来比较困难，而这部分在一些需要分组累加汇总的场景使用很方便。

下一期：hive窗口分析函数使用攻略之二-分组排序窗口函数

按例，欢迎点击此处关注我的个人公众号，交流更多知识。