Hive(12):Hive分析函数-窗口函数(Windowing)

2024-05-24 11:32

本文主要是介绍Hive(12):Hive分析函数-窗口函数(Windowing),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

一、实现功能

对于分组之后的数据进行处理。

官网:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics

二、加载数据

1.emp.txt和dept.txt数据

(1)emp.txt

7369    SMITH   CLERK   7902    1980-12-17      800.00          20
7499    ALLEN   SALESMAN        7698    1981-2-20       1600.00 300.00  30
7521    WARD    SALESMAN        7698    1981-2-22       1250.00 500.00  30
7566    JONES   MANAGER 7839    1981-4-2        2975.00         20
7654    MARTIN  SALESMAN        7698    1981-9-28       1250.00 1400.00 30
7698    BLAKE   MANAGER 7839    1981-5-1        2850.00         30
7782    CLARK   MANAGER 7839    1981-6-9        2450.00         10
7788    SCOTT   ANALYST 7566    1987-4-19       3000.00         20
7839    KING    PRESIDENT               1981-11-17      5000.00         10
7844    TURNER  SALESMAN        7698    1981-9-8        1500.00 0.00    30
7876    ADAMS   CLERK   7788    1987-5-23       1100.00         20
7900    JAMES   CLERK   7698    1981-12-3       950.00          30
7902    FORD    ANALYST 7566    1981-12-3       3000.00         20
7934    MILLER  CLERK   7782    1982-1-23       1300.00         10

(2)dept.txt

10      ACCOUNTING      NEW YORK
20      RESEARCH        DALLAS
30      SALES   CHICAGO
40      OPERATIONS      BOSTON

2.创建分析表并且加载数据

(1)emp人员表

create EXTERNAL table emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
row format delimited fields terminated by '\t' ;
load data local inpath '/opt/datas/emp.txt' into table emp;

(2)dept部门表

create EXTERNAL table dept(
deptno int,
dname string,
loc string
)
row format delimited fields terminated by '\t' ;
load data local inpath '/opt/datas/dept.txt' into table dept;

 

三、分析实例

1.测试表

hive (hadoop)> select * from emp;
OK
emp.empno       emp.ename       emp.job emp.mgr emp.hiredate    emp.sal emp.comm        emp.deptno
7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    20
7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30
7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30
7566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    20
7654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  30
7698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    30
7782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    10
7788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    20
7839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    10
7844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     30
7876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    20
7900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    30
7902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    20
7934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10
Time taken: 0.247 seconds, Fetched: 14 row(s)

2.实例

(1)查询部门编号10的所有员工的信息,按照薪资进行降序排列

select * from emp where deptno='10' order by sal desc;
结果:
emp.empno       emp.ename       emp.job emp.mgr emp.hiredate    emp.sal emp.comm        emp.deptno
7839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    10
7782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    10
7934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10

(2)窗口:查询所有部门的员工的信息,按照薪资进行降序排列,多加一个字段:显示该部门的最高薪资, 或者显示该部门的最低薪资。备注:求最大值,要desc;求最小值,要asc。否则会出错!

  求最大值

hive (hadoop)> select empno,ename,deptno,sal,max(sal) over (partition by deptno order by sal desc) as max_sal from emp;
结果:
empno   ename   deptno  sal     max_sal	
7839    KING    10      5000.0  5000.0	
7782    CLARK   10      2450.0  5000.0	
7934    MILLER  10      1300.0  5000.0	7788    SCOTT   20      3000.0  3000.0	
7902    FORD    20      3000.0  3000.0	
7566    JONES   20      2975.0  3000.0	
7876    ADAMS   20      1100.0  3000.0	
7369    SMITH   20      800.0   3000.0	7698    BLAKE   30      2850.0  2850.0	
7499    ALLEN   30      1600.0  2850.0
7844    TURNER  30      1500.0  2850.0
7654    MARTIN  30      1250.0  2850.0
7521    WARD    30      1250.0  2850.0
7900    JAMES   30      950.0   2850.0

  求最小值

