Forums PostgreSQL.fr

Alexis GAILLOT

Membre

Sequence avec group by

Bonjour,
je voudrais savoir s'il est possible d'utiliser nextval('ma_sequence') dans un esprit GROUP BY, c'est à dire ramener dans un select un compteur qui se réinitialise à chquque nouvelle valeur d'un autre champ.
exemple : voici ma table noeud
gid||origine
1||CAY
2||CAY
3||CAY
4||JOL
5||JOL
6||JOL
7||JOL
8||TBN
9||TBN

Ma séquence est : create sequence ma_seq start with 1;

Je voudrais que mon select du type "select gid, origine, nextval('ma_sequence') as compteur from noeud group by gid, origine" me ramène ceci :

gid||origine||compteur
1||CAY||1
2||CAY||2
3||CAY||3
4||JOL||1
5||JOL||2
6||JOL||3
7||JOL||4
8||TBN||1
9||TBN||2

mais ça ne marche pas car la séquence n'est pas réinitialisée à chaque valeur du group by sur le champ origine.

J'ai contourné le pb avec une sous requête avec un select count(*) sur la même table une deuxième fois, et une condition compliquée, ça marche mais l'écriture est très lourde.

Y a-til un moyen de faire ça simplement avec une séquence ?

Merci. Alexis

Alexis GAILLOT

Membre

Re : Sequence avec group by

Voici la requete que j'ai utilisé :

select gid, nom, origine,
       (select count(*)
         from noeuds p
         where p.origine=n.origine and n.gid>=p.gid and p.type ='PM'
         )
from noeuds n
where type = 'PM';

mais je pensais qu'il y avait moyen de faire plus "postgresqlesque" avec une sequence.
Alexis

gleu

Administrateur

Re : Sequence avec group by

Une séquence ne vous aidera pas dans ce cas. Le mieux, si vous avez une version supérieure ou égale à la 8.4, est certainement ceci :

SELECT gid, origine, row_number() OVER (PARTITION BY origine ORDER gid) AS compteur FROM noeuds GROUP BY id, origine;

Ça demandera certainement un ajustement, étant donné que les données présentées rendent inutile le GROUP BY...

---

Guillaume.

rjuju

Administrateur

Re : Sequence avec group by

et un "ORDER BY" également

---

Julien.
https://rjuju.github.io/

gleu

Administrateur

Re : Sequence avec group by

Ah oui, oups. Désolé.

---

Guillaume.

SQLpro

Membre

Re : Sequence avec group by

Bonjour,
je voudrais savoir s'il est possible d'utiliser nextval('ma_sequence') dans un esprit GROUP BY, c'est à dire ramener dans un select un compteur qui se réinitialise à chauque nouvelle valeur d'un autre champ.

Les séquences ne le permettent pas. La solution à base de ROW_NUMBER() peut devenir incohérente s'il y à des suppressions de lignes.
De plus son temps  de réponse va devenir de plus en plus lent au fur et à mesure de la montée en charge et vous avez toutes les chances d'avoir des télescopages de clef du fait de la concurrence des accès en lecture.

La seule façon correcte d'assurer ce mécansime est de se doter d'une table de clef et d'une procédure adéquate.
Votre table de clef pourrait avoir la forme suivante :

CREATE TABLE T_SEQUENCE_PARTITION_SQP
(SQP_ID             SERIAL PRIMARY KEY,
 SQP_TABLE_SCHEMA   NVARCHAR(128) NOT NULL,
 SQP_TABLE_NAME     NVARCHAR(128) NOT NULL,
 SQP_PARTITION      NVARCHAR(64) NOT NULL,
 SQP_CLEF           BIGINT NOT NULL);

INSERT INTO T_SEQUENCE_PARTITION_SQP
(SQP_TABLE_SCHEMA, SQP_TABLE_NAME, SQP_PARTITION,SQP_CLEF)
SELECT 'public', 'noeud', origine, MAX(compteur)
FROM   public.noeud
GROUP  BY origine;

Puis de réaliser une fonction au plus haut niveau d'isolation, ayant comme signature les éléments suivants :

CREATE FUNCTION F_GET_NEXT_KEY
   in TABLE_SCHEMA
   in TABLE_NAME
   in PARTITION
   out KEY

qui assurera la combinaison d'ordre SQL suivanrt :
1) lire la valeur actuelle de la clef pour la table considérée (SELECT) et placer cette valeur + 1 dans la variable KEY
2) mettre à jour la valeur de cette même clef (UPDATE) pour la table considérée  avec la valeur KEY + 1

Pour plus de détails, lire l'article que j'ai écrit à ce sujet...

A +

---

Frédéric Brouard, alias SQLpro,  ARCHITECTE DE DONNÉES,  Expert langage SQL
Le site sur les SGBD relationnel et langage SQL   : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA, ISEN Toulon,  CESI Aix en Provence  * * * * *

gleu

Administrateur

Re : Sequence avec group by

La solution à base de ROW_NUMBER() peut devenir incohérente s'il y à des suppressions de lignes.

Je ne vois pas comment elle pourrait devenir incohérente. C'est une requête, elle est isolée dans son exécution.

---

Guillaume.

Alexis GAILLOT

Membre

Re : Sequence avec group by

Bonsoir,
à propos de la réponse de gleu, j'ai peut etre induit en erreur : en réalité, mes données ne sont pas forcément ordonnées correctement, c'est à dire que j'aurais plutot dans la table noeud
gid||origine
1||CAY
7||CAY
4||CAY
3||JOL
9||JOL
8||JOL
2||JOL
5||TBN
9||TBN
du coup l'order by gid ne suffit pas (à priori ça marche avec order by origine, gid).

Donc au final merci, ça marche. Je ne connaissais pas cette instruction OVER (PARTITION BY .

Quand à l'autre réponse (sqlpro) je vais tester ça après avoir pris le temps de bien comprendre ce mécanisme de table de cle.
Merci également.

Alexis