一文即懂hive常用开窗函数

人生有三个基本目标：
不作恶，
开心，
自己养活自己。
如果能达到，就是很好的一生了。
—冯唐

一、窗口函数定义

窗口函数（Window Function）是 SQL2003 标准中定义的一项新特性，并在 SQL2011、SQL2016 中又加以完善，添加了若干处拓展。窗口函数不同于我们熟悉的普通函数和聚合函数，它为每行数据进行一次计算：输入多行（一个窗口）、返回一个值。在报表等分析型查询中，窗口函数能优雅地表达某些需求，发挥不可替代的作用。

什么是窗口函数？

窗口函数出现在 SELECT 子句的表达式列表中，它最显著的特点就是 OVER 关键字。语法定义如下：

window_function (expression) OVER (
   [ PARTITION BY part_list ]
   [ ORDER BY order_list ]
   [ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] )

其中包括以下可选项：

1. PARTITION BY 表示将数据先按 part_list 进行分区
2. ORDER BY 表示将各个分区内的数据按 order_list 进行排序
   

最后一项表示 Frame 的定义，即：当前窗口包含哪些数据？

ROWS 选择前后几行，例如 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING 表示往前 3 行到往后 3 行，一共 7 行数据（或小于 7 行，如果碰到了边界）
RANGE 选择数据范围，例如 RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING 表示所有值在 [c−3,c+3][c−3,c+3] 这个范围内的行，cc 为当前行的值

逻辑语义上说，一个窗口函数的计算“过程”如下：

按窗口定义，将所有输入数据分区、再排序（如果需要的话）
对每一行数据，计算它的 Frame 范围
将 Frame 内的行集合输入窗口函数，计算结果填入当前行
注：语法中每个部分都是可选的：

如果不指定 PARTITION BY，则不对数据进行分区；换句话说，所有数据看作同一个分区
如果不指定 ORDER BY，则不对各分区做排序，通常用于那些顺序无关的窗口函数，例如 SUM()
如果不指定 Frame 子句，则默认采用以下的 Frame 定义：
若不指定 ORDER BY，默认使用分区内所有行 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
若指定了 ORDER BY，默认使用分区内第一行到当前值 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
窗口函数 VS. 聚合函数

从聚合这个意义上出发，似乎窗口函数和 Group By 聚合函数都能做到同样的事情。但是，它们之间的相似点也仅限于此了！这其中的关键区别在于：窗口函数仅仅只会将结果附加到当前的结果上，它不会对已有的行或列做任何修改。而 Group By 的做法完全不同：对于各个 Group 它仅仅会保留一行聚合结果。

有的读者可能会问，加了窗口函数之后返回结果的顺序明显发生了变化，这不算一种修改吗？因为 SQL 及关系代数都是以 multi-set 为基础定义的，结果集本身并没有顺序可言，ORDER BY 仅仅是最终呈现结果的顺序。

另一方面，从逻辑语义上说，SELECT 语句的各个部分可以看作是按以下顺序“执行”的：

Figure 3. SQL 各部分的逻辑执行顺序
注意到窗口函数的求值仅仅位于 ORDER BY 之前，而位于 SQL 的绝大部分之后。这也和窗口函数只附加、不修改的语义是呼应的——结果集在此时已经确定好了，再依此计算窗口函数。

二、Hive常用窗口函数

**排序类：**ROW_NUMBER()、RANK()、DENSE_RANK()等；
取值类：

1. FIRST_VALUE(col)、LAST_VALUE(col)、
2. LEAD(col,n,DEFAULT)、LAG(col,n,DEFAULT)等；
   **聚合类：**COUNT()、SUM()、MIN()、MAX()、AVG()等；
   应用场景
   用于分组排序、动态Group By、Top N、累计计算、层次查询。
   应用举例

2.1 分组排序窗口函数举例

2.2 取值相关窗口函数举例

SELECT user_id,
       course,
       score,
       ROW_NUMBER() OVER(PARTITION BY course ORDER BY score ASC) AS rn,
       FIRST_VALUE(score) OVER(PARTITION BY course ORDER BY score ASC) AS first_scorea,
       FIRST_VALUE(score) OVER(PARTITION BY course ORDER BY score DESC) AS first_scored,
       FIRST_VALUE(user_id) OVER(PARTITION BY course ORDER BY score ASC) AS first_usera,
       FIRST_VALUE(user_id) OVER(PARTITION BY course ORDER BY score DESC, user_id ASC) AS first_userda,
       LAST_VALUE(score) OVER(PARTITION BY course ORDER BY score) AS last_scorea,
       LAST_VALUE(user_id) OVER(PARTITION BY course ORDER BY score ASC,user_id ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_user_upcr,
       LAST_VALUE(user_id) OVER(PARTITION BY course ORDER BY score ASC,user_id ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_user_upuf,
       LAG(score,1,0) OVER(PARTITION BY course ORDER BY score) AS lag_1_0
  FROM student_score
  ORDER