patternModerate
Using Row_Number to find consecutive row count
Viewed 0 times
usingrow_numberfindcountrowconsecutive
Problem
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
the resulting data with a consecutive row count column would look like this
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
Produces these results
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.
If my data looks like this
724
727
728
733
735
737
743
747
749the 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 1I'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 bProduces these results
signal consecutiveMarker
727 0
728 1
729 1
735 0
737 0
743 0
744 1
748 0The 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.
Solution
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
Returns
dense_rank rather than row_numberWITH 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 T1Returns
C Consecutive
----------- --------------------
724 1
727 1
728 2
729 3
735 1
737 1
743 1
744 2
747 1
749 1Code Snippets
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 T1C Consecutive
----------- --------------------
724 1
727 1
728 2
729 3
735 1
737 1
743 1
744 2
747 1
749 1Context
StackExchange Database Administrators Q#6632, answer score: 17
Revisions (0)
No revisions yet.