Orcale 分析函數 OVER(PARTITION BY... ORDER BY...)的講解__OVER

OVER(PARTITION BY... ORDER BY...)的使用說明和意義

這個函數長這個樣子：OVER(PARTITION BY... ORDER BY...)

顧名思義，PARTITION 中文是分割的意思，ORDER 是排序的意思，所以翻譯一下就是先把一組資料按照制定的欄位進行分割成各種組，然後組內按照某個欄位排序。

以實際案例來說明這個函數的使用，

首先，我們先看一組普通排序的資料：

然後對其進行新加一個偽劣ROWNUM，看看資料原本的順序：

OVER(ORDER BY ...)的意思是以按照某個欄位排序，所以和直接對錶的order by效果是一樣的：

這裡就顯示了OVER(ORDER BY ...)的第一個功能，就是對新查詢得到的資料進行重新編號，即RNO的值，由於這裡沒有PARTITION BY...，所以可以比作整個列就是一個大塊，然後對大塊的內容進行排序，這個時候再加上PARTITION BY...，查詢結果：

它會按照制定的欄位，把相同值的排在一起，分為一個塊，也就是分組，然後組內排序編號，這樣你就可以取不同組內的任意第幾個值，類似於TOP-N的分析。

在這裡over之前使用的函數是ROW_NUMBER()，就是對資料的編號。

與OVER(PARTITION BY... ORDER BY...)匹配使用的函數 row_number() over()、rank() over()和dense_rank() over()

在上面的例子裡，使用ROW_NUMBER()可以對資料編號，但是有一個問題，例子中的MI_ID是不可以重複的，如果在可以重複的情況下，就有並列的情況，這樣就無法取出並列的資料，只能取單一排序的資料。所以這裡可以換成 rank() over()和dense_rank() over()來進行編號：（rank() over()和dense_rank() over()的區別如圖）

sum() over()，first_value() over()和last_value() over()的使用

sum() over()分組求和

first_value() over()求分組第一條

last_value() over()求分組最後一條

其中用row_number() over()取編號第一條的也可以實現first_value() over()的效果

SELECT DISTINCT *  FROM (SELECT T1.MI_ID,               T3.I_IDENTITY_CARD,               SUM(NVL(T2.IS_VISHEARTPROMEMBER, 0)) OVER(PARTITION BY T3.I_IDENTITY_CARD) PRO_NUM,               SUM(NVL(T2.IS_VISHEARTEXPMEMBER, 0)) OVER(PARTITION BY T3.I_IDENTITY_CARD) EXP_NUM,               SUM(NVL(T2.IS_VISHEARTPOLMEMBER, 0)) OVER(PARTITION BY T3.I_IDENTITY_CARD) POL_NUM, T1.CREATE_TIME,               FIRST_VALUE(T2.CREATE_DATE) OVER(PARTITION BY T3.I_IDENTITY_CARD ORDER BY T2.CREATE_DATE DESC) FIRST_CREATE_DATE          FROM T_ZZ_PETITIONERS T1          LEFT JOIN T_ZZ_VISIT_RECORD T2            ON T1.CI_RS_ID = T2.CI_RS_ID           AND T2.STATUS != '003'          LEFT JOIN T_DC_CI_RS_TOP T3            ON T1.CI_RS_ID = T3.CI_RS_ID           AND T3.STATUS = '1'         WHERE T1.IS_ADD_HEARTPROTECT = '1'           AND T1.STATUS = '1') C ORDER BY C.CREATE_TIME DESC

SELECT *  FROM (SELECT T1.MI_ID,               T3.I_IDENTITY_CARD,               SUM(NVL(T2.IS_VISHEARTPROMEMBER, 0)) OVER(PARTITION BY T3.I_IDENTITY_CARD) PRO_NUM,               SUM(NVL(T2.IS_VISHEARTEXPMEMBER, 0)) OVER(PARTITION BY T3.I_IDENTITY_CARD) EXP_NUM,               SUM(NVL(T2.IS_VISHEARTPOLMEMBER, 0)) OVER(PARTITION BY T3.I_IDENTITY_CARD) POL_NUM,               T1.CREATE_TIME,               ROW_NUMBER() OVER(PARTITION BY T3.I_IDENTITY_CARD ORDER BY T2.CREATE_DATE DESC) RNO          FROM T_ZZ_PETITIONERS T1          LEFT JOIN T_ZZ_VISIT_RECORD T2            ON T1.CI_RS_ID = T2.CI_RS_ID           AND T2.STATUS != '003'          LEFT JOIN T_DC_CI_RS_TOP T3            ON T1.CI_RS_ID = T3.CI_RS_ID          LEFT JOIN T_DC_GRID T4            ON T1.REGION_CODE = T4.INFO_ORG_CODE         WHERE T1.IS_ADD_HEARTPROTECT = '1'           AND T1.STATUS = '1'           AND T3.STATUS = '1'           AND T4.STATUS = '001') C WHERE C.RNO = 1 ORDER BY C.CREATE_TIME DESC

ignore nulls：過濾掉空值

常用的分析函數如下所列:

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 ...)