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