Hive-分析窗口函数

Posted on

xbb

Hive查询使用中难免遇到各种各样奇奇怪怪的需求或者角度，这个时候合理使用分析窗口函数也许会有很好的效果

前面《我的Hive学习之旅》一文主要讲了row_number(),动态group by还有日期处理之类的问题，稍微涉及了一些窗口函数的东西，但是并没有较为详细地整理一下。今天有时间就整理一下吧~

窗口函数：

窗口函数 描述
lead 用于统计窗口内往下第n行值。
lag 与lead相反，用于统计窗口内往上第n行值。
first_value 取分组排序后，截止到当前行，第一个值
last_value 取分组排序后，截止到当前行，最后一个值

分析函数：

分析函数 描述
RANK 返回数据项在分区中的排名。排名值序列可能会有间隔
DENSE_RANK 返回数据项在分区中的排名。排名值序列是连续的，不会有间隔
PERCENT_RANK 计算当前行的百分比排名
ROW_NUMBER 确定分区中当前行的序号
CUME_DIST 计算分区中当前行的相对排名
NTILE() 将每个分区的行尽可能均匀地划分为指定数量的分组

over子句

  • OVER子句可以与标准聚合函数使用(COUNT，SUM，MIN，MAX，AVG)
  • OVER可以与一个或多个任何原始数据类型的分区列的PARTITION BY语句使用。
  • OVER可以与一个或多个任何原始类型的分区列（排序列）的PARTITION BY(ORDER BY)使用。
  • 带有窗口规范的OVER子句。窗口可以在WINDOW子句中单独定义。 窗口规范支持如下格式
    1
    2
    3
    4
    5
    6
    7
    8
    9

    (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

    eg:
    select dt , count(userid) over day_window as user_num
    from table
    window day_window as (partition by userid,dt  order by diffdays ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )

当缺少WINDOW子句并指定使用ORDER BY时，窗口规范默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即从第一行到当前行。

当缺少ORDER BY和WINDOW子句时，窗口规范默认为ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，即第一行到最后一行。

备注

PRECEDING：表示当前行之前的行

UNBOUNDED PRECEDING： 第一行

num PRECEDING：当前行之前的第num行

CURRENT ROW：当前行

FOLLOWING：当前行之后的行

UNBOUNDED FOLLOWING：最后一行

num FOLLOWING：当前行之后的第num行

range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内

rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）

示例代码中便是在带有窗口规范的OVER子句，其窗口便在window子句中单独定义，但如果是分析规范，则不支持。

小提示

在count()等聚合函数中，distinct在hive2.2.0之后才支持order by子句和窗口规范
hive2.1.0之后over子句中支持聚合函数

实战演习

分组取top n

row_number()搭配over子句安排得很明明白白，还有xx_rank()几个函数供使用

1
2
3
4
select seasonid, episodeid , 
row_number() over(partition by seasonid order by episodeid asc) as rank 
from tutor.dw_season_live_information a 
where dt = date_sub(current_date,1)

计算累计

引用网上数据

1
2
3
4
5
6
7
8
9
10

hive> select * from lxw1234;
OK
cookie1 2015-04-10      1
cookie1 2015-04-11      5
cookie1 2015-04-12      7
cookie1 2015-04-13      3
cookie1 2015-04-14      2
cookie1 2015-04-15      4
cookie1 2015-04-16      4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT cookieid,
createtime,
pv,
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1 
SUM(pv) OVER(PARTITION BY cookieid) AS pv3,	--分组内所有行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4,  --当前行+往前3行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5,  --当前行+往前3行+往后1行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6  ---当前行+往后所有行  
FROM lxw1234;
 
cookieid createtime     pv      pv1     pv2     pv3     pv4     pv5      pv6 
-----------------------------------------------------------------------------
cookie1  2015-04-10      1       1       1       26      1       6       26
cookie1  2015-04-11      5       6       6       26      6       13      25
cookie1  2015-04-12      7       13      13      26      13      16      20
cookie1  2015-04-13      3       16      16      26      16      18      13
cookie1  2015-04-14      2       18      18      26      17      21      10
cookie1  2015-04-15      4       22      22      26      16      20      8
cookie1  2015-04-16      4       26      26      26      13      13      4

时间序列分析

主要使用lead()等函数