Hive窗口函数小结

over开窗函数 和group by的区别：

   OVER子句用于为行为定义一个窗口（windows），以便进行特定的运算。可以把行的窗口简单地认为是运算将要操作的一个行的集合。
            例如，聚合函数和排名函数都是可以支持OVER子句的运算类型。由于OVER子句为这些函数提供了一个行的窗口，所以这些函数也称之为开窗函数。　
            聚合函数的要点就是要对一组值进行聚合，聚合函数传统上一直以GROUP BY查询作为操作的上下文。
用group by对数据进行分组以后，查询为每个组只返回一行； 因此，也就是要限制所有的表达式为每个组只能返回一个值。

1、窗口函数的基本结构
    分析函数（如:sum(),max(),row_number()...） + 窗口子句（over函数）
 2、over函数写法：
　　over（partition by year order by id） 或者 over（distribute by year sort by id）
    先根据year字段分区，相同的year分为一区，每个分区内根据id字段排序（默认升序）
    需要注意：over（partition by year order by id）与 over（partition by year）的区别
   
    order by子句会让输入的数据强制排序（窗口函数是SQL语句最后执行的函数，因此可以把SQL结果集想象成输入数据）。
    Order By子句对于诸如Row_Number()，Lead()，LAG()等函数是必须的，如果数据无序，这些函数的结果就没有任何意义。
    因此如果有了Order By子句，则Count()，Min()等计算出来的结果就没有任何意义。
    
    前者：代表（默认）分区内从起始行到当前行范围内排序后的计算或取值
    后者：代表（默认）整个分区范围内计算或取值
    
1）AVG，MIN，MAX，SUM 举例：

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 cookie;

 

如果不指定ROWS BETWEEN,默认统计窗口为从起点到当前行;
如果不指定ORDER BY，则将分组内所有值累加;    
关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点
–其他AVG，MIN，MAX，和SUM用法一样。

2）NTILE

NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
NTILE不支持ROWS BETWEEN，
如果切片不均匀，默认增加第一个切片的分布
用于求分区内的前几片（如前1/3天）

3）ROW_NUMBER

ROW_NUMBER() –从1开始，按照顺序，生成分组内记录的序列
ROW_NUMBER() 主要应用于求topN的场景。
语法：row_number()over(partition by h order by age desc) as rownum

row_number、rank、dense_rank区别：
row_number() 按顺序编号，不留空位
rank() 按顺序编号，相同的值编相同号，留空位
dense_rank() 按顺序编号，相同的值编相同的号，不留空位

4）CUME_DIST、PERCENT_RANK

–CUME_DIST() 小于等于当前值的行数/分组内总行数
--------可以用于统计截止当前行数据所占总数据的比率。
–PERCENT_RANK() 分组内当前行的RANK值-1/分组内总行数-1

5）LAG  与  LEAD

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，
第二个参数为往上第n行（可选，默认为1），
第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

LEAD与LAG相反
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

6）FIRST_VALUE(col) 与  LAST_VALUE(col)

FIRST_VALUE(col) 
取分组内排序后，截止到当前行，第一个值

LAST_VALUE(col)
取分组内排序后，截止到当前行，最后一个值