hive窗口函数

已于 2022-04-15 17:46:18 修改
阅读量403 收藏
点赞数 2
分类专栏： hive 文章标签： hive
于 2022-04-15 17:20:13 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_55834564/article/details/124198528
版权

一、介绍

  • 窗口函数，也叫OLAP函数（Online Anallytical Processing,联机分析处理），可以对数据库数据进行实时分析处理。
  • 窗口函数由开窗函数和分析函数构成，窗口函数就是既要显示聚集前的数据,又要显示聚集后的数据,简单讲，就是你查询的结果上，多出一列值（可以是聚合值或者排序号)，所以分析函数可以分为两类：聚合分析函数和排序分析函数
    基本语法：
<窗口分析函数> over (partition by <用于分组的字段> order by<用于排序的列名>) 

1. 概要

开窗函数就是over()函数，就是限定一个窗口，来显示分析函数的结果

2.开窗函数一般有两种(固定形式，不可更改)

-- 第一种
over(partiton by ... order by ...)
-- 第二种
over(distribute by ... order by ...)

3.区别

  • partiton by是一个一个reduce处理数据的，所以使用全局排序order by distribute
  • distribute by是多个reduce处理数据的，所以使用局部排序sort by

二、函数

1.排序分析函数

  • RANK() OVER();
  • ROW_NUMBER() OVER();
  • DENSE_RANK() OVER();
  • NTILE(n) OVER();

代码如下

创建表

0: jdbc:hive2://192.168.171.151:10000> create table student
. . . . . . . . . . . . . . . . . . > (name string,score int)
. . . . . . . . . . . . . . . . . . > row format delimited fields
. . . . . . . . . . . . . . . . . . > terminated by '\t';
No rows affected (0.877 seconds)

创建数据

[root@hadoop l0415]# vim student.txt 
张三	100
李四	90
王五	80
刘六	100
田七	70

导入数据

0: jdbc:hive2://192.168.171.151:10000> load data local inpath '/usr/word/l0415/student.txt'
. . . . . . . . . . . . . . . . . . > into table student;
No rows affected (1.533 seconds)

 使用RANK() OVER()

特点：并列跳跃排序

0: jdbc:hive2://192.168.171.151:10000> from student
. . . . . . . . . . . . . . . . . . > select name,score,rank()
. . . . . . . . . . . . . . . . . . > over(order by score desc) ranking;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
+-------+--------+----------+--+
| name  | score  | ranking  |
+-------+--------+----------+--+
| 刘六    | 100    | 1        |
| 张三    | 100    | 1        |
| 李四    | 90     | 3        |
| 王五    | 80     | 4        |
| 田七    | 70     | 5        |
+-------+--------+----------+--+
5 rows selected (33.441 seconds)

特点：并列连续排序

0: jdbc:hive2://192.168.17.151:10000> from student
. . . . . . . . . . . . . . . . . . > select name,score,dense_rank()
. . . . . . . . . . . . . . . . . . > over(order by score desc) ranking;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
+-------+--------+----------+--+
| name  | score  | ranking  |
+-------+--------+----------+--+
| 刘六    | 100    | 1        |
| 张三    | 100    | 1        |
| 李四    | 90     | 2        |
| 王五    | 80     | 3        |
| 田七    | 70     | 4        |
+-------+--------+----------+--+
5 rows selected (33.399 seconds)

 总结
通过上面两个函数，我们发现开窗函数能实现普通函数比较难实现或者无法实现的问题，因为聚合函数只能操作分组的字段，这也是聚合函数最大的特点，窗口函数能够操作所有的字段，不受分组的限制。

ROW_NUMBER() PERCENT_RANK() NTILE() 概述

  • ROW_NUMBER()
    特点：连续排序
  • PERCENT_RANK()
    特点：百分比排序
    percent_rank() 含义就是
    当前行-1 / 当前组总行数-1
  • NTILE()
    特点：将有序集分桶(bucket)
0: jdbc:hive2://192.168.17.151:10000> from student
. . . . . . . . . . . . . . . . . . > select name,score,
. . . . . . . . . . . . . . . . . . > ntile(3) over(order by score desc) ntres,
. . . . . . . . . . . . . . . . . . > percent_rank() over(order by score desc) prank,
. . . . . . . . . . . . . . . . . . > row_number() over(order by score desc) rownum;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
+-------+--------+--------+--------+---------+--+
| name  | score  | ntres  | prank  | rownum  |
+-------+--------+--------+--------+---------+--+
| 刘六    | 100    | 1      | 0.0    | 1       |
| 张三    | 100    | 1      | 0.0    | 2       |
| 李四    | 90     | 2      | 0.5    | 3       |
| 王五    | 80     | 2      | 0.75   | 4       |
| 田七    | 70     | 3      | 1.0    | 5       |
+-------+--------+--------+--------+---------+--+
5 rows selected (32.374 seconds)

