Hive函数—（窗口函数概念，over关键字，order by子句，Window子句）

已于 2022-12-15 16:53:38 修改
阅读量265 收藏
点赞数
文章标签： java hive 数据仓库
于 2021-07-10 13:24:27 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43408367/article/details/118634120
版权

文章目录

Hive函数—窗口函数概念

  1. 窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数，很多场景都需要用到。
  2. 窗口函数用于计算基于组的某种聚合值，窗口函数对于每个组返回多行，而聚合函数对于每个组只返回一行。
  3. 窗口函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

Hive窗口函数—over关键字

  1. 使用窗口函数之前一般要要通过over()进行开窗

  2. 查询emp表中的工资总和：select sum(sal) from emp;，但是如果在查询工资总和的基础上再加入别的字段，会报错：

    hive (default)> select ename, sal, sum(sal) salsum from emp;
FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'ename'

  3. 为了解决上述问题，可以使用窗口函数，并且查询员工姓名、薪水、薪水总和

    hive (default)> select ename, sal, sum(sal) over() salsum,
              > concat(round(sal / sum(sal) over()*100, 1) || '%') ratiosal
              > from emp;
Query ID = root_20221215155652_86f66456-32c4-4c1f-bd79-2b113c454ca2
Total jobs = 1
Launching Job 1 out of 1
Status: Running (Executing on YARN cluster with App id application_1671090331623_0003)

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED      1          1        0        0       0       0  
Reducer 2 ...... container     SUCCEEDED      1          1        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 39.40 s    
----------------------------------------------------------------------------------------------
OK
ename	sal	salsum	ratiosal
SMITH	800	29025	2.8%
ALLEN	1600	29025	5.5%
WARD	1250	29025	4.3%
JONES	2975	29025	10.2%
MARTIN	1250	29025	4.3%
BLAKE	2850	29025	9.8%
CLARK	2450	29025	8.4%
SCOTT	3000	29025	10.3%
KING	5000	29025	17.2%
TURNER	1500	29025	5.2%
ADAMS	1100	29025	3.8%
JAMES	950	29025	3.3%
FORD	3000	29025	10.3%
MILLER	1300	29025	4.5%
NULL	NULL	29025	NULL
Time taken: 46.755 seconds, Fetched: 15 row(s)

  4. 窗口函数是针对每一行数据的；如果over中没有参数，默认的是全部结果集；

Hive函数—order by子句

  1. order by 子句对输入的数据进行排序：增加了order by子句；sum：从分组的第一行到当前行求和

    hive (default)> select ename, sal,deptno, sum(sal) over(partition by deptno order by sal) salsum,
              > concat(round(sal / sum(sal) over() * 100,1) || '%')
              > from emp;
Query ID = root_20221215160217_3d50326a-f52f-468f-b949-cee89841f880
Total jobs = 1
Launching Job 1 out of 1
Status: Running (Executing on YARN cluster with App id application_1671090331623_0003)

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED      1          1        0        0       0       0  
Reducer 2 ...... container     SUCCEEDED      1          1        0        0       0       0  
Reducer 3 ...... container     SUCCEEDED      1          1        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 03/03  [==========================>>] 100%  ELAPSED TIME: 6.79 s     
----------------------------------------------------------------------------------------------
OK
ename	sal	deptno	salsum	_c4
NULL	NULL	NULL	NULL	NULL
MILLER	1300	10	1300	4.5%
CLARK	2450	10	3750	8.4%
KING	5000	10	8750	17.2%
SMITH	800	20	800	2.8%
ADAMS	1100	20	1900	3.8%
JONES	2975	20	4875	10.2%
FORD	3000	20	10875	10.3%
SCOTT	3000	20	10875	10.3%
JAMES	950	30	950	3.3%
MARTIN	1250	30	3450	4.3%
WARD	1250	30	3450	4.3%
TURNER	1500	30	4950	5.2%
ALLEN	1600	30	6550	5.5%
BLAKE	2850	30	9400	9.8%
Time taken: 7.563 seconds, Fetched: 15 row(s)

Hive函数—Window子句

  1. 语法格式：

    rows between ... and ...

  2. 如果要对窗口的结果做更细粒度的划分，使用window子句，有如下的几个选项：

    1. unbounded preceding。组内第一行数据
