Comment numéroter des enregistrements consécutifs par île?

L'utilisation de variables utilisateur pourrait conserver le rang et les valeurs précédentes pour produire les résultats:

group | date     | color | rank | prev
:---- | :------- | :---- | ---: | :---
A     | 1-1-2019 | R     |    1 | R   
A     | 1-2-2019 | Y     |      | Y   
B     | 1-1-2019 | R     |    1 | R   
B     | 1-2-2019 | Y     |      | Y   
B     | 1-3-2019 | Y     |      | Y   
B     | 1-4-2019 | R     |    1 | R   
B     | 1-5-2019 | R     |    2 | R   
B     | 1-6-2019 | R     |    3 | R   

 CREATE TABLE tbl (
   `group` VARCHAR(1),
   `date` VARCHAR(8),
   `color` VARCHAR(1)
 );

 INSERT INTO tbl
   (`group`, `date`, `color`)
 VALUES
   ('A', '1-1-2019', 'R'),
   ('A', '1-2-2019', 'Y'),
   ('B', '1-1-2019', 'R'),
   ('B', '1-2-2019', 'Y'),
   ('B', '1-3-2019', 'Y'),
   ('B', '1-4-2019', 'R'),
   ('B', '1-5-2019', 'R'),
   ('B', '1-6-2019', 'R');

 set @seq := 0, @prev := 'B'

 SELECT 
     *, 
     IF(color='R', @seq := IF(@prev = color, @seq + 1, 1), NULL) AS rank,
     @prev := color as prev
 FROM tbl
 ORDER BY `group`, `date`

db fiddle ici

Voici comment cela peut être fait en utilisant les fonctions de fenêtre. Nous créons d'abord un CTE qui a un drapeau qui indique qu'une nouvelle séquence a commencé, puis à partir de là, nous en générons un qui compte les numéros de séquence. Enfin, nous comptons les lignes dans chaque séquence pour obtenir le rang:

CASE WHEN color = 'Y' THEN NULL
     ELSE ROW_NUMBER() OVER (PARTITION BY seq_num) 
     END AS `rank`

Sortie:

group   date        color   rank
A       1-1-2019    R       1
A       1-2-2019    Y       1
B       1-1-2019    R       1
B       1-2-2019    Y       1
B       1-3-2019    Y       2
B       1-4-2019    R       1
B       1-5-2019    R       2
B       1-6-2019    R       3

Démo sur dbfiddle

Notez que cette requête donne également le classement pour Y valeurs, si vous voulez qu'elles soient NULL remplacez la définition de rank par ceci:

WITH cte AS (SELECT `group`, date, color,
                    COALESCE(color = LAG(color) OVER(ORDER BY `group`, date), 0) AS samecolor
             FROM `table`),
sequences AS (SELECT `group`, date, color,
              SUM(samecolor = 0) OVER (ORDER BY `group`, date) AS seq_num
              FROM cte)
SELECT `group`, date, color,
       ROW_NUMBER() OVER (PARTITION BY seq_num) AS `rank`
FROM sequences
ORDER BY `group`, date

Utilisez la fonction de fenêtre row_number () code> pour une solution SQL standard pure dans Postgres - ou tout SGBDR moderne, même MySQL depuis la version 8:

SELECT * FROM rank_color('R');

Ceci suppose que la combinaison (grp, color, the_date) soit définie UNIQUE , les doublons créeraient des résultats non déterministes.

La soustraction des deux numéros de ligne différents calcule un nombre distinct par îlot ( part ). Ensuite, vous pouvez exécuter row_number () une fois de plus, maintenant le partitionnement par le sous-groupe en plus. Voilá.

Pour ne voir que les nombres pour une couleur particulière, 'R' dans l'exemple:

CREATE OR REPLACE FUNCTION rank_color(_color text = 'R')  -- default 'R'
  RETURNS TABLE (grp text, the_date date, color text, rnk int) AS
$func$
DECLARE
   _last_grp text;
BEGIN
   FOR grp, the_date, color IN
      SELECT t.grp, t.the_date, t.color FROM tbl t ORDER BY 1,2
   LOOP
      IF color = $1 THEN
         IF _last_grp = grp THEN
            rnk := COALESCE(rnk + 1, 1);
         ELSE
            rnk := 1;
         END IF;
      ELSIF rnk > 0 THEN  -- minimize assignments
         rnk := NULL;
      END IF;

      RETURN NEXT;
      _last_grp := grp;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Alors que les solutions basées sur des ensembles sont le fort du SGBDR et généralement plus rapide, une solution procédurale ne nécessite qu'une seule analyse pour ce type de problème, donc cette fonction plpgsql devrait être considérablement plus rapide :

SELECT grp, the_date, color, CASE WHEN color = 'R' THEN rnk END AS rnk
FROM  (
   <<query from above, without ORDER BY>>
   ) sub
ORDER  BY grp, the_date, color;

Appel:

SELECT grp, the_date, color
     , row_number() OVER (PARTITION BY grp, color, part
                          ORDER BY the_date) AS rnk
FROM  (
   SELECT *
        , row_number() OVER (PARTITION BY grp ORDER BY the_date, color)
        - row_number() OVER (PARTITION BY grp, color ORDER BY the_date) AS part
   FROM   tbl
   ) sub
ORDER BY grp, the_date, color;

db fiddle ici

Le bouclage n'est pas toujours la mauvaise solution dans une base de données relationnelle.

Lectures complémentaires:

• Sélectionnez la séquence continue la plus longue
• GROUP BY et agréger les valeurs numériques séquentielles

A part: "rank" est un nom plutôt trompeur pour ces numéros de ligne, sauf si vous avez des doublons censés se classer également ...