SQL Найти последовательные числа в группах

Не было времени полировать это... но это один из вариантов.

• Первый CTE (c) создает новый идентификатор столбца
• Второй CTE (mx) получает максимальный номер строки, поскольку вы не можете использовать агрегаты в рекурсивных CTE.

• Конечный CTE (rc) - это то, где мясо.

  ;WITH c AS (
      SELECT ROW_NUMBER() OVER (ORDER BY [UID],[Hour]) Id, 
          [UID],Avail,[Hour]
      FROM #tmp
  ), mx AS (
      SELECT MAX(Id) MaxRowCount FROM c
  ), rc AS (
  
      SELECT Id, [UID], Avail, [Hour], c.Avail AS CummulativeHour
      FROM c
      WHERE Id = 1
  
      UNION ALL
  
      SELECT c.Id, c.[UID], c.Avail, c.[Hour], CASE WHEN rc.Avail = 0 OR c.Avail = 0 OR rc.[UID] <> c.[UID] THEN c.Avail
                                                  WHEN rc. Avail = 1 AND c.Avail = 1 THEN rc.CummulativeHour + 1 END AS CummulativeHour
      FROM rc
      JOIN c
          ON rc.Id + 1 = c.Id
      WHERE c.Id <= (SELECT mx.MaxRowCount FROM mx)
  
  )
  SELECT * FROM rc
  

Вот пример создания данных...

CREATE TABLE #tmp ([UID] INT, Avail INT, [Hour] INT)

INSERT INTO #tmp
        ( UID, Avail, Hour )
VALUES  (123,1,0),
(123,1,1),
(123,0,2),
(123,0,3),
(123,0,4),
(123,1,5),
(123,1,7),
(123,1,8),
(341,1,0),
(341,0,2),
(341,1,3),
(341,1,4),
(341,0,5),
(341,1,6),
(341,1,7),
(341,0,8)

Основной запрос с несколькими CTE ниже дает вам несколько возможностей, чтобы показать, что вам нужно (максимум на пользователя, пользователь с N часами и т. д.). Просто обновите последний запрос под CTE.

Создайте таблицу и данные:

declare @hours table(
uid int
, avail bit
, h tinyint
)
insert into @hours(uid, avail, h) values 
(123, 1, 0),
(123, 1, 1),
(123, 0, 2),
(123, 0, 3),
(123, 0, 4),
(123, 1, 5),
(123, 1, 6),
(123, 1, 7),
(123, 1, 8),
(341, 1, 0),
(341, 1, 1),
(341, 0, 2),
(341, 1, 3),
(341, 1, 4),
(341, 0, 5),
(341, 1, 6), 
(341, 1, 7),
(341, 0, 8),
(341, 1, 23) -- loop through midnight

Последняя строка была добавлена, чтобы показать, что она может обнаруживать непрерывные часы около полуночи (см. рисунок сзади). т. е. 23 => 2 часа ночи для uid 341

Запрос MAX непрерывных часов на пользователя:

-- remove duplicate, wrong hours and not available hours
;with hs as (
    Select distinct uid, h from @hours where avail = 1 and h < 24 
), loop(uid, first, last, diff) as (
    --loop through successive hours
    select uid, cast(h as tinyint), cast(h+1 as int), cast(1 as int) from hs
    union all
    select l.uid, l.first, l.last+1, l.diff+1 from loop as l
    inner join hs as h on l.uid = h.uid and l.last = h.h
), back(uid, first, last, diff) as (
    -- search for successive hours around midnight
    select uid, first, last, diff from loop
    union
    select l1.uid, l1.first, l2.last, l1.diff+l2.diff from loop as l1
    inner join loop as l2 on l1.uid = l2.uid and l1.last = 24 and l2.first = 0
), mx(uid, diff) as (
    -- get max continuous hours per user
    select uid, max(diff) from back group by uid
)
-- main query, change it based on what you need (see below)
select b.uid, b.first, b.last, b.diff from back as b
inner join mx as m on m.uid = b.uid and m.diff = b.diff
order by uid, diff desc

полученные результаты:

uid first   last    diff
123 5       9       4
341 23      2       3 <= present because I added 341/1/23. Would not be here otherwise

Получите пользователя как минимум с 3 непрерывными часами (замените последний выбор на этот):

select distinct uid from back where diff >= 3 -- @n goes here

Пожалуйста, обратите внимание, что я считал, что (123, 1, 5) дает 1 доступный час с 5 до 6. Следовательно, от 5 до 8 дает вам 4 доступных часа с 5 до 9.