立即注册 登录
度量快速开发平台-业界最好的全功能软件开发平台 返回首页

王爱东的个人空间 http://bbs.delit.cn/?3 [收藏] [复制] [RSS]

日志

Oracle分析函数使用介绍

已有 846 次阅读2016-8-13 15:11 |个人分类:数据库技术|系统分类:数据库技术


使用评级函数

评级函数(ranking function)用于计算等级、百分点、n分片等等,下面是几个常用到的评级函数:

RANK():返回数据项在分组中的排名。特点:在排名相等的情况下会在名次中留下空位

DENSE_RANK():RANK不同的是它在排名相等的情况下不会在名次中留下空位

CUME_DIST():返回特定值相对于一组值的位置:他是“cumulative distribution(累积分布)的简写

PERCENT_RANK():返回某个值相对于一组值的百分比排名

NTILE():返回n分片后的值,比如三分片、四分片等等

ROW_NUMBER():为每一条分组纪录返回一个数字

下面我们分别举例来说明这些函数的使用

1RANK()与DENSE-RANK()

首先显示下我们的源表数据的结构及部分数据:

SQL> desc all_sales;

名称 是否为空? 类型

----------------------------------------- -------- -----------

YEAR NOT NULL NUMBER(38)

MONTH NOT NULL NUMBER(38)

PRD_TYPE_ID NOT NULL NUMBER(38)

EMP_ID NOT NULL NUMBER(38)

AMOUNT NUMBER(8,2)

SQL> select * from all_sales where rownum<11;

 

YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT

---------- ---------- ----------- ---------- ----------

2003 1 1 21 10034.84

2003 2 1 21 15144.65

2003 3 1 21 20137.83

2003 4 1 21 25057.45

2003 5 1 21 17214.56

2003 6 1 21 15564.64

2003 7 1 21 12654.84

2003 8 1 21 17434.82

2003 9 1 21 19854.57

2003 10 1 21 21754.19

 

已选择10行。

好接下来我们将举例来说明上述函数的使用:首先是RANK()与DENSE-RANK()的使用:

SQL> select

2 prd_type_id,sum(amount),

3 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank,

4 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense_rank

5 from all_sales

6 where year=2003

7 group by prd_type_id

8 order by rank;

PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK

----------- ----------- ---------- ----------

5 1 1

1 905081.84 2 2

3 478270.91 3 3

4 402751.16 4 4

2 186381.22 5 5

注意:这里PRD_TYPE_ID列为5SUM(AMOUNT)的值为空,RANK()和DENSE-RANK在这一行的返回值为1。因为默认状态下RANK()和DENSE-RANK()在递减排序中将空值指定为最高排名1,而在递增排序中则把它指定为最低排名。这里还有一个问题就是我们的例子中没有SUM(AMOUNT)相等的值,如果有的话RANKDENSE-RANK将表现出区别比如上面的例子如果PRD_TYPE_ID4SUMAMOUNT)的值也为:478270.91的话,那么上面语句的输出则为:

PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK

----------- ----------- ---------- ----------

5 1 1

1 905081.84 2 2

3 478270.91 3 3

4 478270.91 3 3

2 186381.22 5 4

此外这里还有两个参数来限制空值的排序即:NULLS FIRSTNULLS LAST

我们还以上面的例子来看:

SQL> select

2 prd_type_id,sum(amount),

3 RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank,

4 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS dense_rank

5 from all_sales

6 where year=2003

7 group by prd_type_id

8* order by rank

 

PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK

----------- ----------- ---------- ----------

1 905081.84 1 1

3 478270.91 2 2

4 402751.16 3 3

2 186381.22 4 4

5 5 5

可以看出刚才我们不使用NULLS LASTPRD_TYPE_ID5的空值的排序位于第一,现在则位于第五。

接下来来看分析函数与PARTITION BY子句的结合使用:

当需要把分组划分为子分组时,那么我们便可以结合PRATITION BY子句和分析函数同时使用。如下例根据月份划分销量:

SQL> select

2 prd_type_id,month,SUM(amount),

3 RANK() OVER (PARTITION BY month ORDER BY SUM(amount) DESC) AS rank

4 from all_sales

5 where year=2003

6 and amount IS NOT NULL

7 GROUP BY prd_type_id,month

8* ORDER BY month,rank

PRD_TYPE_ID MONTH SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

1 1 38909.04 1

3 1 24909.04 2

4 1 17398.43 3

2 1 14309.04 4

1 2 70567.9 1

4 2 17267.9 2

3 2 15467.9 3

2 2 13367.9 4

1 3 91826.98 1

4 3 31026.98 2

3 3 20626.98 3

 

PRD_TYPE_ID MONTH SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

