Oracle分析函数使用总结
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
oracle trunc函数的使用方法
          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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。