apache / apache/drill

Allow Offset parameter for LEAD and LAG window functions as input parameters

Open
#2,974 1 comment 0 reactions 0 assignees View on GitHub
verified
Dominant language
Java
Stars
2k
Forks
990
Avg merge
1d 8h
Merged PRs (30d)
5

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',

`

![Image](https://github.com/user-attachments/assets/d6d361de-ac82-43ef-8c7e-441e7d02d81b)

**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**

https://issues.apache.org/jira/browse/DRILL-3596

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.