Forums PostgreSQL.fr

philaman

Membre

Accumulateur sur une suite d'enregistrement

Bonjour à tous,

Je suis nouveau sur ce Forum ainsi que sur Postgresql, depuis longtemps je suis limité avec mon ancien SGBD que je ne nommerai pas ici.
Voilà ma problématique : j'ai une table qui contient une liste d'événements horodatés concernant des informations de la localisation de véhicules sous la forme suivante :
Date/ heure ; véhicule ; position (latitude, longitude) ; type d'état ; kilomètre réalisé depuis le dernier arrêt
Le type d'état peut être un des cas suivant : démarrage, conduite, arrêt
J'aimerai savoir si il est possible de créer une vue "Trajet" qui me retourne les informations suivantes :
véhicule ; date/ heure début, date/ heure fin de trajet; kilométrage réalisé
J'ai déjà réalisé ce genre de chose mais avec une application qui va parcourir ma première table et qui à chaque démarrage / arrêt créé un nouveau trajet et compte les kms puis sotcke dans une autre table. L'application fonctionne mais je reste persuadé qu'il est possible de déléguer ce genre de chose directement au SGBDR.
S'agit t-il d'une forme d'accumulateur de somme ou suis-je sur la mauvaise piste ?

Merci beaucoup pour votre aide.

Phil.

Marc Cousin

Membre

Re : Accumulateur sur une suite d'enregistrement

Bonjour,

Qu'est ce qui permet d'identifier un trajet ?

---

Marc.

philaman

Membre

Re : Accumulateur sur une suite d'enregistrement

Bonjour,

Un trajet est défini par la détection d'un démarrage (D), une liste d'événement de conduite (C) et une fin définie par le statut arrêt (A).
En fait dans ma table j'ai une série de : D - C - C - C ... - A - D - C ... - A - C
Pour identifier un trajet il faut parcourir cette liste, prendre le premier D, cumuler les C, et arrêter à un A.
L'identifiant de trajet en tant qu'ID peut être généré automatiquement.

Merci.

Marc Cousin

Membre

Re : Accumulateur sur une suite d'enregistrement

Ok. Mais comment détectez vous les membres d'une série ? Par véhicule et ordre chronologique ?

---

Marc.

philaman

Membre

Re : Accumulateur sur une suite d'enregistrement

Oui c'est tout à fait cela, la table contient l'historique des positions de plusieurs véhicules.

Phil

Marc Cousin

Membre

Re : Accumulateur sur une suite d'enregistrement

Le plus simple pour effectuer cela est d'utiliser une procédure stockée calculant pour chaque trajet son détail. Il va vous falloir coder (en PLPgSQL, c'est le langage approprié pour cela).

http://docs.postgresql.fr/9.0/plpgsql.html

Vous devriez écrire une fonction ramenant, pour un début de trajet, toutes les informations relatives à ce trajet. Vous aurez donc par exemple une fonction calcule_trajet (vehicule, horodatage_debut) qui vous retournera un n_uplet (heure_fin, kilométrage)

Je pense que le code PL devrait faire un traitement du genre :