2.聚合分析函数

例如：

0: jdbc:hive2://192.168.17.151:10000> from student
. . . . . . . . . . . . . . . . . . > select name,score,
. . . . . . . . . . . . . . . . . . > sum(score) over() sumres,
. . . . . . . . . . . . . . . . . . > count(score) over() cres,
. . . . . . . . . . . . . . . . . . > min(score) over() minres,
. . . . . . . . . . . . . . . . . . > max(score) over() maxres,
. . . . . . . . . . . . . . . . . . > avg(score) over() avgres
. . . . . . . . . . . . . . . . . . > ;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
+-------+--------+---------+-------+---------+---------+---------+--+
| name  | score  | sumres  | cres  | minres  | maxres  | avgres  |
+-------+--------+---------+-------+---------+---------+---------+--+
| 田七    | 70     | 440     | 5     | 70      | 100     | 88.0    |
| 刘六    | 100    | 440     | 5     | 70      | 100     | 88.0    |
| 王五    | 80     | 440     | 5     | 70      | 100     | 88.0    |
| 李四    | 90     | 440     | 5     | 70      | 100     | 88.0    |
| 张三    | 100    | 440     | 5     | 70      | 100     | 88.0    |
+-------+--------+---------+-------+---------+---------+---------+--+
5 rows selected (34.494 seconds)
  • 下面的函数的使用是根据over()中的参数变化而变化的

first_value() 函数

  • 用法是根据partiton by 的字段进行分区，如果忽略partition by,会根据order by排序后的结果返回第一条数据
  • 示例 在每行数据后开窗显示总第一名和每个班的第一名

修改数据，并追加

0: jdbc:hive2://192.168.17.151:10000> drop table student;
No rows affected (0.211 seconds)
0: jdbc:hive2://192.168.17.151:10000> 
0: jdbc:hive2://192.168.17.151:10000> create table student
. . . . . . . . . . . . . . . . . . > (name string,score int,class int)
. . . . . . . . . . . . . . . . . . > row format delimited fields
. . . . . . . . . . . . . . . . . . > terminated by '\t';
No rows affected (0.164 seconds)
0: jdbc:hive2://192.168.17.151:10000> load data local inpath '/usr/word/l0415/student.txt'
. . . . . . . . . . . . . . . . . . > into table student;
No rows affected (0.426 seconds)
0: jdbc:hive2://192.168.17.151:10000> select * from student;
+---------------+----------------+----------------+--+
| student.name  | student.score  | student.class  |
+---------------+----------------+----------------+--+
| 张三            | 100            | 1              |
| 李四            | 90             | 2              |
| 王五            | 80             | 3              |
| 刘六            | 100            | 4              |
| 田七            | 70             | 5              |
| 张三五           | 78             | 1              |
| 李四五           | 98             | 2              |
| 王五五           | 85             | 3              |
| 李璐            | 73             | 4              |
| 田七五           | 90             | 5              |
+---------------+----------------+----------------+--+
10 rows selected (0.194 seconds)
[root@hadoop l0415]# cat student.txt 
张三	100	1
李四	90	2
王五	80	3
刘六	100	4
田七	70	5
张三五	78	1
李四五	98	2
王五五	85	3
李璐	73	4
田七五	90	5

last_value() 函数

  • 作用是返回到当前行的最后一条数据
0: jdbc:hive2://192.168.17.151:10000> from student
. . . . . . . . . . . . . . . . . . > select name,score,class,
. . . . . . . . . . . . . . . . . . > last_value(score)
. . . . . . . . . . . . . . . . . . > over(partition by class order by score) lastvalue;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
+-------+--------+--------+------------+--+
| name  | score  | class  | lastvalue  |
+-------+--------+--------+------------+--+
| 张三五   | 78     | 1      | 78         |
| 张三    | 100    | 1      | 100        |
| 李四    | 90     | 2      | 90         |
| 李四五   | 98     | 2      | 98         |
| 王五    | 80     | 3      | 80         |
| 王五五   | 85     | 3      | 85         |
| 李璐    | 73     | 4      | 73         |
| 刘六    | 100    | 4      | 100        |
| 田七    | 70     | 5      | 70         |
| 田七五   | 90     | 5      | 90         |
+-------+--------+--------+------------+--+
10 rows selected (35.058 seconds)

