CODEWITHSUNDEEP
sqloracle-database
Michal Pravda
Michal Pravda

Reputation: 829

get intervals of nonchanging value from a sequence of numbers

I need to sumarize a sequence of values into intervals of nonchanging values - begin, end and value for each such interval. I can easily do it in plsql but would like a pure sql solution for both performance and educational reasons. I have been trying for some time to solve it with analytical functions, but can't figure how to properly define windowing clause. The problem I am having is with a repeated value.

Simplified example - given input:

id  value
1   1
2   1
3   2
4   2
5   1

I'd like to get output

from to val
1    2   1
3    4   2
5    5   1

Upvotes: 0

Views: 291

Answers (2)

Valeklosse
Valeklosse

Reputation: 1017

Using a CTE to collect all the rows and identifying them into changing values, then finally grouping together for the changing values.

CREATE TABLE #temp (
    ID      INT NOT NULL IDENTITY(1,1),
    [Value] INT NOT NULL
)
GO

INSERT INTO #temp ([Value]) 
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 2 UNION ALL
    SELECT 1;


WITH Marked AS (
  SELECT
    *,
    grp = ROW_NUMBER() OVER (ORDER BY ID)
        - ROW_NUMBER() OVER (PARTITION BY Value     ORDER BY ID)
  FROM #temp
)
SELECT MIN(ID) AS [From], MAX(ID) AS [To], [VALUE]
FROM Marked
GROUP BY grp, Value
ORDER BY MIN(ID)

DROP TABLE #temp;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want to identify groups of adjacent values. One method is to use lag() to find the beginning of the sequence, then a cumulative sum to identify the groups.

Another method is the difference of row number:

select value, min(id) as from_id, max(id) as to_id
from (select t.*,
             (row_number() over (order by id) -
              row_number() over (partition by val order by id
             ) as grp
      from table t
     ) t
group by grp, value;

Upvotes: 3

Related Questions