LAGINFRAME

Syntax

LAGINFRAME(expr [, offset [, default]]) OVER (window_spec)

Purpose

This function returns the value of expr from the row offset (default is 1) rows before the current row within the current window frame. If no such row exists in the window frame, it returns default (default is NULL).

• expr: the field or expression to retrieve.
• offset: an optional parameter indicating the number of rows to look back, defaulting to 1.
• default: an optional parameter specifying the default value to return when there are not enough preceding rows.
• window_spec: the window definition clause, including PARTITION BY and ORDER BY.

Examples

1. Enable the ClickHouse extension function mode.

   obclient> ALTER SYSTEM SET sql_func_extension_mode = "ClickHouse";
   

2. Create a test table test_tbl1.

   obclient> CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50), col3 INT, col4 DATE);
   

3. Insert data into the test_tbl1 table.

   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');
   

4. Use LAGINFRAME to group the data by col2, sort each group by col4, and retrieve the col3 value from the previous row. Output the final result in ascending order by col2 and col4.

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