lag() 函数

  • 用法是用于统计分组内的往上前n个值
  • 示例1 排名并显示每个同学和上一位同学的分差
0: jdbc:hive2://192.168.17.151:10000> from student
. . . . . . . . . . . . . . . . . . > select *,
. . . . . . . . . . . . . . . . . . > score-lag(score,1,0) over(order by score desc) as gap
. . . . . . . . . . . . . . . . . . > ;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
+---------------+----------------+----------------+------+--+
| student.name  | student.score  | student.class  | gap  |
+---------------+----------------+----------------+------+--+
| 刘六            | 100            | 4              | 100  |
| 张三            | 100            | 1              | 0    |
| 李四五           | 98             | 2              | -2   |
| 田七五           | 90             | 5              | -8   |
| 李四            | 90             | 2              | 0    |
| 王五五           | 85             | 3              | -5   |
| 王五            | 80             | 3              | -5   |
| 张三五           | 78             | 1              | -2   |
| 李璐            | 73             | 4              | -5   |
| 田七            | 70             | 5              | -3   |
+---------------+----------------+----------------+------+--+
10 rows selected (34.238 seconds)

cume_dist() 函数

  • 用法是如果按升序排列，则统计：小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows）。如果是降序排列，则统计：大于等于当前值的行数/总行数。
    示例1 统计小于等于当前分数的人数占比
0: jdbc:hive2://192.168.17.151:10000> from student
. . . . . . . . . . . . . . . . . . > select *,
. . . . . . . . . . . . . . . . . . > cume_dist() over(order by score) as cume_dist;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
+---------------+----------------+----------------+------------+--+
| student.name  | student.score  | student.class  | cume_dist  |
+---------------+----------------+----------------+------------+--+
| 田七            | 70             | 5              | 0.1        |
| 李璐            | 73             | 4              | 0.2        |
| 张三五           | 78             | 1              | 0.3        |
| 王五            | 80             | 3              | 0.4        |
| 王五五           | 85             | 3              | 0.5        |
| 田七五           | 90             | 5              | 0.7        |
| 李四            | 90             | 2              | 0.7        |
| 李四五           | 98             | 2              | 0.8        |
| 刘六            | 100            | 4              | 1.0        |
| 张三            | 100            | 1              | 1.0        |
+---------------+----------------+----------------+------------+--+
10 rows selected (33.34 seconds)
  • 示例2 统计每个班小于等于当前分数的人数占比
0: jdbc:hive2://192.168.17.151:10000> from student
. . . . . . . . . . . . . . . . . . > select *,
. . . . . . . . . . . . . . . . . . > cume_dist() over(partition by class order by score) as cume_dist;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
+---------------+----------------+----------------+------------+--+
| student.name  | student.score  | student.class  | cume_dist  |
+---------------+----------------+----------------+------------+--+
| 张三五           | 78             | 1              | 0.5        |
| 张三            | 100            | 1              | 1.0        |
| 李四            | 90             | 2              | 0.5        |
| 李四五           | 98             | 2              | 1.0        |
| 王五            | 80             | 3              | 0.5        |
| 王五五           | 85             | 3              | 1.0        |
| 李璐            | 73             | 4              | 0.5        |
| 刘六            | 100            | 4              | 1.0        |
| 田七            | 70             | 5              | 0.5        |
| 田七五           | 90             | 5              | 1.0        |
+---------------+----------------+----------------+------------+--+
10 rows selected (34.501 seconds)

