v.21.4Experimental Features

Support RANGE OFFSET Frame for Floating Point Types in Window Functions

Official CH changelog text
Support RANGE OFFSET frame (for window functions) for floating point types. Implement lagInFrame/leadInFrame window functions, which are analogous to lag/lead, but respect the window frame. They are identical when the frame is between unbounded preceding and unbounded following. This closes #5485. #21895 (Alexander Kuzmenkov).
Added support for RANGE OFFSET frame in window functions for floating point types and introduced new window functions lagInFrame and leadInFrame, which behave like lag and lead but respect the window frame boundaries.

Why it matters

This feature solves the problem of applying offset-based window functions within a specified RANGE OFFSET frame for floating point data, allowing for more precise and frame-aware calculations. It improves the expressiveness and correctness of window functions when working with floating point range frames instead of default row-based or unbounded frames.

How to use it

Users can apply this feature by using the RANGE OFFSET clause in window frames on floating point columns. They can also use the newly introduced window functions lagInFrame and leadInFrame in their queries, which will operate respecting the defined window frame. For example:

SELECT
  some_column,
  lagInFrame(some_value) OVER (ORDER BY timestamp RANGE OFFSET 10 AS FLOAT PRECEDING)
FROM table


This usage ensures that the lag/lead calculation only considers rows within the specified floating point range frame.

Related resources

PreviousNext