Support lead, lag to access the previous or next row

[neverchanje]

LAG(expression [, offset [, default ]]) OVER (ORDER BY expression [ASC|DESC])

LEAD(expression [, offset [, default ]]) OVER (ORDER BY expression [ASC|DESC])

Here is a brief description of each of the parameters:

• expression: The column or expression for which you want to access the previous or next value.
• offset: The number of rows to move backward or forward from the current row. The default value is 1, which means the function will access the value of the previous or next row.
• default: The default value to return if the offset goes beyond the bounds of the window frame. If this parameter is not specified, the default value is NULL.
• ORDER BY expression [ASC|DESC]: Specifies the order in which the rows are processed. The default order is ascending. NULLs can be sorted either first or last.

Here is an example of using LAG to calculate the change in value of a column between consecutive rows:

SELECT value, LAG(value) OVER (ORDER BY timestamp) as prev_value, (value - LAG(value) OVER (ORDER BY timestamp)) as change
FROM my_table

This query calculates the change in value of the value column between consecutive rows, ordered by the timestamp column.

Similarly, you can use LEAD to access the value of a column from the next row within a specific window frame.

[stdrc]

Similar to #4978, the lag and lead functions are supported after #9110, #9248 and #9597, though only for EOWC mode. I'm closing this issue now cuz the implementation of general version of over window executor is basically independent from the lag and lead implementation.

[neverchanje]

Reopen as we currently only support the EOWC version of lead/lag. The batch version and the general version have not been covered yet.

• Batch over window function, incl. lag and lead #9931
• General version of streaming over window function, incl. lag and lead #9932

[stdrc]

I think this is done since we now have eowc streaming, general streaming and batch mode supported.