2 3 16826.98 4

1 4 120344.7 1

3 4 23844.7 2

4 4 16144.7 3

2 4 15664.7 4

1 5 97287.36 1

4 5 20087.36 2

3 5 18687.36 3

2 5 18287.36 4

1 6 57387.84 1

4 6 33087.84 2

 

PRD_TYPE_ID MONTH SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

3 6 19887.84 3

2 6 14587.84 4

3 7 81589.04 1

1 7 60929.04 2

2 7 15689.04 3

4 7 12089.04 4

1 8 75608.92 1

3 8 62408.92 2

4 8 58408.92 3

2 8 16308.92 4

1 9 85027.42 1

 

PRD_TYPE_ID MONTH SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

4 9 49327.42 2

3 9 46127.42 3

2 9 19127.42 4

1 10 105305.22 1

4 10 75325.14 2

3 10 70325.29 3

2 10 13525.14 4

1 11 55678.38 1

3 11 46187.38 2

4 11 42178.38 3

2 11 16177.84 4

 

PRD_TYPE_ID MONTH SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

3 12 48209.04 1

1 12 46209.04 2

4 12 30409.05 3

2 12 12509.04 4

 

已选择48行。

接下来我们再来看分析函数与我们上次学的ROLLUPCUBEGROUPING SETS的结合使用:

SELECT

prd_type_id,SUM(amount),

RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank

FROM all_sales

WHERE year=2003

GROUP BY ROLLUP(prd_type_id)

ORDER BY rank;

 

PRD_TYPE_ID    SUM(AMOUNT)    RANK

1972485.13    1 (注:RULLUP的总计排在了最前)

1    905081.84    2

3    478270.91    3

4    402751.16    4

2    186381.22    5

5         6

SELECT

prd_type_id,emp_id,SUM(amount),

RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank

FROM all_sales

WHERE year=2003

GROUP BY CUBE(prd_type_id,emp_id)

ORDER BY prd_type_id,emp_id;

PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

1 21 197916.96 12

1 22 214216.96 10

1 23 98896.96 19

1 24 207216.96 11

1 25 93416.96 21

1 26 93417.04 20

1 905081.84 2

2 21 20426.96 33

2 22 19826.96 34

2 23 19726.96 35

2 24 43866.96 27

 

PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

2 25 32266.96 31

2 26 50266.42 24

2 186381.22 14

3 21 140326.96 15

3 22 116826.96 16

3 23 112026.96 17

3 24 34829.96 29

3 25 29129.96 32

3 26 45130.11 26

3 478270.91 3

4 21 108326.96 18

 

PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

4 22 81426.96 23

4 23 92426.96 22

4 24 47456.96 25

4 25 33156.96 30

4 26 39956.36 28

4 402751.16 6

5 21 36

5 22 36

5 23 36

5 24 36

5 25 36

 

PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

5 26 36

5 36

21 466997.84 4

22 432297.84 5

23 323077.84 8

24 333370.84 7

25 187970.84 13

26 228769.93 9

1972485.13 1

 

已选择42行。

SQL> SELECT

2 prd_type_id,emp_id,SUM(amount),

3 RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank

4 FROM all_sales

5 WHERE year=2003

6 GROUP BY GROUPING SETS(prd_type_id,emp_id)

7 ORDER BY prd_type_id,emp_id;

PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

1 905081.84 1

2 186381.22 10

3 478270.91 2

4 402751.16 5

5 11

21 466997.84 3

22 432297.84 4

23 323077.84 7

24 333370.84 6

25 187970.84 9

26 228769.93 8

 

已选择11行。

2CUME-DIST()和PERCENT-RANK()函数

下面这个例子说明了CUME-DIST()与PERCENT-RANK()的使用,它得到的是销量的累积分布和百分比排名:

SQL> SELECT

2 prd_type_id,SUM(amount),

3 CUME_DIST() OVER (ORDER BY SUM(amount) DESC) AS cume_dist,

4 PERCENT_RANK() OVER (ORDER BY SUM(amount) DESC) AS percent_rank

5 FROM all_sales

6 WHERE year=2003

7 GROUP BY prd_type_id

8 ORDER BY prd_type_id;

PRD_TYPE_ID SUM(AMOUNT) CUME_DIST PERCENT_RANK

----------- ----------- ---------- ------------

1 905081.84 .4 .25

2 186381.22 1 1

3 478270.91 .6 .5

4 402751.16 .8 .75

5 .2 0

3NTILE()函数的使用

前面我们已经介绍了这个函数的作用就是把记录结果集分成N部分的意思,这个函数的参数为NTILEbuckets),这个bucket参数指定了分片的片数,下面我们看例子来说明

SQL> SELECT

