FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 Aggregate FunctionsSystem Packages 

FIRST_VALUE()

First value of the current partition

Result type

The same as type as expr

Syntax
FIRST_VALUE ( <expr> ) OVER <window_name_or_spec>
Table 1. Arguments of FIRST_VALUE
Argument Description

expr

Expression.May contain a table column, constant, variable, expression, scalar function.Aggregate functions are not allowed as an expression.
See also

[fblangref50-windowfuncs-last-value], [fblangref50-windowfuncs-nth-value]

LAG()

Value from row in the current partition with a given offset before the current row

Result type

The same as type as expr

Syntax
LAG ( <expr> [, <offset [, <default>]])
  OVER <window_name_or_spec>
Table 1. Arguments of 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 1.offset can be a column, subquery or other expression that results in a positive integer value, or another type that can be implicitly converted to BIGINT.offset cannot be negative (use LEAD instead).

default

The default value to return if offset points outside the partition.Default is NULL.

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.

docnext count = 5

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 also

[fblangref50-windowfuncs-lead]

LAST_VALUE()

Last value from the current partition

Result type

The same as type as expr

Syntax
LAST_VALUE ( <expr> ) OVER <window_name_or_spec>
Table 1. Arguments of LAST_VALUE
Argument Description

expr

Expression.May contain a table column, constant, variable, expression, scalar function.Aggregate functions are not allowed as an expression.

See also note on frame for navigational functions.

See also

[fblangref50-windowfuncs-first-value], [fblangref50-windowfuncs-nth-value]

LEAD()

Value from a row in the current partition with a given offset after the current row

Result type

The same as type as expr

Syntax
LEAD ( <expr> [, <offset [, <default>]])
  OVER <window_name_or_spec>
Table 1. Arguments of 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 1.offset can be a column, subquery or other expression that results in a positive integer value, or another type that can be implicitly converted to BIGINT.offset cannot be negative (use LAG instead).

default

The default value to return if offset points outside the partition.Default is NULL.

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.

See also

[fblangref50-windowfuncs-lag]

NTH_VALUE()

The Nth value starting from the first or the last row of the current frame

Result type

The same as type as expr

Syntax
NTH_VALUE ( <expr>, <offset> )
  [FROM {FIRST | LAST}]
  OVER <window_name_or_spec>
Table 1. Arguments of 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 (FROM FIRST), or the last (FROM LAST) to get the value identified by expr.offset can be a column, subquery or other expression that results in a positive integer value, or another type that can be implicitly converted to BIGINT.offset cannot be zero or negative.

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.

See also

[fblangref50-windowfuncs-first-value], [fblangref50-windowfuncs-last-value]

Aggregate Functions Inside Window Specification

It is possible to use aggregate functions (but not window functions) inside the OVER clause.In that case, first the aggregate function is applied to determine the windows, and only then the window functions are applied on those windows.

Note

When using aggregate functions inside OVER, all columns not used in aggregate functions must be specified in the GROUP BY clause of the SELECT.
Using an Aggregate Function in a Window Specification
select
  code_employee_group,
  avg(salary) as avg_salary,
  rank() over (order by avg(salary)) as salary_rank
from employee
group by code_employee_group