2. n preceding。组内当前行的前n行数据
3. current row。当前行数据
4. n following。组内当前行的后n行数据
5. unbounded following。组内最后一行数据

在这里插入图片描述

  1. 传统方式：按照deptno为分区字段，并以sal为排序字段，查询员工姓名，部门薪水总和：
hive (default)> select ename,sal,deptno,sum(sal) over(partition by deptno order by sal) from emp;
Query ID = root_20221215162616_ca9f4d1f-ece3-42d1-95fc-efcee6757be9
Total jobs = 1
Launching Job 1 out of 1
Status: Running (Executing on YARN cluster with App id application_1671090331623_0004)

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED      1          1        0        0       0       0  
Reducer 2 ...... container     SUCCEEDED      1          1        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 15.61 s    
----------------------------------------------------------------------------------------------
OK
ename	sal	deptno	sum_window_0
NULL	NULL	NULL	NULL
MILLER	1300	10	1300
CLARK	2450	10	3750
KING	5000	10	8750
SMITH	800	20	800
ADAMS	1100	20	1900
JONES	2975	20	4875
FORD	3000	20	10875
SCOTT	3000	20	10875
JAMES	950	30	950
MARTIN	1250	30	3450
WARD	1250	30	3450
TURNER	1500	30	4950
ALLEN	1600	30	6550
BLAKE	2850	30	9400
Time taken: 16.3 seconds, Fetched: 15 row(s)

  1. 使用 Window子句 子句方式实现按照deptno为分区字段，并以ename 为排序字段，查询员工姓名，部门薪水总和：（注意：sum_window_0，sum_window_0是第一行到当前行的 sal的和）

    hive (default)> select ename, sal, deptno,sum(sal)
              > over(partition by deptno order by ename rows between unbounded preceding and current row)
              > from emp;
Query ID = root_20221215163355_b70b527f-9f1f-470c-9d1a-84debaf8bef2
Total jobs = 1
Launching Job 1 out of 1
Tez session was closed. Reopening...
Session re-established.
Status: Running (Executing on YARN cluster with App id application_1671090331623_0005)

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED      1          1        0        0       0       0  
Reducer 2 ...... container     SUCCEEDED      1          1        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 5.58 s     
----------------------------------------------------------------------------------------------
OK
ename	sal	deptno	sum_window_0
NULL	NULL	NULL	NULL
CLARK	2450	10	2450
KING	5000	10	7450
MILLER	1300	10	8750
ADAMS	1100	20	1100
FORD	3000	20	4100
JONES	2975	20	7075
SCOTT	3000	20	10075
SMITH	800	20	10875
ALLEN	1600	30	1600
BLAKE	2850	30	4450
JAMES	950	30	5400
MARTIN	1250	30	6650
TURNER	1500	30	8150
WARD	1250	30	9400
Time taken: 13.829 seconds, Fetched: 15 row(s)

  2. 使用window求组内的，第一行到最后一行的和 ，注意看sum_window_0字段

    hive (default)> select ename, sal, deptno,
              > sum(sal) over(partition by deptno order by ename
              > rows between unbounded preceding and
              > unbounded following
              > ) from emp;
Query ID = root_20221215164027_27c5f3bd-e0b9-4755-8e80-f3266d8e2e0a
Total jobs = 1
Launching Job 1 out of 1
Tez session was closed. Reopening...
Session re-established.
Status: Running (Executing on YARN cluster with App id application_1671090331623_0006)

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED      1          1        0        0       0       0  
Reducer 2 ...... container     SUCCEEDED      1          1        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 5.15 s     
----------------------------------------------------------------------------------------------
OK
ename	sal	deptno	sum_window_0
NULL	NULL	NULL	NULL
CLARK	2450	10	8750
KING	5000	10	8750
MILLER	1300	10	8750
ADAMS	1100	20	10875
FORD	3000	20	10875
JONES	2975	20	10875
SCOTT	3000	20	10875
SMITH	800	20	10875
ALLEN	1600	30	9400
BLAKE	2850	30	9400
JAMES	950	30	9400
MARTIN	1250	30	9400
TURNER	1500	30	9400
WARD	1250	30	9400
Time taken: 10.499 seconds, Fetched: 15 row(s)

  3. 求组内：前一行，当前行，后一行的和 注意看：sum_window_0字段

    hive (default)> select ename, sal, deptno,
              > sum(sal) over(partition by deptno order by ename
              > rows between 1 preceding and 1 following)
              > from emp;
