SQLite query to GROUP BY nearby row

Question

I have a SQLite .db file that contains the Thread table that looks like this:

ThreadID  ClusterID
1         0
2         0
3         0
4         1
5         1
6         0
7         1
8         1
9         0
10        1

And I would like to GROUP BY the ClusterID by only with the nearby row. Output would be:

ThreadID  ClusterID
1         0     
4         1
6         0
7         1
9         0
10        1

Or ideally:

ThreadID  ClusterID ClusterSwitch
1         0         NO
2         0         NO
3         0         NO
4         1         YES
5         1         NO
6         0         YES
7         1         YES
8         1         NO
9         0         YES
10        1         YES

The whole design its to detect when a cluster switched from 0 to 1 and from 1 to 0

Thanks for your help it is really appreciated :) -Steve

Answer 1

Assuming your thread ids are really in order with no gaps, you can just use a self join:

select t.*,
       (case when tprev.clusterid <> t.clusterid then 1 else 0 end) as ClusterSwitch
from threads t left join
     threads tprev
     on t.threadid = tprev.threadid + 1;

If you cannot be sure of no gaps, you can do this with a correlated subquery:

select t.*,
       (case when t.clusterid <>
                  (select t2.clusterid
                   from threads t2
                   where t2.id < t.id 
                   order by t2.id desc
                   limit 1
                  )
             then 1 else 0 end) as ClusterSwitch
from threads t;

However, this query will not scale well, so performance could be an issue.