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