分析函数 OVER(PARTITION BY... ORDER BY...)的讲解

以你之姓@ 2021-09-14 21:08 387阅读 0赞

[http://blog.csdn.net/east\_mrchiu/article/details/70183428][http_blog.csdn.net_east_mrchiu_article_details_70183428]

这个函数长这个样子：OVER(PARTITION BY... ORDER BY...)

顾名思义，PARTITION 中文是分割的意思，ORDER 是排序的意思，所以翻译一下就是先把一组数据按照制定的字段进行分割成各种组，然后组内按照某个字段排序。

以实际案例来说明这个函数的使用，

首先，我们先看一组普通排序的数据：

![Center][]

然后对其进行新加一个伪劣ROWNUM，看看数据原本的顺序：

![Center 1][]

OVER(ORDER BY ...)的意思是以按照某个字段排序，所以和直接对表的order by效果是一样的：

![Center 2][]

这里就显示了OVER(ORDER BY ...)的第一个功能，就是对新查询得到的数据进行重新编号，即RNO的值，由于这里没有PARTITION BY...，所以可以比作整个列就是一个大块，然后对大块的内容进行排序，这个时候再加上PARTITION BY...，查询结果：

![Center 3][]

它会按照制定的字段，把相同值的排在一起，分为一个块，也就是分组，然后组内排序编号，这样你就可以取不同组内的任意第几个值，类似于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()的区别如图）

![Center 4][]

![Center 5][]

## sum() over()，first\_value() over()和last\_value() over()的使用 ##

sum() over()分组求和

first\_value() over()求分组第一条

last\_value() over()求分组最后一条

其中用row\_number() over()取编号第一条的也可以实现first\_value() over()的效果

\[sql\]  [view plain][]  [copy][view plain]

1.  SELECT DISTINCT \*  
2.    FROM (SELECT T1.MI\_ID,  
3.                 T3.I\_IDENTITY\_CARD,  
4.                 SUM(NVL(T2.IS\_VISHEARTPROMEMBER, 0)) OVER(PARTITION BY T3.I\_IDENTITY\_CARD) PRO\_NUM,  
5.                 SUM(NVL(T2.IS\_VISHEARTEXPMEMBER, 0)) OVER(PARTITION BY T3.I\_IDENTITY\_CARD) EXP\_NUM,  
6.                 SUM(NVL(T2.IS\_VISHEARTPOLMEMBER, 0)) OVER(PARTITION BY T3.I\_IDENTITY\_CARD) POL\_NUM,  
7.                               T1.CREATE\_TIME,  
8.                 <span style="color:\#ff0000;">FIRST\_VALUE</span>(T2.CREATE\_DATE) OVER(PARTITION BY T3.I\_IDENTITY\_CARD ORDER BY T2.CREATE\_DATE DESC) FIRST\_CREATE\_DATE  
9.            FROM T\_ZZ\_PETITIONERS T1  
10.           LEFT JOIN T\_ZZ\_VISIT\_RECORD T2  
11.             ON T1.CI\_RS\_ID = T2.CI\_RS\_ID  
12.            AND T2.STATUS != '003'  
13.           LEFT JOIN T\_DC\_CI\_RS\_TOP T3  
14.             ON T1.CI\_RS\_ID = T3.CI\_RS\_ID  
15.            AND T3.STATUS = '1'  
16.          WHERE T1.IS\_ADD\_HEARTPROTECT = '1'  
17.            AND T1.STATUS = '1') C  
18.  ORDER BY C.CREATE\_TIME DESC

![Center 6][]

\[sql\]  [view plain][]  [copy][view plain]

1.  SELECT \*  
2.    FROM (SELECT T1.MI\_ID,  
3.                 T3.I\_IDENTITY\_CARD,  
4.                 SUM(NVL(T2.IS\_VISHEARTPROMEMBER, 0)) OVER(PARTITION BY T3.I\_IDENTITY\_CARD) PRO\_NUM,  
5.                 SUM(NVL(T2.IS\_VISHEARTEXPMEMBER, 0)) OVER(PARTITION BY T3.I\_IDENTITY\_CARD) EXP\_NUM,  
6.                 SUM(NVL(T2.IS\_VISHEARTPOLMEMBER, 0)) OVER(PARTITION BY T3.I\_IDENTITY\_CARD) POL\_NUM,  
7.                 T1.CREATE\_TIME,  
8.                 <span style="color:\#ff0000;">ROW\_NUMBER()</span> OVER(PARTITION BY T3.I\_IDENTITY\_CARD ORDER BY T2.CREATE\_DATE DESC) RNO  
9.            FROM T\_ZZ\_PETITIONERS T1  
10.           LEFT JOIN T\_ZZ\_VISIT\_RECORD T2  
11.             ON T1.CI\_RS\_ID = T2.CI\_RS\_ID  
12.            AND T2.STATUS != '003'  
13.           LEFT JOIN T\_DC\_CI\_RS\_TOP T3  
14.             ON T1.CI\_RS\_ID = T3.CI\_RS\_ID  
15.           LEFT JOIN T\_DC\_GRID T4  
16.             ON T1.REGION\_CODE = T4.INFO\_ORG\_CODE  
17.          WHERE T1.IS\_ADD\_HEARTPROTECT = '1'  
18.            AND T1.STATUS = '1'  
19.            AND T3.STATUS = '1'  
20.            AND T4.STATUS = '001') C  
21.  WHERE <span style="color:\#ff0000;">C.RNO = 1</span>  
22.  ORDER BY C.CREATE\_TIME DESC

![Center 7][]

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

[http_blog.csdn.net_east_mrchiu_article_details_70183428]: http://blog.csdn.net/east_mrchiu/article/details/70183428http://
[Center]: /images/20210527/a1233ca1098d454393e17272b3ed9318.png
[Center 1]: /images/20210527/5eb384e8d32640078893942df6d860d8.png
[Center 2]: /images/20210527/f9bb7a36b81b4a11aaa4e7e887d0f441.png
[Center 3]: /images/20210527/eca334d1e1ec4f77808cebd43d0c1882.png
[Center 4]: /images/20210527/960dd4cc62df497e9111bf90dc86d4e1.png
[Center 5]: /images/20210527/e79ffaf4a6b4413b813f8aa925253f91.png
[view plain]: http://blog.csdn.net/east_mrchiu/article/details/70183428#
[Center 6]: /images/20210527/a07ff97b39f24b88a755f725091fb7e1.png
[Center 7]: /images/20210527/e18c76b9655741b685eddf29b1af4b69.png