hive 窗口函数分析函数

参考文章

1. 语法

-- Window functions appear between the key words SELECT and FROM
SELECT   ...,
         <window_function>,
         ...
FROM     <tablename>
...
;

-- They consist of three main parts:
--   1. function type (which is the name of the function)
--   2. key word 'OVER'
--   3. specification, which rows constitute the 'sliding window' (partition, order and frame)
<window_function>      := <window_function_type> OVER <window_specification>

<window_function_type> := ROW_NUMBER() | RANK() | LEAD(<column>) | LAG(<column>) |
                          FIRST_VALUE(<column>) | LAST_VALUE(<column>) | NTH_VALUE(<column>, <n>) |
                          SUM(<column>) |  MIN(<column>) | MAX(<column>) | AVG(<column> | COUNT(<column>)

<window_specification> := [ <window_partition> ] [ <window_order> ] [ <window_frame> ]

<window_partition>     := PARTITION BY <column>
<window_order>         := ORDER BY <column>
<window_frame>         := see below

2. 执行顺序

1. where子句执行
2. 分区子句执行
3. order子句执行
4. 窗口帧子句执行
5. 窗口函数作用在窗口帧上

3. 基本的窗口函数

Signature 	Scope 	Meaning / Return Value
FIRST_VALUE(<column>) 	-- 取出窗口帧的第一个元素
LAST_VALUE(<column>) 	-- 取出窗口帧最后一个元素
LAG(<column> [, <n>]) 	-- 默认n为1，往上n行的数据元素，针对的是**分区**，也就是窗口帧无效
LEAD(<column> [, <n>]) 	-- 往下n行去数据，针对**分区**
ROW_NUMBER() 	
RANK() 	
NTH_VALUE(<column>, <n>) 	
SUM(<column>)
MIN(<column>)
MAX(<column>)
AVG(<column>)
COUNT(<column>) 	

3. 窗口帧

<window_frame>  := [ROWS | GROUPS | RANGE ] BETWEEN 
                   [ UNBOUNDED PRECEDING | <n> PRECEDING | CURRENT ROW ] AND
                   [ UNBOUNDED FOLLOWING | <n> FOLLOWING | CURRENT ROW ]
                    Rows in a partition and the according key words
 -     <--   UNBOUNDED PRECEDING (first row)
 ...
 -     <-- 2 PRECEDING
 -     <-- 1 PRECEDING
 -     <--   CURRENT ROW
 -     <-- 1 FOLLOWING
 -     <-- 2 FOLLOWING
 ...
 -     <--   UNBOUNDED FOLLOWING (last row)

4. 默认值

1. 如果没有窗口帧子句，默认就是第一行到当前行
2. 如果没有order子句，那么就不可以设置窗口帧子句，一个分区就是一个帧
3. 如果没有分区子句，那么所有数据当做一个分区