【Hive】Hive窗口函数

窗口函数是用于分析用的一类函数，要理解窗口函数要先从聚合函数说起。

• 聚合函数是将某列中多行的值合并为一行，比如sum、count等。
• 窗口函数则可以在本行内做运算，得到多行的结果，即每一行对应一行的值。

语法概括：

Function() Over (Partition By Column1，Column2，Order By Column3)

• FIRST_VALUE(col)，LAST_VALUE(col) 

可以返回窗口帧中第一条或最后一条记录的指定字段值；

• LEAD(col, n)，LAG(col, n)             

 返回当前记录的上 n 条或下 n 条记录的字段值；

• RANK()，ROW_NUMBER()         

会为帧内的每一行返回一个序数，区别在于存在字段值相等的记录时，RANK() 会返回相同的序数；

• COUNT()， SUM(col)，MIN(col)           

和一般的聚合操作相同

---

窗口函数又分为以下三类： 聚合型窗口函数      分析型窗口函数     取值型窗口函数 

• 聚合型窗口函数：

          SUM(), MIN(),MAX(),AVG(),COUNT()这些常见的聚合函数。

 

• 分析型窗口函数：

         RANK(),ROW_NUMBER(),DENSE_RANK()等常见的排序用的窗口函数

 

• 取值型窗口函数：
  • LAG是迟滞的意思，也就是对某一列进行往后错行
  • LEAD是LAG的反义词，也就是对某一列进行提前几行
  • FIRST_VALUE是对该列到目前为止的首个值
  • LAST_VALUE是到目前行为止的最后一个值

基本用法：

(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

解释：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 

表示选择分区起始记录到当前记录的所有行

SUM(close) RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING 

则通过 字段差值 来进行选择，如当前行的 close 字段值是 200，那么这个窗口帧的定义就会选择分区中 close 字段值落在 100 至 400 区间的记录

---

窗口函数基本原理：

窗口分区： PARTITION 语句会按照一个或多个指定字段，将查询结果集拆分到不同的 窗口分区 中，并可按照一定规则排序

窗口帧： 用于从分区中选择指定的多条记录，供窗口函数处理。Hive 提供了两种定义窗口帧的形式：ROWS 和 RANGE。两种类型都需要配置上界和下界

窗口函数： 会基于当前窗口帧的记录计算结果

 

group by 与 partition by的区别：

带上group by的hive sql语句只能显示与分组聚合相关的字段

而带上over(partition by ……)的hive sql语句能显示所有字段

 

hive中三个排序函数rank、row_number、dense_rank三者的区别：

1. rank()函数                 此排序方法进行排序时，相同的排序是一样的，而且下一个不同值是跳着排序的。
2. row_number()函数    此方法不管排名是否有相同的，都按照顺序1，2，3…..n
3. dense_rank()函数     此方法对于排名相同的名次一样，且后面名次不跳跃

 

示例：

1月100,2月200,3月100,4月200.统计如下效果：1月100,2月300,3月500,4月600.【就是每月统计一次前面所有的月的总额】

select month,amount,sum(amount) over(order by month asc) from expense;
 
select t.* ，(select sum(amount) from expense where month <= t.month) from expense t;

 

参考：

http://shzhangji.com/cnblogs/2017/09/05/hive-window-and-analytical-functions/

http://www.qubenhao.com/2019/01/17/winfunc/

https://blog.csdn.net/qq_32445015/article/details/102538313