Other Functions

ROW_NUMBER

ROW_NUMBER() OVER()

Description:

Assign a sequential integer to each row as a row number. The row number starts from 1.

Example

ROW_NUMBER() OVER()

Result:

row_number

1

2

3

LAG

LAG(column1, [n_lags]) OVER(ORDER BY column2)

Description

Move each value in column1 forward to the next row. The table will be ordered by column2 first before values are moved. If n_lags is specified, each value is shifted to the next n_lags rows. The first n_lags values will be NULL.

Example

price_lag = LAG(price) OVER(ORDER BY date)

Result:

date	price	price_lag
12-11	30	NULL
12-12	32	30
12-13	28	32