Skip to content

Instantly share code, notes, and snippets.

@chadbaldwin
Last active September 1, 2023 00:57
Show Gist options
  • Save chadbaldwin/19f83b2d3eb48de04f103767484803b1 to your computer and use it in GitHub Desktop.
Save chadbaldwin/19f83b2d3eb48de04f103767484803b1 to your computer and use it in GitHub Desktop.
Find repeating number runs of length N
IF OBJECT_ID('tempdb..#gaps','U') IS NOT NULL DROP TABLE #gaps; --SELECT * FROM #gaps
CREATE TABLE #gaps (
ID int NOT NULL IDENTITY,
Val int NOT NULL,
);
DECLARE @repeat int, @randval int, @sql nvarchar(MAX);
DECLARE @c int = 1000; -- How many islands to create
WHILE (@c > 0)
BEGIN;
SELECT @repeat = FLOOR(RAND(CHECKSUM(NEWID()))*(20))+1 -- How many consecutive rows to insert
, @randval = FLOOR(RAND(CHECKSUM(NEWID()))*(100))+1 -- What random value to use
SELECT @sql = CONCAT('INSERT INTO #gaps (Val) VALUES ','(',@randval,')', REPLICATE(CONCAT(',(',@randval,')'), @repeat-1))
EXEC sys.sp_executesql @sql;
SET @c -= 1;
END;
------------------------------------------------------------------------------
GO
------------------------------------------------------------------------------
-- Find islands of size N
DECLARE @n int = 10
SELECT x.IslandID, IslandStart = MIN(x.ID), IslandEnd = MAX(x.ID), Val = MAX(x.Val), IslandSize = COUNT(*)
FROM (
SELECT x.ID, x.Val
, IslandID = SUM(x.IslandStart) OVER (ORDER BY x.ID)
FROM (
SELECT g.ID, g.Val
, IslandStart = IIF(g.Val <> LAG(g.Val, 1, 0) OVER (ORDER BY g.ID), 1, 0)
FROM #gaps g
) x
) x
GROUP BY x.IslandID
HAVING COUNT(*) = @n
ORDER BY x.IslandID
@chadbaldwin
Copy link
Author

[reserving first comment]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment