ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle分析函数使用总结

Oracle分析函数使用总结

原创 Linux操作系统 作者:fengzj 时间:2008-11-11 17:45:06 0 删除 编辑

 Oracle分析函数使用总结
使用评级函数
评级函数(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()函数是比较常用的(相对于其他几个而言),因此我们最好要掌握而其他几个大家只要知道了解就可以了。
反百分点函数的使用
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
窗口函数
窗口函数主要用来计算一定的记录范围内、一定的值域内、或一段时间内的累积和及移动平均值等。之所以叫“窗口”因为处理结果中使用了一个滑动的查询结果集范围。
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行。

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/219982/viewspace-490822/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-11

  • 博文量
    76
  • 访问量
    177393