之前在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;
![](https://i-blog.csdnimg.cn/blog_migrate/d813cebb0f7177d2d11ff37989e3480f.png)
三:数据实现:方法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;
![](https://i-blog.csdnimg.cn/blog_migrate/9e4b3ee7579397b8a7e4014fde8b34c0.png)
三:数据实现:方法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
);