Allow Offset parameter for LEAD and LAG window functions as input parameters #2974
Description
Is your feature request related to a problem? Please describe.
For time-series analysis, I would like to be able to lag columns for more than one position offset. currently the Lag function is restricted to an offset of '1' and does not allow it to be parameterized.
using Lag() on a computed column generates nulls.
Describe the solution you'd like
Ideally aligning to the following syntax
lag(expr[, offset[, default]][ IGNORE NULLS])
Returns expr evaluated at the row that is offset rows before the current row within the window frame;
https://duckdb.org/docs/sql/functions/window_functions.html#lagexpr-offset-default-ignore-nulls
Lag() on a computed column should provide the previous value of the expression outcome
`
'tickpos (Last*)' as 'tickpos (Last*)-0t',
LAG('tickpos (Last*)',1) OVER ( PARTITION BY tickpos_instrument_id, tickpos_granularity ORDER BY tickpos_unixtime_upper )
as 'tickpos (Last*)-1t',
LAG('tickpos (Last*)-1t',1) OVER ( PARTITION BY tickpos_instrument_id, tickpos_granularity ORDER BY tickpos_unixtime_upper )
as 'tickpos (Last*)-2t',
`
Describe alternatives you've considered
- lagging a lag computed column (this provides nulls)
- calculating the unix epoch time of lag time slices and self-joining based on that calculated timestamp
- potentially (but not tried) taking the first() element of a time window frame, ordered by timestamp
Additional context