Q: Comment puis-je classer les enregistrements basés sur la valeur changeante de 1 colonne?
J'ai les données suivantes ( https://pastebin.com/vdtb1jrt ): P>
WITH CTE AS ( SELECT EmployeeID = 'ABH12345', [Date] = CAST(N'2016-01-01' AS Date), [Onleave] = 0 UNION SELECT 'ABH12345', CAST(N'2016-01-02' AS Date), 0 UNION SELECT 'ABH12345', CAST(N'2016-01-03' AS Date), 0 UNION SELECT 'ABH12345', CAST(N'2016-01-04' AS Date), 0 UNION SELECT 'ABH12345', CAST(N'2016-01-05' AS Date), 0 UNION SELECT 'ABH12345', CAST(N'2016-01-06' AS Date), 0 UNION SELECT 'ABH12345', CAST(N'2016-01-07' AS Date), 0 UNION SELECT 'ABH12345', CAST(N'2016-01-08' AS Date), 0 UNION SELECT 'ABH12345', CAST(N'2016-01-09' AS Date), 0 UNION SELECT 'ABH12345', CAST(N'2016-01-10' AS Date), 1 UNION SELECT 'ABH12345', CAST(N'2016-01-11' AS Date), 1 UNION SELECT 'ABH12345', CAST(N'2016-01-12' AS Date), 1 UNION SELECT 'ABH12345', CAST(N'2016-01-13' AS Date), 1 UNION SELECT 'ABH12345', CAST(N'2016-01-14' AS Date), 0 UNION SELECT 'ABH12345', CAST(N'2016-01-15' AS Date), 0 UNION SELECT 'ABH12345', CAST(N'2016-01-16' AS Date), 0 UNION SELECT 'ABH12345', CAST(N'2016-01-17' AS Date), 0 ) SELECT * FROM CTE
3 Réponses :
Ceci est un exemple de problème de groupe et de îles. Dans ce cas, vous pouvez utiliser la date arithmétique. L'observation clé est que la soustraction d'une séquence d'entiers à partir de la colonne Date identifie les îles de valeurs similaires.
comme une requête, cela ressemble à: p> Vous pouvez exécuter La sous-requête, regarder aux résultats et faire l'arithmétique pour voir pourquoi cela fonctionne. P> Voici le Exemple . P> p>
Intéressant .. La sortie est un peu toujours la même d'où j'ai commencé. Comment puis-je être capable de résumer le résultat en seulement 3 lignes?
Un moyen de plus de le faire avec Lag code>. Attribuez des groupes en obtenant la valeur précédente OnLeave pour chaque employéId et en le réinitialisant lorsqu'une valeur différente est trouvée.
select employeeid,min(date) as date_from,max(date) as date_to,max(onleave) as onleave
from (select t.*,sum(case when prev_ol=onleave then 0 else 1 end) over(partition by employeeid order by date) as grp
from (select c.*,lag(onleave,1,onleave) over(partition by employeeid order by date) as prev_ol
from cte c
) t
) t
group by employeeid,grp
Fonctionne comme un charme! Attribuer des groupes à l'aide de la gamme. C'est malin. Merci beaucoup!
Voici un autre, un peu plus simple, un moyen d'obtenir la sortie souhaitée - accéder à la table une seule fois. Résultat: P> employeeid datevalidfrom datevalidto onleave
---------- ------------- ----------- -----------
ABH12345 2016-01-01 2016-01-09 0
ABH12345 2016-01-10 2016-01-13 1
ABH12345 2016-01-14 2016-01-17 0
Plus 1 pour les échantillons de données
Conseil: Il est utile de taguer des questions sur la base de données avec le logiciel approprié (mysql, oracle, db2, ...) et la version, par ex.
SQL-Server-2014 code>. Les différences de syntaxe et de fonctionnalités affectent souvent les réponses. Dans ce cas,
Lag code> est une fonctionnalité relativement nouvelle.
ajouté SQL-Server-2014, merci @habo