Hive窗口函数保姆级教程

在SQL中有一类函数叫做聚合函数，例如sum()、avg()、max()等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据，又要显示聚集后的数据，这时我们便引入了窗口函数。窗口函数又叫OLAP函数/分析函数，窗口函数兼具分组和排序功能。

本文分为两部分：第一部分是Hive窗口函数详解，剖析各种窗口函数（几乎涵盖Hive所有的窗口函数）；第二部分是窗口函数实际应用，这部分总共有五个例子，都是工作常用、面试必问的非常经典的例子。

Hive 窗口函数

partition byorder by

XXX over (partition by xxx order by xxx)

特别注意不是必选

窗口函数我划分了几个大类，我们一类一类的讲解。

1. SUM、AVG、MIN、MAX

讲解这几个窗口函数前，先创建一个表，以实际例子讲解大家更容易理解。

首先创建用户访问页面表：user_pv

给上面这个表加上如下数据：

执行如下查询语句：

结果如下：（因命令行原因，下图字段名和值是错位的，请注意辨别！）

执行如下查询语句：

结果如下：

第一条SQL的over()里面加 order by ，第二条SQL没加order by ，结果差别很大

所以要注意了

over()里面加 order by 表示

over()里面不加 order by 表示

AVG，MIN，MAX，和SUM用法一样，这里就不展开讲了，但是要注意 AVG，MIN，MAX 的over()里面加不加 order by 也和SUM一样，如 AVG 求平均值，如果加上 order by，表示分组内从起点到当前行的平局值，不是全部的平局值。MIN，MAX 同理。

2. ROW_NUMBER、RANK、DENSE_RANK、NTILE

还是用上述的用户登录日志表：user_pv，里面的数据换成如下所示：

ROW_NUMBER()从1开始，按照顺序，生成分组内记录的序列。

结果如下：

RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位。

DENSE_RANK()生成数据项在分组中的排名，排名相等会在名次中不会留下空位。

结果如下：

有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?NTILE函数即可以满足。

ntile可以看成是：把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。

然后可以根据桶号，选取前或后 n分之几的数据。数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。

结果如下：

3. LAG、LEAD、FIRST_VALUE、LAST_VALUE

讲解这几个窗口函数时还是以实例讲解，首先创建用户访问页面表：user_url

表中加入如下数据：

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值。

第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

结果如下：

解释：

与LAG相反

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值。

第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

结果如下：

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

结果如下：

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

结果如下：

如果想要取分组内排序后最后一个值，则需要变通一下：

注意上述SQL，使用的是 FIRST_VALUE 的倒序取出分组内排序最后一个值！

结果如下：

此处要特别注意order  by

如果不指定ORDER BY，则进行排序混乱，会出现错误的结果

结果如下：

上述 url2 和 url55 的createtime即不属于最靠前的时间也不属于最靠后的时间，所以结果是混乱的。

4. CUME_DIST

先创建一张员工薪水表：staff_salary

表中加入如下数据：

此函数的结果和order by的排序顺序有关系。

CUME_DIST：小于等于当前值的行数/分组内总行数。  order默认顺序：正序

比如，统计小于等于当前薪水的人数，所占总人数的比例。

结果如下：

解释：

5. GROUPING SETS、GROUPING__ID、CUBE、ROLLUP

这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。

还是先创建一个用户访问表：user_date

表中加入如下数据：

grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。

等价于将不同维度的GROUP BY结果集进行UNION ALL。

注：上述SQL中的GROUPING__ID，是个关键字，表示结果属于哪一个分组集合，根据grouping sets中的分组条件month，day，1是代表month，2是代表day。

结果如下：

上述SQL等价于：

根据GROUP BY的维度的所有组合进行聚合。

结果如下：

上述SQL等价于：

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

比如，以month维度进行层级聚合：

结果如下：

把month和day调换顺序，则以day维度进行层级聚合：

结果如下：

这里，根据日和月进行聚合，和根据日聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样。

窗口函数实际应用

1. 第二高的薪水

难度简单。

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）。

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

这道题可以用 row_number 函数解决。

参考代码：

更简单的代码：

OFFSET：偏移量，表示从第几条数据开始取，0代表第1条数据。

2. 分数排名

难度简单。

编写一个 SQL 查询来实现分数排名。

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

参考代码：

3. 连续出现的数字

难度中等。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

参考代码：

4. 连续N天登录

难度困难。

写一个 SQL 查询,  找到活跃用户的 id 和 name，活跃用户是指那些至少连续 5 天登录账户的用户，返回的结果表按照 id 排序。

表 Accounts：

表 Logins：

例如，给定上面的Accounts和Logins表，至少连续 5 天登录账户的是id=7的用户

思路：

参考代码：

注意点：

5. 给定数字的频率查询中位数

难度困难。

Numbers 表保存数字的值及其频率。

在此表中，数字为 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3，所以中位数是 (0 + 0) / 2 = 0。

请编写一个查询来查找所有数字的中位数并将结果命名为 median 。

参考代码：

–END–