SQL> --RANK DENSE_RANK 函数
SQL> 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 and amount is not null ---过滤空值、
7 group by prd_type_id
8 order by prd_type_id
PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK
----------- ----------- ---------- ----------
1 905081.84 1 1
2 186381.22 4 4
3 478270.91 2 2
4 402751.16 3 3
SQL> 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 --and amount is not null
7 group by prd_type_id
8 order by prd_type_id
PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK
----------- ----------- ---------- ----------
1 905081.84 2 2
2 186381.22 5 5
3 478270.91 3 3
4 402751.16 4 4
5 1 1
SQL> select prd_type_id,sum(amount),
2 rank() over (order by sum(amount) desc nulls last) as rank,
3 dense_rank() over (order by sum(amount) desc nulls last) as dense_rank
4 from all_sales
5 where year =2003
6 --and amount is not null
7 group by prd_type_id
8* order by prd_type_id
SQL> /
PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK
----------- ----------- ---------- ----------
1 905081.84 1 1
2 186381.22 4 4
3 478270.91 2 2
4 402751.16 3 3
5 5 5
SQL> select prd_type_id,sum(amount),
2 rank() over (partition by month order by sum(amount) desc nulls last) as rank
3 --dense_rank() over (order by sum(amount) desc nulls last) as dense_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 prd_type_id,month
PRD_TYPE_ID SUM(AMOUNT) RANK
----------- ----------- ----------
1 38909.04 1
1 70567.9 1
1 91826.98 1
1 120344.7 1
1 97287.36 1
1 57387.84 1
1 60929.04 2
1 75608.92 1
1 85027.42 1
1 105305.22 1
1 55678.38 1
1 46209.04 2
2 14309.04 4
2 13367.9 4
2 16826.98 4
2 15664.7 4
2 18287.36 4
2 14587.84 4
2 15689.04 3
2 16308.92 4
2 19127.42 4
2 13525.14 4
2 16177.84 4
2 12509.04 4
3 24909.04 2
3 15467.9 3
3 20626.98 3
3 23844.7 2
3 18687.36 3
3 19887.84 3
3 81589.04 1
3 62408.92 2
3 46127.42 3
3 70325.29 3
3 46187.38 2
3 48209.04 1
4 17398.43 3
4 17267.9 2
4 31026.98 2
4 16144.7 3
4 20087.36 2
4 33087.84 2
4 12089.04 4
4 58408.92 3
4 49327.42 2
4 75325.14 2
4 42178.38 3
4 30409.05 3
已选择48行。
SQL> select prd_type_id,month,sum(amount),
2 rank() over (partition by month order by sum(amount) desc nulls last) as rank
3 --dense_rank() over (order by sum(amount) desc nulls last) as dense_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 prd_type_id,month;
PRD_TYPE_ID MONTH SUM(AMOUNT) RANK
----------- ---------- ----------- ----------
1 1 38909.04 1
1 2 70567.9 1
1 3 91826.98 1
1 4 120344.7 1
1 5 97287.36 1
1 6 57387.84 1
1 7 60929.04 2
1 8 75608.92 1
1 9 85027.42 1
1 10 105305.22 1
1 11 55678.38 1
1 12 46209.04 2
2 1 14309.04 4
2 2 13367.9 4
2 3 16826.98 4
2 4 15664.7 4
2 5 18287.36 4
2 6 14587.84 4
2 7 15689.04 3
2 8 16308.92 4
2 9 19127.42 4
2 10 13525.14 4
2 11 16177.84 4
2 12 12509.04 4
3 1 24909.04 2
3 2 15467.9 3
3 3 20626.98 3
3 4 23844.7 2
3 5 18687.36 3
3 6 19887.84 3
3 7 81589.04 1
3 8 62408.92 2
3 9 46127.42 3
3 10 70325.29 3
3 11 46187.38 2
3 12 48209.04 1
4 1 17398.43 3
4 2 17267.9 2
4 3 31026.98 2
4 4 16144.7 3
4 5 20087.36 2
4 6 33087.84 2
4 7 12089.04 4
4 8 58408.92 3
4 9 49327.42 2
4 10 75325.14 2
4 11 42178.38 3
4 12 30409.05 3
已选择48行。
SQL> select prd_type_id,sum(amount),
2 rank() over ( order by sum(amount) desc nulls last) as rank
3 --dense_rank() over (order by sum(amount) desc nulls last) as dense_rank
4 from all_sales
5 where year =2003
6 --and amount is not null
7 group by rollup(prd_type_id)
8 order by prd_type_id
PRD_TYPE_ID SUM(AMOUNT) RANK
----------- ----------- ----------
1 905081.84 2
2 186381.22 5
3 478270.91 3
4 402751.16 4
5 6
1972485.13 1
已选择6行。
SQL> --根据产品类型和员工ID获得销售排名
SQL> select prd_type_id,emp_id ,sum(amount),
2 rank() over ( order by sum(amount) desc ) as rank
3 --dense_rank() over (order by sum(amount) desc nulls last) as dense_rank
4 from all_sales
5 where year =2003
6 --and amount is not null
7 group by cube(prd_type_id,emp_id)
8 order by prd_type_id,emp_id
PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK
----------- ---------- ----------- ----------
1 21 197916.96 19
1 22 214216.96 17
1 23 98896.96 26
1 24 207216.96 18
1 25 93416.96 28
1 26 93417.04 27
1 905081.84 9
2 21 20426.96 40
2 22 19826.96 41
2 23 19726.96 42
2 24 43866.96 34
2 25 32266.96 38
2 26 50266.42 31
2 186381.22 21
3 21 140326.96 22
3 22 116826.96 23
3 23 112026.96 24
3 24 34829.96 36
3 25 29129.96 39
3 26 45130.11 33
3 478270.91 10
4 21 108326.96 25
4 22 81426.96 30
4 23 92426.96 29
4 24 47456.96 32
4 25 33156.96 37
4 26 39956.36 35
4 402751.16 13
5 21 1
5 22 1
5 23 1
5 24 1
5 25 1
5 26 1
5 1
21 466997.84 11
22 432297.84 12
23 323077.84 15
24 333370.84 14
25 187970.84 20
26 228769.93 16
1972485.13 8
已选择42行。