Database Reference
In-Depth Information
department_no | department_name | department_location | nth_value
---------------+-----------------+---------------------+----------
10 | ACCOUNTING | NEW YORK |
20 | RESEARCH | DALLAS |
20 | RESEARCH_OPEN | PARIS | PARIS
30 | SALES | CHICAGO |
30 | SALES_INSIDE | CHICAGO | CHICAGO
30 | SALES_OUTER | CHICAGO | CHICAGO
40 | OPERATIONS | BOSTON |
(7 rows)
In the preceding example, we used the
PARTITION BY
clause and partitioned the
records based on department number, so in every partition, the nth number (in our
case, we give
2
) will be the output of the
nth_value
function. This can be explained
in the following manner:
• The department number
10
only has one row and no second row, so the nth
value is null
• The department number
20
has two rows and the second row has
department_location
as
PARIS
• The department number
30
has three rows and the second row has
department_location
as
CHICAGO
• The department number
40
has one row and no second row, so the nth value
is null
The ntile() function
The
ntile()
function returns an integer ranging from 1 to the argument value,
divides the partition as equally as possible, and assigns an appropriate bucket
number to each row. Let's understand this with the following example:
warehouse_db=# SELECT department_no, department_name,
department_location, ntile(2) OVER (ORDER BY
department_no) FROM warehouse_departments;
department_no | department_name | department_location | ntile
---------------+-----------------+---------------------+-------
10 | ACCOUNTING | NEW YORK | 1
20 | RESEARCH_OPEN | PARIS | 1
20 | RESEARCH | DALLAS | 1
30 | SALES | CHICAGO | 1
30 | SALES_OUTER | CHICAGO | 2
30 | SALES_INSIDE | CHICAGO | 2
40 | OPERATIONS | BOSTON | 2
(7 rows)