#開窗函數默認情況:

##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;