sql 累计占比_排名、占比、增长率怎么算,一个SQL语句搞定。（oracle 10g以上支持正则表达式）...

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

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