LAG()

`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.

`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

LAG()

LAG()

LAG Examples

`LAG()`

`LAG` Examples