Using Row_Number to find consecutive row count

Question

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.

Answer 1

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