Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this column of ints that represent an occurrence of a signal and I'm trying to add a column that shows the count of consecutive row

If my data looks like this

724
727
728
733
735
737
743
747
749

the resulting data with a consecutive row count column would look like this

724 1
727 1
728 2
729 3
735 1
737 1
743 1
744 2
748 1

I've done it using a looping function but I'm trying to figure out using a cte. Here is a sample of my latest attempt

DECLARE @d TABLE ( signal INT )
INSERT  INTO @d
        SELECT  724
        UNION
        SELECT  727
        UNION
        SELECT  728
        UNION
        SELECT  729
        UNION
        SELECT  735
        UNION
        SELECT  737
        UNION
        SELECT  743
        UNION
        SELECT  744
        UNION
        SELECT  748 ;
WITH    a AS ( SELECT   signal,
                        ROW_NUMBER() OVER ( ORDER BY signal ) AS marker
               FROM     @d
             ) ,
        b AS ( SELECT   a1.signal,
                        CASE ( a1.signal - a2.signal )
                          WHEN 1 THEN 1
                          ELSE 0
                        END consecutiveMarker
               FROM     a a1
                        INNER JOIN a a2 ON a2.marker = a1.marker - 1
             )
    SELECT  *
    FROM    b

Produces these results

signal  consecutiveMarker
727 0
728 1
729 1
735 0
737 0
743 0
744 1
748 0

The first obvious issue is missing the first signal in a series. Barring that, I thought I could then pass this to another cte with a row_number partitioning on the consecutiveMarker. That didn't work because it partitioned it as one partition. I couldn't find a way to indicate to the partitioning method that one series is separate from the next

Any help is appreciated.

share|improve this question
    
There seems to be a mismatch between source data and desired results. –  Martin Smith Oct 9 '11 at 14:49

3 Answers 3

The general name for this type of query is "gaps and islands". One approach below. If you can have duplicates in the source data you might need dense_rank rather than row_number

WITH DATA(C) AS
(
SELECT 724 UNION ALL
SELECT 727 UNION ALL
SELECT 728 UNION ALL
SELECT 729 UNION ALL
SELECT 735 UNION ALL
SELECT 737 UNION ALL
SELECT 743 UNION ALL
SELECT 744 UNION ALL
SELECT 747 UNION ALL
SELECT 749
), T1 AS
(
SELECT C,
       C - ROW_NUMBER() OVER (ORDER BY C) AS Grp
FROM DATA)
SELECT C,
       ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY C) AS Consecutive
FROM T1

Returns

C           Consecutive
----------- --------------------
724         1
727         1
728         2
729         3
735         1
737         1
743         1
744         2
747         1
749         1
share|improve this answer
    
Brilliant !! I know about gaps and island though never I have never had to tackle them before. I couldn't see the forest thru the trees here. Thank you! –  OrangeYoda Oct 9 '11 at 15:04
    
@Martin Smith , This was helpful. How does the C- rownumber actually apply when using this for establishing consecutive months thought? –  g00p3k Mar 26 '12 at 23:09
1  
@g00p3k - You can use a similar approach but need to convert the months to integers first. e.g. DATEPART(YEAR, YourCol) * 12 + DATEPART(MONTH, YourCol) –  Martin Smith Mar 27 '12 at 13:12

In SQL 2012 you can also do this using LAG and the window functions, eg

DECLARE @d TABLE ( signal INT PRIMARY KEY) 

INSERT INTO @d 
VALUES
    ( 724 ),
    ( 727 ),
    ( 728 ),
    ( 729 ),
    ( 735 ),
    ( 737 ),
    ( 743 ),
    ( 744 ),
    ( 748 )

SELECT signal
    , 1 + ( SUM( is_group ) OVER ( ORDER BY signal ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING ) * is_group )
FROM
    (
    SELECT *
        , CASE WHEN LAG(signal) OVER( ORDER BY signal ) = signal - 1 THEN 1 ELSE 0 END is_group
    FROM @d
    ) x
share|improve this answer

As usual with such problems, it is very easy to accomplish in Java or C++ or C#.

If you really need to do it in the database, you can use an RDBMS with fast cursors, such as Oracle, write a simple cursor, and enjoy fast performance without having to write anything complex.

If you need to do it in T-SQL, and you cannot change database design, Itzik Ben-Gan has written up several solutions in "MVP Deep Dives vol 1", and some new solutions using OLAP functions in his new book about window functions in SQL 2012.

Alternatively, you can add another column consecutiveMarker to your table, and store precalculated values in it. We can use constraints to ensure that pre-calculated data is always valid. If anyone is interested, I can explain how.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.