lambda-小张
关注
  • 2
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
Hive 窗口函数
Qinsurpass的博客
06-01 224
Hive 窗口函数简介概念数据准备实例聚合函数+overpartition by子句order by子句window子句窗口函数中的序列函数NTILELAG和LEAD函数first_value和last_value扩展：总结： 简介 本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析（在线分析处理）。 概念 我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规.
hive开窗函数
qq_15031121的博客
02-26 183
Hive 开窗函数 展开 工作中用到了几个hive开窗函数,便想把hive开窗函数系统梳理一遍。 开窗函数 普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。因此,普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每行都返回一个值。 简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。 开窗函数一般分为两类,聚合开窗函数和排序开窗函数。 测试...
hive中开窗函数 ：percent_rank（）的含义
qq_46402333的博客
02-25 6173
这是hql语句 查询结果 percent_rank() 含义就是 当前行-1 / 当前组总行数-1 比如第一行： 1-1/14-1 = 0 ； 第二行：2-1/14-1=0.07692，，，，，，
HIVE函数汇总--开窗函数篇
小猿天地
01-04 1万+
详细介绍开窗函数的思路及其用法
hive的开窗函数篇
热门推荐
qq_40202995的博客
02-22 1万+
hive的开窗函数over（）
Hive学习——开窗函数精讲
Helen_1997_1997的博客
02-24 2342
Hive中开窗函数十分重要，很多业务需求都可以用开窗函数解决
【Hive】窗口函数（开窗函数部分）
康师傅没有眼泪
11-24 2971
我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数.目前，我用的多的就是 row_number() , sum(), 解决topN 问题，分区排序，累积计数。hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于OLAP分析（在线分析处理）。
HIVE开窗函数
weixin_46602525的博客
06-10 1906
ETL,SQL面试高频考点——HIVE开窗函数 目录标题ETL,SQL面试高频考点——HIVE开窗函数一，窗口函数介绍二，开窗函数三，开窗函数分类排序开窗函数：实列解析:后续更新中~ 一，窗口函数介绍 窗口函数，也叫OLAP函数（Online Anallytical Processing,联机分析处理），可以对数据库数据进行实时分析处理。 二，开窗函数 开创函数就是定义一个行为列，简单讲，就是你查询的结果上，多出一列值（可以是聚合值或者排序号，所以开窗函数可以分为两类：聚合开窗函数和排序开窗函数，特征就是
hive窗口函数.docx
07-08
Hive 窗口函数详解 Hive 窗口函数是 Hive 中的一种强大的分析函数，它可以对数据进行分类、排序、聚合和排名等操作。下面将详细介绍 Hive 窗口函数的语法结构、分类、应用场景和实践练习。 窗口函数语法结构 窗口...
Hive窗口函数，定义、demo
05-14
Hive窗口函数的定义，demo等
大数据hive中窗口函数的一些常用函数
10-19
大数据Hive中窗口函数的一些常用函数 窗口函数是Hive中一种强大的分析工具，可以对数据进行复杂的分析和计算。窗口函数可以像聚合函数一样对一组数据进行分析并返回结果，但不同之处在于，窗口函数不是将一组数据...
彻底理解冷门函数PERCENT_RANK()函数？如何利用其特性巧解实际问题？
石榴姐yyds
02-26 4208
一直以来percent_rank()函数作为分析函数中冷门函数，被好多人所不知，甚至成为知识盲区。包括很多官方文档，给出的定义并不明确，甚至理解起来也具有误导性，如下图所示，某官方产品给出的定义解释为百分比排名，咋一听就很晕乎，什么叫百分比排名？用在什么地方、场景并不清楚，该函数percentile()函数及cume_dist()函数有什么区分？相信很多同学还是一头雾水，本文就针对该函数的用法、使用场景及几个函数区分进行讲解
hive的开窗函数
Climber
11-22 3795
hive的开窗函数 开窗函数可用于组内数据分析排序。 开窗函数的语法 Function (arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>]) hive常用的开窗函数Function ： -- 聚合开窗函数 count(); -- 窗口内总条数 sum(); -- 窗口内数据的和 min(); -- 窗口内最小值 max(); -- 窗口内最
Hive学习之路 （十五）Hive分析窗口函数(三) CUME_DIST和PERCENT_RANK
mnasd的博客
07-29 1749
目录 数据准备 数据格式 创建表 玩一玩CUME_DIST 说明 查询语句 查询结果  结果说明 玩一玩PERCENT_RANK 说明 查询语句 查询结果 结果说明   正文  这两个序列分析函数不是很常用，这里也练习一下。 回到顶部 数据准备 数据格式 cookie3.txt d1,user1,1000 d1,user2,...
Hive SQl 学习笔记
yiyi的博客
12-03 470
Hive SQL 常用窗口函数 排序函数： row_number() rank() dense_rank() 需要和over()搭配使用 over() 内常用参数有 order by 排序 partition by 分组 1. 场景1：分组求TOP N 2.行/列转换
Hive学习笔记：05Hive中常用分析函数使用解析
11-23 1262
本文将介绍Hive SQL中常用的窗口分析函数的使用，这些函数的用法不仅仅适用于Hive，对于很多数数据库来说同样也适用，比如SParkSQL，FlinkSQL以及Mysql8，Oracle，MSSQL等传统的关系型数据库。
【Druid 未授权访问漏洞】解决办法
最新发布
苏叶新城
07-13 190
【代码】【Druid 未授权访问漏洞】解决办法。
hive 窗口函数
05-30
Hive窗口函数是一种在查询结果集的特定行上执行计算和分析的函数。它们通常与OVER子句一起使用，OVER子句指定了窗口的范围和排序方式。通过使用窗口函数，可以在不使用子查询或自连接的情况下，对结果集中的数据进行...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

lambda-小张

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值