LAG() is a window function that accesses data from previous rows without performing a self-join. It retrieves values from a row that is N rows before the current row within a partition.
LAG ( <expr> [, <offset> [, <default> ] ] )
| Parameter | Description |
|---|---|
| expr | The expression whose value is to be retrieved, supported type: tinyint/smallint/int/bigint/float/double/decimal/string/date/datetime/ |
| offset | Optional Bigint Type. Number of rows to look ahead. Default is 1. |
| default | Optional Type is same as first param. Value to return when the offset goes beyond window bounds. Default is NULL |
Returns the same data type as the input expression.
Calculate the difference between each salesperson‘s current sales amount and the previous day’s sales amount:
select stock_symbol, closing_date, closing_price, lag(closing_price,1, 0) over (partition by stock_symbol order by closing_date) as "yesterday closing" from stock_ticker order by closing_date;
+--------------+---------------------+---------------+-------------------+ | stock_symbol | closing_date | closing_price | yesterday closing | | ------------ | ------------------- | ------------- | ----------------- | | JDR | 2014-09-13 00:00:00 | 12.86 | 0 | | JDR | 2014-09-14 00:00:00 | 12.89 | 12.86 | | JDR | 2014-09-15 00:00:00 | 12.94 | 12.89 | | JDR | 2014-09-16 00:00:00 | 12.55 | 12.94 | | JDR | 2014-09-17 00:00:00 | 14.03 | 12.55 | | JDR | 2014-09-18 00:00:00 | 14.75 | 14.03 | | JDR | 2014-09-19 00:00:00 | 13.98 | 14.75 | +--------------+---------------------+---------------+-------------------+