Le portail francophone de la géomatique

preliator · Mon 23 March 2020 13:25

Bonjour,

Travaillant avec la mobilité professionnelle de l'Insee, je cherche à faire la somme d'un champ nommé Ipondi seulement sur les trajets commune de résidence à travail, et non pas travail à commune de résidence.

Admettons la colonne de commune de résidence nommée "Départ", et commune de travail nommée "Arrivée", et le champ que je souhaite faire la somme nommée "Ipondi", et admettons le cas suivant :

Code:

départ; arrivée; ipondi

La Ciotat; Marseille; 84
Aubagne; Ceyreste; 12
Marseille; La Ciotat; 73

Je souhaite donc faire la somme des 2 premières lignes (donc 84 + 12 = 96), et non pas la dernière car elle représente le trajet retour de la première ligne.

Comment faire cela sur PostgreSQL ?

Merci.

Nicolas Ribot · Mon 23 March 2020 19:16

Bonsoir,

Marrant...
Une façon super alambiquée il me semble, mais je vois pas (encore) plus direct:

Code:

create table trajet (
    id serial primary key ,
    depart text,
    arrivee text,
    ipondi int
);

insert into trajet (depart, arrivee, ipondi) values
    ('La Ciotat', 'Marseille', 84),
    ('Aubagne', 'Ceyreste', 12),
    ('Marseille', 'La Ciotat', 73),
    ('Istres', 'Grandbois', 23),
    ('Istres', 'Marseille', 3),
    ('Marseille', 'Toulon', 11),
    ('Marseille', 'Istres', 13);

Trouver les trajets qui ont des allers-retours:

Code:

select t.*, row_number() over () as newid
from trajet t
where exists(
              select null
              from trajet t2
              where t.depart = t2.arrivee and t.arrivee = t2.depart
          );

newid permet de numéroter les lignes, et donc d'avoir des paires de trajets aller/retour: les id impairs peuvent etre vus comme les id des trajets dans un sens, les id pairs les trajets dans l'autre sens:

Code:

id    depart    arrivee    ipondi    newid
1    La Ciotat    Marseille    84    1
3    Marseille    La Ciotat    73    2
5    Istres    Marseille    3    3
7    Marseille    Istres    13    4

Ensuite on peut choisir parmi les trajets ceux qui ne sont pas dans cette liste pour les trajets retour (considérons ici que les trajets retour ont des id pairs)

Code:

with tmp as (
    select t.*, row_number() over () as newid
    from trajet t
    where exists(
                  select null
                  from trajet t2
                  where t.depart = t2.arrivee and t.arrivee = t2.depart
              )
)
select t.*, sum(t.ipondi) over ()
from trajet t
where not exists(
        select null
        from tmp
        where tmp.newid % 2 = 0 and t.id = tmp.id
    );

Windows function inutile ici, mais elle permet de voir la somme des ipondi tout en voyant les trajets individuels, pour contrôle:

Code:

id    depart    arrivee    ipondi    sum
1    La Ciotat    Marseille    84    133
2    Aubagne    Ceyreste    12    133
4    Istres    Grandbois    23    133
5    Istres    Marseille    3    133
6    Marseille    Toulon    11    133

Une somme simple sur ipondi suffit donc au final:

Code:

with tmp as (
    select t.*, row_number() over () as newid
    from trajet t
    where exists(
                  select null
                  from trajet t2
                  where t.depart = t2.arrivee and t.arrivee = t2.depart
              )
)
select sum(t.ipondi) as sum_ipondi
from trajet t
where not exists(
        select null
        from tmp
        where tmp.newid % 2 = 0 and t.id = tmp.id
    );

sum_ipondi
133

Cherchons plus simple...

Nicolas

preliator · Tue 24 March 2020 10:32

Un grand merci pour votre réponse
J'avais pour objectif d'appliquer cette solution sur la mobilité professionnelle de l'Insee, et malheureusement je trouve quelques erreurs (des retours encore présents ** mis à part si c'est moi qui ai mal écris la formule ^^).

Après quelques recherches, j'ai pu trouver la formule qui résout mon problème :

Code:

drop table if exists trajet;
create table trajet as
select commune, dclt, sum(ipondi)
from mobil_pro
where commune like '26%' or commune like '07%'
group by commune, dclt

with rang as (select commune, dclt, sum(sum) as sum from trajet group by 1, 2),
retour as (
    select a.commune, a.dclt, a.sum
    from rang a
    join rang b on a.commune = b.dclt and a.dclt = b.commune and a.sum <= b.sum
)

select * from rang except (select * from retour) order by sum desc ;

Cordialement.

tumasgiu · Tue 24 March 2020 15:47

Nicolas a écrit:

Cherchons plus simple... wink

Si j'ai bien compris la demande :

Code:

SELECT
    sum(ipondi)
FROM(
    SELECT
        p1.*
    FROM
        p p1 
    LEFT JOIN 
        p p2
    ON
        p1.arrivee = p2.depart AND
        p2.arrivee = p1.depart AND
    WHERE p2.depart IS NULL
    UNION
    SELECT 
        p1.*
    FROM
        p p1 
    JOIN 
        p p2
    ON
        p1.arrivee = p2.depart AND
        p2.arrivee = p1.depart AND
        p1.id < p2.id
    ) sreq

EDIT: Si pas de colonne de clé primaire dispo pour la seconde
partie de l'union on peut prendre arrivee ou depart, je pense
que ca fonctionne aussi.

?

On doit peut être même pouvoir encore simplifier.

Dernière modification par tumasgiu (Tue 24 March 2020 15:48)

tumasgiu · Tue 24 March 2020 15:53

De plus, comme les distances entre allers et retours varient,
cette méthode a l'avantage de produire un résultat invariant,
les précédentes dépendront de l'ordre de retour des lignes
des SELECT (bon on peut les corriger cela dit )

Nicolas Ribot · Tue 24 March 2020 16:12

Ah oui, plus simple et direct !

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 Mon 23 March 2020 13:25

[PostgreSQL] Sélectionner l'aller mais pas le retour

Code:

#2 Mon 23 March 2020 19:16

Re: [PostgreSQL] Sélectionner l'aller mais pas le retour

Code:

Code:

Code:

Code:

Code:

Code:

#3 Tue 24 March 2020 10:32

Re: [PostgreSQL] Sélectionner l'aller mais pas le retour

Code:

#4 Tue 24 March 2020 15:47

Re: [PostgreSQL] Sélectionner l'aller mais pas le retour

Code:

#5 Tue 24 March 2020 15:53

Re: [PostgreSQL] Sélectionner l'aller mais pas le retour

#6 Tue 24 March 2020 16:12

Re: [PostgreSQL] Sélectionner l'aller mais pas le retour

Pied de page des forums