20、Hive 实战 - Hive的函数之窗口函数

1. 窗口函数概述

1.1. 什么是窗口函数

• 简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。
• 开窗函数一般就是说的是over（）函数，其窗口是由一个 OVER 子句 定义的多行记录
• 开窗函数一般分为两类,聚合开窗函数和排序开窗函数。

1.2. 窗口函数的功能

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名> [rows between ?? and ???])

• 同时具有分组和排序的功能
• 不减少原表的行数

1.3. 窗口函数的种类和位置

• 专用窗口函数，包括rank, dense_rank, row_number等专用窗口函数
• 聚合函数，如sum(). avg(), count(), max(), min()等,rows between…and…
• 因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中

1.4. 窗口函数说明

• over()：是窗口函数的核心，需要开窗的全部都需要使用这个函数
• partition by：用来对表分组，后续跟上分组的字段，会给该字段中每个不同的值开一个窗（可以添加多个字段）
• order by：是对分组后的结果进行排序，默认是按照升序（asc）排列，可以进行多个字段降序
• rows between ?? and ???：用来对窗口中已经排序完成后的上下数据进行筛选

2. 聚合窗口函数

聚合窗口函数就是在通过over开窗的情况下，使用sum、max等聚合函数对开窗之后的结果进行聚合汇总；跟普通的聚合函数不同，普通聚合函数只会生成一条结果数据，聚合窗口函数会在每一行中都新增一列，用了存储这聚合结果，这个窗口有多少条数据，那就会有多少条一模一样的结果数据。

原始数据如下表所示（只列举出了部分数据）：

SQL编写和查询结果展示：

select
    user_id
    , third_cat_id as cat_id
    , score
    , max(score) over(partition by user_id) max_score_of_user
    , min(score) over(partition by user_id) min_score_of_user
    , count(score) over(partition by user_id) cnt_score_of_user
    , avg(score) over(partition by user_id) avg_score_of_user
    , sum(score) over(partition by user_id) sum_score_of_user
from yishou_recommendation_system.user_third_category_score_15day

3. 排序窗口函数

3.1. 不同排序窗口函数的区别

• rank()：如果有并列名次的行，会占用下一名次的位置，排序结果是 1,1,3
• dense_rank()：如果有并列名次的行，不占用下一名次的位置，排序结果是 1,1,2
• row_number()：不考虑并列名次的情况，排序结果是 1,2,3

3.2. SQL编写和查询结果展示

select
    user_id
    , third_cat_id as cat_id
    , score
    , rank() over(partition by user_id order by score) score_rank_of_user
    , dense_rank() over(partition by user_id order by score) score_dense_rank_of_user
    , row_number() over(partition by user_id order by score) score_row_number_of_user
from yishou_recommendation_system.user_third_category_score_15day

4. 排序截取数据函数

4.1. 排序截取数据函数的区别

• lag(col,n,default_val)：获取往前第n行数据，col是列名，n是往上的行数，当第n行为null的时候取default_val
• lead(col,n, default_val)：往后第n行数据，col是列名，n是往下的行数，当第n行为null的时候取default_val
• ntile(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。
• cume_dist（），计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。

4.2. 原始数据

4.3. lag和lead函数的使用和结果

select
    user_id
    , record_date
    , lag(record_date, 1, '1970-01-01 00:00:00') over(partition by user_id order by record_date) as start_date
    , lead(record_date, 1, '9999-99-99 00:00:00') over(partition by user_id order by record_date) as end_date
from user_table

使用user_id分组，并使用record_date进行升序排序；lag函数会获取之前一行的record_date数据作为start_date字段的值，并回使用 '1970-01-01 00:00:00' 作为默认值；lead函数会获取之后一行的record_date数据作为end_date字段的值，并回使用 '9999-99-99 00:00:00' 作为默认值；结果如下所示：

具体赋值如下图所示：

5. 排序限制分区函数

5.1. 各排序限制分区函数说明

• rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量；
• OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化；
• current row：当前行；
• n preceding：往前n行数据；
• n following：往后n行数据；
• unbounded preceding：该窗口数据的起点；
• unbounded following：该窗口数据的终点；

5.2. SQL编写和结果展示

select
    user_id
    , third_cat_id as cat_id
    , score
    
    -- 以user_id分组，按照score进行升序排序
    , row_number() over(partition by user_id order by score) as row_number_id
    
    -- 以user_id分组，按照score进行升序排序，求该窗口中第一行到当前行的和
    , sum(score) over(partition by user_id order by score rows between unbounded preceding and current row) sum_1
    
    -- 以user_id分组，按照score进行升序排序，求该窗口中之前一行到当前行的和
    , sum(score) over(partition by user_id order by score rows between 1 preceding and current row) sum_2
    
    -- 以user_id分组，按照score进行升序排序，求该窗口中当前行到之后一行的和
    , sum(score) over(partition by user_id order by score rows between current row and 1 following) sum_3
    
    -- 以user_id分组，按照score进行升序排序，求该窗口中当前行到最后一行的和
    , sum(score) over(partition by user_id order by score rows between current row and unbounded following) sum_3
    
from yishou_recommendation_system.user_third_category_score_15day