- Trouver le début du trajet suivant (s'il y en a un).
- Faire un SELECT max(horodatage),sum(km) FROM table where vehicule = xxxxx AND horodatage >= horodatage_debut AND horodatage < horodata_trouve precedemment
- Retourner ces deux valeurs.

Une fois que vous avez cette fonction, il est assez facile de déclarer une vue utilisant cette fonction :

CREATE VIEW ma_vue AS SELECT heure_debut, calcule_trajet.heure_fin AS heure_fin, calcule_trajet.kms as kilometrage
                                          FROM (SELECT horodatage AS heure_debut, calcule_trajet(vehicule, horodatage) AS calcule_trajet
                                                    FROM ma_table WHERE type_evenement = 'D') AS tmp

La vue est un petit peu complexe simplement parce que la fonction calcule_trajet devra renvoyer un type composite (il renvoie deux valeurs). Il faut donc un autre SELECT à l'extérieur pour l'éclater.

Ça a l'air compliqué au premier abord, mais ça ne l'est pas tant que ça. Si vous avez besoin d'aide sur les différents composants, n'hésitez pas à continuer à demander. Commencez par définer la fonction (CREATE FUNCTION), et avant ça, définissez un type de retour pour cette fonction (CREATE TYPE)

---

Marc.

philaman

Membre

Re : Accumulateur sur une suite d'enregistrement

Merci beaucoup. Dans mon application d'origine je parcourais la table et je créai un identifiant de trajet dès que je détectai un nouveau démarrage.
Votre solution semble plus élégante et c'est directement le SGBD qui gère tout cela, je serai tenté de dire que c'est plus performant.
Pour résumer je dois créer :
- une table avec les historiques (évidement)
- une fonction calcule_trajet qui prend en paramètre une date et un véhicule et qui retourne le km calculé ainsi que la date du dernier événement du trajet
- une vue permettant de tester tout cela
Je vais maquetter cette solution et je reviendrai probablement vers vous, je pensais utiliser la dernière version la 9.0.

Merci encore

Dernière modification par philaman (05/10/2010 11:52:40)

Marc Cousin

Membre

Re : Accumulateur sur une suite d'enregistrement

Sinon, vous n'êtes pas forcément obligé d'utiliser une vue, c'est comme vous le sentez. J'en ai parlé parce que c'est ce que vous demandiez. Utiliser simplement la fonction sera peut-être plus pratique, suivant les besoins de votre application.

---

Marc.

philaman

Membre

Re : Accumulateur sur une suite d'enregistrement

Bonsoir,

J'ai pu mettre en application tout ceci en rentrant et j'en suis à l'étape 3 : la plus importante.
J'ai donc créé mes tables avec des données, créer une fonction mais je bloque sur la vue avec le "split" des données dans mon type spécifique (avec 2 attributs).

Voici mon type :

CREATE TYPE journey_calculus_type as (journeyEndDate timestamp, mileage integer);

Voici ma fonction :

CREATE OR REPLACE FUNCTION compute_journey(journeyBeginDate timestamp, vehicleId integer)
RETURNS journey_calculus_type AS $$
DECLARE
    next_journey_start_date timestamp;
    result journey_calculus_type%rowtype;
BEGIN
    SELECT ptime INTO next_journey_start_date FROM Positions WHERE pvehicleid=vehicleId AND pstate='Start' AND ptime > journeyBeginDate LIMIT 1;
    SELECT max(ptime),sum(pkms) INTO result FROM Positions where pvehicleid=vehicleId AND ptime >= journeyBeginDate AND ptime < next_journey_start_date;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

Quand je créé ma vue sous la forme :

CREATE VIEW journeyView AS
SELECT BeginDate, journeyComputed.journeyEndDate AS EndDate, journeyComputed.mileage AS Mileage
FROM (SELECT ptime AS BeginDate, compute_journey(ptime, pvehicleId) AS journeyComputed
FROM Positions WHERE pstate = 'Start') AS tmp;

=> Résultat il manque un FROM sur le journeyComputed du premier SELECT

J'ai alors remplacé le premier select par un * (je sais c'est pas très joli mais c'est pour tester), la vue devient donc :

CREATE VIEW journeyView AS
SELECT *
FROM (SELECT ptime AS BeginDate, compute_journey(ptime, pvehicleId) AS journeyComputed
FROM Positions WHERE pstate = 'Start') AS tmp;

=> J'ai bien mes résultat quand je requête cette vue mais la deuxième colonne contient un champs de type "journeyComputed" que je n'arrive pas à séparer.

Merci pour votre aide.

Phil

philaman

Membre

Re : Accumulateur sur une suite d'enregistrement

Re-bonsoir,

En fouillant un peu partout des exemples de types composites j'ai essayé avec des parenthèses d'accéder à ce fameux type :

CREATE VIEW journeyView AS
SELECT BeginDate, (journeyComputed).journeyEndDate AS EndDate, (journeyComputed).mileage AS Mileage
FROM (SELECT ptime AS BeginDate, compute_journey(ptime, pvehicleId) AS journeyComputed
FROM Positions WHERE pstate = 'Start') AS tmp;

Dernière question : est-ce bien performant comme principe ? Quels sont les best practices sur le sujet ?

Merci encore pour votre aide.

Marc Cousin

Membre

Re : Accumulateur sur une suite d'enregistrement

Effectivement, j'avais oublié les parenthèses pour l'accès aux composites

Bien performant, j'en doute. La question, c'est surtout de savoir quel plan le moteur choisira si vous faites :

SELECT * FROM jouneyView WHERE BeginDate = xxx : est ce que PostgreSQL arrive à ne faire le calcul que pour cette date, ou est ce qu'il matérialise l'ensemble de la vue pour répondre ?

Pouvez vous mettre le résultat de

EXPLAIN SELECT * FROM jouneyView WHERE BeginDate = xxx ? (en mettant une date qui ait un sens )

Autre chose qui m'étonne dans la vue : où est obtenu l'id du véhicule ? S'il est obtenu à partir de BeginDate, est-ce normal ? (on pourrait imaginer deux véhicules démarrant exactement à la même date). Par ailleurs, dans la vue, ça serait bien d'avoir l'id du véhicule aussi.

---

Marc.

philaman

Membre

Re : Accumulateur sur une suite d'enregistrement

Bonjour,

J'ai modifié la vue pour prendre en plus le véhicule, la vue devient donc :

CREATE VIEW journeyView AS
SELECT VehicleId, BeginDate, (journeyComputed).journeyEndDate AS EndDate, (journeyComputed).mileage AS Mileage
FROM (SELECT pvehicleId as VehicleId, ptime AS BeginDate, compute_journey(ptime, pvehicleId) AS journeyComputed
FROM Positions WHERE pstate = 'Start') AS tmp;

En faisant quelques explain sur les requêtes :

1) Requête sur une date et un véhicule : explain select * from journeyView where BeginDate='2010-10-04 12:00:00' and VehicleId ='1';
"Subquery Scan on tmp  (cost=0.00..31.79 rows=1 width=44)"
"  ->  Seq Scan on positions  (cost=0.00..31.78 rows=1 width=12)"
"        Filter: ((pstate = 'Start'::state) AND (ptime = '2010-10-04 12:00:00'::timestamp without time zone) AND (pvehicleid = 1))"

