MS SQL Server
  1. MS SQL Server
  3. Si la différence entre deux séquences est supérieure à 30, déduire une plus grande séquence
Intereting Posts
Comment contrôler les éléments de données inexistants dans le graphique SSRS lors de l'exécution? Y a-t-il une différence réelle dans les deux instructions SQL suivantes (mises à jour qui n'entraînent aucune modification nette)? Aplatissement de la relation 1-à-plusieurs Créer un script d'index de text intégral sur toutes les colonnes sans spécifier chaque colonne par son nom Comment apporter des modifications en direct à la database SQL Obtenir le nième niveau sur la table d'auto-referencement Tables temporaires dans le server SQL ADO.NET – La valeur de bit SQL "1" est toujours lue comme "false" par DataReader Remplacer la fonction dans SQL Server 2014 ajoute des zéros de fin dans le champ Char Agréger sur la colonne Datetime pour le pivot Ordre par statut de promotion (startDate <getDate () <endDate)? Comment grouper des lignes séparées par une ligne spécifique dans SQL Comment extraire une colonne de database (et ses données) dans une nouvelle table? Utilisation de bcp.exe pour importer des données, même si l'ordre des colonnes dans le file de format diffère de celui de la table search de requêtes possibles pour afficher une sortie similaire au tableau croisé dynamic dans sql server 2005

Si la différence entre deux séquences est supérieure à 30, déduire une plus grande séquence

J'ai du mal à faire une requête qui obtient beaucoup de nombres, une séquence de nombres, et si la différence entre deux d'entre eux est supérieure à 30, la séquence se réinitialise à partir de ce nombre. Donc, j'ai le tableau suivant, qui a une autre colonne autre que le numéro un, qui devrait être maintenu intact: 

+----+--------+--------+ | Id | Number | Status | +----+--------+--------+ | 1 | 1 | OK | | 2 | 1 | Failed | | 3 | 2 | Failed | | 4 | 3 | OK | | 5 | 4 | OK | | 6 | 36 | Failed | | 7 | 39 | OK | | 8 | 47 | OK | | 9 | 80 | Failed | | 10 | 110 | Failed | | 11 | 111 | OK | | 12 | 150 | Failed | | 13 | 165 | OK | +----+--------+--------+

Cela devrait le transformer en celui-ci: 

 +----+--------+--------+ | Id | Number | Status | +----+--------+--------+ | 1 | 1 | OK | | 2 | 1 | Failed | | 3 | 2 | Failed | | 4 | 3 | OK | | 5 | 4 | OK | | 6 | 1 | Failed | | 7 | 4 | OK | | 8 | 12 | OK | | 9 | 1 | Failed | | 10 | 1 | Failed | | 11 | 2 | OK | | 12 | 1 | Failed | | 13 | 16 | OK | +----+--------+--------+

Merci pour votre attention, je serai disponible pour effacer tout doute concernant mon problème! 🙂

EDIT: Exemple de cette table ici: http://sqlfiddle.com/#!6/ded5af

Avec ce cas de test: 

 declare @data table (id int identity, Number int, Status varchar(20)); insert @data(number, status) values ( 1,'OK') ,( 1,'Failed') ,( 2,'Failed') ,( 3,'OK') ,( 4,'OK') ,( 4,'OK') -- to be deleted, ensures IDs are not sequential ,(36,'Failed') -- to be deleted, ensures IDs are not sequential ,(36,'Failed') ,(39,'OK') ,(47,'OK') ,(80,'Failed') ,(110,'Failed') ,(111,'OK') ,(150,'Failed') ,(165,'OK') ; delete @data where id between 6 and 7;

Ce SQL: 

 with renumbered as ( select rn = row_number() over (order by id), data.* from @data data ), paired as ( select this.*, startNewGroup = case when this.number - prev.number >= 30 or prev.id is null then 1 else 0 end from renumbered this left join renumbered prev on prev.rn = this.rn -1 ), groups as ( select Id,Number, GroupNo = Number from paired where startNewGroup = 1 ) select Id ,Number = 1 + Number - ( select top 1 GroupNo from groups where groups.id <= paired.id order by GroupNo desc) ,status from paired ;

rendements comme souhaité: 

 Id Number status ----------- ----------- -------------------- 1 1 OK 2 1 Failed 3 2 Failed 4 3 OK 5 4 OK 8 1 Failed 9 4 OK 10 12 OK 11 1 Failed 12 1 Failed 13 2 OK 14 1 Failed 15 16 OK

