在SQL中有一类函数叫做聚合函数，例如sum()、avg()、max()等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据，又要显示聚集后的数据，这时我们便引入了窗口函数。窗口函数又叫OLAP函数/分析函数，窗口函数兼具分组和排序功能。
本文分为两部分：
第一部分是Hive窗口函数详解，剖析各种窗口函数（几乎涵盖Hive所有的窗口函数）；
第二部分是窗口函数实际应用，这部分总共有五个例子，都是工作常用、面试必问的非常经典的例子。
窗口函数最重要的关键字是 partition by 和 order by
具体语法如下：XXX over (partition by xxx order by xxx)
特别注意：over()里面的 partition by 和 order by 都不是必选的，over()里面可以只有partition by，也可以只有order by，也可以两个都没有，大家需根据需求灵活运用。
窗口函数我划分了几个大类，我们一类一类的讲解。
1. SUM、AVG、MIN、MAX
讲解这几个窗口函数前，先创建一个表，以实际例子讲解大家更容易理解。
首先创建用户访问页面表：
给上面这个表加上如下数据：
- SUM()使用
执行如下查询语句：
结果如下：（因命令行原因，下图字段名和值是错位的，请注意辨别！）
执行如下查询语句：
结果如下：
第一条SQL的over()里面加 order by ，第二条SQL没加order by ，结果差别很大
所以要注意了：
- over()里面加 order by 表示：分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号；
- 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
还是用上述的用户登录日志表：，里面的数据换成如下所示：
- ROW_NUMBER()使用：
ROW_NUMBER()从1开始，按照顺序，生成分组内记录的序列。
结果如下：
- RANK 和 DENSE_RANK 使用：
RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位。
DENSE_RANK()生成数据项在分组中的排名，排名相等会在名次中不会留下空位。
结果如下：
- NTILE的使用：
有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?NTILE函数即可以满足。
ntile可以看成是：把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
然后可以根据桶号，选取前或后 n分之几的数据。数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。
结果如下：
3. LAG、LEAD、FIRST_VALUE、LAST_VALUE
讲解这几个窗口函数时还是以实例讲解，首先创建用户访问页面表：
表中加入如下数据：
- LAG的使用：
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值。
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
结果如下：
解释：
- LEAD的使用：
与LAG相反
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值。
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
结果如下：
- FIRST_VALUE的使用：
取分组内排序后，截止到当前行，第一个值。
结果如下：
- LAST_VALUE的使用：
取分组内排序后，截止到当前行，最后一个值。
结果如下：
如果想要取分组内排序后最后一个值，则需要变通一下：
注意上述SQL，使用的是 FIRST_VALUE 的倒序取出分组内排序最后一个值！
结果如下：
此处要特别注意order by
如果不指定ORDER BY，则进行排序混乱，会出现错误的结果
结果如下：
上述 url2 和 url55 的createtime即不属于最靠前的时间也不属于最靠后的时间，所以结果是混乱的。
4. CUME_DIST
先创建一张员工薪水表：
表中加入如下数据：
- CUME_DIST的使用：
此函数的结果和order by的排序顺序有关系。
CUME_DIST：小于等于当前值的行数/分组内总行数。 order默认顺序 ：正序
比如，统计小于等于当前薪水的人数，所占总人数的比例。
结果如下：
解释：
5. GROUPING SETS、GROUPING__ID、CUBE、ROLLUP
这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。
还是先创建一个用户访问表：
表中加入如下数据：
- GROUPING SETS的使用：
grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。
等价于将不同维度的GROUP BY结果集进行UNION ALL。
注：上述SQL中的GROUPING__ID，是个关键字，表示结果属于哪一个分组集合，根据grouping sets中的分组条件month，day，1是代表month，2是代表day。
结果如下：
上述SQL等价于：
- CUBE的使用：
根据GROUP BY的维度的所有组合进行聚合。
结果如下：
上述SQL等价于：
- ROLLUP的使用：
是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的用户
思路：
- 去重：由于每个人可能一天可能不止登陆一次，需要去重
- 排序：对每个ID的登录日期排序
- 差值：计算登录日期与排序之间的差值，找到连续登陆的记录
- 连续登录天数计算：select id, count(*) group by id, 差值（伪代码）
- 取出登录5天以上的记录
- 通过表合并，取出id对应用户名
参考代码：
注意点：
- DATE_SUB的应用：DATE_SUB (DATE, X)，注意，X为正数表示当前日期的前X天；
- 如何找连续日期：通过排序与登录日期之间的差值，因为排序连续，因此若登录日期连续，则差值一致；
- GROUP BY和HAVING的应用：通过id和差值的GROUP BY，用COUNT找到连续天数大于5天的id，注意COUNT不是一定要出现在SELECT后，可以直接用在HAVING中
5. 给定数字的频率查询中位数
难度困难。
Numbers 表保存数字的值及其频率。
在此表中，数字为 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3，所以中位数是 (0 + 0) / 2 = 0。
请编写一个查询来查找所有数字的中位数并将结果命名为 median 。