2 prd_type_id,SUM(amount),

3 NTILE(2) OVER (ORDER BY SUM(amount) DESC) AS ntile

4 FROM all_sales

5 WHERE year=2003

6 AND amount IS NOT NULL

7 GROUP BY prd_type_id

8 ORDER BY prd_type_id;

PRD_TYPE_ID SUM(AMOUNT) NTILE

----------- ----------- ----------

1 905081.84 1

2 186381.22 2

3 478270.91 1

4 402751.16 2

注意这里的N2,因此分成了下面的12两片

SQL> SELECT

2 prd_type_id,SUM(amount),

3 NTILE(3) OVER (ORDER BY SUM(amount) DESC) AS ntile

4 FROM all_sales

5 WHERE year=2003

6 AND amount IS NOT NULL

7 GROUP BY prd_type_id

8 ORDER BY prd_type_id;

PRD_TYPE_ID SUM(AMOUNT) NTILE

----------- ----------- ----------

1 905081.84 1

2 186381.22 3

3 478270.91 1

4 402751.16 2

注意这里的N3,因此分成了下面的123三片,这里我的看法是当分片不均时,都是向上最加(即有两个1片)

N=4时就与RANK相同了

PRD_TYPE_ID SUM(AMOUNT) NTILE

----------- ----------- ----------

1 905081.84 1

2 186381.22 4

3 478270.91 2

