10.5. Navigational Functions
The navigational functions get the simple (non-aggregated) value of an expression from another row of the query, within the same partition.
FIRST_VALUE, LAST_VALUE and NTH_VALUE also operate on a window frame.
Currently, Firebird always applies a frame from the first to the current row of the partition, not to the last.
This is equivalent to using the SQL standard syntax (currently not supported by Firebird):
|ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
This is likely to produce strange or unexpected results for NTH_VALUE and especially LAST_VALUE.
Firebird 4 will introduce support for specifying the window frame.
Example of Navigational Functions
|select|id,|salary,|first_value(salary) over (order by salary),|last_value(salary) over (order by salary),|nth_value(salary, 2) over (order by salary),|lag(salary) over (order by salary),|lead(salary) over (order by salary)|from employee|order by salary;
Results
|
id salary first_value last_value nth_value lag lead
|-- ------ ----------- ---------- --------- ------ ------
|3 8.00 8.00 8.00 <null> <null> 9.00
|4 9.00 8.00 9.00 9.00 8.00 10.00
|1 10.00 8.00 10.00 9.00 9.00 10.00
|5 10.00 8.00 10.00 9.00 10.00 12.00
|2 12.00 8.00 12.00 9.00 10.00 <null>
10.5.1. FIRST_VALUE
Available inDSQL, PSQL
Result typeThe same as type as expr
Syntax
|FIRST_VALUE ( <expr> ) OVER <window-specification>
FIRST_VALUE| Argument | Description |
|---|---|
expr | Expression. May contain a table column, constant, variable, expression, scalar function. Aggregate functions are not allowed as an expression. |
Returns the first value from the current partition.
See alsoSection 10.5.3, “LAST_VALUE”, Section 10.5.5, “NTH_VALUE”
10.5.2. LAG
Available inDSQL, PSQL
Result typeThe same as type as expr
Syntax
|
LAG ( <expr> [, <offset [, <default>]])
| OVER <window-specification>
LAG| Argument | Description |
|---|---|
expr | Expression. May contain a table column, constant, variable, expression, scalar function. Aggregate functions are not allowed as an expression. |
offset | The offset in rows before the current row to get the value identified by expr.
If offset is not specified, the default is |
default | The default value to return if offset points outside the partition.
Default is |
The LAG function provides access to the row in the current partition with a given offset before the current row.
If offset points outside the current partition, default will be returned, or NULL if no default was specified.
offset can be a parameter, but explicit casting to INTEGER or BIGINT is currently required (eg LAG(somecolumn, cast(? as bigint))).
See CORE-6421
10.5.2.1. LAG Examples
Suppose you have RATE table that stores the exchange rate for each day.
To trace the change of the exchange rate over the past five days you can use the following query.
|select|bydate,|cost,|cost - lag(cost) over (order by bydate) as change,|100 * (cost - lag(cost) over (order by bydate)) /|lag(cost) over (order by bydate) as percent_change|from rate|where bydate between dateadd(-4 day to current_date)|and current_date|order by bydate
Result
|
bydate cost change percent_change
|---------- ------ ------ --------------
|27.10.2014 31.00 <null> <null>
|28.10.2014 31.53 0.53 1.7096
|29.10.2014 31.40 -0.13 -0.4123
|30.10.2014 31.67 0.27 0.8598
|31.10.2014 32.00 0.33 1.0419
See alsoSection 10.5.4, “LEAD”
10.5.3. LAST_VALUE
Available inDSQL, PSQL
Result typeThe same as type as expr
Syntax
|LAST_VALUE ( <expr> ) OVER <window-specification>
LAST_VALUE| Argument | Description |
|---|---|
expr | Expression. May contain a table column, constant, variable, expression, scalar function. Aggregate functions are not allowed as an expression. |
Returns the last value from the current partition.
See alsoSection 10.5.1, “FIRST_VALUE”, Section 10.5.5, “NTH_VALUE”
10.5.4. LEAD
Available inDSQL, PSQL
Result typeThe same as type as expr
Syntax
|
LEAD ( <expr> [, <offset [, <default>]])
| OVER <window-specification>
LEAD| Argument | Description |
|---|---|
expr | Expression. May contain a table column, constant, variable, expression, scalar function. Aggregate functions are not allowed as an expression. |
offset | The offset in rows after the current row to get the value identified by expr.
If offset is not specified, the default is |
default | The default value to return if offset points outside the partition.
Default is |
The LEAD function provides access to the row in the current partition with a given offset after the current row.
If offset points outside the current partition, default will be returned, or NULL if no default was specified.
offset can be a parameter, but explicit casting to INTEGER or BIGINT is currently required (eg LEAD(somecolumn, cast(? as bigint))).
See CORE-6421
See alsoSection 10.5.2, “LAG”
10.5.5. NTH_VALUE
Available inDSQL, PSQL
Result typeThe same as type as expr
Syntax
|
NTH_VALUE ( <expr>, <offset> )
| [FROM {FIRST | LAST}]
| OVER <window-specification>
NTH_VALUE| Argument | Description |
|---|---|
expr | Expression. May contain a table column, constant, variable, expression, scalar function. Aggregate functions are not allowed as an expression. |
offset | The offset in rows from the start ( |
The NTH_VALUE function returns the Nth value starting from the first (FROM FIRST) or the last (FROM LAST) row of the current frame, see also note on frame for navigational functions.
Offset 1 with FROM FIRST is equivalent to FIRST_VALUE, and offset 1 with FROM LAST is equivalent to LAST_VALUE.
offset can be a parameter, but explicit casting to INTEGER or BIGINT is currently required (eg LEAD(somecolumn, cast(? as bigint))).
See CORE-6421
See alsoSection 10.5.1, “FIRST_VALUE”, Section 10.5.3, “LAST_VALUE”