#開窗函數(shù)默認(rèn)情況:

##1,當(dāng)over中沒有指定分區(qū)、排序和滑動(dòng)窗口范圍時(shí),表中所有記錄為一個(gè)區(qū),默認(rèn)計(jì)算分區(qū)內(nèi)的所有記錄

select *,sum(amount) over() as 訂單總金額 from order_tab;

select *,sum(amount) as 訂單總金額 from order_tab;

##2,當(dāng)over中指定了分區(qū),但是沒有指定排序和滑動(dòng)窗口范圍時(shí),默認(rèn)計(jì)算當(dāng)前分區(qū)內(nèi)的所有記錄

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,當(dāng)over中指定了分區(qū),排序，但是沒有指定滑動(dòng)窗口范圍時(shí)，默認(rèn)基于排序字段值范圍來計(jì)算（當(dāng)前分區(qū)內(nèi)的第一行到當(dāng)前行(排序字段)取值范圍內(nèi)的記錄）

select user_no, create_date,sum(amount) over (partition by user_no order by create_date ) as 累計(jì)金額 from order_tab;

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

##4 ,指定分區(qū),排序, 帶上滑動(dòng)窗口范圍.

##例如:計(jì)算當(dāng)日及前后日金額總和

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 當(dāng)前日及前后日金額總和

from order_tab;