Groupe SQL par si les valeurs sont proches

Ceux-ci donnent le résultat correct, en utilisant le fait que vous devez avoir le même nombre de départs de groupes que de fins et qu'ils seront tous deux dans l'ordre croissant.

 if object_id('tempdb..#temp') is not null drop table #temp create table #temp (class char(1),Value int); insert into #temp values ('A',1); insert into #temp values ('A',2); insert into #temp values ('A',3); insert into #temp values ('A',10); insert into #temp values ('A',13); insert into #temp values ('A',14); insert into #temp values ('b',7); insert into #temp values ('b',8); insert into #temp values ('b',9); insert into #temp values ('b',12); insert into #temp values ('b',22); insert into #temp values ('b',26); insert into #temp values ('b',67); 

Méthode 1 Utilisation de CTE et décalages de ligne

 with cte as (select distinct class,value,ROW_NUMBER() over ( partition by class order by value ) as R from #temp), cte2 as ( select c1.class ,c1.value ,c2.R as PreviousRec ,c3.r as NextRec from cte c1 left join cte c2 on (c1.class = c2.class and c1.R= c2.R+1 and c1.Value < c2.value + 5) left join cte c3 on (c1.class = c3.class and c1.R= c3.R-1 and c1.Value > c3.value - 5) ) select Starts.Class ,Starts.Value as StartValue ,Ends.Value as EndValue from ( select class ,value ,row_number() over ( partition by class order by value ) as GroupNumber from cte2 where PreviousRec is null) as Starts join ( select class ,value ,row_number() over ( partition by class order by value ) as GroupNumber from cte2 where NextRec is null) as Ends on starts.class=ends.class and starts.GroupNumber = ends.GroupNumber 

** Méthode 2 Les vues en ligne ne sont pas utilisées **

 select Starts.Class ,Starts.Value as StartValue ,Ends.Value as EndValue from ( select class,Value ,row_number() over ( partition by class order by value ) as GroupNumber from (select distinct class,value from #temp) as T where not exists (select 1 from #temp where class=t.class and Value < t.Value and Value > t.Value -5 ) ) Starts join ( select class,Value ,row_number() over ( partition by class order by value ) as GroupNumber from (select distinct class,value from #temp) as T where not exists (select 1 from #temp where class=t.class and Value > t.Value and Value < t.Value +5 ) ) ends on starts.class=ends.class and starts.GroupNumber = ends.GroupNumber 

Dans les deux methods j'utilise un select distinct pour commencer car si vous avez une input dulpicate au début ou à la fin d'un groupe, les choses tournent mal sans cela.

Vous essayez de grouper les choses par des écarts entre les valeurs. La façon la plus simple de le faire est d'utiliser la fonction lag() pour find les écarts:

 select class, min(value) as minvalue, max(value) as maxvalue from (select class, value, sum(IsNewGroup) over (partition by class order by value) as GroupId from (select class, value, (case when lag(value) over (partition by class order by value) > value - 5 then 0 else 1 end) as IsNewGroup from t ) t ) t group by class, groupid; 

Notez que cela suppose SQL Server 2012 pour l'utilisation de lag() et de la sum cumulative.

Mise à jour: * Cette réponse est incorrecte *

En supposant que la table que vous avez donnée s'appelle sd_test, la requête suivante vous donnera la sortie que vous attendez

En bref, nous avons besoin d'un moyen de find quelle était la valeur de la ligne précédente. Ceci est déterminé en utilisant une jointure sur les identifiants de ligne. Ensuite, créez un groupe pour voir si la différence est inférieure à 5. puis c'est juste un 'Group By' régulier.

Si votre version de SQL Server prend en charge les fonctions de fenêtrage avec partitionnement, le code serait beaucoup plus lisible.

 SELECT A.CLASS ,MIN(A.VALUE) AS MIN_VALUE ,MAX(A.VALUE) AS MAX_VALUE FROM (SELECT ROW_NUMBER()OVER(PARTITION BY CLASS ORDER BY VALUE) AS ROW_ID ,CLASS ,VALUE FROM SD_TEST) AS A LEFT JOIN (SELECT ROW_NUMBER()OVER(PARTITION BY CLASS ORDER BY VALUE) AS ROW_ID ,CLASS ,VALUE FROM SD_TEST) AS B ON A.CLASS = B.CLASS AND A.ROW_ID=B.ROW_ID+1 GROUP BY A.CLASS,CASE WHEN ABS(COALESCE(B.VALUE,0)-A.VALUE)<5 THEN 1 ELSE 0 END ORDER BY A.CLASS,cASE WHEN ABS(COALESCE(B.VALUE,0)-A.VALUE)<5 THEN 1 ELSE 0 END DESC 

ps: Je pense que ce qui précède est conforme à ANSI. Donc devrait fonctionner dans la plupart des variantes SQL. Quelqu'un peut me corriger si ce n'est pas le cas.

Voici une façon d'get l'information que vous searchz:

 SELECT Under5.Class, ( SELECT MIN(m2.Value) FROM MyTable AS m2 WHERE m2.Value < 5 AND m2.Class = Under5.Class ) AS ValueMin, ( SELECT MAX(m3.Value) FROM MyTable AS m3 WHERE m3.Value < 5 AND m3.Class = Under5.Class ) AS ValueMax FROM ( SELECT DISTINCT m1.Class FROM MyTable AS m1 WHERE m1.Value < 5 ) AS Under5 UNION SELECT Over4.Class, ( SELECT MIN(m4.Value) FROM MyTable AS m4 WHERE m4.Value >= 5 AND m4.Class = Over4.Class ) AS ValueMin, ( SELECT Max(m5.Value) FROM MyTable AS m5 WHERE m5.Value >= 5 AND m5.Class = Over4.Class ) AS ValueMax FROM ( SELECT DISTINCT m6.Class FROM MyTable AS m6 WHERE m6.Value >= 5 ) AS Over4