Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

#1 Mon 23 March 2020 13:25

preliator
Membre
Date d'inscription: 17 Nov 2018
Messages: 352

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

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.

Hors ligne

 

#2 Mon 23 March 2020 19:16

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1263

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

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... wink

Nicolas

Hors ligne

 

#3 Tue 24 March 2020 10:32

preliator
Membre
Date d'inscription: 17 Nov 2018
Messages: 352

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

Un grand merci pour votre réponse smile
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.

Hors ligne

 

#4 Tue 24 March 2020 15:47

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1015

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

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)

Hors ligne

 

#5 Tue 24 March 2020 15:53

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1015

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

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 smile )

Hors ligne

 

#6 Tue 24 March 2020 16:12

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1263

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

Ah oui, plus simple et direct !

Hors ligne

 

Pied de page des forums

Powered by FluxBB

Partagez  |