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