How to split data into groups in pyspark

Question

I need to find groups in time series data.

Data sample

I need to output column group based on value and day.

I've tried using lag, lead and row_number but it ended up to nothing.

Answer 1

It seems like you want to increment the group everytime the value changes. If so, this is a kind of gaps-and-islands problem.

Here is one approach that uses lag() and a cumulative sum():

select
    value,
    day,
    sum(case when value = lag_value then 0 else 1 end) over(order by day) grp
from (
    select t.*, lag(value) over(order by day) lag_value
    from mytable t
) t