MSSQL: Créer une label de ligne incrémentielle par groupe

Dans ma table, j'ai une key primaire et une date. Ce que je voudrais réaliser est d'avoir une label incrémentale basée sur s'il y a ou non une pause entre les dates – la colonne Goal .

Maintenant, ci-dessous est un exemple. La colonne de break été calculée en utilisant la fonction LEAD (j'ai pensé que cela pourrait aider).

Je suis capable de le résoudre en utilisant T-SQL, mais ce serait le dernier recours. Rien que j'ai essayé n'a fonctionné jusqu'ici. J'utilise MSSQL 2014.

 PK | Date | break | Goal | ------------------------------- 1 | 03/2017 | 0 | 1 | 1 | 04/2017 | 0 | 1 | 1 | 08/2017 | 1 | 2 | 1 | 09/2017 | 0 | 2 | 1 | 10/2017 | 0 | 2 | 1 | 02/2018 | 1 | 3 | 1 | 03/2018 | 0 | 3 |

Voici un code pour reproduire cet exemple:

 CREATE TABLE #test ( ConsumerId INT, FullDate DATE, Goal INT ) INSERT INTO #test (ConsumerId, FullDate, Goal) VALUES (1,'2017-03-01',1) INSERT INTO #test (ConsumerId, FullDate, Goal) VALUES (1,'2017-04-01',1) INSERT INTO #test (ConsumerId, FullDate, Goal) VALUES (1,'2017-08-01',2) INSERT INTO #test (ConsumerId, FullDate, Goal) VALUES (1,'2017-09-01',2) INSERT INTO #test (ConsumerId, FullDate, Goal) VALUES (1,'2017-10-01',2) INSERT INTO #test (ConsumerId, FullDate, Goal) VALUES (1,'2018-02-01',3) INSERT INTO #test (ConsumerId, FullDate, Goal) VALUES (1,'2018-03-01',3) SELECT ConsumerId, FullDate, CASE WHEN (datediff(month, isnull( LEAD (FullDate,1) OVER (PARTITION BY ConsumerId ORDER BY FullDate DESC), FullDate), FullDate) > 1) THEN 1 ELSE 0 END AS break, Goal FROM #test ORDER BY FullDate ASC

MODIFIER

Ceci est apparemment un problème célèbre "Îles et lacunes" comme indiqué dans les commentaires. Et Google propose de nombreuses solutions ainsi que d'autres questions ici à SO.

Essaye ça…

 WITH cte_TestGap AS ( SELECT t.ConsumerId, t.FullDate, Gap = CASE WHEN DATEDIFF(mm, t.FullDate, LAG(t.FullDate, 1) OVER (PARTITION BY t.ConsumerId ORDER BY t.FullDate)) = -1 THEN 0 ELSE ROW_NUMBER() OVER (PARTITION BY t.ConsumerId ORDER BY t.FullDate) END FROM #test t ), cte_SmearGap AS ( SELECT tg.ConsumerId, tg.FullDate, GV = MAX(tg.Gap) OVER (PARTITION BY tg.ConsumerId ORDER BY tg.FullDate ROWS UNBOUNDED PRECEDING) FROM cte_TestGap tg ) SELECT sg.ConsumerId, sg.FullDate, GroupValue = DENSE_RANK() OVER (PARTITION BY sg.ConsumerId ORDER BY sg.GV) FROM cte_SmearGap sg;

Une explication du code et comment cela fonctionne … La première requête, dans cte_TestGap, utilise la fonction LAG avec la fonction ROW_NUMBER () pour marquer l'location de l'écart dans datatables. Nous pouvons le voir en le décomposant et en regardant les résultats …

 WITH cte_TestGap AS ( SELECT t.ConsumerId, t.FullDate, Gap = CASE WHEN DATEDIFF(mm, t.FullDate, LAG(t.FullDate, 1) OVER (PARTITION BY t.ConsumerId ORDER BY t.FullDate)) = -1 THEN 0 ELSE ROW_NUMBER() OVER (PARTITION BY t.ConsumerId ORDER BY t.FullDate) END FROM #test t ) SELECT * FROM cte_TestGap;

Résultats de cte_TestGap …

 ConsumerId FullDate Gap ----------- ---------- -------------------- 1 2017-03-01 1 1 2017-04-01 0 1 2017-08-01 3 1 2017-09-01 0 1 2017-10-01 0 1 2018-02-01 6 1 2018-03-01 0

À ce stade, nous voulons que les valeurs 0 prennent la valeur des valeurs non-0 précédentes, ce qui permet de les regrouper. Ceci est fait dans la 2ème requête (cte_SmearGap) en utilisant la fonction MAX avec un " frame window ". Donc, si nous regardons la sortie de cte_SmearGap, nous pouvons voir que …

 WITH cte_TestGap AS ( SELECT t.ConsumerId, t.FullDate, Gap = CASE WHEN DATEDIFF(mm, t.FullDate, LAG(t.FullDate, 1) OVER (PARTITION BY t.ConsumerId ORDER BY t.FullDate)) = -1 THEN 0 ELSE ROW_NUMBER() OVER (PARTITION BY t.ConsumerId ORDER BY t.FullDate) END FROM #test t ), cte_SmearGap AS ( SELECT tg.ConsumerId, tg.FullDate, GV = MAX(tg.Gap) OVER (PARTITION BY tg.ConsumerId ORDER BY tg.FullDate ROWS UNBOUNDED PRECEDING) FROM cte_TestGap tg ) SELECT * FROM cte_SmearGap;

Résultats de cte_SmearGap …

 ConsumerId FullDate GV ----------- ---------- -------------------- 1 2017-03-01 1 1 2017-04-01 1 1 2017-08-01 3 1 2017-09-01 3 1 2017-10-01 3 1 2018-02-01 6 1 2018-03-01 6

À ce stade Toutes les lignes sont dans des groupes distincts … mais … Nous aimerions avoir nos numéros de groupe dans une séquence contiguë (1,2,3) par opposition à (1,3,6). Bien sûr, c'est assez facile à corriger en utilisant la fonction DENSE_Rank (), qui est ce qui se passe dans la sélection finale …

 WITH cte_TestGap AS ( SELECT t.ConsumerId, t.FullDate, Gap = CASE WHEN DATEDIFF(mm, t.FullDate, LAG(t.FullDate, 1) OVER (PARTITION BY t.ConsumerId ORDER BY t.FullDate)) = -1 THEN 0 ELSE ROW_NUMBER() OVER (PARTITION BY t.ConsumerId ORDER BY t.FullDate) END FROM #test t ), cte_SmearGap AS ( SELECT tg.ConsumerId, tg.FullDate, GV = MAX(tg.Gap) OVER (PARTITION BY tg.ConsumerId ORDER BY tg.FullDate ROWS UNBOUNDED PRECEDING) FROM cte_TestGap tg ) SELECT sg.ConsumerId, sg.FullDate, GroupValue = DENSE_RANK() OVER (PARTITION BY sg.ConsumerId ORDER BY sg.GV) FROM cte_SmearGap sg;

Le résultat final …

 ConsumerId FullDate GroupValue ----------- ---------- -------------------- 1 2017-03-01 1 1 2017-04-01 1 1 2017-08-01 2 1 2017-09-01 2 1 2017-10-01 2 1 2018-02-01 3 1 2018-03-01 3

Le commentaire de David Browne était en fait extrêmement utile. Si vous google "Islands and Gaps", il existe de nombreuses variantes de la solution. Voici celui que j'ai le plus aimé.

À la fin, j'avais besoin de la colonne Goal pour pouvoir regrouper les dates en MIN / MAX. Cette solution ignore cette étape et crée directement la plage agrégée.

Voici la source .

 SELECT MIN(FullDate) AS range_start, MAX(FUllDate) AS range_end FROM ( SELECT FullDate, DATEADD(MM, -1 * ROW_NUMBER() OVER(ORDER BY FullDate), FullDate) AS grp FROM #test ) a GROUP BY a.grp

Et la sortie:

 range_start | range_end | -------------------------- 2017-03-01 | 2017-04-01 | 2017-08-01 | 2017-10-01 | 2018-02-01 | 2018-03-01 |