大数据学习（十八）hive的窗口函数（含例子）

阿齐（努力打工版）

已于 2022-10-17 09:46:29 修改

阅读量219

点赞数 1

文章标签：学习

于 2022-10-16 19:15:56 首次发布

本文链接：https://blog.csdn.net/weixin_44702289/article/details/127316363

版权

一、前言

窗口函数可真是个好帮手，能帮我们解决很多常见的业务场景，比如求连续天数、求每个班级数学学科分数最高、某地区各个品牌销售量最好的手机等等，只要是在按照特定设置的分组里面，去分析某个指标，比如最大、最小、平均、求和等各种聚合值。

二、窗口函数语法

基本语法：<分析函数> over ( partition by <用于分组的列名> order by <用于排序的列名> desc/asc rows between 开始行 and 结束行 )

分析函数：

partition by: 分区，分析函数是按照每一组的数据进行分析计算的

order by: 排序

desc/asc: desc降序 asc升序

rows between 开始位置 and 结束位置，指定数据范围；详见

rows between unbounded preceding and current row  -- 从窗口起点到当前行
rows between unbounded preceding and unbounded following -- 从窗口起点到窗口终点
rows between 2 preceding and 1 following -- 往前2行到往后1行
rows between 2 preceding and 1 current row -- 往前2行到当前行

三、案例

select 
     date
    ,prize_cost
    ,name
    ,sum(prize_cost) over (partition by name order by date asc rows between unbounded preceding and current row) as total_cost -- 一月当前累计收入  
from 
(
select '2022-01-01' as date,'10' as prize_cost,'小明' as name
union all
select '2022-01-02' as date,'20' as prize_cost,'小明' as name
union all
select '2022-01-03' as date,'30' as prize_cost,'小明' as name
union all
select '2022-01-04' as date,'40' as prize_cost,'小明' as name
union all
select '2022-01-01' as date,'10' as prize_cost,'小红' as name
union all
select '2022-01-02' as date,'10' as prize_cost,'小红' as name
union all
select '2022-01-03' as date,'20' as prize_cost,'小红' as name
)

查询结果：

   date         name          total_cost 
2022-01-01      小明            10.0
2022-01-02      小明            30.0
2022-01-03      小明            60.0
2022-01-04      小明            90.0
2022-01-01      小红            10.0
2022-01-02      小红            20.0
2022-01-03      小红            40.0