4 402751.16 3`

4ROW-NUMBER()函数

SQL> SELECT

2 prd_type_id,SUM(amount),

3 ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS row_number

4 FROM all_sales

5 WHERE year=2003

6 GROUP BY prd_type_id

7 ORDER BY prd_type_id;

PRD_TYPE_ID SUM(AMOUNT) ROW_NUMBER

----------- ----------- ----------

1 905081.84 2

2 186381.22 5

3 478270.91 3

4 402751.16 4

5 1

这里ROW-NUMBER()函数就相当于RANK()函数。

总结:在上面介绍的这些评级函数中其中RANK()、DENSE-RANK()、PERCENT-RANK()函数是比较常用的(相对于其他几个而言),因此我们最好要掌握而其他几个大家只要知道了解就可以了。

反百分点函数的使用

PERCENTILE-DISCX)函数与CUME-DIST相反,它在每一个分组中检查累积分布的数值,直到找到大于或等于X的值。

PERCENTILE-CONTX)函数与PERCENT-RANK()相反,在每一个分组中检查百分比排名的值,直到

找到大于或等于X的值。

下面我们来看个例子获取百分点大于等于0.6的销售总量:

SQL> SELECT

2 PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY SUM(amount) DESC) AS percentile_cont,

3 PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY SUM(amount) DESC) AS percentile_disc

4 FROM all_sales

5 WHERE year=2003

6 GROUP BY prd_type_id;

PERCENTILE_CONT PERCENTILE_DISC

--------------- ---------------

417855.11 402751.16

窗口函数

窗口函数主要用来计算一定的记录范围内、一定的值域内、或一段时间内的累积和及移动平均值等。之所以叫“窗口”因为处理结果中使用了一个滑动的查询结果集范围。

1.计算累积和

下面这个例子是计算出2003年从1月到12月的累积销量。

SQL> SELECT

2 month 月份,SUM(amount) AS 月总销量,

3 SUM(SUM(amount)) OVER

4 (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 月累积销量 --定义了窗口的起点和终点

5 FROM all_sales

6 WHERE year=2003

7 GROUP BY month

8 ORDER BY month;

 

月份 月总销量 月累积销量

---------- ---------- ----------

1 95525.55 95525.55

2 116671.6 212197.15

3 160307.92 372505.07

4 175998.8 548503.87

5 154349.44 702853.31

6 124951.36 827804.67

7 170296.16 998100.83

8 212735.68 1210836.51

9 199609.68 1410446.19

10 264480.79 1674926.98

11 160221.98 1835148.96

 

月份 月总销量 月累积销量

---------- ---------- ----------

12 137336.17 1972485.13

 

已选择12行。   

那如果是计算6月到12月的累积销量呢!!!

SQL> SELECT

2 month 月份,SUM(amount) AS 月总销量,

3 SUM(SUM(amount)) OVER

4 (ORDER BY month ROWS UNBOUNDED PRECEDING) AS 月累积销量

5 FROM all_sales

6 WHERE year=2003

7 AND month BETWEEN 6 AND 12 --612换为相应的月就可以了

8 GROUP BY month

9 ORDER BY month;

 

月份 月总销量 月累积销量

---------- ---------- ----------

6 124951.36 124951.36

7 170296.16 295247.52

8 212735.68 507983.2

9 199609.68 707592.88

10 264480.79 972073.67

11 160221.98 1132295.65

12 137336.17 1269631.82

 

已选择7行。

2.计算移动平均值

计算本月与前三个月之间销量的移动平均值

SQL> SELECT

2 month 月份,SUM(amount) AS 月总销量,

3 AVG(SUM(amount)) OVER

4 (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS 三月平均累积销量

5 FROM all_sales

6 WHERE year=2003

7 GROUP BY month

8 ORDER BY month;

 

月份 月总销量 三月平均累积销量

---------- ---------- ----------------

1 95525.55 95525.55

2 116671.6 106098.575 --前两月的平均销量

3 160307.92 124168.357 --三月

4 175998.8 137125.968 --本月加前三月

5 154349.44 151831.94

6 124951.36 153901.88

7 170296.16 156398.94

8 212735.68 165583.16

9 199609.68 176898.22

10 264480.79 211780.578

11 160221.98 209262.033

 

月份 月总销量 三月平均累积销量

---------- ---------- ----------------

12 137336.17 190412.155

 

已选择12行。

3.计算中心平均值

计算当前月份前、后各一个月内的销量移动平均值:

SQL> SELECT

2 month 月份,SUM(amount) AS 月总销量,

3 AVG(SUM(amount)) OVER

4 (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 平均累积销量

5 FROM all_sales

6 WHERE year=2003

7 GROUP BY month

8 ORDER BY month;

 

月份 月总销量 平均累积销量

---------- ---------- ------------

1 95525.55 106098.575

2 116671.6 124168.357

3 160307.92 150992.773

4 175998.8 163552.053

5 154349.44 151766.533

6 124951.36 149865.653

7 170296.16 169327.733

8 212735.68 194213.84

9 199609.68 225608.717

10 264480.79 208104.15

11 160221.98 187346.313

 

月份 月总销量 平均累积销量

---------- ---------- ------------

12 137336.17 148779.075

4.FIRST-VALUE()和LAST-VALUE()函数的使用:

下面这个例子是用FIRST-VALUE()和LAST-VALUE()来获得前一个月和后一个月的销量:

SQL> SELECT

2 month 月份,SUM(amount) AS 月总销量,

3 FIRST_VALUE(SUM(amount)) OVER

4 (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 前月销量,

5 LAST_VALUE(SUM(amount)) OVER

6 (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 后月销量

7 FROM all_sales

8 WHERE year=2003

9 GROUP BY month

10 ORDER BY month;

 

月份 月总销量 前月销量 后月销量

---------- ---------- ---------- ----------

1 95525.55 95525.55 116671.6

2 116671.6 95525.55 160307.92

3 160307.92 116671.6 175998.8

4 175998.8 160307.92 154349.44

5 154349.44 175998.8 124951.36

6 124951.36 154349.44 170296.16

7 170296.16 124951.36 212735.68

8 212735.68 170296.16 199609.68

9 199609.68 212735.68 264480.79

10 264480.79 199609.68 160221.98

11 160221.98 264480.79 137336.17

 

月份 月总销量 前月销量 后月销量

---------- ---------- ---------- ----------

12 137336.17 160221.98 137336.17

 

已选择12行。

还有一个例子是计算当前月与前、后各一个月的比率

SQL> SELECT

2 month 月份,SUM(amount) AS 月总销量,

3 SUM(amount)/FIRST_VALUE(SUM(amount)) OVER

4 (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 当月与前月比率,

5 SUM(amount)/LAST_VALUE(SUM(amount)) OVER

6 (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 当月与后月比率

7 FROM all_sales

8 WHERE year=2003

9 GROUP BY month

10 ORDER BY month;

 

月份 月总销量 当月与前月比率 当月与后月比率

---------- ---------- -------------- --------------

1 95525.55 1 .818755807

2 116671.6 1.22136538 .727796855

3 160307.92 1.37400978 .910846665

4 175998.8 1.09787963 1.14026199

5 154349.44 .876991434 1.23527619

6 124951.36 .809535558 .733729756

7 170296.16 1.36289961 .800505867

8 212735.68 1.24921008 1.06575833

9 199609.68 .93829902 .754722791

10 264480.79 1.3249898 1.65071478

11 160221.98 .605798175 1.16664081

 

月份 月总销量 当月与前月比率 当月与后月比率

---------- ---------- -------------- --------------

12 137336.17 .857161858 1

 

已选择12行。


路过

鸡蛋

鲜花

握手

雷人

评论 (0 个评论)

facelist

您需要登录后才可以评论 登录 | 立即注册

手机版|小黑屋|重庆度量科技  本站关键词:快速开发平台

GMT+8, 2018-1-17 03:19 , Processed in 0.203167 second(s), 21 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

返回顶部