- 浏览: 785071 次
- 性别:
- 来自: 广州
文章分类
最新评论
窗口函数应用场景:
(1)用于分区排序
(2)动态Group By
(3)Top N
(4)累计计算
(5)层次查询
一、分析函数
用于等级、百分点、n分片等。
函数 说明
RANK() 返回数据项在分组中的排名,排名相等会在名次中留下空位
DENSE_RANK() 返回数据项在分组中的排名,排名相等会在名次中不会留下空位
NTILE() 返回n分片后的值
ROW_NUMBER() 为每条记录返回一个数字
Rank、DENSE_RANK
RANK()在出现等级相同的元素时预留为空,DENSE_RANK()不会。
Eg:某产品类型有两个并列第一
RANK():第一二为1,第三位3
DENSE_RANK():第一二为1,第三位2
Sql代码 收藏代码
SELECT
column_name,
RANK() OVER (ORDER BY column_name DESC) AS rank,
DENSE_RANK() OVER (ORDER BY SUM(column_name) DESC) AS dense_rank
FROM table_name
OVER 需要,括号内为编号顺序
注意:order by 时,desc NULL 值排在首位,ASC时NULL值排在末尾
可以通过NULLS LAST、NULLS FIRST 控制
Java代码 收藏代码
RANK() OVER (ORDER BY column_name DESC NULLS LAST)
PARTITION BY 分组排列顺序
Java代码 收藏代码
RANK() OVER(PARTITION BY month ORDER BY column_name DESC)
这样,就会按照month 来分,即所需要排列的信息先以month 的值来分组,在分组中排序,各个分组间不干涉
CUBE,ROLLUP,GROUPING SETS() 详见:HIVE增强的聚合,也可以结合RANK()使用实现具体逻辑。
NTILE
按层次查询,如一年中,统计出工资前1/5之的人员的名单,使用NTILE分析函数,把所有工资分为5份,为1的哪一份就是我们想要的结果:
Sql代码 收藏代码
select empno,ename,sum(sal),ntile(5) over (order by sum(sal) desc nulls last) til from emp group by empno,ename;
ROW_NUMBER
ROW_NUMBER()从1开始,为每条记录返回一个数字
Sql代码 收藏代码
SELECT
ROW_NUMBER() OVER (ORDER BY column_name DESC)AS row_name
FROM table_name;
二、窗口函数
可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等。
可以结合聚集函数SUM() 、AVG() 等使用。
可以结合FIRST_VALUE() 和LAST_VALUE(),返回窗口的第一个和最后一个值
(1)计算累计和
eg:统计1-12月的累积销量,即1月为1月份的值,2月为1.2月份值的和,3月为123月份的和,12月为1-12月份值的和
Java代码 收藏代码
SELECT
month,SUM(amount) month_amount,
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM table_name
GROUP BY month
ORDER BY month;
其中:
SUM( SUM(amount)) 内部的SUM(amount)为需要累加的值,在上述可以换为 month_amount
ORDER BY month 按月份对查询读取的记录进行排序,就是窗口范围内的排序
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义起点和终点,UNBOUNDED PRECEDING 为起点,表明从第一行开始, CURRENT ROW为默认值,就是这一句等价于:
ROWS UNBOUNDED PRECEDING
PRECEDING:在前 N 行的意思。
FOLLOWING:在后 N 行的意思。
计算前3个月之间的和
Sql代码 收藏代码
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS cumulative_amount
也可以
Java代码 收藏代码
SUM( SUM(amount)) OVER (ORDER BY month 3 PRECENDING) AS cumulative_amount
前后一个月之间的和
Sql代码 收藏代码
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS cumulative_amount
窗体第一条和最后一条的值
Java代码 收藏代码
FIRST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS xxxx;
LAST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS xxxx;
三、LAG、LEAD
获得相对于当前记录指定距离的那条记录的数据
LAG()为向前、LEAD()为向后
Sql代码 收藏代码
LAG(column_name1,1) OVER(ORDER BY column_name2)
LEAG(column_name1,1) OVER(ORDER BY column_name2)
这样就获得前一条、后一条的数据
四、FIRST、LAST
获得一个排序分组中的第一个值和组后一个值。可以与分组函数结合
Java代码 收藏代码
SELECT
MIN(month) KEEP(DENSE_RANK FIRST ORDER BY SUM(amount)) AS highest_sales_month,
MIN(month) KEEP(DENSE_RANK LAST ORDER BY SUM(amount)) AS lows_sales_month
FROM table_name
GROUP BY month
ORDER BY month;
这样就可以求得一年中销量最高和最低的月份。
输出的是月份,但是用SUM(amount)来判断。
示例
PARTITION BY with one partitioning column, no ORDER BY or window specification
SELECT a, COUNT(b) OVER (PARTITION BY c) FROM T;
根据c分派任务, 选择a和每个c下b的个数
PARTITION BY with two partitioning columns, no ORDER BY or window specification
SELECT a, COUNT(b) OVER (PARTITION BY c, d) FROM T;
根据c, d分派任务, 选择a和每个c, d下b的个数
PARTITION BY with one partitioning column, one ORDER BY column, and no window specification
SELECT a, SUM(b) OVER (PARTITION BY c ORDER BY d) FROM T;
根据c分派任务, 每个任务组依据d排序, 选择a和每个c下字段b的和
PARTITION BY with two partitioning columns, two ORDER BY columns, and no window specification
SELECT a, SUM(b) OVER (PARTITION BY c, d ORDER BY e, f) FROM T;
根据c,d分派任务, 每个任务组依据e,f排序, 选择a和每个c, d下字段的b的和
PARTITION BY with partitioning, ORDER BY, and window specification
SELECT a, SUM(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM T;
根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 从最开始到当前列的字段b的和
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) FROM T;
根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 从当前列之前3个到当前列的字段b的平均数
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) FROM T;
根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 当前列前后各3列的字段b的平均数
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM T;
根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 当前列到最后一列的字段b的平均数
WINDOW clause
SELECT a, SUM(b) OVER w FROM T; WINDOW w AS (PARTITION BY c ORDER BY d ROWS UNBOUNDED PRECEDING)
根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 第一列到当前列字段b的和
LEAD using default 1 row lead and not specifying default value
SELECT a, LEAD(a) OVER (PARTITION BY b ORDER BY C ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM T;
根据b分派任务, 每个任务组依据c排序, 选择a和下一条记录的a
LAG specifying a lag of 3 rows and default value of 0
SELECT a, LAG(a, 3, 0) OVER (PARTITION BY b ORDER BY C ROWS 3 PRECEDING) FROM T;
根据b分派任务, 每个任务组依据c排序, 选择a和前面第三条记录的a, 如果超出窗口, 返回0
本文转自:http://yugouai.iteye.com/blog/1908121
(1)用于分区排序
(2)动态Group By
(3)Top N
(4)累计计算
(5)层次查询
一、分析函数
用于等级、百分点、n分片等。
函数 说明
RANK() 返回数据项在分组中的排名,排名相等会在名次中留下空位
DENSE_RANK() 返回数据项在分组中的排名,排名相等会在名次中不会留下空位
NTILE() 返回n分片后的值
ROW_NUMBER() 为每条记录返回一个数字
Rank、DENSE_RANK
RANK()在出现等级相同的元素时预留为空,DENSE_RANK()不会。
Eg:某产品类型有两个并列第一
RANK():第一二为1,第三位3
DENSE_RANK():第一二为1,第三位2
Sql代码 收藏代码
SELECT
column_name,
RANK() OVER (ORDER BY column_name DESC) AS rank,
DENSE_RANK() OVER (ORDER BY SUM(column_name) DESC) AS dense_rank
FROM table_name
OVER 需要,括号内为编号顺序
注意:order by 时,desc NULL 值排在首位,ASC时NULL值排在末尾
可以通过NULLS LAST、NULLS FIRST 控制
Java代码 收藏代码
RANK() OVER (ORDER BY column_name DESC NULLS LAST)
PARTITION BY 分组排列顺序
Java代码 收藏代码
RANK() OVER(PARTITION BY month ORDER BY column_name DESC)
这样,就会按照month 来分,即所需要排列的信息先以month 的值来分组,在分组中排序,各个分组间不干涉
CUBE,ROLLUP,GROUPING SETS() 详见:HIVE增强的聚合,也可以结合RANK()使用实现具体逻辑。
NTILE
按层次查询,如一年中,统计出工资前1/5之的人员的名单,使用NTILE分析函数,把所有工资分为5份,为1的哪一份就是我们想要的结果:
Sql代码 收藏代码
select empno,ename,sum(sal),ntile(5) over (order by sum(sal) desc nulls last) til from emp group by empno,ename;
ROW_NUMBER
ROW_NUMBER()从1开始,为每条记录返回一个数字
Sql代码 收藏代码
SELECT
ROW_NUMBER() OVER (ORDER BY column_name DESC)AS row_name
FROM table_name;
二、窗口函数
可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等。
可以结合聚集函数SUM() 、AVG() 等使用。
可以结合FIRST_VALUE() 和LAST_VALUE(),返回窗口的第一个和最后一个值
(1)计算累计和
eg:统计1-12月的累积销量,即1月为1月份的值,2月为1.2月份值的和,3月为123月份的和,12月为1-12月份值的和
Java代码 收藏代码
SELECT
month,SUM(amount) month_amount,
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM table_name
GROUP BY month
ORDER BY month;
其中:
SUM( SUM(amount)) 内部的SUM(amount)为需要累加的值,在上述可以换为 month_amount
ORDER BY month 按月份对查询读取的记录进行排序,就是窗口范围内的排序
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义起点和终点,UNBOUNDED PRECEDING 为起点,表明从第一行开始, CURRENT ROW为默认值,就是这一句等价于:
ROWS UNBOUNDED PRECEDING
PRECEDING:在前 N 行的意思。
FOLLOWING:在后 N 行的意思。
计算前3个月之间的和
Sql代码 收藏代码
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS cumulative_amount
也可以
Java代码 收藏代码
SUM( SUM(amount)) OVER (ORDER BY month 3 PRECENDING) AS cumulative_amount
前后一个月之间的和
Sql代码 收藏代码
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS cumulative_amount
窗体第一条和最后一条的值
Java代码 收藏代码
FIRST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS xxxx;
LAST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS xxxx;
三、LAG、LEAD
获得相对于当前记录指定距离的那条记录的数据
LAG()为向前、LEAD()为向后
Sql代码 收藏代码
LAG(column_name1,1) OVER(ORDER BY column_name2)
LEAG(column_name1,1) OVER(ORDER BY column_name2)
这样就获得前一条、后一条的数据
四、FIRST、LAST
获得一个排序分组中的第一个值和组后一个值。可以与分组函数结合
Java代码 收藏代码
SELECT
MIN(month) KEEP(DENSE_RANK FIRST ORDER BY SUM(amount)) AS highest_sales_month,
MIN(month) KEEP(DENSE_RANK LAST ORDER BY SUM(amount)) AS lows_sales_month
FROM table_name
GROUP BY month
ORDER BY month;
这样就可以求得一年中销量最高和最低的月份。
输出的是月份,但是用SUM(amount)来判断。
示例
PARTITION BY with one partitioning column, no ORDER BY or window specification
SELECT a, COUNT(b) OVER (PARTITION BY c) FROM T;
根据c分派任务, 选择a和每个c下b的个数
PARTITION BY with two partitioning columns, no ORDER BY or window specification
SELECT a, COUNT(b) OVER (PARTITION BY c, d) FROM T;
根据c, d分派任务, 选择a和每个c, d下b的个数
PARTITION BY with one partitioning column, one ORDER BY column, and no window specification
SELECT a, SUM(b) OVER (PARTITION BY c ORDER BY d) FROM T;
根据c分派任务, 每个任务组依据d排序, 选择a和每个c下字段b的和
PARTITION BY with two partitioning columns, two ORDER BY columns, and no window specification
SELECT a, SUM(b) OVER (PARTITION BY c, d ORDER BY e, f) FROM T;
根据c,d分派任务, 每个任务组依据e,f排序, 选择a和每个c, d下字段的b的和
PARTITION BY with partitioning, ORDER BY, and window specification
SELECT a, SUM(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM T;
根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 从最开始到当前列的字段b的和
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) FROM T;
根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 从当前列之前3个到当前列的字段b的平均数
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) FROM T;
根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 当前列前后各3列的字段b的平均数
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM T;
根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 当前列到最后一列的字段b的平均数
WINDOW clause
SELECT a, SUM(b) OVER w FROM T; WINDOW w AS (PARTITION BY c ORDER BY d ROWS UNBOUNDED PRECEDING)
根据c分派任务, 每个任务组依据d排序, 选择a和每个c下, 第一列到当前列字段b的和
LEAD using default 1 row lead and not specifying default value
SELECT a, LEAD(a) OVER (PARTITION BY b ORDER BY C ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM T;
根据b分派任务, 每个任务组依据c排序, 选择a和下一条记录的a
LAG specifying a lag of 3 rows and default value of 0
SELECT a, LAG(a, 3, 0) OVER (PARTITION BY b ORDER BY C ROWS 3 PRECEDING) FROM T;
根据b分派任务, 每个任务组依据c排序, 选择a和前面第三条记录的a, 如果超出窗口, 返回0
本文转自:http://yugouai.iteye.com/blog/1908121
发表评论
-
hive常见优化问题汇总
2016-04-11 11:20 6156一、总体思想 http://blog.csdn.net/ ... -
hive-数据倾斜解决详解
2016-04-11 11:11 8677hive在跑数据时经常会出现数据倾斜的情况,使的作业经常r ... -
抽样类型详细说明
2016-03-24 10:41 2860抽样的类型 根据 ... -
hive配置详解
2016-03-18 10:48 1294hive中有许多配置将帮 ... -
hive打开调试信息方法
2016-03-17 17:53 1053当用hive查询时,有时可能会报错,当要查看详细的报错信息时 ... -
set hive.groupby.skewindata与数据倾斜
2016-03-16 10:03 12353hive和其它关系数据库一样,支持count(distinc ... -
hive:[Fatal Error] Operator FS_14 (id=14): Number of dynamic partitions exceeded
2016-03-10 10:44 1092向动态分区时,报错:hive:[Fatal Error] O ... -
Hive中Join的原理和机制
2016-01-22 20:36 938Hive中Join的原理和机制 笼统的说,Hive中的J ... -
hive 随机抽样
2016-01-22 20:23 145571. Random sampling 使用RAND()函 ... -
hive 分组取每个组中的top N条记录
2016-01-22 10:00 0SELECT id, rate, score FROM ( ... -
hive文件合并方法
2015-12-26 17:40 4342当Hive输入由很多个小 ... -
hive 查看一个表的总文件大小方法
2015-11-12 18:02 14397要查看一个hive表文件总大小时,我们可以通过一行脚本快速 ... -
hive 行转列和列转行的方法
2015-11-06 19:56 20359一、行转列的使用 1、问题 hive如何将 a ... -
hive array、map、struct使用
2015-11-06 19:40 2084hive提供了复合数据类型:Structs: structs ... -
hive with查询用法及CTAS的使用
2015-11-04 17:48 10660hive 可以通过with查询来提高查询性 ... -
hive创建表是报错: Specified key was too long; max key length is 767 bytes
2015-07-13 09:19 3725今天在hive客户端创建表时报错,具体操作如下 h ... -
hive having count 不能去重
2015-07-03 11:10 7563hive在使用having count()是,不支持去重计 ... -
Hive分析窗口函数 LAG,LEAD,FIRST_VALUE,LAST_VALUE
2015-05-25 10:37 10019问题导读1.LAG功能是什么?2.LEAD与LAG功能 ... -
Hive几种导出数据方式
2015-05-11 21:31 2241Hive几种导出数据方式 1.拷贝 ... -
hive 客户端查询报堆内存溢出解决方法
2015-03-25 21:32 5460hive> select * from t_test ...
相关推荐
Hive 窗口函数是 Hive 中的一种强大的分析函数,它可以对数据进行分类、排序、聚合和排名等操作。下面将详细介绍 Hive 窗口函数的语法结构、分类、应用场景和实践练习。 窗口函数语法结构 窗口函数的基本语法结构...
### Hive窗口函数详解 #### 一、概述 在大数据处理领域,Apache Hive 是一个广泛使用的数据仓库工具,它提供了一种SQL-like的语言——HiveQL,使得用户能够更方便地进行数据汇总、特殊查询和分析。其中,窗口函数...
本资料主要探讨Hive的高级分析函数及其优化技术,旨在帮助用户更高效地利用Hive进行大数据处理。以下是对标题和描述中涉及知识点的详细解析: 1. **Hive分析函数**: Hive提供了丰富的内置函数,包括聚合函数(如...
03.hive窗口分析函数--应用场景--累积报表--用传统方法实现.mp4
本学习笔记主要聚焦于HiveSQL中的窗口函数,这是进行复杂数据分析的重要工具。 窗口函数允许我们在一个数据集上进行计算,而不仅仅是基于单行记录,而是基于一个“窗口”内的多行数据。这个窗口可以是当前行及其...
Hive窗口函数主要包括以下几种类型: 1. **聚合函数**:如`SUM()`, `AVG()`, `COUNT()`, `MIN()`, `MAX()`,它们在窗口内执行聚合操作。 2. **排名函数**:如`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`,它们为窗口...
02.hive内置函数--窗口分析函数--row_number_over.mp4
【Hive SQL大厂必考常用窗口函数及面试题】主要涵盖了窗⼝函数在大数据分析中的应用,尤其在OLAP(在线分析处理)场景中的重要性。窗⼝函数是一种标准SQL功能,它允许对数据库数据进行实时分析处理,如市场分析、...
窗口函数是Hive中一种强大的分析工具,可以对数据进行复杂的分析和计算。窗口函数可以像聚合函数一样对一组数据进行分析并返回结果,但不同之处在于,窗口函数不是将一组数据汇总成单个结果,而是为每一行数据都返回...
在进行大数据分析时,了解并熟练掌握Hive的窗口函数和多维聚合操作是非常重要的。它们可以帮助我们快速生成复杂的分析报告,提高数据洞察力,从而在业务决策中提供有力支持。因此,建议开发者们在日常工作中多加练习...
在Hive中,函数是数据分析和处理的重要工具,它们提供了丰富的功能来操作和转化数据。以下是对"hive函数大全.doc"文档中提到的部分知识点的详细解释: ### 一、关系运算: 1. **等值比较**(=):用于判断两个...
其中,Hive的窗口函数是数据分析中的一种强大功能,可以用来处理复杂的排序和分组计算。本文将详细探讨Hive的开窗函数,包括ROW_NUMBER、RANK、DENSE_RANK以及分析窗口函数SUM、AVG、MIN和MAX的用法。 一、窗口函数...
本教程将深入探讨Hive数仓的架构与设计,Hive SQL的基本语法及高级特性,以及如何自定义函数以满足特定需求,并详细解析Hive的重要参数配置。 1. Hive数仓: - 数据仓库概念:数据仓库是为决策支持系统设计的,...
- **分析函数 (Analytics functions)**:例如 `RANK`, `ROW_NUMBER`, `DENSE_RANK`, `CUME_DIST`, `PERCENT_RANK`, `NTILE` 等,这些函数用于对数据进行排序和分组。 - **混合函数**:如 `java_method`, `reflect`...
在大数据处理领域,Hive作为一个广泛使用的数据仓库工具,提供了丰富的内置函数来支持数据的处理与分析。本文档旨在介绍Hive中的常用函数及其用法,帮助用户更好地理解和应用这些函数。需要注意的是,由于Hive的不同版本...
Hive提供了丰富的内置函数,使得对大数据进行分析变得更为便捷。以下是对“Hive函数大全”这个主题的详细讲解。 1. **数据类型**:Hive支持多种数据类型,包括基本类型如整型(TINYINT, SMALLINT, INT, BIGINT)、...
20.Hive中分析函数与窗口函数 21.Hive中UDF的介绍 22.Hive中使用自定义UDF实现日期格式转换 23. HiveServer2的介绍及三种连接方式 24.Hive元数据、fetch task和严格模式的介绍 第3章:Sqoop Sqoop及用户行为分析...
总之,这个资料包将带你走进Hadoop和Hive的世界,通过理论学习与实践操作,你可以掌握大数据分析的基本技能,包括环境搭建、Hadoop原理、Hive操作及函数应用,从而更好地应对大数据的挑战。通过深入学习和不断实践,...