Database Reference
In-Depth Information
In the preceding example, we give three arguments to the
lag()
function. The irst is
the column name on the basis of which the
lag()
function will access
department_
no
. The second argument is an offset value, which in our case is
3
, and this means
that the cursor will start from the fourth value and make a self join of
department_
no
with the remaining record.
The lead() function
The
lead()
function is used to get the evaluated values of rows that are offset rows
after the current row within the partition. If the offset argument is not given at the
time of calling the function, it is set to
1
by default.
Let's understand this with the following example. In this example, we partition a table
on the base of
department_no
and then call the
lead()
function for
department_name
with an offset value equal to
1
; this will result in the following output:
warehouse_db=# SELECT department_no, department_name,
department_location, lead(department_name,1) OVER
(PARTITION BY department_no ORDER BY department_no)
FROM warehouse_departments;
department_no | department_name | department_location | lead
--------------+-----------------+---------------------+----------
10 | ACCOUNTING | NEW YORK |
20 | RESEARCH_OPEN | PARIS | RESEARCH
20 | RESEARCH | DALLAS |
30 | SALES | CHICAGO |SALES_OUTER
30 | SALES_OUTER | CHICAGO |SALES_INSIDE
30 | SALES_INSIDE | CHICAGO |
40 | OPERATIONS | BOSTON |
(7 rows)
The preceding example has the following results:
• The department number
10
has only one row, so there is no
lead
value as the
offset is
1
• The department number
20
has two rows and has
RESEARCH
as
lead
• The department number
30
has three rows; the irst row is skipped as offset
is
1
and the remaining rows have
SALES_OUTER
and
SALES_INSIDE
as
lead
• The department number
40
has only one row, so no
lead
value as offset is
1
