Discussion :

[rednik]

Bonjour à tous,

Je dois utiliser les sequences dans une table mais d'une facon particuliere :
J'ai une table contenant des colonnes A (qui ont pour valeur 1,2,3,4) et des colonnes B (qui est en fonction de A), qui ont pour valeur des entiers plus ou moins ordonnés et que je cherche à renuméroter.

Pour cela, je crée une nouvelle colonne : renumB et j'essaie d'utiliser ma séquence pour lui donner une valeur. Le problème est que je voudrais que la renumerotation se fasse pour chaque valeur de A :

A B renumB
----------------
1 1 1
1 3 2
1 4 3
2 2 1
2 3 2
----------------

Voilà alors je n'arrive pas à "grouper" mon renumB sachant que je ne peux pas utiliser les boucles.

Si quelqu'un a une petite idée

Merci beaucoup & bonne journée,

[SQLpro]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE TABLE T (A INT, B INT)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
INSERT INTO T VALUES (1, 1)
INSERT INTO T VALUES (1, 3)
INSERT INTO T VALUES (1, 4)
INSERT INTO T VALUES (2, 2)
INSERT INTO T VALUES (2, 3)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) AS Renum
FROM T

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
A           B           Renum
----------- ----------- --------------------
1           1           1
1           3           2
1           4           3
2           2           1
2           3           2

A +

[scheu]

Malheureusement les fonctions de fenêtrage n'existent pas (encore) sur Postgresql

[rednik]

Ah c'est bien dommage, parce que ça aurait autrement plus pratique.

A défaut la solution suivante est possible, qui n'est peut être pas des plus optimisées mais qui offre l'avantage d'être plus rapide que mes boucles.

Je crée une nouvelle table T2 qui est la copie conforme de ma table sauf que je l'ordonne correctement par A,B. Je lui rajoute une colonne serial seq ainsi qu'une colonne renumB.

A B seq renumB
----------------
1 2 1
1 3 2
1 4 3
2 1 4
2 3 5
3 1 6
Ensuite je crée une deuxième table qui contient selon la table A la première valeur de seq pour chaque nouveau A :

A min_seq
-----------
1 1
2 4
3 6

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
create table TableTemp as select A, min(seq) as min_seq from T2 
group by A order by A;

Et après la formule magique:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
UPDATE t2 t2 set renumB = t2.seq - temp.min_seq + 1 
from TableTemp temp where t2.A=temp.A;

A B seq renumB
----------------
1 2 1 1
1 3 2 2
1 4 3 3
2 1 4 1
2 3 5 2
3 1 6 1

Si vous avez mieux, je suis super preneuse

Bonne journée à tous,

[estofilo]

Effectivement c'est un problème difficile en SQL de base, sans les fonctions de fenêtrage, mais c'est quand même jouable en s'appuyant sur les tableaux et la fonction generate_series()
D'abord il faut créer l'aggrégat array_accum si tu ne l'as pas déjà dans ta base:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

(cette fonction est dans la doc de postgresql, elle transforme une colonne en tableau)

Ensuite le principe est de
1- transformer la colonne B en tableau trié pour chaque A distinct
2- récupérer la taille de ce tableau pour chaque A
3- pour obtenir la séquence renumb, appliquer generate_series(1, taille_tableau) pour chaque A
4- enfin pour chaque A et chaque valeur de la séquence, l'utiliser comme indice dans le tableau pour ressortir la valeur correspondante de B

Ces 4 étapes se combinent dans une seule requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT  a,arrb[b2] AS b, b2 FROM
  (SELECT  a, arrb,generate_series(1,cnt) AS b2 FROM
    (SELECT a,arrb,array_upper(arrb,1) AS cnt FROM
      (SELECT a,array_accum(b) AS arrb FROM
        (SELECT a,b FROM t ORDER BY b) t1
       GROUP BY a
      ) t2
    ) t3
  ) t4
ORDER BY 1,2;

Ca reste à tester sur tes vraies données mais je crois que dans le principe ça répond au problème. Tout ça grâce aux tableaux qui sont très bien intégrés dans le moteur SQL de postgres

[rednik]

Merci vraiment beaucoup estofilo pour cette réponse très complète !


Je ne connaissais pas bien le système des aggregate, mais là c'est bien plus clair.

Merci encore !