文章目录
- 一、窗口函数
- 1、 窗口范围分为:基于值和基于行两种(用between and指定范围)。
- 1-1、基于行:unbounded preceding从第一行开始(负无穷),[num] preceding从前num行开始,current row从当前行开始,[num] followwing后的num行开始;[num] preceding 前num行结束,current tow当前行结束,[num] following后num行结束,unbounded following至末尾结束(正无穷);
- 1-2、基于值:`order by 排序时只能用整数型`,between [num1] preceding and [num2] preceding 基于范围时:取(当前值-num1到当前值+num2)
- 2、缺省
- 二、常用窗口函数
一、窗口函数
定义:能为每行数据划分一个窗口,然后对窗口范围内的数据进行计算,最后将计算结果给该行数据。
语法:select * ,函数(amount) over(窗口范围) total_amount from order_info;
1、 窗口范围分为:基于值和基于行两种(用between and指定范围)。
1-1、基于行:unbounded preceding从第一行开始(负无穷),[num] preceding从前num行开始,current row从当前行开始,[num] followwing后的num行开始;[num] preceding 前num行结束,current tow当前行结束,[num] following后num行结束,unbounded following至末尾结束(正无穷);
1-2、基于值:order by 排序时只能用整数型
,between [num1] preceding and [num2] preceding 基于范围时:取(当前值-num1到当前值+num2)
2、缺省
over()中的三个部分内容partition by 、 order by 、(row|range)between…and…均可省略不写。
2-1、partition by 省略不写
1. 表示不分区
2-2、order by 省略不写
1.表示不排序
2.通常rows方法都会用order by排序
3.当rows的范围是从第一行到最后一行,通常才会省略
4.当range方法没有用order by时,无法确定划分区域,后面的between...and无效,相当于从第一行到最后一行
2-3、(rows|range)between…and…省略不写
则使用其默认值,默认值下:
1、若over()包含order by ,则默认值为:
range between unbounded preceding and current row
2、 若over()中不包含order by,则默认值为
row between unbounded preceding and unbounded follwing
二、常用窗口函数
按照功能,常用窗口可划分为以下几类:聚合函数、跨行取值函数、排名函数。
1、聚合函数
1-1、max:最大值
求指定范围窗口中的最大值
1-2、 min:最小值
求指定范围窗口中的最小值
1-3、 sum:求和
求指定范围窗口中的某字段相加之和
1-4、 avg:平均值
求指定范围窗口中的字段平均值
1-5、couont:计数
求指定范围窗口中的字段记录进行计数
2、跨行取值函数
2-1、lag和lead
注:lag和lead不支持指定自定义窗口
功能:获取当前行的上/下边某行、某字段的值
语法:lag/lead(字段名,num,默认值) over(范围)
例:lag(order_date,1,‘2003-08-21’) over(partition by usser_id order_date) last_date
根据用户id分区,订单日期升序排序,取前一条的订单日期,默认值为‘2003-08-21’重新命名为last_date
2-2、first_value和last_value
可以窗口自定义
功能:获取第一个值/最后一 个值
语法:first_value(字段名,布尔值) over(范围)
布尔值的值为true:不跳过Null值,直接返回
布尔类型的值为false:当第一条记录这个字段为Null时,往下一条记录继续找,找到为止
例:first_value(order_date,false) over(partition by user_id order by order_date) first_date
`根据用户id分区,订单日期升序排序,取第一行数据的订单日期字段,找不到则返回null`
5、排名函数
常用排名函数:rank()、dense_rank()、row_number()
功能:计算排名
语法:rank() over(窗口范围)
例:rank() over(partition by course order by score desc ) rk
根据课程分组,成绩升序排序,然后成绩排名
5-1、rank()
当遇到相同结果时,返回并列排序结果,第三位是3
5-2、dense_rank()
当遇到相同结果时,返回并列排序结果,第三位是2
5-3、row _number()
当遇到相同结果时,随机排出1,2,3
6、列表函数
常用与列表有关的窗口函数函数:collect_list()、concat_ws()
6-1、collect_list()
功能:将某个范围内容写进列表
语法1:collect_list(存入字段名) over(窗口范围)
例:collect_list(provinceids) over(partition by regionid) provinceids
根据地区id分组后,取出范围内的每个省份名称放到provinceids中
6-2、concat_ws()
功能:将列表中的数据用指定字符分隔提取出来
语法:concat_ws(分隔字符串,提取列表名)
例:concat_ws(“,”,provinceids) provinceids
将provinceids列表中的数据通过,分隔读取出来,放到provinceids字段中