Oracle分析函数使用总结 转

b3uj3gjdpd

Oracle分析函数使用总结
1.       使用评级函数
评级函数（rankingfunction）用于计算等级、百分点、n分片等等，下面是几个常用到的评级函数：
RANK（）：返回数据项在分组中的排名。特点：在排名相等的情况下会在名次中留下空位
DENSE_RANK():与RANK不同的是它在排名相等的情况下不会在名次中留下空位
CUME_DIST()：返回特定值相对于一组值的位置：他是“cumulativedistribution”(累积分布)的简写
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_IDSUM(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_IDSUM(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
此外这里还有两个参数来限制空值的排序即：NULLSFIRST和NULLSLAST
我们还以上面的例子来看：
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_IDSUM(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的空值的排序位于第一，现在则位于第五。
接下来来看分析函数与PARTITIONBY子句的结合使用：
当需要把分组划分为子分组时，那么我们便可以结合PRATITIONBY子句和分析函数同时使用。如下例根据月份划分销量：
SQL>select
  2   prd_type_id,month,SUM(amount),
  3   RANK() OVER (PARTITION BYmonth 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、GROUPINGSETS的结合使用：
SELECT
 prd_type_id,SUM(amount),
 RANK() OVER (ORDERBYSUM(amount)DESC NULLS LAST) ASrank
FROMall_sales
WHEREyear=2003
GROUPBY ROLLUP(prd_type_id)
ORDERBYrank;
 
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 (ORDERBYSUM(amount)DESCNULLSLAST) ASrank
FROMall_sales
WHEREyear=2003
GROUPBYCUBE(prd_type_id,emp_id)
ORDERBYprd_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   22876