Le portail francophone de la géomatique

bruhnild · Wed 21 February 2018 18:18

Bonjour,

J'essaye de générer une séquence d'id_opp dans une table a en utilisant des jointures sur deux tables (b et c).
Voici ma requête :

Code:

 SELECT id, ('OPP_'||pr ||'-'|| nro_ref||'-'||nbr_doublon) as id_opp
   FROM ( WITH sequenc AS (
   SELECT
   a.id,
   a.commune,
   b.nro_ref,
   CASE WHEN b.pr IS NULL THEN 'XX'::character varying ELSE b.pr END AS pr,
   CASE WHEN c.nbr_doublon = 1 THEN 1 ELSE c.nbr_doublon END AS nbr_doublon
   FROM coordination.chaussee_2018_07 as a
   JOIN  administratif.communes as b ON a.commune = b.commune
   JOIN coordination.vue_doublons_nro_all as c ON b.nro_ref = c.id_nro 
                )
         SELECT *
         FROM sequenc
          ) concat

Le résultat de cette requête est en pièce jointe. Le problème arrive à la fin de la séquence. J'aimerais pouvoir incrémenter '00'+1 dès lors que la valeur nro_ref est utilisée.
Je m'explique :

Pour trois entités ayant le même nro_ref, jobtient ces séquence d'id_opp:

OPP_XX-LT_07181_POUZ-2
OPP_XX-LT_07181_POUZ-2
OPP_XX-LT_07181_POUZ-2

alors que j'aimerais obtenir ces séquences d'id_opp:

OPP_XX-LT_07181_POUZ-002
OPP_XX-LT_07181_POUZ-003
OPP_XX-LT_07181_POUZ-004

Pourriez vous m'aider à trouver une solution?

Merci d'avance pour vos réponses!

Marine.

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

bruhnild · Wed 21 February 2018 18:24

Voici la requete pour retrouver le nbr_doublon :

Code:

 SELECT doublons_nro.nbr_doublon,
    doublons_nro.id_nro
   FROM ( WITH compte AS (
                 SELECT numerisation.id_nro,
                    numerisation.id_opp
                   FROM coordination.numerisation
                  GROUP BY numerisation.id_nro, numerisation.id_opp
                  ORDER BY numerisation.id_nro
                )
         SELECT count(compte.id_nro) + 1 AS nbr_doublon,
            compte.id_nro
           FROM compte
          GROUP BY compte.id_nro) doublons_nro
  ORDER BY doublons_nro.nbr_doublon DESC;

saisem · Wed 21 February 2018 21:27

Bonsoir

Pour y arriver, je concatène '00' et nbr_doublon, et je conserve les 3 derniers caractères issus de la concaténation.

Code:

  ('OPP_'||pr ||'-'|| nro_ref||'-'||right(('00'||nbr_doublon),3)) as id_opp

J'espère que ça vous aidera.

Dernière modification par saisem (Wed 21 February 2018 21:28)

tumasgiu · Wed 21 February 2018 21:38

Salut,

on peut simplifier votre requête comme ceci :

Code:

SELECT id, 'OPP_'||pr ||'-'|| nro_ref||'-'|| 
lpad(row_number() OVER (PARTITION BY nro_ref ORDER BY id), 3, '0') id_opp
FROM coordination.chaussee_2018_07 as a
JOIN  administratif.communes as b USING (commune)

La fonction row_number() est une fonction de fenetre, ou window function.
Pour chaque ligne du résultat de la requête, une fonction de fenêtrage
va partitionner l'ensemble des résultats selon un critère déterminé à l'aide
des valeurs de colonne de cette ligne et y appliquer un calcul.

Ici le discriminant de la partition est la colonne nro_ref, et la fonction
row_number() renvoie le numéro de ligne dans la partition
de la ligne correspondante du résultat.

Les fonctions de fenêtrage peuvent être assimilées à une sous requête
utilisant un GROUP BY, comme dans votre requête.

Il est conseillé d'ordonner la partition avec une clause ORDER BY
pour garantir que les résultats entre plusieurs exécution d'une même
requête sur un même jeu de données soient identiques,
car comme pour une clause SELECT, l'ordre des résultats n'est jamais
garanti sans la clause ORDER BY.

La fonction lpad, elle, sert à masquer le numéro de doublon avec '0'.

Dernière modification par tumasgiu (Wed 21 February 2018 21:44)

bruhnild · Thu 22 February 2018 17:02

Bonjour et merci pour vos réponses!

J'approche du résultat mais je rencontre encore un obstacle pour incrémenter le numéro à 3 chiffres à la fin de la séquence de l'id_opp.

Code:

SELECT id, nbr_doublon, 'OPP_'||pr ||'_'|| nro_ref||'_'||lpad(CAST(row_number() OVER (PARTITION BY nro_ref, nbr_doublon ORDER BY id)AS VARCHAR), 3, '0')id_opp
FROM ( WITH sequenc AS (
SELECT 
    a.id as id , 
    b.nro_ref,
    c.nbr_doublon +1 as nbr_doublon,
    CASE 
   WHEN b.lot = 1 and b.pr IS NULL THEN '1-XX'::character varying 
   WHEN b.lot = 2 and b.pr IS NULL THEN '2-XX'::character varying 
   WHEN b.lot = 3 and b.pr IS NULL THEN '3-XX'::character varying 
   WHEN b.lot = 4 and b.pr IS NULL THEN '4-XX'::character varying 
   ELSE b.pr END AS pr
FROM coordination.chaussee_2018_07 as a
JOIN  administratif.communes as b USING (commune)
JOIN  coordination.vue_doublons_nro_all  as c USING (nro_ref)
order by id_opp
)
SELECT *
FROM sequenc
) concat
;

Je n'arrive pas à comprendre pourquoi le numéro d'incrémentation part toujours de 1, sans prendre en compte la commande pad(CAST(row_number() OVER (PARTITION BY nro_ref, nbr_doublon ORDER BY id)..

Vous trouverez le résultat en PJ.

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

tumasgiu · Thu 22 February 2018 18:08

La fonction row_number() démarre toujours à 1.
si j'ai bien compris ce que vous voulez faire au vu de votre exemple,
vous pouvez ajouter au résultat de row_number la valeur de nbr_doublon
décrémentée une fois pour obtenir le bon chiffre non ?

Code:

row_number() OVER (PARTITION BY nro_ref, nbr_doublon ORDER BY id) + nbr_doublon - 1

Dernière modification par tumasgiu (Thu 22 February 2018 23:43)

bruhnild · Sat 24 February 2018 09:28

Cette dernière réponse m’a permis de terminer la requête, merci! Je vais la metttre dans une fonction pour l’executer dans un trigger afin d’autoincrementer l’id_opp a chaque fois que la table de numérisation est modifiée. Je vous tiendrai au courant du résultat!

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Wed 21 February 2018 18:18

Séquence ID avec concaténation+incrementation[postgres]

Code:

#2 Wed 21 February 2018 18:24

Re: Séquence ID avec concaténation+incrementation[postgres]

Code:

#3 Wed 21 February 2018 21:27

Re: Séquence ID avec concaténation+incrementation[postgres]

Code:

#4 Wed 21 February 2018 21:38

Re: Séquence ID avec concaténation+incrementation[postgres]

Code:

#5 Thu 22 February 2018 17:02

Re: Séquence ID avec concaténation+incrementation[postgres]

Code:

#6 Thu 22 February 2018 18:08

Re: Séquence ID avec concaténation+incrementation[postgres]

Code:

#7 Sat 24 February 2018 09:28

Re: Séquence ID avec concaténation+incrementation[postgres]

Pied de page des forums