How to get longest consecutive same value?-CodePudding

How to get the rows of the longest consecutive same value?

Table Learning:

rowID	values
1	1
2	1
3	0
4	0
5	0
6	1
7	0
8	1
9	1
10	1

Longest consecutive value is 1 (rowID 8-10 as rowID 1-2 is 2 and rowID 6-6 is 1). How to query to get the actual rows of consecutive values (not just rowStart and rowEnd values) like :

rowID	values
8	1
9	1
10	1

And for longest consecutive values of both 1 and 0?

DB Fiddle

CodePudding user response：

I think that the simplest approach is to use a window count to define the islands. Then to get the "longest" island, we just need to aggregate, sort and limit:

select min(valueid) grp_start, max(valueid) grp_end 
from (select t.*, sum(value = 0) over(order by valueid) grp from testing t) t
where value = 1
group by grp
order by count(*) desc limit 1

In the DB Fiddle that you provided, the query returns:

grp_start	grp_end
8	10

CodePudding user response：

This is a gaps and islands problem, and one approach is to use the difference in row numbers method:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY rowID) rn1,
              ROW_NUMBER() OVER (PARTITION BY values ORDER BY rowID) rn2
    FROM yourTable
),
cte2 AS (
    SELECT *,
        MIN(rowID) OVER (PARTITION BY values, rn1 - rn2) AS minRowID,
        MAX(rowID) OVER (PARTITION BY values, rn1 - rn2) AS maxRowID
    FROM cte1
),
cte3 AS (
    SELECT *, RANK() OVER (PARTITION BY values ORDER BY maxRowID - minRowID DESC) rnk
    FROM cte2
)

SELECT rowID, values
FROM cte3
WHERE rnk = 1
ORDER BY values, rowID;