Hive sql 专用窗口函数

更新时间：2022-01-06 09:56:13 标签：hql 窗口函数 sql

提示

Hive SQL 教程编写中，使用过程中有任何建议，提供意见、建议、纠错、催更加微信 gairuo123。欢迎关注本教程作者新书《深入浅出Pandas：利用Python进行数据处理与分析》。作者开办 Python 数据分析培训，详情 Python 数据分析培训。

在理解了SQL 窗口计算的原理后，我们来了解一下 Hive SQL 专门的窗口函数。hive sql 的窗口函数功能是对 hive sql 的功能增强，确实目前用于离线数据分析逻辑日趋复杂，很多场景都需要用到。

窗口函数概览

Hive SQL 的专有窗口函数有：

序号:

rank() OVER([partition_by_clause] order_by_clause);
row_number() OVER([partition_by_clause] order_by_clause);
dense_rank() OVER([partition_by_clause] order_by_clause)

首尾值:

first_value(expr) OVER([partition_by_clause] order_by_clause [window_clause]);
last_value(expr) OVER([partition_by_clause] order_by_clause [window_clause])

移动:

lag(expr [, offset] [, default]) OVER ([partition_by_clause] order_by_clause);
lead(expr [, offset] [, default]) OVER([partition_by_clause] order_by_clause)

序号

关于排序后的序号显示功能有以下三个，它们都没有参数：

row_number()：不间断，序号不重复，如 1、2、3、4、5
rank()：间断，相同值同序号，如 1、1、3、4、5
dense_rank()：不间断，相同值同序号，如 1、1、2、3、3

下边我们看看案例。

row_number()

-- 按班级给语文成绩排名次
SELECT
    id,
    name,
    class,
    chinese,
    ROW_NUMBER() over(PARTITION by class ORDER by chinese DESC) as rnbr
FROM
    students
'''
id|class|chinese|rnbr|
--|-----|-------|----|
 6|    1|     99|   1|
 8|    1|     99|   2|
 1|    1|     77|   3|
 3|    1|     55|   4|
 2|    2|     99|   1|
 4|    2|     87|   2|
 7|    2|     66|   3|
 5|    3|     66|   1|
 9|    3|     66|   2|
'''

rank()

-- 按班级给语文成绩排名次
SELECT
    id,
    class,
    chinese,
    RANK() over(PARTITION by class ORDER by chinese DESC) as rnbr
FROM
    students
'''
id|class|chinese|rnbr|
--|-----|-------|----|
 6|    1|     99|   1|
 8|    1|     99|   1|
 1|    1|     77|   3|
 3|    1|     55|   4|
 2|    2|     99|   1|
 4|    2|     87|   2|
 7|    2|     66|   3|
 5|    3|     66|   1|
 9|    3|     66|   1|
'''

dense_rank()

-- 按班级给语文成绩排名次
SELECT
    id,
    class,
    chinese,
    DENSE_RANK() over(PARTITION by class ORDER by chinese DESC) as rnbr
FROM
    students
'''
id|class|chinese|rnbr|
--|-----|-------|----|
 6|    1|     99|   1|
 8|    1|     99|   1|
 1|    1|     77|   2|
 3|    1|     55|   3|
 2|    2|     99|   1|
 4|    2|     87|   2|
 7|    2|     66|   3|
 5|    3|     66|   1|
 9|    3|     66|   1|
'''

首尾值

first_value(expr)：分组内第一个值
last_value(expr)：分组内最后一个值

但是不是真正意义上的第一个或最后一个，而是截至到当前行的第一个或最后一个。

例如，显示班级分组中的第一个值：

SELECT
    id,
    class,
    chinese,
    first_value(chinese) over(PARTITION by class) as v
FROM
    students
'''
id|class|chinese|v |
--|-----|-------|--|
 1|    1|     77|77|
 3|    1|     55|77|
 6|    1|     99|77|
 8|    1|     99|77|
 2|    2|     99|99|
 4|    2|     87|99|
 7|    2|     66|99|
 5|    3|     66|66|
 9|    3|     66|66|
'''

偏移

偏移是一种数据处理技术，主要用于前后数据的对比，它有两个方法：

lag(expr [, offset] [, default]): 向下位移，意思是滞后
lead(expr [, offset] [, default]): 向上位移，意思是超前

它们有三个参数：

expr：移动对的数据列
[, offset]：可选，默认1，移动的位数，时间类型为时间单位
[, default]：可选，默认 null，首尾可能无值时的默认填充值

LAG

此函数使用前一行的列值返回表达式的值。指定整数偏移量，该偏移量将行位置指定为当前行之前的若干行。expr 表达式参数中的任何列引用都引用前一行中的列值。

例如，向下移动两位，无值的填充为 0：

SELECT
    id,
    class,
    chinese,
    lag(chinese, 2, 0) over(PARTITION by class) as v
FROM
    students
'''
id|class|chinese|v |
--|-----|-------|--|
 1|    1|     77| 0|
 3|    1|     55| 0|
 6|    1|     99|77|
 8|    1|     99|55|
 2|    2|     99| 0|
 4|    2|     87| 0|
 7|    2|     66|99|
 5|    3|     66| 0|
 9|    3|     66| 0|
'''

LEAD

此函数使用下一行的列值返回表达式的值。您指定一个整数偏移量，它指定一个行位置到当前行之后一定数量的行。 expr 表达式参数中的任何列引用都引用该后一行中的列值。

例如，向上移动1位（取默认），无值的填充为 null（默认）：

SELECT
    id,
    class,
    chinese,
    lead(chinese) over(PARTITION by class) as v
FROM
    students
'''
id|class|chinese|v |
--|-----|-------|--|
 1|    1|     77|55|
 3|    1|     55|99|
 6|    1|     99|99|
 8|    1|     99|  |
 2|    2|     99|87|
 4|    2|     87|66|
 7|    2|     66|  |
 5|    3|     66|66|
 9|    3|     66|  |
'''

再次强调的是，以上操作都在窗口内进行，不会影响其他窗口。

参考

参见官方介绍
https://www.jianshu.com/p/12eaf61cf6e1
https://acadgild.com/blog/windowing-functions-in-hive