oracle p over,Oracle 分析函數Over(partition by...)以及開窗函數

Over(Partition by...)  為Oracle特有函數，用於計算基於組的某種聚合值。

它和聚合函數的不同之處是：對於每個組返回多行，而聚合函數對於每個組只返回一行。

說明

partition by： 按哪個字段划分組；

order by ：按哪個字段排序；

常用：

row_number() over(partition by ... order by ...)

rank() over(partition by ... order by ...)

dense_rank() over(partition by ... order by ...)

count() over(partition by ... order by ...)

max() over(partition by ... order by ...)

min() over(partition by ... order by ...)

sum() over(partition by ... order by ...)

avg() over(partition by ... order by ...)

first_value() over(partition by ... order by ...)

last_value() over(partition by ... order by ...)

lag() over(partition by ... order by ...)

lead() over(partition by ... order by ...)

示例：

1. 查詢表中每個emptypeid中wages最高的個體(rank()的用法)。

一般的寫法：

select p.* from py_0325 p inner join(select max(wages) wages,emptypeid frompy_0325 pgroup by p.emptypeid ) des on p.emptypeid = des.emptypeid and p.wages = des.wages

用over()函數

select * from(selectp.empid,p.orgid,p.emptypeid,p.wages,

rank()over(partition by p.emptypeid order by p.wages desc) ranks frompy_0325 p

) deswhere des.ranks = 1

用dense_over()函數

select * from(selectp.empid,p.orgid,p.emptypeid,p.wages,

dense_rank()over(partition by p.emptypeid order by p.wages desc) ranks frompy_0325 p

) deswhere des.ranks = 1

2. 查詢表中每個emptypeid中wages最高的個體與最低個體的差額(max()和min()的用法)。

一般的寫法：

select des.maxwages - des.minwages chae,des.emptypeid from(select max(wages) maxwages,min(wages) minwages ,emptypeid frompy_0325 pgroup by p.emptypeid ) des

分析函數寫法：

select distinctp.emptypeid,max(p.wages) over(partition by p.emptypeid ) - min(p.wages) over(partition byp.emptypeid ) chaefrom py_0325 p

注意：要加order by 的話

MAX() OVER(PARTITION BY .. ORDER BY .. DESC) 排序規則只能為desc，否則不起作用，將查詢到目前為止排序值最高字段的對應值

MIN() OVER(PARTITION BY .. ORDER BY .. ASC ) 排序規則只能為asc，否則不起作用，將查詢到目前為止排序值最低的字段的對應值

3、lead()/lag() over(partition by ... order by ...) 取前面/后面第n行記錄

說明：

lead(列名,n,m): 當前記錄后面第n行記錄的的值，沒有則默認值為m；如果不帶參數n,m，則查找當前記錄后面第一行的記錄的值，沒有則默認值為null。

lag(列名,n,m): 當前記錄前面第n行記錄的的值，沒有則默認值為m；如果不帶參數n,m，則查找當前記錄前面第一行的記錄的值，沒有則默認值為null。

使用 lead() 和 lag() 時，必須要帶order by否則非法

舉例：

查詢emptypeid分組下個人工資比自己高一位，低一位的差額：

selectp.empid,p.orgid,p.emptypeid,p.wages,

lead(p.wages,1,0) over(partition by p.emptypeid order byp.wages )- lag(p.wages,1,0) over(partition by p.emptypeid order byp.wages ) chaefrom py_0325 p

4、FIRST_VALUE/LAST_VALUE() OVER(PARTITION BY ...) 取首尾記錄

selectp.empid,p.orgid,p.emptypeid,p.wages,

FIRST_VALUE(p.wages)over(partition by p.emptypeid order byp.wages ) FIRST_VALUE,

LAST_VALUE(p.wages)over(partition by p.emptypeid order byp.wages ) LAST_VALUEfrom py_0325 p

5、ROW_NUMBER() OVER(PARTITION BY.. ORDER BY ..) 排序(應用：分頁)

row_num() 和 rank() dense_rank()的區別：

row_num()  ： 1,2,3,4,5

rank() ：1,1,3,4,5

dense_rank()：1,1,2,3,4

函數舉例：

selectp.wages,

row_number()over(partition by p.emptypeid order by p.wages desc) numsfrom py_0325 p

6、sum/avg/count() over(partition by ..)

select p.*,sum(p.wages) over(partition byp.emptypeid ) sums,count(p.wages) over(partition byp.emptypeid ) counts,avg(p.wages) over(partition byp.emptypeid ) avgsfrom py_0325 p

計算表中最少條數之和是總條數之后50%的數據條數

select max(counts) from (

select pp.itemid,pp.salesprice,

row_number() over(order by pp.salesprice desc) counts,

sum(pp.salesprice) over(order by pp.salesprice desc)/sum(pp.salesprice) over() sums

from dict_item_charge pp )

where sums < 0.5

7、 rows/range between … preceding and … following 上下范圍內求值

說明：unbounded：不受控制的，無限的

preceding：在...之前

following：在...之后

rows between … preceding and … following

舉例1：獲取分部門工資最高值

selectp.empid,

p.orgid,

p.emptypeid,

p.wages,

--MAX(p.wages) OVER(PARTITION BY p.orgid) aa

last_value(p.wages)over(partition by p.orgid order byp.wages

rowsbetween unbounded preceding andunbounded following ) aafrom py_0325 p

舉例2：對各部門進行分組，並附帶顯示第一行至當前行的匯總

selectp.empid,

p.orgid,

p.emptypeid,

p.wages,sum(p.wages) over(partition by p.orgid order byp.empid

rowsbetween unbounded preceding and currentrow ) aafrom py_0325 p

示例3：當前行至最后一行的匯總

selectp.empid,

p.orgid,

p.emptypeid,

p.wages,sum(p.wages) over(partition by p.orgid order byp.empid

rowsbetween current row andunbounded following) aafrom py_0325 p

示例4：當前行的上一行(rownum-1)到當前行的匯總

selectp.empid,

p.orgid,

p.emptypeid,

p.wages,sum(p.wages) over(partition by p.orgid order byp.empid

rowsbetween 1 preceding and currentrow) aafrom py_0325 p

示例5：當前行的上一行(rownum-1)到當前行的下兩行(rownum+2)的匯總

selectp.empid,

p.orgid,

p.emptypeid,

p.wages,sum(p.wages) over(partition by p.orgid order byp.empid

rowsbetween 1 preceding and 2following) aafrom py_0325 p

其他

1、NULLS FIRST/LAST 將空值字段記錄放到最前或最后顯示

說明：

通過RANK()、DENSE_RANK()、ROW_NUMBER()對記錄進行全排列、分組排列取值，但有時候，會遇到空值的情況，空值會影響得到的結果的正確性

NULLS FIRST/LAST 可以幫助我們在處理含有空值的排序排列中，將空值字段記錄放到最前或最后顯示，幫助我們得到期望的結果。

selectp.empid,

p.orgid,

p.emptypeid,

p.wages,

rank()over(partition by p.orgid order byp.wages nulls last ) aafrom py_0325 p

2、NTILE(n)

把數據分組等分，效果如下：