clickhouse中的lead和lag实现有多种方法,在标准的SQL中使用的windows function即可实现。
示例数据: CREATE TABLE llexample ( g Int32, a Date ) ENGINE = Memory; INSERT INTO llexample SELECT number % 3, toDate('2020-01-01') + number FROM numbers(10); SELECT * FROM llexample ORDER BY g,a; ┌─g─┬──────────a─┐ │ 0 │ 2020-01-01 │ │ 0 │ 2020-01-04 │ │ 0 │ 2020-01-07 │ │ 0 │ 2020-01-10 │ │ 1 │ 2020-01-02 │ │ 1 │ 2020-01-05 │ │ 1 │ 2020-01-08 │ │ 2 │ 2020-01-03 │ │ 2 │ 2020-01-06 │ │ 2 │ 2020-01-09 │ └───┴────────────┘
方法一:使用常规 window functions进行实现
使用常规窗口函数进行实现sing window functions (starting from Clickhouse 21.3) SET allow_experimental_window_functions = 1; SELECT g, a, any(a) OVER (PARTITION BY g ORDER BY a ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev, any(a) OVER (PARTITION BY g ORDER BY a ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next FROM llexample ORDER BY g ASC, a ASC; ┌─g─┬──────────a─┬───────prev─┬───────next─┐ │ 0 │ 2020-01-01 │ 1970-01-01 │ 2020-01-04 │ │ 0 │ 2020-01-04 │ 2020-01-01 │ 2020-01-07 │ │ 0 │ 2020-01-07 │ 2020-01-04 │ 2020-01-10 │ │ 0 │ 2020-01-10 │ 2020-01-07 │ 1970-01-01 │ │ 1 │ 2020-01-02 │ 1970-01-01 │ 2020-01-05 │ │ 1 │ 2020-01-05 │ 2020-01-02 │ 2020-01-08 │ │ 1 │ 2020-01-08 │ 2020-01-05 │ 1970-01-01 │ │ 2 │ 2020-01-03 │ 1970-01-01 │ 2020-01-06 │ │ 2 │ 2020-01-06 │ 2020-01-03 │ 2020-01-09 │ │ 2 │ 2020-01-09 │ 2020-01-06 │ 1970-01-01 │ └───┴────────────┴────────────┴────────────┘
方法二:使用clickhouse自带的lagInFrame/leadInFrame进行实现
Using lagInFrame/leadInFrame (starting from ClickHouse 21.4) SELECT g, a, lagInFrame(a) OVER (PARTITION BY g ORDER BY a ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prev, leadInFrame(a) OVER (PARTITION BY g ORDER BY a ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS next FROM llexample ORDER BY g ASC, a ASC; ┌─g─┬──────────a─┬───────prev─┬───────next─┐ │ 0 │ 2020-01-01 │ 1970-01-01 │ 2020-01-04 │ │ 0 │ 2020-01-04 │ 2020-01-01 │ 2020-01-07 │ │ 0 │ 2020-01-07 │ 2020-01-04 │ 2020-01-10 │ │ 0 │ 2020-01-10 │ 2020-01-07 │ 1970-01-01 │ │ 1 │ 2020-01-02 │ 1970-01-01 │ 2020-01-05 │ │ 1 │ 2020-01-05 │ 2020-01-02 │ 2020-01-08 │ │ 1 │ 2020-01-08 │ 2020-01-05 │ 1970-01-01 │ │ 2 │ 2020-01-03 │ 1970-01-01 │ 2020-01-06 │ │ 2 │ 2020-01-06 │ 2020-01-03 │ 2020-01-09 │ │ 2 │ 2020-01-09 │ 2020-01-06 │ 1970-01-01 │ └───┴────────────┴────────────┴────────────┘
参考:
https://clickhouse.com/docs/zh/sql-reference/window-functions
clickhouse lag/lead_vkingnew的博客-CSDN博客
标签:01,06,lead,lag,1970,ASC,2020,ORDER,clickHouse From: https://www.cnblogs.com/-courage/p/17517489.html