hive 开窗函数OVER(PARTITION)详解（一）

村口蹲点的阿三

已于 2024-10-16 09:49:49 修改

阅读量2.8k

点赞数

分类专栏： hive-sql语法文章标签： hive big data sql 大数据

于 2021-09-23 11:01:00 首次发布

本文链接：https://blog.csdn.net/liuya19921123/article/details/120430584

版权

hive-sql语法专栏收录该内容

16 篇文章 2 订阅

订阅专栏

本文详细介绍了Hive中的窗口函数，它在SQL2003标准中引入，允许对数据进行复杂分析。窗口函数通过OVER关键字定义，可选择PARTITION BY进行分区，ORDER BY进行排序，并定义ROWS或RANGE来指定数据范围。与GROUP BY不同，窗口函数不改变原有数据行，而是为每一行计算结果。Hive中窗口函数的执行分为两个阶段，首先完成其他运算，然后计算窗口函数。文章后续将探讨Hive中具体的窗口函数及应用。

摘要由CSDN通过智能技术生成

什么是窗口函数？

窗口函数（Window Function）是 SQL2003 标准中定义的一项新特性，并在 SQL2011、SQL2016 中又加以完善，添加了若干处拓展。窗口函数不同于我们熟悉的普通函数和聚合函数，它为每行数据进行一次计算：输入多行（一个窗口）、返回一个值。在报表等分析型查询中，窗口函数能优雅地表达某些需求，发挥不可替代的作用。

窗口函数出现在 SELECT 子句的表达式列表中，它最显著的特点就是 OVER 关键字。语法定义如下：

window_function (expression) OVER ( 
        [ PARTITION BY part_list ] 
        [ ORDER BY order_list ] 
        [ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] 
)

其中包括以下可选项：

PARTITION BY 表示将数据先按 part_list 进行分区
ORDER BY 表示将各个分区内的数据按 order_list 进行排序

最后一项表示 Frame 的定义，即：当前窗口包含哪些数据？

ROWS 选择前后几行，例如 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING 表示往前 3 行到往后 3 行，一共 7 行数据（或小于 7 行，如果碰到了边界）
RANGE 选择数据范围，例如 RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING 表示所有值在 [c−3,c+3][c−3,c+3] 这个范围内的行，cc 为当前行的值

通俗演绎frame的范围：

逻辑语义上说，一个窗口函数的计算“过程”如下：

按窗口定义，将所有输入数据分区、再排序（如果需要的话）
对每一行数据，计算它的 Frame 范围
将 Frame 内的行集合输入窗口函数，计算结果填入当前行

窗口函数 VS. 聚合函数

从聚合这个意义上出发，似乎窗口函数和 Group By 聚合函数都能做到同样的事情。但是，它们之间的相似点也仅限于此了！这其中的关键区别在于：窗口函数仅仅只会将结果附加到当前的结果上，它不会对已有的行或列做任何修改。而 Group By 的做法完全不同：对于各个 Group 它仅仅会保留一行聚合结果。

有的读者可能会问，加了窗口函数之后返回结果的顺序明显发生了变化，这不算一种修改吗？因为 SQL 及关系代数都是以 multi-set 为基础定义的，结果集本身并没有顺序可言，ORDER BY 仅仅是最终呈现结果的顺序。

另一方面，从逻辑语义上说，SELECT 语句的各个部分可以看作是按以下顺序“执行”的：

注意到窗口函数的求值仅仅位于 ORDER BY 之前，而位于 SQL 的绝大部分之后。这也和窗口函数只附加、不修改的语义是呼应的——结果集在此时已经确定好了，再依此计算窗口函数。

窗口函数的执行

窗口函数经典的执行方式分为排序和函数求值这 2 步。

可以看出ptf的执行是在group by执行完成后，在输出的结果集再进行分组排序计算的过程，可以看执行计划，测试语句：

explain 
select cdate 
    ,pname 
    ,sum(rm) as dp_rm_num
    ,sum(sum(rm))over(partition by pname         
         order by cdate range between 1 preceding and current row) as p_rm_num1             
    ,avg(sum(rm))over(partition by pname          
         order by cdate range between 1 preceding and current row) as p_rm_num2 from ptf_over_test 
group by cdate ,pname

执行计划过程：

考虑以上代码，在Hive中具体实现主要有两个阶段：

stage-1

计算除窗口函数以外所有的其他运算，如：group by，join ，having等。上面的代码的第一阶段即为：

select cdate,pname,sum(rm) as dp_rm_num
from ptf_over_test
group by cdate,pname

stage-2

将上一步的输出作为 WindowingTableFunction 函数的输入，计算对应的窗口函数值。上面代码的第一阶段即为：

select cdate,pname,dp_rm_num,p_rm_num1,p_rm_num2 from 
WindowingTableFunction(
-- 上一阶段的输出
<select cdate,pname,sum(rm) as dp_rm_num
   from ptf_over_test
   group by cdate,pname>,
-- 窗口函数的分区list
partition by pname,
-- 窗口函数的order list
order by cdate,
-- 窗口函数调用
[r:<sum()>, dr:<avg()>]
)

后面会继续介绍hive目前有哪些常用的开窗函数与用法