3
votes

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

J'ai un tableau qui ressemble à:

SELECT 
    *, 
    RANK() OVER (PARTITION BY group, color order by group, date) as rank
FROM table

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

Et il est trié par groupe et par date. Je veux une colonne supplémentaire indiquant le numéro séquentiel de la couleur consécutive "R" pour chaque groupe.

Sortie requise:

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

J'ai essayé d'utiliser la fonction de fenêtre avec partition par groupe et colonnes de couleur, mais elle renvoie la sortie ci-dessous qui n'est pas correcte.

Mauvaise requête et sortie: strong >

group    date        color
  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

Je me demande si c'est faisable en SQL, ou devrais-je passer à un autre langage (comme Python)?


4 commentaires

Utilisez-vous postgres (Microsoft SQL Server) ou mysql? Vous avez tagué mysql mais votre sql ressemble à postgres


J'utilise presto, mais toute solution de base de données est appréciée. Merci.


Et vous ne voulez que des chiffres pour R , pas pour Y ou pour toute autre couleur? Et peut-il y avoir des doublons? Si oui, comment les gérer?


@RannieXue. . . J'ai supprimé les balises de base de données incompatibles. Veuillez n'indiquer que la base de données que vous utilisez réellement.


3 Réponses :


3
votes

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


2 commentaires

PS. J'espère que vous utilisez un type DATE plutôt qu'un varchar comme je l'ai ici. Pas important pour cette question, mais commencez à la commander / sélectionner de la manière spécifiée et vous aurez des problèmes.


Je vous remercie! C'est vraiment utile!



4
votes

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


1 commentaires

Parfait! Je vous remercie!



1
votes

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:

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 ...


0 commentaires