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)
 
Search WWH ::




Custom Search