Database Reference
In-Depth Information
The cume_dist() function
The
cume_dist()
function is used to get the relative rank of the current row. It is
calculated by dividing the number of rows preceding the current row by the total
number of rows. This can be seen by the following formula:
Rank of current row = Number of rows preceding the current row / total number
of rows
Consider the
warehouse_departments
table that has data, as shown here:
warehouse_db=# SELECT * FROM warehouse_departments
department_no | department_name | department_location
---------------+-----------------+---------------------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
30 | SALES_OUTER | CHICAGO
30 | SALES_INSIDE | CHICAGO
20 | RESEARCH_OPEN | PARIS
(7 rows)
Let's call the
cume_dist()
function to see what we actually have in the results using
the following statement:
warehouse_db=# SELECT department_no, department_name,
department_location,cume_dist() OVER (ORDER BY
department_no) FROM warehouse_departments;
department_no | department_name | department_location | cume_dist
---------------+-----------------+---------------------+----------
10 | ACCOUNTING | NEW YORK | 0.14
20 | RESEARCH_OPEN | PARIS | 0.42
20 | RESEARCH | DALLAS | 0.42
30 | SALES | CHICAGO | 0.85
30 | SALES_OUTER | CHICAGO | 0.85
30 | SALES_INSIDE | CHICAGO | 0.85
40 | OPERATIONS | BOSTON | 1
(7 rows)
As we used the
OVER
clause on department number, the
cume_dist()
function will
assign the same rank to departments that have the same department number.
