CDA持证人阿涛哥

2022-11-30   阅读量: 267

mysql学习35--开窗函数之mysql开窗函数默认情况

扫码加入数据分析学习群

#开窗函数默认情况:

##1,当over中没有指定分区、排序和滑动窗口范围时,表中所有记录为一个区,默认计算分区内的所有记录

select *,sum(amount) over() as 订单总金额 from order_tab;

select *,sum(amount) as 订单总金额 from order_tab;

##2,当over中指定了分区,但是没有指定排序和滑动窗口范围时,默认计算当前分区内的所有记录

select *,sum(amount) over(partition by user_no) as 订单总金额 from order_tab;

select *,sum(amount) over (partition by user_no) as 订单总金额 from order_tab;

select * from order_tab;

##3,当over中指定了分区,排序,但是没有指定滑动窗口范围时,默认基于排序字段值范围来计算(当前分区内的第一行到当前行(排序字段)取值范围内的记录)

select user_no, create_date,sum(amount) over (partition by user_no order by create_date ) as 累计金额 from order_tab;

select user_no,create_date, sum(amount) from order_tab group by create_date, user_no order by create_date;

##4 ,指定分区,排序, 带上滑动窗口范围.

##例如:计算当日及前后日金额总和

select user_no, create_date ,sum(amount)

over(partition by user_no order by create_date range between interval 1 day preceding and interval 1 day following )

as 当前日及前后日金额总和

from order_tab;


0.0000 0 0 关注作者 收藏

评论(0)


暂无数据

推荐课程