Mise à jour : l'utilisation de la nouvelle fonction LAG () permet un SQL un peu plus simple sans auto-jonction précoce: 

 with renumbered as ( select data.* ,gap = number - lag(number, 1) over (order by number) from @data data ), paired as ( select *, startNewGroup = case when gap >= 30 or gap is null then 1 else 0 end from renumbered ), groups as ( select Id,Number, GroupNo = Number from paired where startNewGroup = 1 ) select Id ,Number = 1 + Number - ( select top 1 GroupNo from groups where groups.id <= paired.id order by GroupNo desc ) ,status from paired ;

Je ne mérite pas de réponse mais je pense que c'est encore plus court 

 with gapped as ( select id, number, gap = number - lag(number, 1) over (order by id) from @data data ), select Id, status ReNumber = Number + 1 - isnull( (select top 1 gapped.Number from gapped where gapped.id <= data.id and gap >= 30 order by gapped.id desc), 1) from @data data;

C'est simplement la réponse de Pieter Geerkens légèrement simplifiée. J'ai supprimé quelques résultats et colonnes intermédiaires: 

 with renumbered as ( select data.*, gap = number - lag(number, 1) over (order by number) from @data data ), paired as ( select * from renumbered where gap >= 30 or gap is null ) select Id, Number = 1 + Number - (select top 1 Number from paired where paired.id <= renumbered.id order by Number desc) , status from renumbered;

Cela aurait dû être un commentaire, mais c'est trop long et cela ne serait pas compréhensible.

Vous devrez peut-être créer un autre cte avant cela et utiliser row_number au lieu de ID pour join le cte récursif, si vos ID ne sont pas dans l'ordre séquentiel 

 WITH cte AS ( SELECT Id, [Number], [Status], 0 AS Diff, [Number] AS [NewNumber] FROM Table1 WHERE Id = 1 UNION ALL SELECT t1.Id, t1.[Number], t1.[Status], CASE WHEN t1.[Number] - cte.[Number] >= 30 THEN t1.Number - 1 ELSE Diff END, CASE WHEN t1.[Number] - cte.[Number] >= 30 THEN 1 ELSE t1.[Number] - Diff END FROM Table1 t1 JOIN cte ON cte.Id + 1 = t1.Id ) SELECT Id, [NewNumber], [Status] FROM cte

SQL Fiddle

Voici un autre Fiddle SQL avec un exemple de ce que vous feriez si l'ID n'est pas séquentiel.

SQL Fiddle 2

Au cas où sql fiddle arrête de fonctionner 

 --Order table to make sure there is a sequence to follow WITH OrderedSequence AS ( SELECT ROW_NUMBER() OVER (ORDER BY Id) RnId, Id, [Number], [Status] FROM Sequence ), RecursiveCte AS ( SELECT Id, [Number], [Status], 0 AS Diff, [Number] AS [NewNumber], RnId FROM OrderedSequence WHERE Id = 1 UNION ALL SELECT t1.Id, t1.[Number], t1.[Status], CASE WHEN t1.[Number] - cte.[Number] >= 30 THEN t1.Number - 1 ELSE Diff END, CASE WHEN t1.[Number] - cte.[Number] >= 30 THEN 1 ELSE t1.[Number] - Diff END, t1.RnId FROM OrderedSequence t1 JOIN RecursiveCte cte ON cte.RnId + 1 = t1.RnId ) SELECT Id, [NewNumber], [Status] FROM RecursiveCte

J'ai essayé d'optimiser les requêtes ici, puisqu'il a fallu 1h20m pour traiter mes données. Je l'ai eu jusqu'à 30 ans après quelques searchs supplémentaires. 

 WITH AuxTable AS ( SELECT id, number, status, relevantId = CASE WHEN number = 1 OR ((number - LAG(number, 1) OVER (ORDER BY id)) > 29) THEN id ELSE NULL END, deduct = CASE WHEN ((number - LAG(number, 1) OVER (ORDER BY id)) > 29) THEN number - 1 ELSE 0 END FROM @data data ) ,AuxTable2 AS ( SELECT id, number, status, AT.deduct, MAX(AT.relevantId) OVER (ORDER BY AT.id ROWS UNBOUNDED PRECEDING ) AS lastRelevantId FROM AuxTable AT ) SELECT id, number, status, number - MAX(deduct) OVER(PARTITION BY lastRelevantId ORDER BY id ROWS UNBOUNDED PRECEDING ) AS ReNumber, FROM AuxTable2

Je pense que ça va plus vite, mais ce n'est pas plus court.