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)?
3 Réponses :
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
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!
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
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
Parfait! Je vous remercie!
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 ...
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 pourY
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.