Oracle分析函数使用总结

发表于 2014-7-22 11:32:59

马上注册，结交更多数据大咖，获取更多知识干货，轻松玩转大数据

您需要登录才可以下载或查看，没有帐号？立即注册

x

1.使用评级函数

评级函数（ranking function）用于计算等级、百分点、n分片等等，下面是几个常用到的评级函数：

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

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

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

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

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

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

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

1）RANK（）与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列为5的SUM(AMOUNT)的值为空，RANK（）和DENSE-RANK在这一行的返回值为1。因为默认状态下RANK（）和DENSE-RANK（）在递减排序中将空值指定为最高排名1，而在递增排序中则把它指定为最低排名。这里还有一个问题就是我们的例子中没有SUM(AMOUNT)相等的值，如果有的话RANK与DENSE-RANK将表现出区别比如上面的例子如果PRD_TYPE_ID为4的SUM（AMOUNT）的值也为：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 FIRST和NULLS 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 LAST时PRD_TYPE_ID为5的空值的排序位于第一，现在则位于第五。

接下来来看分析函数与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行。

接下来我们再来看分析函数与我们上次学的ROLLUP、CUBE、GROUPING 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行。

2）CUME-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

3）NTILE（）函数的使用

前面我们已经介绍了这个函数的作用就是把记录结果集分成N部分的意思，这个函数的参数为NTILE（buckets），这个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

注意这里的N为2，因此分成了下面的1，2两片

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

注意这里的N为3，因此分成了下面的1，2，3三片，这里我的看法是当分片不均时，都是向上最加（即有两个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`

4）ROW-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（）函数是比较常用的（相对于其他几个而言），因此我们最好要掌握而其他几个大家只要知道了解就可以了。

2. 反百分点函数的使用

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

PERCENTILE-CONT（X）函数与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

3. 窗口函数

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

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 --6和12换为相应的月就可以了

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行。

上文资料来源:Oracle Database 10g SQL开发指南

补充1：oracle 10g以上支持正则表达式,下面的语句可以找出XX代码不是6位数字的记录

Sql代码

SELECT * FROM TZQDM WHERE ZQDM NOT IN (SELECT ZQDM FROM tzqdm where REGEXP_LIKE(zqdm,'^[0-9][0-9][0-9][0-9][0-9][0-9]+'))

补充2：求每个部门的平均工资以及每个人与所在部门的工资差额

Sql代码

select deptno,ename,sal ,
round(avg(sal) over(partition by deptno)) as dept_avg_sal,
round(sal-avg(sal) over(partition by deptno)) as dept_sal_diff
from emp;

补充3：获得每个人在所在部门的工资排名

Sql代码

select emp.*,rank() over(PARTITION BY deptno ORDER BY sal ) from emp

补充4：关于微软SQL Server类似资料：

http://technet.microsoft.com/zh-cn/library/ms189461.aspx

发表于 2014-7-22 14:14:11

帮你顶下哈！！

发表于 2014-7-22 22:34:22

沙发！沙发!

发表于 2014-7-22 22:46:19

支持一下

发表于 2014-7-23 11:40:44

没看完~~~~~~ 先顶，好同志

发表于 2014-7-23 13:43:54

过来看看的

发表于 2014-7-23 14:27:06

学习了，谢谢分享、、、

帐号		自动登录	找回密码
密码			立即注册

Oracle分析函数使用总结

马上注册，结交更多数据大咖，获取更多知识干货，轻松玩转大数据

站长推荐 /1