oracle分析函数——over

最新推荐文章于 2022-03-04 16:25:10 发布

赶路人儿

最新推荐文章于 2022-03-04 16:25:10 发布

阅读量863

点赞数

分类专栏：数据库-oracle 文章标签： oracle 分析函数 over

本文链接：https://blog.csdn.net/liuxiao723846/article/details/49020801

版权

数据库-oracle 专栏收录该内容

52 篇文章 2 订阅

订阅专栏

原文见我得博客：点击打开链接

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