2) Requête uniquement sur une date : explain select * from journeyView where BeginDate='2010-10-04 12:00:00';
"Subquery Scan on tmp  (cost=0.00..28.71 rows=1 width=44)"
"  ->  Seq Scan on positions  (cost=0.00..28.70 rows=1 width=12)"
"        Filter: ((pstate = 'Start'::state) AND (ptime = '2010-10-04 12:00:00'::timestamp without time zone))"

3) Requête sur l'intégralité de la vue : explain select * from journeyView;
"Subquery Scan on tmp  (cost=0.00..26.93 rows=6 width=44)"
"  ->  Seq Scan on positions  (cost=0.00..26.88 rows=6 width=12)"
"        Filter: (pstate = 'Start'::state)"

Je ne sais pas bien interpréter ces résultats, mais apparemment il filtre d'abord et exécute la vue ensuite, est-ce bien cela ?

Merci.

Marc Cousin

Membre

Re : Accumulateur sur une suite d'enregistrement

Oui, c'est bon, c'est ce que je voulais savoir: aucun intérêt à calculer tous les trajets, si c'est sur un tout petit sous-ensemble qu'il travaille. Je voulais juste vérifier qu'effectivement, il arrivait à pousser la clause where jusque dans la sous-requête

Par contre, ça serait certainement intéressant d'indexer positions. Il vous faut au moins un index composé sur (pvehicleId,ptime) pour que la fonction PL soit efficace.

Il vous faudra certainement aussi un second index sur ptime uniquement, puisque je présume que vous allez faire des recherches sur des périodes de temps.

---

Marc.

philaman

Membre

Re : Accumulateur sur une suite d'enregistrement

Bonjour,

J'ai effectué des tests de charges en insérant 100 000 records dans ma table de positions :
- requête 1 : sur l'intégralité des données de la vue : 4 min 30
- requête 2 : sur un véhicule dans la vue : 9 s
Ensuite j'ai suivi votre conseil avec un index sur (pvehicleId, ptime) :
- requête 1 : 3 s
- requête 2 : 130 ms
Conclusion : sans commentaire

Voici l'index créé :
CREATE INDEX veh_time_index
  ON positions
  USING btree
  (ptime, pvehicleid);

Est-ce correct ? je ne sais pas ce que signifie btree.

Dois je créé un index sur ptime seul et sur pvehicleId seul ? quel est l'impact ?

Merci encore pour vos conseils avisés.

Phil

Dernière modification par philaman (07/10/2010 10:14:55)

Marc Cousin

Membre

Re : Accumulateur sur une suite d'enregistrement

L'index créé est correct, dans le sens syntaxique. Le using btree est facultatif, btree est le type par défaut. C'est un index en arbre balancé, c'est à dire la structure la plus classique, proposée par défaut par tous les SGBD.

Par contre, je vous avais conseillé sur pvehicleid,ptime, qui sera à mon avis plus performant pour la procédure stockée.

Ensuite: les index composés sont capables de répondre de façon très efficaces à toute requête sur leur n premières colonnes. Comme là vous avez 2 colonnes, ce que cela signifie, c'est qu'un index sur pvehicleid,ptime saura aussi répondre aux requêtes pour lesquelles on aurait créé un index sur pvehicleid.

Ce que je vous recommande donc, c'est :

un index sur (pvehicleid,ptime) et un sur ptime. Supprimez celui que vous venez de créer.

La raison pour laquelle je pense qu'un index sur (pvehicleid,ptime) sera plus efficace qu'un index sur (ptime,pvehicleid) est que dans votre procédure PL, la requête principale demande, pour UN vehicleid donné, tous les ptime compris entre deux valeurs. Il est plus efficace pour un index composé de parcourir un «range» (une plage de valeur) sur sa dernière colonne que sur les colonnes précédentes, à cause de sa structure physique.

---

Marc.