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

Posted on by hackdl

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

数据结果

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

数据结果

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

数据结果

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

数据结果

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

数据结果

服务器托管

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

数据结果

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

数据结果

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

数据结果

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

数据明细

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

数据结果

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窗口分析函数使用攻略之二-分组排序窗口函数

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

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net

相关推荐: 华为HCIE课堂笔记第十三章 IPv6地址配置

目录 第十三章 IPv6地址配置 13.1 IPv6地址无状态自动配置 13.1.1 RS和RA报文格式 13.1.2 RA的Flags字段 13.1.3 地址的生存周期 13.1.4 RA报文中前缀中的Flags 13.2 DHCPv6 13.2.1 DHC…

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: