当前位置:首页 > 开发 > 数据库 > 正文

HIVE 窗口及分析函数

发表于: 2015-02-06   作者:daizj   来源:转载   浏览:
摘要: 窗口函数应用场景: (1)用于分区排序 (2)动态Group By (3)Top N (4)累计计算 (5)层次查询 一、分析函数 用于等级、百分点、n分片等。 函数             说明 RANK()     &nbs
窗口函数应用场景:
(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 窗口及分析函数

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
快过年了,总算没那么忙了,可以完善一下这篇文章了。之前只能说是备忘罢了,很多细节没说清楚。 首
(本文尝试通过一些简单的实验,来分析Windows的窗口机制,并对微软的设计理由进行一定的猜测,需要
(本文尝试通过一些简单的实验,来分析Windows的窗口机制,并对微软的设计理由进行一定的猜测,需要
一位开发人员每天跑相同的任务,但是他发现某一天同样的任务处理大约差不多量的数据(都是大约4亿条
一位开发人员每天跑相同的任务,但是他发现某一天同样的任务处理大约差不多量的数据(都是大约4亿条
http://www.cnblogs.com/linjiqin/archive/2013/03/07/2948078.html 实战2——Hadoop的日志分析 1).
1、日志格式分析 首先分析 Hadoop 的日志格式, 日志是一行一条, 日志格式可以依次描述为:日期、时间
目前postgresql中的部分函数在hive中也是存在的,所以今天就以postgresql为例来进行说明,这个过程
永久自定义hive函数 1:做这件事的原因: 有一些函数是比较基础的,公用的,每次都要create tempora
[Author]: kwu 1 、UDF自定义函数 自定义函数包括三种UDF、UDAF、UDTF UDF(User-Defined-Function)
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号