Declaration
LAGINFRAME(expr [, offset [, default]]) OVER (window_spec)
Description
This function returns the value of expr for the row located offset (default is 1) rows before the current row, within the current window frame. If that row does not exist within the window frame, it returns default (default is NULL).
expr: the field or expression to retrieve.offset: Optional. The number of rows to skip. Default: 1.default: Optional. The default value returned when there are not enough preceding lines.window_spec: the window definition clause, which includesPARTITION BYandORDER BY.
Notice
This is a ClickHouse-compatible function. By default, it is not available. To use it, you must set the sql_func_extension_mode parameter to enable it. 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 the test table
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
LAGINFRAMEto group the data bycol2, sort each group bycol4, then retrieve the value ofcol3from the preceding row. The final result is output in ascending order bycol2andcol4.obclient> SELECT col2, col3, col4, LAGINFRAME(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 | NULL | | a1 | 110 | 2026-03-01 | 100 | | a1 | 120 | 2026-03-02 | 110 | | a2 | 150 | 2026-03-02 | NULL | | a2 | 130 | 2026-03-03 | 150 | | a3 | 160 | 2026-03-04 | NULL | +------+------+------------+-----------------+ 6 rows in set