WINDOW FUNCTION LAG

description

The LAG() method is used to calculate the value of the current line several lines ahead.

1. LAG(expr, offset, default) OVER (partition_by_clause order_by_clause)

example

Calculate the previous day’s closing price

1. select stock_symbol, closing_date, closing_price,
2. lag(closing_price,1, 0) over (partition by stock_symbol order by closing_date) as "yesterday closing"
3. from stock_ticker
4. order by closing_date;

6. | stock_symbol | closing_date        | closing_price | yesterday closing |
7. |--------------|---------------------|---------------|-------------------|
8. | JDR          | 2014-09-13 00:00:00 | 12.86         | 0                 |
9. | JDR          | 2014-09-14 00:00:00 | 12.89         | 12.86             |
10. | JDR          | 2014-09-15 00:00:00 | 12.94         | 12.89             |
11. | JDR          | 2014-09-16 00:00:00 | 12.55         | 12.94             |
12. | JDR          | 2014-09-17 00:00:00 | 14.03         | 12.55             |
13. | JDR          | 2014-09-18 00:00:00 | 14.75         | 14.03             |
14. | JDR          | 2014-09-19 00:00:00 | 13.98         | 14.75             |

keywords

1. WINDOW,FUNCTION,LAG