2万字长文，最全面的Hive开窗函数讲解和实战指南（必看）

导读：

文章来源于：白程序员的自习室

预计阅读需：20分钟

分享这篇好文的原因是我觉得契合我最近写作的主题【Hive开发】，《大数据阶梯之路》公众号内分享的文章也都是我经过挑选的，欢迎大家一同学习，一同成长。 By：Akin

👆点击关注｜设为星标｜干货速递👆

窗口函数（Window Function）是 SQL2003 标准中定义的一项新特性，并在 SQL2011、SQL2016 中又加以完善，添加了若干拓展。

窗口函数不同于我们熟悉的常规函数及聚合函数，它为每行数据进行一次计算，特点是输入多行（一个窗口）、返回一个值。

在报表等数据分析场景中，你会发现窗口函数真的很强大，灵活运用窗口函数可以解决很多复杂问题，比如去重、排名、同比及环比、连续登录等等。

既然窗口函数这么强大，更要了解和灵活运用它了，本文将对窗口函数进行一个全面的整理，讲一讲窗口函数是什么，有哪些分类，用法是什么，以及窗口函数的案例加深大家的理解。

那什么是窗口函数呢？

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

Function (arg1,…, argn) 可以是下面的函数：

Aggregate Functions: 聚合函数,比如：sum(…)、 max(…)、min(…)、avg(…)等.

Sort Functions: 数据排序函数, 比如 ：rank(…)、row_number(…)等.

Analytics Functions: 统计和比较函数, 比如：lead(…)、lag(…)、 first_value(…)等.

OVER ([PARTITION BY <…>] [ORDER BY <….>]

PARTITION BY 表示将数据先按 字段 进行分区

ORDER BY 表示将各个分区内的数据按 排序字段 进行排序

window_expression 用于确定窗边界

窗口边界使用详解

若不指定 ORDER BY，默认使用分区内所有行 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING;若指定了 ORDER BY，默认使用分区内第一行到当前值 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

窗口函数的计算过程（语法中每个部分都是可选的）

数据准备

窗口聚合函数有哪些？

排名窗口函数

值窗口函数

注意: last_value默认的窗口是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，表示当前行永远是最后一个值，需改成RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

开窗案例举例

如何使用开窗函数去重

如何使用开窗函数进行排名

数仓增量数据合并

基于上述的排名和区中方法结合，可以实现数仓增量抽取的数据和历史数据合并去重。

你需要了解的全量表,增量表及拉链表

环比

数据准备

需求描述

查询店铺上个月的营业额，结果字段如下：| 月份  | 商铺  | 本月营业额  | 上月营业额|

不使用开窗函数实现方案

lag 开窗函数实现环比

lag 其他用法演示

lead 求下月营业额

lead(col,n,default)与lag相反，统计分组内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，不填默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）。

first_value(col)

用于取分组内排序后，截止到当前行，第一个col的值。

last_value(col)

用于取分组内排序后，截止到当前行，最后一个col的值。

连续登录

数据准备

展现连续登陆两天的用户信息

统计连续登陆两天的用户个数

(n天就只需要把lead(date,2,-1)中的2改成n-1并且把date_sub(cast(b.date as date),2)中的2改成n-1)

特说说明：上文指出了连续登录2天的场景，针对其他连续登录场景，假设连续登录n天，可将lead(date,1,-1)中的1改成n-1，date_sub(cast(b.date as date),1)中的1改成n-1。

占比、同比、环比计算（lag函数，lead函数）

数据准备

使用窗口函数实现占比

使用窗口函数实现环比计算

什么是环比、什么是同比？与上年度数据对比称”同比”，与上月数据对比称”环比”。

相关公式如下:同比增长率计算公式：(当年值-上年值)/上年值x100%

环比增长率计算公式：(当月值-上月值)/上月值x100%

其他案例

间隔，最近两次间隔，登录间隔，出院间隔等等

扩展

一些优化思想

有时候，一个 SELECT 语句中包含多个窗口函数，它们的窗口定义（OVER 子句）可能相同、也可能不同。显然，对于相同的窗口，完全没必要再做一次分区和排序，我们可以将它们合并成一个 Window 算子。

那如何利用一次排序计算多个窗口函数呢？某些情况下，这是可能的。下面的例子如下：

虽然这 2 个窗口并非完全一致，但是 AVG(sales) 不关心分区内的顺序，完全可以复用 ROW_NUMBER() 的窗口，这里提供了一种方式，尽一切可能利用能够复用的机会。

窗口函数 VS. 聚合函数

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

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

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

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

往期推荐

实时数仓演进&访问对比选型落地

「数据湖篇」一文带你识数据湖「数据湖篇」一文带你识数据湖

「数仓建设篇」主题域与主题划分「数仓建设篇」主题域与主题划分

「Hive进阶篇」万字长文超详述Hive企业级优化「Hive进阶篇」万字长文超详述Hive企业级优化

欢迎关注下方我的公众号《大数据阶梯之路》，分享行业内最新技术，加我wx，拉你进交流群，共享资料