#開窗函數(shù)課堂練習

#課堂練習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 ;