ck实现lag\lead功能

之前在mysql中有介绍：https://blog.csdn.net/qq_41081716/article/details/108865011，在ck中的实现如下：

一、数据准备：建表

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;

三：数据实现：方法1

select g, (arrayJoin(tuple_ll) as ll).1 a, ll.2 prev, ll.3 next
from (
select g, arrayMap( i,j,k -> (i,j,k),
                    arraySort(groupArray(a)) as aa,
                    arrayPopBack(arrayPushFront(aa, toDate(0))),
                    arrayPopFront(arrayPushBack(aa, toDate(0))) ) tuple_ll
from llexample
group by g)
order by g, a;

三：数据实现：方法2

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;

三：数据实现：方法3

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;

三：数据实现：方法4

select
    g,
    a,
    neighbor(a, -1) AS prev,
    neighbor(a, 1) AS next
from
(
    select *
    from llexample
    order by
        g asc,
        a asc
);