Hive中的开窗操作over()

最新推荐文章于 2024-09-03 08:15:00 发布
最新推荐文章于 2024-09-03 08:15:00 发布
阅读量1k 收藏 2
点赞数
分类专栏： 大数据学习之路 笔记
小卧底
本文链接：https://blog.youkuaiyun.com/qq_37276835/article/details/112346374
版权

前提

over() 可以为 聚合函数，窗口函数和分析函数进行开窗操作；
开窗之后，每一行的数据都会对应一个数据窗口，这个数据窗口中的数据可能会随着行的变化而变化。

over(参数) 中可以指定的参数

over() 开窗开出来的数据是整个查询结果排除开窗操作，都执行完之后的数据。也可以说是开窗操作是在整个select查询结束后才执行的，因此开窗出来的数据也是在当前select的查询结果上进行划分的。

over中可以指定的参数(官方定义： WINDOW specification)：

(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
rows 限定的是行所在的范围，range限定的是某一列的值所在的范围

  • partition by col：按照col进行分区（类似于分组），每个分区拥有自己独立的数据窗口
  • order by col：窗口中的数据按照col进行排序；
  • current row ：当前行
  • n preceding：往前n行数据
  • n following：往后n行数据
  • unbounded：有两种边界值：unbounded preceding 表示所开窗出来的起始数据（最小值）；unbounded following 表示所开窗出来的最后一行数据（最大值）。

两个常用的窗口函数,配合over开窗来使用

  1. lag(col, n) 获取当前行往前第n行col列的数据，也可以指定默认值。
  2. lead(col, n) 获取当前行往后第n行col列的数据。

常用的分析函数

  1. RANK 排名，排序相同时会重复，总数不变：1,1,3,4
  2. ROW_NUMBER 根据顺序计算，相同元素的排名不会重复 ：1,2,3,4
  3. DENSE_RANK (稠密的排名) 相同元素的排名会重复，但是总数会减少：1,1,2,3
  4. NTILE(n), 为开窗出来的每行数据进行分组编号， n为指定的分组个数，返回每行数据的分组编号。当数据不能均匀分布时，默认从第一个组开始一次增加一个元素，不同组别之间的元素的个数最多相差一个元素

接下来我们通过例子，更加直观的学习上述所述的开窗相关的内容。

数据准备

name	orderdate	cost
------------------------
jack	2017-01-01	10
tony	2017-01-02	15
jack	2017-02-03	23
tony	2017-01-04	29
jack	2017-01-05	46
jack	2017-04-06	42
tony	2017-01-07	50
jack	2017-01-08	55
mart	2017-04-08	62
mart	2017-04-09	68
neil	2017-05-10	12
mart	2017-04-11	75
neil	2017-06-12	80
mart	2017-04-13	94

创建表并加载数据

create table business(
name string, 
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/opt/module/datas/business.txt" into table business;

查询表中的所有元素

business.name	business.orderdate	business.cost
jack	2017-01-01	10
tony	2017-01-02	15
jack	2017-02-03	23
tony	2017-01-04	29
jack	2017-01-05	46
jack	2017-04-06	42
tony	2017-01-07	50
jack	2017-01-08	55
mart	2017-04-08	62
mart	2017-04-09	68
neil	2017-05-10	12
mart	2017-04-11	75
neil	2017-06-12	80
mart	2017-04-13	94

查询在2017年4月份购买过的顾客及总人数（over() 简单的使用）

select
	name,
	count(*) over()
from business
where substring(orderdate, 1, 7)='2017-04'
group by name;

结果：
+----+------------------------------------------------------------------------+
|name|count(1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)|
+----+------------------------------------------------------------------------+
|mart|2                                                                       |
|jack|2                                                                       |
+----+------------------------------------------------------------------------+

上述代码我们通过spark on hive的方式来访问hive中的数据，加快执行效率；如果没有spark，可以之间在hive shell中运行hql语句

partition by col测试：查询顾客的购买明细及月购买总额

    val spark: SparkSession = SparkSession.builder()
      .appName("HiveRead").master("local[*]")
      .enableHiveSupport()
      .config("spark.sql.warehouse.dir", "hdfs://hadoop102:9000/user/hive/warehouse")
      .getOrCreate()
    spark.sql(
      """
        |select
        | name,
        | orderdate,
        | cost,
        | sum(cost) over(partition by month(orderdate))
        |from business
        |""".stripMargin
    ).show(false)

结果：
+----+----------+----+-------------------------------------------------------------------------------------------------------------------------------------+
|name|orderdate |cost|sum(CAST(cost AS BIGINT)) OVER (PARTITION BY month(CAST(orderdate AS DATE)) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)|
+----+----------+----+-------------------------------------------------------------------------------------------------------------------------------------+
|jack|2017-01-01|10  |205                                                                                                                                  |
|tony|2017-01-02|15  |205                                                                                                                                  |
|tony|2017-01-04|29  |205                                                                                                                                  |
|jack|2017-01-05|46  |205                                                                                                                                  |
|tony|2017-01-07|50  |205                                                                                                                                  |
|jack|2017-01-08|55  |205                                                                                                                                  |
|neil|2017-06-12|80  |80                                                                                                                                   |
|neil|2017-05-10|12  |12                                                                                                                                   |
|jack|2017-04-06|42  |341                                                                                                                                  |
|mart|2017-04-08|62  |341                                                                                                                                  |
|mart|2017-04-09|68  |341                                                                                                                                  |
|mart|2017-04-11|75  |341
最低0.47元/天 解锁文章
HIVE over() 超全讲解
csdn问鼎
06-16 2万+
over() over() 函数在Hive中运用广泛，通常搭配row_number() ,min(),max(),sum()来使用,总结下over函数划定窗口的范围 id name num 1 a 1 2 b 3 3 a 5 4 c 9 5 b 3 over() 此时每一行的窗口都是所有的行 SELECT id,name,num, sum(num) ov...
【Hive】 关于开窗函数over()的理解
Harbour_zhang的博客
02-28 1139
一、引言 我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数. 二、over() 开窗函数 over() 用于指定分析函数工作的数据窗口大小。 假设有如下数据： -- 字段名为name，odate，cost, 导入以下数据 jack,2015-01-01,10 jack,2015-01-01,10 ton
hive 开窗函数over()
qq_28603127的博客
07-10 1583
开窗函数over()是将聚合函数以列的形式展开,替代之前需要join的处理 数据: CREATE TABLE `test`( `id` int, `class` string, `name` string, `score` int); insert into test values (1,'a','zhang',90), (2,'a','li',80), (3,'a','zhao',95), (4,'a','qian',85), (5,'a','sun',60), (6,'a',
Hive 窗口函数（开窗函数） over
热门推荐
不是一枚开发
09-16 7万+
文章目录OVER函数1.含义：2.示例： OVER函数 1.含义： 窗口函数主要解决 分析整体数据时，多个数据和一个结果对应，为每一条数据开启指定行范围的窗口； over() 指定 分析函数 工作时的数据窗口大小，这个窗口大小可能会随行的改变而改变； 用于between … and … 表示范围概念： CURRENT ROW：当前行 n PRECEDING：往前n行数据 n FOLLOWING：往后n行数据 UNBOUNDED：起点， UNBOUNDED PRECEDING 表示从前面的起点， UNBOUN
OVER () 开窗函数
Cecilia_W422的博客
03-23 691
sqlserver的开窗函数分两大类,一类是聚合开窗函数,一类是排序开窗函数。 聚合类开窗函数聚合函数(列)over(选项)，选项可以是partition by子句,但不可以是order by 子句。 over关键字表示把聚合函数当成聚合开窗函数而不是聚合函数。SQL标准允许将所有聚合函数用做聚合开窗函数。举个例子,开窗函数count(*) over() 对于查询结果的每一行都返回所有符合条件的行...
开窗函数over()的妙用
最新发布
weixin_40104407的博客
09-03 1592
开窗函数是对一组值进行操作，不需要使用group by 语句来进行分组，能够在同一行中同时返回基础行的列，和聚合的列；使用关键字over(),必须和聚合函数或排序函数等一起使用，不能单独存在。
hive开窗函数详细介绍
03-28
本文将详细探讨Hive的开窗函数，包括ROW_NUMBER、RANK、DENSE_RANK以及分析窗口函数SUM、AVG、MIN和MAX的用法。 一、窗口函数 1. ROW_NUMBER() ROW_NUMBER()函数为每个分区内的记录分配一个唯一的连续编号，根据...
大数据-Hive开窗函数
MsSpark的博客
12-20 3531
Hive开窗函数结构： xx() over(partition by xx order by xx) Hive开窗函数，大致有以下两类： 1. 排序开窗函数 row_number()：组内排名后获取行号 rank()：组内排名（连续） dense_rank()：组内排名（跳跃） first_value() last_value() lag(col, n, default)：用于统计窗口内往上取第n个值 lead(col, n, default)：用于统计窗口内往下取第n个值 cume_dist(col)：计
hive开窗函数总结
weixin_46134848的博客
02-21 518
【阿里P7强推的 MySQL 数据库高级进阶版全集， MySQL 优化王者晋级之路！over关键字来指定函数执行的范围,包含三个分析子句:分组(partition by)子句,排序(order by)子句,窗口(rows)子句。在语言模型中，编码器和解码器都是由一个个的 Transformer 组件拼接在一起形成的。
Hive窗口函数 over()
JasonChen3318的博客
07-29 817
有以下数据： Jack,2017-01-01,10 Tony,2017-01-02,15 Jack,2017-02-03,23 Tony,2017-01-04,29 Jack,2017-01-05,46 Jack,2017-04-06,42 Tony,2017-01-07,50 Jack,2017-01-08,55 Mark,2017-04-08,62 Mart,2017-04-09,68 M...
hive窗口函数over()
TU_JCN的博客
07-23 3110
over()，指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
over开窗函数
qq_28656707的博客
10-25 163
优秀的示例：本贴结合写下自己的理解 https://www.cnblogs.com/lanzi/archive/2010/10/26/1861338.html
hive over窗口函数的使用
Mrerlou的博客
08-31 2243
前言：我们在学习hive窗口函数的时候，一定要先了解窗口函数的结构。而不是直接百度sum() over()、row_number() over()、或者count() over()的用法，如果这样做，永远也掌握不到窗口函数的核心，当然我刚开始的时候也是这样做的。 还好我比较顽强，在HIVE窗口函数问题上折腾了半个月、看了很多文章后才知道over()才是窗口函数，而sum、row_numb...
Hive窗口函数总结
孙张宾博客
01-03 706
基础的：SUM,AVG,MIN,MAX NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值 ROW_NUMBER() –从1开始，按照顺序，生成分组内记录的序列 RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位 DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位 CUME_DIST 小于等于当前值的行数/分组内总行数 PE
over(Partition by…) 一个超级好用的特有（开窗）函数。
人生有酒多忘欢
01-06 7998
over(Partition by…) 一个超级牛皮的特有函数。分析函数over 及开窗函数一：分析函数over Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是 对于每个组返回多行，而聚合函数对于每个组只返回一行。 下面通过几个例子来说明其应用。 1：统计某商店的营业额。 date sale 1
Hive中over()函数详解
感谢你的关注~~~~ღ( ´･ᴗ･` )
02-02 863
over() over() 函数在Hive中运用广泛，通常搭配row_number() ,min(),max(),sum()来使用,总结下over函数划定窗口的范围 id name num 1 a 1 2 b 3 3 a 5 4 c 9 5 b 3 over() 此时每一行的窗口都是所有的行 SELECT id,name,num, sum(num) over() sum1 from aa ov.
Hive——over（窗口函数）详解
d_l_w_d_l_w的博客
10-08 1927
over() over() 函数在Hive中运用广泛，通常搭配row_number() ,min(),max(),sum()来使用,总结下over函数划定窗口的范围 over() 因为没设置窗口范围，此时每一行的窗口都是所有的行。 SELECT id, name, num, sum(num) over() sum1 from aa 也就是每一行后面的sum1是对所有行的num进行sum(num)操作，如果设置好窗口范围，每一行后面的结果是对以当前行为中心按照窗口范围内的行进行sum(
HIVE中的OVER函数解析
zhao_guo_feng的博客
07-10 968
over函数解析
Hive中over详解
m0_57320261的博客
10-24 1705
over() over() 函数在Hive中运用广泛，通常搭配row_number() ,min(),max(),sum()来使用,总结下over函数划定窗口的范围 over() 此时每一行的窗口都是所有的行 SELECT id,name,num, sum(num) over() sum1 from aa over(order by id) id=1的窗口只有一行，id=2的窗口包括id=1,id=2 SELECT id,name,num, sum(num) over(orde...
hive的开窗函数介绍
09-09
Hive中的开窗函数是一种可以在窗口中对行集进行聚合的函数。与普通的聚合函数不同，普通的聚合函数聚合的行集是组，而开窗函数聚合的行集是窗口。因此，开窗函数可以为窗口中的每一行返回一个值。开窗函数的语法结构通常为 xx() over(partition by xx order by xx ROWS/RANGE between xx and xx)。 使用开窗函数可以对窗口内的数据进行排序、分组和聚合等操作。通过使用PARTITION BY子句可以将数据按照指定的列进行分组，而ORDER BY子句用于指定按照哪个字段排序。在开窗函数中，ROWS和RANGE关键字用于定义窗口的范围，可以指定窗口内的行数或者某个值的范围。 总的来说，Hive的开窗函数提供了一种灵活的方式来处理窗口内的数据，可以方便地进行排序、分组和聚合等操作。通过合理使用开窗函数，可以更好地满足不同的数据分析需求。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值