hive (hadoop)> select empno,ename,deptno,sal,min(sal) over (partition by deptno order by sal asc) as min_sal from emp;
结果:
empno   ename   deptno  sal     min_sal
7934    MILLER  10      1300.0  1300.0
7782    CLARK   10      2450.0  1300.0
7839    KING    10      5000.0  1300.0
7369    SMITH   20      800.0   800.0
7876    ADAMS   20      1100.0  800.0
7566    JONES   20      2975.0  800.0
7788    SCOTT   20      3000.0  800.0
7902    FORD    20      3000.0  800.0
7900    JAMES   30      950.0   950.0
7654    MARTIN  30      1250.0  950.0
7521    WARD    30      1250.0  950.0
7844    TURNER  30      1500.0  950.0
7499    ALLEN   30      1600.0  950.0
7698    BLAKE   30      2850.0  950.0

(3)分析函数row_number:查询所有部门的员工的信息,按照薪资进行降序排列,最后一列显示编号

hive (hadoop)> select empno,ename,deptno,sal,row_number() over (partition by deptno order by sal desc) as rn from emp;
结果:
empno   ename   deptno  sal     rn
7839    KING    10      5000.0  1
7782    CLARK   10      2450.0  2
7934    MILLER  10      1300.0  37788    SCOTT   20      3000.0  1
7902    FORD    20      3000.0  2
7566    JONES   20      2975.0  3
7876    ADAMS   20      1100.0  4
7369    SMITH   20      800.0   57698    BLAKE   30      2850.0  1
7499    ALLEN   30      1600.0  2
7844    TURNER  30      1500.0  3
7654    MARTIN  30      1250.0  4
7521    WARD    30      1250.0  5
7900    JAMES   30      950.0   6

(4)去重薪水一样的(总共有14个人,有4个人两两薪水是一样的,hql结果应该是12人)

hive (hadoop)> select deptno,count(DISTINCT sal) over (partition by deptno ) as countNum from emp group by deptno;
结果:
deptno  countnum
10      3
20      4
30      5

(5)统计每个部门的人数

hive (hadoop)> select deptno,count(*) as count from emp group by deptno;
结果:
deptno  count
10      3
20      5
30      6

或者使用窗口函数

hive (hadoop)> select deptno,count(empno) over (partition by deptno) as count from emp group by deptno,empno;
结果:
deptno  count
10      3
10      3
10      3
20      5
20      5
20      5
20      5
20      5
30      6
30      6
30      6
30      6
30      6
30      6

(6)windows范围

PRECEDING:往前
FOLLOWING:往后
CURRENT ROW:当前行
UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING:表示到后面的终点

//1.分组内所有行排序
hive (hadoop)>  select empno,ename,deptno,sal,row_number() over (partition by deptno order by sal desc) as rn from emp;//2.分组内当前行+往前1行:加和
hive (hadoop)>  select empno,ename,deptno,sal,row_number() over (partition by deptno order by sal desc) as rn,
sum(sal) over (partition by deptno order by sal desc ) s1,
sum(sal) over (partition by deptno order by sal desc rows between 1 PRECEDING and CURRENT ROW) s2
from emp;结果:s2是本行和上一行的加和
empno   ename   deptno  sal     rn      s1      s2
7839    KING    10      5000.0  1       5000.0  5000.0
7782    CLARK   10      2450.0  2       7450.0  7450.0
7934    MILLER  10      1300.0  3       8750.0  3750.0
7788    SCOTT   20      3000.0  1       6000.0  3000.0
7902    FORD    20      3000.0  2       6000.0  6000.0
7566    JONES   20      2975.0  3       8975.0  5975.0
7876    ADAMS   20      1100.0  4       10075.0 4075.0
7369    SMITH   20      800.0   5       10875.0 1900.0
7698    BLAKE   30      2850.0  1       2850.0  2850.0
7499    ALLEN   30      1600.0  2       4450.0  4450.0
7844    TURNER  30      1500.0  3       5950.0  3100.0
7654    MARTIN  30      1250.0  4       8450.0  2750.0
7521    WARD    30      1250.0  5       8450.0  2500.0
7900    JAMES   30      950.0   6       9400.0  2200.0
Time taken: 23.458 seconds, Fetched: 14 row(s)//3.分组内当前行+往后所有行:加和
hive (hadoop)>  select empno,ename,deptno,sal,row_number() over (partition by deptno order by sal desc) as rn,
sum(sal) over (partition by deptno order by sal desc ) s1,
sum(sal) over (partition by deptno order by sal desc rows between CURRENT ROW and unbounded following ) s2
from emp;
结果:s2即为本行到最后一行累加结果(当然,是在窗口内)empno   ename   deptno  sal     rn      s1      s2
7839    KING    10      5000.0  1       5000.0  8750.0
7782    CLARK   10      2450.0  2       7450.0  3750.0
7934    MILLER  10      1300.0  3       8750.0  1300.0
7788    SCOTT   20      3000.0  1       6000.0  10875.0
7902    FORD    20      3000.0  2       6000.0  7875.0
7566    JONES   20      2975.0  3       8975.0  4875.0
7876    ADAMS   20      1100.0  4       10075.0 1900.0
7369    SMITH   20      800.0   5       10875.0 800.0
7698    BLAKE   30      2850.0  1       2850.0  9400.0
7499    ALLEN   30      1600.0  2       4450.0  6550.0
7844    TURNER  30      1500.0  3       5950.0  4950.0
7654    MARTIN  30      1250.0  4       8450.0  3450.0
7521    WARD    30      1250.0  5       8450.0  2200.0
7900    JAMES   30      950.0   6       9400.0  950.0
Time taken: 22.953 seconds, Fetched: 14 row(s)

