1. Toy模板网首页
  2. > Toy博客

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

5月前 作者：鲁边 分类：Toy博客 阅读(28) 违法举报

这篇具有很好参考价值的文章主要介绍了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窗口分析函数使用攻略之二-分组排序窗口函数

按例，欢迎点击此处关注我的个人公众号，交流更多知识。文章来源地址https://www.toymoban.com/news/detail-844419.html

到了这里，关于hive窗口分析函数使用详解系列一的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

  • HiveSQL在使用聚合类函数的时候性能分析和优化详解

    HiveSQL在使用聚合类函数的时候性能分析和优化详解

    前文我们写过简单SQL的性能分析和解读，简单SQL被归类为select-from-where型SQL语句，其主要特点是只有map阶段的数据处理，相当于直接从hive中取数出来，不需要经过行变化。在非多个节点的操作上，其性能甚至不比Tez和Spark差。 而这次我们主要说的是使用聚合类函数的hiveSQL，这

    2024年02月09日
    浏览(32)
  • (07)Hive——窗口函数详解

    (07)Hive——窗口函数详解

            窗口函数可以拆分为【窗口+函数】。窗口函数官网指路： LanguageManual WindowingAndAnalytics - Apache Hive - Apache Software Foundation https://cwiki.apache.org/confluence/display/Hive/LanguageManual%20WindowingAndAnalytics 窗口： over()，指明函数要处理的 数据范围 函数： 指明函数 计算逻辑 window_nam

    2024年02月19日
    浏览(26)

  • 聚合函数与窗口函数

    聚合函数（Aggregate Functions）是SQL中的函数，用于对一组数据进行计算，并返回单个结果。聚合函数通常用于统计和汇总数据，包括计算总和、平均值、计数、最大值和最小值等。 以下是一些常见的聚合函数： SUM()：计算指定列或表达式的总和。 AVG()：计算指定列或表达式的

    2024年02月10日
    浏览(34)
  • Flink系列Table API和SQL之：滚动窗口、滑动窗口、累计窗口、分组聚合

    Flink系列Table API和SQL之：滚动窗口、滑动窗口、累计窗口、分组聚合

    有了时间属性，接下来就可以定义窗口进行计算了。窗口可以将无界流切割成大小有限的桶(bucket)来做计算，通过截取有限数据集来处理无限的流数据。在DataStream API中提供了对不同类型的窗口进行定义和处理的接口，而在Table API和SQL中，类似的功能也都可以实现。 在Flink 1

    2023年04月27日
    浏览(50)
  • 【大数据之Hive】十三、Hive-HQL函数之单行函数和高级聚合函数

    【大数据之Hive】十三、Hive-HQL函数之单行函数和高级聚合函数

      Hive内置函数：单行函数、聚合函数、炸裂函数、窗口函数。   单行函数的特点是一进一出，即输入一行，输出一行；包括算数运算函数、数值函数、字符串函数、日期函数、流程控制函数、集合函数等。 3.1 substring 截取字符串 语法： 3.2 replace 全局替换 语法： 3.3 re

    2024年02月09日
    浏览(38)
  • 【MySQL新手到通关】第七章 聚合函数使用详解

    【MySQL新手到通关】第七章 聚合函数使用详解

    为了方便测试，我们导入一些数据 数据如下 什么是聚合函数 聚合函数作用于一组数据，并对一组数据返回一个值。 聚合函数类型 AVG() 求平均值 SUM() 求和 MAX() 求最大值 MIN() 求最小值 COUNT() 求总行数 聚合函数语法 聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称

    2024年02月08日
    浏览(32)
  • MySQL---控制流函数、窗口函数（序号函数、开窗聚合函数、分布函数、前后函数、头尾函数、其他函数）

    MySQL---控制流函数、窗口函数（序号函数、开窗聚合函数、分布函数、前后函数、头尾函数、其他函数）

    格式 解释 案例 IF(expr,v1,v2) 如果表达式 expr 成立，返回结果 v1 ；否则，返回结果 v2 。 SELECT IF(1 0,\\\' 正确 \\\',\\\' 错误 \\\')    - 正确 IFNULL(v1,v2） 如果 v1 的值不为 NULL ，则返回 v1 ，否则返回 v2 。 SELECT IFNULL(null,\\\'Hello Word\\\') -Hello Word ISNULL(expression) 判断表达式是否为 NULL SELECT ISNULL(NUL

    2024年02月04日
    浏览(33)
  • hive 函数使用详解

    hive 函数使用详解

    在任何一种编程语言中，函数可以说是必不可少的，像mysql、oracle中，提供了很多内置函数，或者通过自定义函数的方式进行定制化使用，而hive作为一门数据分析软件，随着版本的不断更新迭代，也陆续出现了很多函数，以满足日常数据查询与分析的各种场景。 Hive内建了不

    2024年02月03日
    浏览(27)

  • Hive窗口函数-lead/lag函数

    前面我们学习的first_value和last_value 取的是排序后的数据截止当前行的第一行数据和最后一行数据 Lag和Lead分析函数可以在一次查询中取出当前行后N行和前N行的数据，虽然可以不用排序，但是往往只有在排序的场景下取前面或者后面N 行数据才有意义 这种操作可以代替表的自

    2024年02月16日
    浏览(50)
  • Hive窗口函数全解

    Hive窗口函数全解

    在SQL中有一类函数叫做聚合函数，例如sum()、avg()、max()等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据，又要显示聚集后的数据，这时我们便引入了窗口函数。窗口函数又叫OLAP函数

    2024年02月03日
    浏览(38)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包