MS SQL Server
  1. MS SQL Server
  3. Identifier le début et la fin de la séquence dans SQL Server
Intereting Posts
Convertir le résultat de SQL dynamic en XML Paging de kendo côté server avec des procédures stockées Comment puis-je charger un assembly avec la dépendance BouncyCastle.crypto.dll dans SQL Server 2014? Impossible de se connecter au server local en utilisant nom_server / nom_instance Est-il possible d'appeler une fonction définie par l'user sans le nom du schéma? Cette méthode est-elle une bonne approche pour get des valeurs SQL à partir de C #? SSRS – Plusieurs appels de procédure stockée basés sur 1 paramètre dans 1 rapport? DataTable est vide d'appeler la procédure stockée Erreur de syntaxe SQL – Gestionnaire de database SQL Server gratuit Comment get la date la plus proche dans SQL Server? Créer BAK à partir de MDF sans SQL Server Management Studio (SSMS) string de connection Itération sur des lignes dans un DataTableReader avec foreach Comment get un set de lignes à partir d'un tableau d'ID?

Identifier le début et la fin de la séquence dans SQL Server

J'ai une table qui a des données arrangées d'une manière qui ressemble à ceci: 

ID | BOUNDARY | TIMESTAMP 1 | NULL | 2016-01-01 00:20:00 2 | A | 2016-01-01 00:20:10 3 | A | 2016-01-01 00:20:14 4 | A | 2016-01-01 00:20:22 5 | NULL | 2016-01-01 00:20:38 6 | A | 2016-01-01 00:20:45 7 | B | 2016-01-01 00:21:02 8 | B | 2016-01-01 00:21:12 9 | A | 2016-01-01 00:21:16 10 | A | 2016-01-01 00:21:22 11 | C | 2016-01-01 00:21:30 12 | A | 2016-01-01 00:21:35 13 | A | 2016-01-01 00:21:40 14 | A | 2016-01-01 00:21:46 15 | A | 2016-01-01 00:21:50

Ce que je voudrais faire est de find un moyen efficace de marquer l'ID et les horodateurs pour le début et la fin d'une séquence dans SQL Server 2014. Un segment serait quand une limite n'est pas nulle et se répète pendant au less deux fois consécutives. Par exemple, le premier segment serait de ID 2-4 le deuxième segment serait ID 7-8, le troisième serait 9-10.

L'approche que j'ai essayée en premier consistait à créer deux colonnes, un col "startflag" et une colonne "endflag". Je crée une requête de mise à jour qui marque correctement le début et la fin, mais j'aimerais créer une vue où je peux la voir comme un logging, comme ci-dessous: 

 BOUNDARY | START ID | END ID A | 2 | 4 B | 7 | 8 A | 9 | 10 A | 12 | 15

Ok, je suis sûr qu'il y a de meilleures façons de le faire, mais cela fonctionne: 

 WITH CTE AS ( SELECT *, RN1 = ROW_NUMBER() OVER(ORDER BY [TIMESTAMP]), RN2 = ROW_NUMBER() OVER(PARTITION BY BOUNDARY ORDER BY [TIMESTAMP]) FROM #YourTable ), CTE2 AS ( SELECT *, RN1-RN2 RN3, COUNT(*) OVER(PARTITION BY RN1-RN2) N FROM CTE ) SELECT BOUNDARY, MIN(ID) [START ID], MAX(ID) [END ID] FROM CTE2 WHERE N > 1 AND BOUNDARY IS NOT NULL GROUP BY BOUNDARY, RN3 ORDER BY [START ID];

Si nous utilisons cet exemple de tableau: 

 CREATE TABLE #YourTable ([ID] int, [BOUNDARY] varchar(4), [TIMESTAMP] datetime) ; INSERT INTO #YourTable ([ID], [BOUNDARY], [TIMESTAMP]) VALUES (1, NULL, '2016-01-01 00:20:00'), (2, 'A', '2016-01-01 00:20:10'), (3, 'A', '2016-01-01 00:20:14'), (4, 'A', '2016-01-01 00:20:22'), (5, NULL, '2016-01-01 00:20:38'), (6, 'A', '2016-01-01 00:20:45'), (7, 'B', '2016-01-01 00:21:02'), (8, 'B', '2016-01-01 00:21:12'), (9, 'A', '2016-01-01 00:21:16'), (10, 'A', '2016-01-01 00:21:22'), (11, 'C', '2016-01-01 00:21:30'), (12, 'A', '2016-01-01 00:21:35'), (13, 'A', '2016-01-01 00:21:40'), (14, 'A', '2016-01-01 00:21:46'), (15, 'A', '2016-01-01 00:21:50') ;

Les résultats sont: 

 ╔══════════╦══════════╦════════╗ ║ BOUNDARY ║ START ID ║ END ID ║ ╠══════════╬══════════╬════════╣ ║ A ║ 2 ║ 4 ║ ║ B ║ 7 ║ 8 ║ ║ A ║ 9 ║ 10 ║ ║ A ║ 12 ║ 15 ║ ╚══════════╩══════════╩════════╝

La key est de créer des groupes d'îles par

  1. Calcul du nombre de lignes en fonction de l'heure (qui est votre ID )
  2. Calcul du nombre de lignes pour chaque valeur distincte
  3. Regroupement = (1) – (2)

Jetez un oeil à l'exemple ci-dessous: 

 declare @T table (ID int, BOUNDARY char(1), [TIMESTAMP] datetime2) insert into @T values (1, null, '2016-01-01 00:20:00'), (2, 'A', '2016-01-01 00:20:10'), (3, 'A', '2016-01-01 00:20:14'), (4, 'A', '2016-01-01 00:20:22'), (5, null, '2016-01-01 00:20:38'), (6, 'A', '2016-01-01 00:20:45'), (7, 'B', '2016-01-01 00:21:02'), (8, 'B', '2016-01-01 00:21:12'), (9, 'A', '2016-01-01 00:21:16'), (10, 'A', '2016-01-01 00:21:22'), (11, 'C', '2016-01-01 00:21:30'), (12, 'A', '2016-01-01 00:21:35'), (13, 'A', '2016-01-01 00:21:40'), (14, 'A', '2016-01-01 00:21:46'), (15, 'A', '2016-01-01 00:21:50') select BOUNDARY, min(ID) as [START ID], max(id) as [END ID] from ( select ID, BOUNDARY, ID - row_number() over (partition by BOUNDARY order by TIMESTAMP) as grp from @T as t ) as T where BOUNDARY is not null group by grp, BOUNDARY having count(*) >= 2 order by min(ID)