SQL Trouver des nombres consécutifs dans des groupes

Si vous utilisez SQL Server 2012+, vous pouvez utiliser une SUM fenêtrée, mais vous devez spécifier le nombre de lignes dans le cadre de la window à l'avance car il n'acceptera pas les variables, ce n'est donc pas si flexible:

 ;with cte as ( select distinct UID, SUM(avail) over (partition by uid order by hour rows between current row and 2 following ) count from table1 ) select uid from cte where count = 3; 

Si vous voulez de la flexibilité, vous pouvez en faire une procédure stockée et utiliser SQL dynamic pour build et exécuter l'instruction, quelque chose comme ceci:

 create procedure testproc (@n int) as declare @sql nvarchar(max) set @sql = concat(' ;with cte as ( select distinct UID, SUM(avail) over (partition by uid order by hour rows between current row and ', @n - 1 , ' following ) count from table1 ) select uid from cte where count = ' , @n , ';') exec sp_executesql @sql 

et l'exécuter en utilisant execute testproc 3

Une solution encore plus inflexible consiste à utiliser des sous-requêtes corrélées, mais vous devez ensuite append une autre sous-requête pour chaque count ajouté:

 select distinct uid from Table1 t1 where Avail = 1 and exists (select 1 from Table1 where Avail = 1 and UID = t1.UID and Hour = t1.Hour + 1) and exists (select 1 from Table1 where Avail = 1 and UID = t1.UID and Hour = t1.Hour + 2); 

Et encore une autre façon, en utilisant row_number pour find des îles et en filtrant par sum de disponibilité pour chaque île:

 ;with c as ( select uid, avail, row_number() over (partition by uid order by hour) - row_number() over (partition by uid, avail order by hour) grp from table1 ) select uid from c group by uid, grp having sum(avail) >= 3 

Cela fonctionne … Il fait une jointure automatique sur userID et tout ce qui se trouve dans la 2ème table avec in @n (3hr) ne renvoie que les loggings ayant un nombre de 3 loggings.

 SELECT A.UID FROM foo A INNER JOIN foo B on A.UId = B.UID and A.Hour+3 <= B.Hour and A.Avail= 1 and B.Avail=1 GROUP BY A.UID HAVING count(distinct B.hour) =3 

http://sqlfiddle.com/#!6/f97ee

Je n'ai pas eu le time de polir ça … mais c'est une option.

• Le premier CTE (c) crée le nouvel identifiant de colonne
• Le second CTE (mx) obtient le nombre de lignes maximum car vous ne pouvez pas utiliser les agrégats dans les CTE récursifs

• Le CTE final (rc) est l'endroit où la viande est.

   ;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 

Voici l'exemple de création de données …

 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) 

La requête principale avec plusieurs CTE ci-dessous vous donne plusieurs possibilités pour montrer ce dont vous avez besoin (max par user, user avec N heures, etc.). Juste mettre à jour la dernière requête ci-dessous le CTE.

Créer une table et des données:

 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 

La dernière ligne a été ajoutée pour montrer qu'elle peut détecter des heures continues vers minuit (voir return cte). soit 23 => 2 heures pour l'uid 341

Interroger MAX heures par user:

 -- 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 = hh ), 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 

résultats:

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

Obtenez l'user avec au less 3 heures continues (remplacez le dernier choix par celui-ci):

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

S'il vous plaît ne pas que j'ai considéré que (123, 1, 5) donnent 1 heure disponible de 5 à 6. Donc 5 à 8 vous donne 4 heures disponibles de 5 à 9 heures.