hive 窗口函数_Hive 的窗口函数

本文首发于：微信公众号【大数据每日哔哔，文章：Hive SQL 窗口函数
在 SQL 中有一类函数叫做聚合函数，例如 sum()、avg()、max()、min() 等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲聚集后的行数是要少于聚集前的行数的。但是，有时候我们既要显示聚集前的数据，又要显示聚集后的数据，此时我们便引入了窗口函数。窗口函数主要用于 OLAP 数据分析。

在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by子句之前。

| 窗口函数 | 描述 | | :--- | :--- | | LAG() | LAG()窗口函数返回分区中当前行之前行（可以指定第几行）的值。 如果没有行，则返回null。 | | LEAD() | LEAD()窗口函数返回分区中当前行后面行（可以指定第几行）的值。 如果没有行，则返回null。 | | FIRST_VALUE | FIRST_VALUE窗口函数返回相对于窗口中第一行的指定列的值。 | | LAST_VALUE | LAST_VALUE窗口函数返回相对于窗口中最后一行的指定列的值。 |

LAG 和 LEAD 的用法：

LAG 

FIRST_VALUE 和 LAST_VALUE 的用法：

FIRST_VALUE 

下面举个例子，数据集如下：

hive

LAG(col,n,default)

与 partitioned by 结合使用，返回当前分区中，当前行之前的第 n 行对应的值。如果没有则默认换回 null。第一个参数为列名，第二个参数为当前行之前第n行（可选，默认为1），第三个参数为缺失时默认值（当前行之前第n行为NULL没有时，返回该默认值，如不指定，则为NULL）。

为了比较每个用户浏览次数与前一天的浏览次数进行比较，查询返回当前浏览次数以及前一天的浏览数量。由于在2019-02-10之前没有浏览行为，前一天的浏览次数设置为0（不设置默认为NULL）。

hive

LEAD(col,n,default)

与 LAG 函数相反。

FIRST_VALUE(col,布尔值)

第一个参数是需要第一个值的列，第二个（可选）参数必须是默认为false的布尔值。如果设置为true，则跳过空值。

hive

LAST_VALUE(col,布尔值)

与 FIRST_VALUE() 函数相反，这里就不进行演示了。

over子句

官方 OVER子句 包括几个部分：

• 聚合函数（count, sum, min, max, avg）
• OVER 子句
• PARTITION BY 子句
• ORDER BY 子句
• WINDOW 子句

结合具体的业务场景，SQL 语句如下：

---1)201504月份的销售额

windows子句

带有窗口规范的OVER子句。窗口可以在WINDOW子句中单独定义。窗口规范支持如下格式：

| 关键字 | 说明 | | --- | --- | | PRECEDING | 表示当前行之前的行 | | UNBOUNDED PRECEDING | 表示当前行之前无边界行，即第一行 | | num PRECEDING | 表示当前行之前第num行 | | CURRENT ROW | 表示当前行 | | FOLLOWING | 表示当前行后面的行 | | UNBOUNDED FOLLOWING | 表示当前行后面无边界行，即最后一行 | | num FOLLOWING | 表示当前行后面第num行 |

当缺少WINDOW子句并指定使用ORDER BY时，窗口规范默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即从第一行到当前行。
当缺少ORDER BY和WINDOW子句时，窗口规范默认为ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，即第一行到最后一行。

参考

• Windowing and Analytics Functions
• HiveQL的窗口函数

订阅