Database Reference
In-Depth Information
In the preceding example, the table is divided into two partitions by the
ntile()
function.
Let's call the function again, give
3
as the argument to the
ntile()
function, and see
the impact on the results as follows:
warehouse_db=# SELECT department_no, department_name,
department_location, ntile(3) 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 | 2
30 | SALES_OUTER | CHICAGO | 2
30 | SALES_INSIDE | CHICAGO | 3
40 | OPERATIONS | BOSTON | 3
(7 rows)
In the preceding example, the table is divided into three partitions by the
ntile()
function.
The lag() function
The
lag()
function is used to access more than one row of a table at the same time
without using a
self join
. Considering that the number of rows returned from
a query and the position of the cursor,
lag()
gives direction to a row at a given
physical offset prior to that position. To understand this functionality in detail,
let's try to actually use this in our query:
warehouse_db=# SELECT department_no, department_name,
department_location, lag(department_no,3) OVER
(ORDER BY department_no) FROM
warehouse_departments;
department_no | department_name | department_location | lag
---------------+-----------------+---------------------+-----
10 | ACCOUNTING | NEW YORK |
20 | RESEARCH_OPEN | PARIS |
20 | RESEARCH | DALLAS |
30 | SALES | CHICAGO | 10
30 | SALES_OUTER | CHICAGO | 20
30 | SALES_INSIDE | CHICAGO | 20
40 | OPERATIONS | BOSTON | 30
(7 rows)
