Declaration
LEADINFRAME(expr [, offset [, default]]) OVER (window_spec)
Description
This function returns the value of expr for the row located offset rows after the current row within the current window frame. If that row does not exist within the window frame, it returns default (which is NULL by default).
Notice
This is a ClickHouse-compatible function. By default, it is not available. To use it, you must enable the sql_func_extension_mode parameter. For more information, see sql_func_extension_mode.
Examples
Enable the ClickHouse extension function mode.
obclient> ALTER SYSTEM SET sql_func_extension_mode = "ClickHouse";Create a test table named
test_tbl1.obclient> CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50), col3 INT, col4 DATE);Insert data into the
test_tbl1table.obclient> INSERT INTO test_tbl1 VALUES (1, 'a1', 100, '2026-03-01'), (2, 'a1', 120, '2026-03-02'), (3, 'a1', 110, '2026-03-01'), (4, 'a2', 150, '2026-03-02'), (5, 'a2', 130, '2026-03-03'), (6, 'a3', 160, '2026-03-04');Use
LEADINFRAMEto group the data bycol2, sort each group bycol4, then fetch the value ofcol3from the next row. The final result is output in ascending order ofcol2andcol4.obclient> SELECT col2, col3, col4, LEADINFRAME(col3) OVER (PARTITION BY col2 ORDER BY col4) AS laginframe_col3 FROM test_tbl1 ORDER BY col2, col4;The return result is as follows:
+------+------+------------+-----------------+ | col2 | col3 | col4 | laginframe_col3 | +------+------+------------+-----------------+ | a1 | 100 | 2026-03-01 | 110 | | a1 | 110 | 2026-03-01 | NULL | | a1 | 120 | 2026-03-02 | NULL | | a2 | 150 | 2026-03-02 | NULL | | a2 | 130 | 2026-03-03 | NULL | | a3 | 160 | 2026-03-04 | NULL | +------+------+------------+-----------------+ 6 rows in set