orace评级函数

rank() over (order by XX):

 /***********
    这个sql可以看出 rank其实就是拿当前的一列和别的行相同的列进行排列
 */
 select 
   t.amount 
   ,rank() over (order by t.amount desc) as rank
  from all_sales t
 where t.year=2003
 and t.amount is not null
 order by t.amount desc

 结果为：

1	32754.19	1
2	29057.45	2
3	27264.84	3
4	25214.56	4
5	25057.45	5
6	24137.83	6
7	24057.45	7
8	23754.19	8
9	23264.84	9
10	22754.19	10
11	22754.19	10
12	22137.83	12
 

加上group by

select 
t.prd_type_id
,sum(amount)  --有没有sum(amount)都是一样的。
,RANK() OVER(ORDER BY avg(amount) desc )as rank  --它的意思是在此分组的情况下 此时sum(amount)和别的行的sum(amount)的排列
,DENSE_RANK() OVER (ORDER BY SUM(amount) desc) as dense_rank  --排列相同的情况下，也有编号
 from all_sales t
 where t.year=2003
 and t.amount is not null
 group by t.prd_type_id
 order by t.prd_type_id

 

结果：

1	1	905081.84	1	1
2	2	186381.22	4	4
3	3	478270.91	2	2
4	4	402751.16	3	3

 

 

结合partition by子句使用：

select 
t.prd_type_id
,t.month
,sum(amount)
,rank() over(partition by t.month order by sum(t.amount) desc) as rank  --加上了partition说明 month这列是子分组，排列是按照 t.prd_type_id,和sum(t.amount)来进行排列的
 from all_sales t
 where t.year=2003
 and t.amount is not null
 group by t.prd_type_id,t.month  --（1）总的来说是按照 2列来进行分组查找的，
 order by t.prd_type_id,t.month

 

这也说明分组最少要有2组使用partition 才有意义，如果是一组，去掉一个子分组 就是分组那个组，等于没分组。

结果：

1	1	1	38909.04	1
2	1	2	70567.9	1
3	1	3	91826.98	1
4	1	4	120344.7	1
5	1	5	97287.36	1
6	1	6	57387.84	1
7	1	7	60929.04	2
8	1	8	75608.92	1
9	1	9	85027.42	1
10	1	10	105305.22	1
11	1	11	55678.38	1
12	1	12	46209.04	2
13	2	1	14309.04	4
14	2	2	13367.9	4
15	2	3	16826.98	4
16	2	4	15664.7	4
17	2	5	18287.36	4
18	2	6	14587.84	4
19	2	7	15689.04	3
20	2	8	16308.92	4
21	2	9	19127.42	4
22	2	10	13525.14	4
23	2	11	16177.84	4
24	2	12	12509.04	4
25	3	1	24909.04	2
26	3	2	15467.9	3
27	3	3	20626.98	3
28	3	4	23844.7	2
29	3	5	18687.36	3
30	3	6	19887.84	3
31	3	7	81589.04	1
32	3	8	62408.92	2
33	3	9	46127.42	3
34	3	10	70325.29	3
35	3	11	46187.38	2
36	3	12	48209.04	1
37	4	1	17398.43	3
38	4	2	17267.9	2
39	4	3	31026.98	2
40	4	4	16144.7	3
41	4	5	20087.36	2
42	4	6	33087.84	2
43	4	7	12089.04	4
44	4	8	58408.92	3
45	4	9	49327.42	2
46	4	10	75325.14	2
47	4	11	42178.38	3
48	4	12	30409.05	3