PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 18/10/2021 19:39:14

Timestamp et requêtes imbriquées

Bonjour à tous,
Voilà quelques heures que je passe à me "casser la tête" sur une requête PostgreSQL qui semble abordable et pourtant m'échappe.

Un carpooling proof est ce que l’on considère être un “trajet passager”. Cela correspond au trajet entre un conducteur et un passager à un horaire donné.
On souhaite savoir en moyenne combien de passagers un conducteur prend lors d’un de ses déplacements.
Pour cela nous avons besoin de reconstruire la notion de “trajet conducteur”. C’est-à-dire savoir quels sont les trajets passagers qui doivent être regroupés ensemble car correspondant au même déplacement du conducteur.
Schématiquement cela correspond à créer la colonne “trip_id”.
Ecrire la requête qui permet de créer la colonne “trip_id” qui regroupe les trajets passagers en trajet conducteur. (On reprend l’id du premier trajet passager à regrouper comme trip_id)
Pour répondre à cette question, il n’est pas nécessaire de modifier la table en elle-même mais 'simplement' d’écrire une requête.

La table est constituée comme cela

CREATE TABLE carpooling_proofs (
"id" UUID PRIMARY KEY,
"driver_id" UUID,
"passenger_id" UUID,
"meeting_at" TIMESTAMP
);
INSERT INTO carpooling_proofs
("id", "driver_id", "passenger_id", "meeting_at")
VALUES
('73b76183-51e2-489a-b45e-714db8f2e1ad', 'f7d65f90-e8ec-42d6-9bbf-4c8d6b2fbfde',
'94523464-df44-4646-85f0-14eb50e708b8', '2020-06-07 13:55:40'),
('154b1602-4755-4b39-9a16-5edda862e51d', 'f7d65f90-e8ec-42d6-9bbf-4c8d6b2fbfde', ...

Je suis perdu entre l'imbriquement nécessaire de requêtes, l'utilisation du TimeStamp (dont je n'ai pas l'habitude) et l'organisation globale de tout cela.

Si quelqu'un a une minute ou deux pour m'aider, ce serait éminemment sympa.

Je vous souhaite sincèrement une belle journée

Hors ligne

#2 19/10/2021 10:27:23

Marc Cousin
Membre

Re : Timestamp et requêtes imbriquées

Bonjour,

Comment on considère que plusieurs entrées de carpooling_proofs correspondent au même trajet d'un conducteur ? Parce que là on peut imaginer qu'un conducteur prenne un passager à 14h, un autre à 15h, tous les deux sur le même trajet, et un autre à 17h sur le trajet retour?


Marc.

Hors ligne

#3 25/10/2021 11:30:31

c2main
Membre

Re : Timestamp et requêtes imbriquées

Bientotoperationnel a écrit :

Bonjour à tous,
Voilà quelques heures que je passe à me "casser la tête" sur une requête PostgreSQL qui semble abordable et pourtant m'échappe.

Un carpooling proof est ce que l’on considère être un “trajet passager”. Cela correspond au trajet entre un conducteur et un passager à un horaire donné.
On souhaite savoir en moyenne combien de passagers un conducteur prend lors d’un de ses déplacements.
Pour cela nous avons besoin de reconstruire la notion de “trajet conducteur”. C’est-à-dire savoir quels sont les trajets passagers qui doivent être regroupés ensemble car correspondant au même déplacement du conducteur.
Schématiquement cela correspond à créer la colonne “trip_id”.
Ecrire la requête qui permet de créer la colonne “trip_id” qui regroupe les trajets passagers en trajet conducteur. (On reprend l’id du premier trajet passager à regrouper comme trip_id)
Pour répondre à cette question, il n’est pas nécessaire de modifier la table en elle-même mais 'simplement' d’écrire une requête.

La table est constituée comme cela

CREATE TABLE carpooling_proofs (
"id" UUID PRIMARY KEY,
"driver_id" UUID,
"passenger_id" UUID,
"meeting_at" TIMESTAMP
);
INSERT INTO carpooling_proofs
("id", "driver_id", "passenger_id", "meeting_at")
VALUES
('73b76183-51e2-489a-b45e-714db8f2e1ad', 'f7d65f90-e8ec-42d6-9bbf-4c8d6b2fbfde',
'94523464-df44-4646-85f0-14eb50e708b8', '2020-06-07 13:55:40'),
('154b1602-4755-4b39-9a16-5edda862e51d', 'f7d65f90-e8ec-42d6-9bbf-4c8d6b2fbfde', ...

Je suis perdu entre l'imbriquement nécessaire de requêtes, l'utilisation du TimeStamp (dont je n'ai pas l'habitude) et l'organisation globale de tout cela.

Si quelqu'un a une minute ou deux pour m'aider, ce serait éminemment sympa.

Je vous souhaite sincèrement une belle journée

Je rejoins Marc sur la question en suspens, et j'ajoute:

* il faut une notion de trajet, elle est absente du schéma de cette table.

* il n'y a pas de jours, juste une heure, alors peut-être manque-t-il des informations "métier": quelles sont les données dans cette table ? Est-ce que le conducteur ne prend des passagers qu'au début du trajet ? Est-ce que l'on parle de trajet récurrent type covoiturage boulot, et donc oui, on pourrait se baser uniquement sur le couple (driver_id/meeting_at) pour définir un trajet récurent mais alors comment distinguer le nombre de trajet ? (un minibus avec 10 personnes , un seul trajet, ou 5 trajets avec 2 personnes ?)...

* préferer les ID en int ou bigint avant tout, choisir UUID uniquement s'il n'y a pas d'autres alternatives. (d'ailleurs je n'ai pas vraiment compris l'intérêt de la PK sur ID, ne serait-ce pas sur driver_id/passanger_id/timestamp ?

* le type date range pourra s'avérer utile...


Je crois que vous devriez trouver des lectures intéressantes avec MobilityDB, cela semble en rapport.
Je vous suggère la lecture de https://docs.mobilitydb.com/pub/MobilityDW_IJGI19.pdf  (Mobility Data Warehouses)

Hors ligne

Pied de page des forums