【SQL 學習】分析函式之RANK() DENSE_RANK ()

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行。