Query ID = root_20221215164818_0c08d9de-634f-4e75-a65d-441c29e429c2
Total jobs = 1
Launching Job 1 out of 1
Tez session was closed. Reopening...
Session re-established.
Status: Running (Executing on YARN cluster with App id application_1671090331623_0007)

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED      1          1        0        0       0       0  
Reducer 2 ...... container     SUCCEEDED      1          1        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 6.72 s     
----------------------------------------------------------------------------------------------
OK
ename	sal	deptno	sum_window_0
NULL	NULL	NULL	NULL
CLARK	2450	10	7450
KING	5000	10	8750
MILLER	1300	10	6300
ADAMS	1100	20	4100
FORD	3000	20	7075
JONES	2975	20	8975
SCOTT	3000	20	6775
SMITH	800	20	3800
ALLEN	1600	30	4450
BLAKE	2850	30	5400
JAMES	950	30	5050
MARTIN	1250	30	3700
TURNER	1500	30	4000
WARD	1250	30	2750
Time taken: 13.409 seconds, Fetched: 15 row(s)
想做CTO的任同学...
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive：带你理解over()子句及案例应用
乔治在哪里
09-30 1738
目录 1.什么是over()子句？ 2.over()子句的开窗范围 2.1 window clause 2.2 默认 2.3 order by 2.4 partition by 2.5 partition by + order by 3.案例 3.1 数据准备 3.2 默认情况：over() 3.3 partition by 3.4 order by 3.5 partit...
hive踩坑笔记 —— 开窗函数为聚合函数时，加order by 与不加 order by的区别
一纸春秋的博客
11-28 6045
over()开窗函数前分排序函数和聚合函数两种 当为排序函数，如row_number(),rank()等时，over中的order by只起到窗口内排序作用。 当为聚合函数，如max，min，count等时，over中的order by不仅起到窗口内排序，还起到窗口内从当前行到之前所有行的聚合（多了一个范围）。 以 sum 举例，下面的结果中，左图为不加 order by的结果，右图为加上order by的结果 发现不加order by score的话，就是针对一整个分区进行sum求和。加上or.
Hive（一）：row_number over(partition by,order by)用法
u011250186的博客
03-10 5430
Hive（一）：row_number over(partition by,order by)用法
hive over()窗口函数 order by 窗口作用范围
qq_39269455的博客
12-05 1521
之前理解over() 单独使用order by窗口的作用范围是从上无边界到当前行 其实理解的不够深刻，如果order by后面所跟字段在表数据中有重复值 则order by的作用范围应该是从上无边界到当前id（例如id为2，窗口覆盖到所有id为2的表数据） 例如上图 id为2时到第二行取聚合值时，窗口覆盖范围其实是1到3行，所以聚合值为9。 按照以前的理解，当到第2行时，即上无边界到当前行，窗口函数聚合值为6，这是不对的。 ...
hive窗口函数总结
不积跬步无以至千里
08-25 925
窗口函数用于对 -Query出来的数据- 进行 -分窗口- 的 -动作-. 这句话有3个重点: 1.Query出来的数据 2. 分窗口 3. 动作 我们来看下 标准sql select *,action(col) over([partition by col] [order by col] [row betweenUNBOUNDED PRECEDING andCURRENT...
Hive学习笔记：04 SQL的窗口函数、OVER的使用
11-18 1281
本文将介绍Hive SQL中窗口函数、分析函数以及Over的使用，其实不仅仅是Hive，对于很多数数据库来说同样也适用，比如Mysql8，Oracle，MSSQL等传统的关系型数据库。
hive窗口函数.docx
07-08
select *, count() over(partition by userid order by logday rows between unbounded preceding and current row) as score_days from test_window; ``` 等等。 Hive 窗口函数是一种非常有用的分析函数，可以帮助...
Hive 窗口函数
01-07
over()：指定分析函数工作的数据窗口的大小，这个数据窗口大小会随着行的变而变化。over跟在聚合函数后面，只对聚合函数生效。 current row：当前行 n preceding：往前n行数据 n following：往后n行数据 unbounded：...
hive窗口函数
01-09
hive窗口系列函数
快速学习-Hive函数
01-20
本篇文章将详细讲解Hive的内置函数和自定义函数，帮助你快速掌握如何利用Hive进行高效的数据处理。 **7.1 系统内置函数** Hive 内置了大量的函数，覆盖了常见的数据处理需求。你可以通过以下命令来查看、了解这些...
大数据hive中窗口函数的一些常用函数
最新发布
10-19
窗口函数的基础语法为：<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名> <窗口范围>)。其中，partition by子句用于指定分组的列名，order by子句用于指定排序的列名，窗口范围指定了窗口...
Hive——over（窗口函数）详解
d_l_w_d_l_w的博客
10-08 1733
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】（九）Hive 窗口函数总结
云祁QI
12-16 1917
文章目录一、简介二、概念三、数据准备四、聚合函数+over()五、partition by 子句六、order by 子句七、window 子句八、窗口函数中的序列函数NTILErow_numberrankdense_rankLAG和LEAD函数 一、简介 本文主要介绍Hive中的窗口函数，Hive中的窗口函数和SQL中的窗口函数相类似,都是用来做一些数据分析类的工作，一般用于olap分析（在线分...
hive sql——窗口函数使用实战小结
qq_41018861的博客
01-12 889
hive推出的窗口函数功能是对hive sql的功能增强，确实目前用于离线数据分析逻辑日趋复杂，很多场景都需要用到。用于实现分组内所有和连续累积的统计。 一、什么是窗口函数 1、窗口函数指定了函数工作的数据窗口大小(当前行的上下多少行)，这个数据窗口大小可能会随着行的变化而变化。 2、窗口函数对于每个组返回多行，组内每一行对应返回一行值。 二、窗口函数介绍 (一)、聚合函数： 1.sum(col) over() : 分组对col累计求和，over() 中的语法如下 2.count(col) ov.
hive-OVER(PARTITION BY order by)函数 的使用
小哇
10-22 5482
准备测试数据 dss,1,95 fda,1,80 ffd,1,95 cfe,2,74 gds,2,92 3dd,3,78 adf,3,45 asdf,3,55 ddd,3,99 gf,3,99 use test; create external table T2_TEMP (name string, class string, sroce int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n
Hive sql 中 over 总结
leveretz的博客
06-20 1892
原文地址：https://blog.csdn.net/qq_28289405/article/details/81326767
HIVE-窗口字句
thatdanielyang的博客
11-27 526
Hive - 窗口函数 Hive的窗口函数用法基本和SQL一致，不过为了加强印象，还是在再归纳一下hive的窗口函数 1. 窗口函数语法结构 Group by 普通聚合函数每组只有一条记录，简单来说返回的行数以group by后面的分类为准，一般搭配的聚合函数sum，avg，max，min等； 而窗口函数则可以为窗口中的每行都返回一个值。 普通聚合函数聚合的行集是组，开窗函数聚合的行集是窗口。 窗口函数语法结构： 聚合函数+ over (partition by [] order by [] asc/de
Hive开窗函数中加Order by 和不加Order by的区别，over partition by与group by的区别
weixin_47980649的博客
08-27 3183
使用环境为hive，over()开窗函数前分排序函数和聚合函数两种。 当为排序函数，如row_number(),rank()等时，over中的order by只起到窗口内排序作用。 当为聚合函数，如max，min，count等时，over中的order by不仅起到窗口内排序，还起到窗口内从当前行到之前所有行的聚合（多了一个范围）。 如：select id, dept, salary, min(salary) over(partition by dept) min_sal from dept; – ①
Hive中over详解
m0_57320261的博客
10-24 1633
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窗口函数over
09-13
Hive中的窗口函数（Window Functions）可以通过使用OVER子句来实现。它们允许在查询结果集中进行分组、排序和聚合操作，并计算每个行的窗口内结果。 例如，我们可以使用窗口函数计算每个部门的平均工资，并在结果集中显示每个员工的平均工资。 下面是一个使用窗口函数计算平均工资的示例查询： ``` SELECT employee_id, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS avg_salary FROM employees; ``` 在上述查询中，我们使用了AVG函数作为窗口函数，并通过PARTITION BY子句将数据按部门进行分组。然后，通过OVER子句将窗口函数应用于每个分组，计算每个部门的平均工资。 请注意，Hive中的窗口函数支持多种聚合函数，如SUM、COUNT、MIN、MAX等。你可以根据具体需求选择适合的聚合函数，并使用相应的窗口函数语法来实现所需的计算。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值