LEADINFRAME

Syntax

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

Purpose

This function returns the value of expr in the row that is offset (default is 1) rows after the current row within the current window frame. If such a row does not exist in the window frame, it returns default (default is NULL).

Examples

1. Enable the ClickHouse extension function mode.

   obclient> ALTER SYSTEM SET sql_func_extension_mode = "ClickHouse";
   

2. Create a test table named 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 the LEADINFRAME function to group the data by col2, sort each group by col4, and retrieve the value of col3 from the next row. Output the final result in ascending order by col2 and col4.

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