Group close happening events together in ClickHouse

Question

I have ordered table with events, having their time (in ClickHouse table):

time	event
10:30:00	event1
10:30:05	event2
10:30:59	event2
10:31:00	event2
10:31:00	event1

For me, the same events happening in a row are the same. I need function that will merge these events together. Expected result is:

time	event
10:30:00	event1
10:30:05 (it can be any time from recurring events)	event2
10:31:00	event1

Answer 1

you can use window funtions like so to group the gaps and islands :

with cte as (
select * 
, rank() over (order by time) - rank() over(partition by event order by time) as grp
from EventLog
)

select min(time) as time, event 
from cte 
group by grp, event
order by time 

output:

time	event
10:30:00	event1
10:30:05	event2
10:31:00	event1

you can use max() to get the last time of the same event instead.