Database Reference
In-Depth Information
If we use department_name in the OVER clause, we will have different ranks, as
shown in the following output, because we have all distinct department names:
warehouse_db=# SELECT department_no, department_name,
department_location, dense_rank() OVER (ORDER BY
department_name) FROM warehouse_departments;
department_no | department_name | department_location | dense_rank
--------------+-----------------+---------------------+-----------
10 | ACCOUNTING | NEW YORK | 1
40 | OPERATIONS | BOSTON | 2
20 | RESEARCH | DALLAS | 3
20 | RESEARCH_OPEN | PARIS | 4
30 | SALES | CHICAGO | 5
30 | SALES_INSIDE | CHICAGO | 6
30 | SALES_OUTER | CHICAGO | 7
(7 rows)
The percent_rank() function
The percent_rank() function is used to get the relative rank of the current row.
The relative rank of the current row is calculated using the following formula:
Relative rank of the current row = (rank - 1) / (total number of rows - 1)
Let's understand this with the following example:
warehouse_db=# SELECT department_no, department_name,
department_location, percent_rank() OVER (PARTITION
BY department_no ORDER BY department_name) FROM
warehouse_departments;
department_no | department_name | department_location | percent_rank
--------------+-----------------+---------------------+-------------
10 | ACCOUNTING | NEW YORK | 0
20 | RESEARCH | DALLAS | 0
20 | RESEARCH_OPEN | PARIS | 1
30 | SALES | CHICAGO | 0
30 | SALES_INSIDE | CHICAGO | 0.5
30 | SALES_OUTER | CHICAGO | 1
40 | OPERATIONS | BOSTON | 0
(7 rows)
 
Search WWH ::




Custom Search