Hive（20）：Hive的函数之窗口函数

目录

0. 相关文章链接

1. 窗口函数概述

1.1. 什么是窗口函数

1.2. 窗口函数的功能

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

1.4. 窗口函数说明

2. 聚合窗口函数

3. 排序窗口函数

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

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

4. 排序截取数据函数

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

4.2. 原始数据

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

5. 排序限制分区函数

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

5.2. SQL编写和结果展示

---

0. 相关文章链接

 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

---

注：其他Hive相关系列文章链接由此进 -> Hive文章汇总

---