5
votes

Regrouper les lignes en fonction de la valeur actuelle à partir de la ligne suivante

Je dois incrémenter le numéro de groupe pour toutes les lignes suivantes avec la valeur z . z signifierait que toutes les lignes suivantes selon b auront le même numéro de groupe.

CREATE TABLE #tmp
(
    a CHAR(1)
  , b INT
);

INSERT INTO #tmp (a
                , b)
VALUES ('a', 1)
     , ('b', 2)
     , ('z', 3)
     , ('c', 4)
     , ('z', 5)
     , ('z', 6)
     , ('d', 7);

SELECT       t.a
           , t.b
           , SUM(v.is_z) OVER (ORDER BY t.b ROWS UNBOUNDED PRECEDING) - ROW_NUMBER() OVER (ORDER BY t.b) group_nbr
  FROM       #tmp                                                 AS t
 CROSS APPLY (SELECT CASE WHEN a = 'z' THEN 2 ELSE 1 END AS is_z) AS v
 ORDER BY 2;

DROP TABLE #tmp;

Dans ma requête, le groupe est incrémenté à partir de la ligne avec la valeur z , mais je dois commencer à l'incrémenter pour la ligne suivante.

Résultat attendu:

sql sql-server

2 commentaires

«Suivant» selon quoi (b)?

Oui. Modifié la question.

3 Réponses :

4
votes

Une solution consiste à incrémenter de 1 chaque fois que vous trouvez un z , puis LAG le résultat par 1 ligne à la fin.

a   b   CumulativeZ Group
a   1   0           0
b   2   0           0
z   3   1           0
c   4   1           1
z   5   2           1
z   6   3           2
d   7   3           3

Résultat:

;WITH CumulativeZ AS
(
    SELECT
        T.*,
        CumulativeZ = SUM(CASE WHEN T.a = 'z' THEN 1 ELSE 0 END) OVER(ORDER BY T.b ASC)
    FROM
        #tmp AS T
)
SELECT
    C.a,
    C.b,
    C.CumulativeZ,
    [Group] = LAG(C.CumulativeZ, 1, 0) OVER (ORDER BY C.b ASC)
FROM
    CumulativeZ AS C

Veuillez noter que la fonction LAG est pour SQL Server 2012+. Vous pouvez imiter sa fonctionnalité avec un ROW_NUMBER sur la version 2008+.

0 commentaires

1
votes

J'utiliserais simplement apply :

select t.a, t.b, tt.grp
from #tmp t outer apply
     (select count(*) as grp
      from #tmp t1 
      where t1.b < t.b and t1.a = 'z'
     ) tt;

1 commentaires

@DmitrijKultasev. . . C'est une manière très inefficace de faire ce que vous voulez. L'utilisation des fonctions de fenêtre est généralement beaucoup plus rapide, ce qu'elles peuvent être utilisées.

1
votes

Vous voulez que les "z" identifient la fin d'un groupe. Vous pouvez attribuer des numéros de groupe à l'aide de sommes cumulées. Je pense que c'est la méthode la plus simple pour faire ce que vous voulez.

Si vous ne vous souciez pas de l'ordre réel des nombres, vous pouvez simplement faire:

select t.*,
       coalesce(sum(case when a = 'z' then 1 else 0 end) over
                    (order by b
                     rows between unbounded preceding and 1 preceding), 0
               ) as grp
from #tmp t;

Si vous voulez que les groupes soient numérotés "dans le bon sens", vous pouvez utiliser une expression un peu plus compliquée:

select t.*,
       sum(case when a = 'z' then 1 else 0 end) over (order by b desc) as grp_desc 
from #tmp t;

Voici un violon db .

3 commentaires

Le plan estimé est environ deux fois pire que dans la réponse acceptée

@DmitrijKultasev. . . Si vos données comportent en fait 7 lignes, cela n'a pas d'importance. Pour toutes les données de taille significative, l'application croisée peut coûter cher.

Ok, vous m'avez convaincu :) Je viens de vérifier les données réelles (~ 32M lignes) et cela surpasse toutes les solutions proposées à plusieurs reprises.