oracle分析函数——over

oracle分析函数——over

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;

A

----------

1

2

2

2

3

4

5

6

7

9

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

2                   1.75                   7

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

2                          6

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

2                                                        14

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

5           2                 1224992         12         12         12

9          23                1224992         12         12         13

9          24                1224992         12         12         14

10          30               1216858         15           13            15

30 rows selected.

first_value() 和last_value()：

FIRST_VALUE 返回组中数据窗口的第一个值 ；LAST_VALUE   返回组中数据窗口的最后一个值。

lag()和lead（）：

Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。

语法：lag(exp_str,offset,defval) over()

exp_str 是要做对比的字段；offset 是exp_str字段的偏移量 比如说 offset 为2 则 拿exp_str的第一行和第三行对比，第二行和第四行，依次类推，offset的默认值为1！defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。