#開(kāi)窗函數(shù)課堂練習(xí)

#課堂練習(xí)1 查詢每一天的累計(jì)3日訂單金額總和,即計(jì)算當(dāng)日及前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 當(dāng)前日及前2日金額總和

from order_tab;

#課堂練習(xí)2 查詢每個(gè)用戶按照訂單順序,計(jì)算當(dāng)前行的前一行到后一行的平均訂單金額

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 當(dāng)前行的前一行到后一行的平均訂單金額

from order_tab ;

#課堂練習(xí)3 查詢每個(gè)用戶按照訂單順序,計(jì)算當(dāng)前行及前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 當(dāng)前行的前一行到后一行的平均訂單金額

from order_tab ;