#开窗函数课堂练习

#课堂练习1 查询每一天的累计3日订单金额总和,即计算当日及前2日金额总和

select user_no,create_date,sum(amount)

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

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

from order_tab;

#课堂练习2 查询每个用户按照订单顺序,计算当前行的前一行到后一行的平均订单金额

select * from order_tab;

select user_no,order_id,amount,create_date,avg(amount)

over(partition by user_no order by create_date rows between 1 preceding and 1 following)

as 当前行的前一行到后一行的平均订单金额

from order_tab ;

#课堂练习3 查询每个用户按照订单顺序,计算当前行及前2行的平均订单金额

select * from order_tab;

select user_no,order_id,amount,create_date,avg(amount)

over(partition by user_no order by create_date rows between 2 preceding and 0 following)

as 当前行的前一行到后一行的平均订单金额

from order_tab ;