oracle分析函数——over

逃离我推掉我的手 2021-06-24 14:34 572阅读 0赞

原文见我得博客：[点击打开链接][Link 1]

1、分析函数：

1）分析函数：

Oracle从8.1.6开始提供分析函数，专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。分析函数用于计算基于组的某种聚合值。

它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。普通的聚合函数用group by分组，每个分组返回一个统计值；而分析函数采用partitionby分组，并且每组每行都可以返回一个统计值。

2）开窗函数：

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。例如**over**函数

3）分析函数的形式

分析函数带有一个开窗函数over()，在窗口函数中包含三个分析子句:分组(partitionby), 排序(order by), 窗口(rows) ，他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。

注：窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提。

例如：统计函数+over()、排序函数+over()、数据分布函数+over()、统计分析函数+over()。

2、开窗函数：

1）over(order by col) ：可以理解为按照col列排序进行**累计**，orderby是个默认的开窗函数

SQL> select \*from t\_over;

\----------

10 rows selected.

SQL> selecta,avg(a)over(order by a),sum(a)over(order by a) from t\_over;

A AVG(A)OVER(ORDERBYA) SUM(A)OVER(ORDERBYA)

\------------------------------ --------------------

1                      1                   1

2                   1.75                   7

3                      2                  10

4             2.33333333                  14

5             2.71428571                  19

6                  3.125                  25

7             3.55555556                  32

9                    4.1                  41

10 rows selected.

2）分区（partition by col）：按照col进行分区统计

SQL> selecta,sum(a)over(partition by a) from t\_over;

A SUM(A)OVER(PARTITIONBYA)

\----------------------------------

1                          1

2                          6

3                          3

4                          4

5                          5

6                          6

7                          7

9                          9

10 rows selected.

3）范围over（order by salary range between 5preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的。

SQL> selecta,sum(a)over(order by a range between 2 preceding and 2 following) from t\_over;

ASUM(A)OVER(ORDERBYARANGEBETWEEN2PRECEDINGAND2FOLLOWING)

\-----------------------------------------------------------------

1                                                        10         --  1减2加2范围是\-1到3，a在这个范围内有1、2、2、2、3，所以是10

2                                                        14

3                                                        19

4                                                        24

5                                                        25       --  5减2加2范围是3到7，a在这个范围内有3、4、5、6、7，所以是25

6                                                        22

7                                                        27

9                                                        16       --  9减2加2范围是7到11，a在这个范围内有7、9，所以是16

10 rows selected.

4）范围over（order by salary rows between 5preceding and 5 following）：窗口范围为当前行前后各移动5行。

SQL> selecta,sum(a)over(order by a rows between 2 preceding and 2 following) from t\_over;

ASUM(A)OVER(ORDERBYAROWSBETWEEN2PRECEDINGAND2FOLLOWING)

\----------------------------------------------------------------

1                                                        5

2                                                        7

2                                                       10

2                                                       13

3                                                       16

4                                                       20

5                                                       25

6                                                       31

7                                                       27

9                                                       22

10 rows selected.

5）可以混合使用：

SELECT E.DEPTNO,

E.EMPNO,

E.ENAME,

E.SAL,

LAST\_VALUE(E.SAL)

OVER(PARTITION BY E.DEPTNO

ORDER BY E.SAL

ROWS

--unbounded preceding and unbounedfollowing针对当前所有记录的前一条、后一条记录，也就是表中的所有记录

--unbounded：不受控制的，无限的

--preceding：在...之前

--following：在...之后

BETWEEN UNBOUNDED PRECEDING ANDUNBOUNDED FOLLOWING) MAX\_SAL

FROM EMP E;

3、常见分析函数：

• 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）Row\_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。例如：row\_number()over(partition by col1 orderby col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。

与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码。row\_number()和rownum差不多，功能更强一点（可以在各个分组内从1开始排序）。

2）Dense\_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。

3）Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

SQL> select region\_id, customer\_id, sum(customer\_sales) total,

2         rank() over(order by sum(customer\_sales) desc) rank,

3         dense\_rank() over(order by sum(customer\_sales) desc) dense\_rank,

4         row\_number() over(order by sum(customer\_sales) desc) row\_number

5    from user\_order

6   group by region\_id, customer\_id;

REGION\_ID CUSTOMER\_ID      TOTAL       RANK DENSE\_RANK ROW\_NUMBER

\---------- ----------- ---------- ---------- ---------- ----------

8          18                1253840         11         11         11