注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Study Desk

Share my desk with you!......

 
 
 

日志

 
 

Oracle分析函数使用总结   

2009-04-21 20:45:34|  分类: 计总会 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
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代码
  1. 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]+'))  
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代码
  1. select deptno,ename,sal ,  
  2.        round(avg(sal) over(partition by deptno)) as dept_avg_sal,  
  3.        round(sal-avg(sal) over(partition by deptno)) as dept_sal_diff  
  4.    from emp;  
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代码
  1. select emp.*,rank() over(PARTITION BY deptno ORDER BY sal ) from emp  
select emp.*,rank() over(PARTITION BY deptno ORDER BY sal ) from emp
  评论这张
 
阅读(572)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017