RANK函数小结

ANK（）：返回数据项在分组中的排名。特点：在排名相等的情况下会在名次中留下空位

DENSE_RANK():与RANK不同的是它在排名相等的情况下不会在名次中留下空位

[@more@]

RANK函数小结

RANK（）：返回数据项在分组中的排名。特点：在排名相等的情况下会在名次中留下空位

DENSE_RANK():与RANK不同的是它在排名相等的情况下不会在名次中留下空位

源数据，建表脚本见附件:

SQL> select prd_type_id,sum(amount) from all_sales where year=2003 group by prd_type_id;

PRD_TYPE_ID SUM(AMOUNT)

----------- -----------

1 349535.92

2 349533.8

3 349535.92

4 699071.84

5 null

6 349535.08

7 349535.92

7 rows selected.

问题：根据sum(amount)排名，显示每个部门的名次

SQL> ;

1 select prd_type_id,sum(amount),

2 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank,

3 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense_rank

4 from all_sales

5 where year=2003

6 group by prd_type_id

7* order by rank

5	null	1	1
4	699071.84	2	2
1	349535.92	3	3
3	349535.92	3	3
7	349535.92	3	3
6	349535.08	6	4
2	349533.8	7	5

注意：这里PRD_TYPE_ID列为5的SUM(AMOUNT)的值为空，RANK（）和DENSE-RANK在这一行的返回值为1。因为默认状态下RANK（）和DENSE-RANK（）在递减排序中将空值指定为最高排名1，而在递增排序中则把它指定为最低排名。

此外这里还有两个参数来限制空值的排序即：NULLS FIRST和NULLS LAST

我们还以上面的例子来看：

SQL> select prd_type_id,sum(amount),

RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank,

DENSE_RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS dense_rank

from all_sales

where year=2003

group by prd_type_id

order by rank

PRD_TYPE_ID	SUM(AMOUNT)	RANK	DENSE_RANK
4	699071.84	1	1
1	349535.92	2	2
7	349535.92	2	2
3	349535.92	2	2
6	349535.08	5	3
2	349533.8	6	4
5	null	7	5

可以看出刚才我们不使用NULLS LAST时PRD_TYPE_ID为5的空值的排序位于第一，现在则位于最后。

下来来看分析函数与PARTITION BY子句的结合使用：

当需要把分组划分为子分组时，那么我们便可以结合PRATITION BY子句和分析函数同时使用。如下例根据月份划分销量排名：根据月份排列对每个部门的销量排名

select prd_type_id,month,sum(amount),

rank() over(partition by month order by sum(amount) desc ) as rank

from all_sales

where year=2003

and amount is not null

group by prd_type_id,month

order by month,rank

;

PRD_TYPE_ID MONTH SUM(AMOUNT) RANK

----------- ---------- ----------- ----------

4 1 40136.94 1

1 1 20069.68 2

7 1 20068.92 3

6 1 20068.91 4

2 1 20068.89 5

3 1 20068.31 6

4 2 60576.85 1

1 2 30289.3 2

7 2 30288.73 3

6 2 30288.72 4

3 2 30288.2 5

2 2 30288.05 6

4 3 80549.12 1

1 3 40275.66 2

7 3 40274.91 3

由上我们看出1月份排名第一的是prd4.

资源 附件：exp_all_sales.sql

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

转载于:http://blog.itpub.net/271063/viewspace-912070/