3.总结

窗口函数核心特征:不减少原始数据个数,只是开辟一个新的维度去观察当前数据。

四、参考

1.hive窗口函数必备宝典

https://blog.csdn.net/dingchangxiu11/article/details/83145151

这篇关于Hive(12):Hive分析函数-窗口函数(Windowing)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


原文地址:
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.chinasem.cn/article/998288

相关文章

Olingo分析和实践之EDM 辅助序列化器详解(最佳实践)

《Olingo分析和实践之EDM辅助序列化器详解(最佳实践)》EDM辅助序列化器是ApacheOlingoOData框架中无需完整EDM模型的智能序列化工具,通过运行时类型推断实现灵活数据转换,适用... 目录概念与定义什么是 EDM 辅助序列化器?核心概念设计目标核心特点1. EDM 信息可选2. 智能类

Olingo分析和实践之OData框架核心组件初始化(关键步骤)

《Olingo分析和实践之OData框架核心组件初始化(关键步骤)》ODataSpringBootService通过初始化OData实例和服务元数据,构建框架核心能力与数据模型结构,实现序列化、URI... 目录概述第一步:OData实例创建1.1 OData.newInstance() 详细分析1.1.1

Olingo分析和实践之ODataImpl详细分析(重要方法详解)

《Olingo分析和实践之ODataImpl详细分析(重要方法详解)》ODataImpl.java是ApacheOlingoOData框架的核心工厂类,负责创建序列化器、反序列化器和处理器等组件,... 目录概述主要职责类结构与继承关系核心功能分析1. 序列化器管理2. 反序列化器管理3. 处理器管理重要方

postgresql使用UUID函数的方法

《postgresql使用UUID函数的方法》本文给大家介绍postgresql使用UUID函数的方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录PostgreSQL有两种生成uuid的方法。可以先通过sql查看是否已安装扩展函数,和可以安装的扩展函数

MySQL字符串常用函数详解

《MySQL字符串常用函数详解》本文给大家介绍MySQL字符串常用函数,本文结合实例代码给大家介绍的非常详细,对大家学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql字符串常用函数一、获取二、大小写转换三、拼接四、截取五、比较、反转、替换六、去空白、填充MySQL字符串常用函数一、

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种

解决1093 - You can‘t specify target table报错问题及原因分析

《解决1093-Youcan‘tspecifytargettable报错问题及原因分析》MySQL1093错误因UPDATE/DELETE语句的FROM子句直接引用目标表或嵌套子查询导致,... 目录报js错原因分析具体原因解决办法方法一:使用临时表方法二:使用JOIN方法三:使用EXISTS示例总结报错原

C++中assign函数的使用

《C++中assign函数的使用》在C++标准模板库中,std::list等容器都提供了assign成员函数,它比操作符更灵活,支持多种初始化方式,下面就来介绍一下assign的用法,具有一定的参考价... 目录​1.assign的基本功能​​语法​2. 具体用法示例​​​(1) 填充n个相同值​​(2)

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入(单行/多行及冲突处理),SELECT实现数据检索(列选择、条件过滤、排序分页),UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更

PostgreSQL中rank()窗口函数实用指南与示例

《PostgreSQL中rank()窗口函数实用指南与示例》在数据分析和数据库管理中,经常需要对数据进行排名操作,PostgreSQL提供了强大的窗口函数rank(),可以方便地对结果集中的行进行排名... 目录一、rank()函数简介二、基础示例:部门内员工薪资排名示例数据排名查询三、高级应用示例1. 每