Skip site navigation (1) Skip section navigation (2)

Re: [pgsql-fr-generale] Re: SQL ou plpgsql pour recherche journée travail

From: François Figarola <francois(dot)figarola(at)i-consult(dot)fr>
To: Claude Castello <ccastello(at)athmo(dot)eu>
Cc: Liste PostgreSql <pgsql-fr-generale(at)postgresql(dot)org>
Subject: Re: [pgsql-fr-generale] Re: SQL ou plpgsql pour recherche journée travail
Date: 2007-11-15 07:23:09
Message-ID: 473BF3DD.7090002@i-consult.fr (view raw or flat)
Thread:
Lists: pgsql-fr-generale
Claude Castello a écrit :
> Bonjour
>> Comme un exemple vaut
>> mieux qu'un long discours, 
> extrait de : select * from activite;
>
> id_activite |        debut                 |         
> fin                    | type |  id_personne
> ------------------+---+----------------------------+---------------------------------+------+------------- 
>
>                1 | 01/10/2007 09:00:00 | 01/10/2007 10:00:00 |    1 
> |           2
>                2 | 01/10/2007 10:00:00 | 01/10/2007 10:30:00 |    2 
> |           2
>                3 | 01/10/2007 10:30:00 | 01/10/2007 12:30:00 |    1 
> |           2
>                4 | 01/10/2007 12:30:00 | 01/10/2007 14:00:00 |    3 
> |           2
>               5 | 01/10/2007 14:00:00 | 01/10/2007 16:00:00 |    1 
> |           2
>                6 | 01/10/2007 16:00:00 | 01/10/2007 18:00:00 |    2 
> |           2
>                7 | 03/10/2007 22:00:00 | 03/10/2007 22:35:00 |    1 
> |           3
>                8 | 03/10/2007 22:35:00 | 03/10/2007 23:00:00 |    2 
> |           3
>               9 | 03/10/2007 23:00:00 | 04/10/2007 04:00:00 |    1 
> |           3
>             10 | 04/10/2007 04:00:00 | 04/10/2007 05:30:00 |    2 
> |           3
>
>
> je voudrais obtenir :
> id_personne   |     debut                      |      fin
>        2              |  01/10/2007 09:00:00  |  01/10/2007 18:00      |
>        3              |  01/10/2007 22:00:00  |  04/10/2007 05:30:00 |
>
>> Avec la fonction age() vous pouvez récupérer la durée d'une "journée"
>> (ou activité?) de travail.
> Effectivement, pour la suite des travaux, je compte bien l'utiliser.
>>
>>
>> Une journée de travail = plusieurs activités apparemment.
>>   
> oui
>> Cependant, il nous manque une table alors...
>>   
> Malheureusement oui. Ce sont des données qui sont importées. Je me 
> dois de les traiter et donc de les relier entre elles.
>
> -- pour ce qui est du "seqscan"
>
> un seqscan est à éviter sur les grosses tables mais qu'ils sont 
> valables sur les petites. ==> OK
>
> Cette table va énormément grossir donc cette option n'est pas la bonne.)
>
>
> PS : j'ai déjà retiré quelques enseignements des éléments transmis. 
> Ils devraient me servir pour la suite du développement. Donc déjà merci.
>
>
Bonjour,

Au vu du jeu de données, je vais y aller de ma contribution ...

Pour ma part, je traiterais cette problématique via une simple fonction 
qui va
parcourir la table de façon ordonnée suivant l'id_personne et le 
timestamp de début
de la période de travail.
Ainsi, une journée de travail devrait se terminer dans 2 cas :
- soit l'id_personne change.
- soit on détecte une période d'inactivité (on n'est pas des machines, 
tout de même !).

Concrètement, j'ai celà codé comme suit :

1°/ Création d'un type de données qui sera retourné par la fonction :
CREATE TYPE journee_de_travail AS (

    debut timestamp without time zone,
    fin  timestamp without time zone,
    id_personne integer);

2°/ La fonction proprement dite :

CREATE OR REPLACE FUNCTION calcule_journees()
  RETURNS SETOF journee_de_travail AS
$BODY$
DECLARE rec RECORD;
DECLARE j journee_de_travail;
DECLARE deb boolean;
BEGIN
    deb := true;
    j.id_personne := -1;
    FOR rec IN SELECT * FROM activite ORDER BY id_personne, debut
    LOOP
        IF (j.id_personne <> rec.id_personne) OR (age(j.fin, rec.debut) < '00:00:00') THEN
            IF (deb = true) THEN
                deb := false;
            ELSE
                RETURN NEXT j;
            END IF;
            j.debut := rec.debut;
            j.fin := rec.fin;
            j.id_personne := rec.id_personne;
        ELSE
            j.fin := rec.fin;
        END IF;
    END LOOP;
    RETURN NEXT j;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

N'étant pas un super-pro de plPgSql, tout commentaire sur ce qui précède
est le bienvenu !

Maintenant, la pratique :

J'ai doublé le jeu de données fourni par une seconde journée pour 
vérifier que la
détection s'opère bien ...

select * FROM activite;
 id_activite |        debut        |         fin         | type | 
id_personne
-------------+---------------------+---------------------+------+-------------
           1 | 2007-10-01 09:00:00 | 2007-10-01 10:00:00 |    1 
|           2
           2 | 2007-10-01 10:00:00 | 2007-10-01 10:30:00 |    2 
|           2
           3 | 2007-10-01 10:30:00 | 2007-10-01 12:30:00 |    1 
|           2
           4 | 2007-10-01 12:30:00 | 2007-10-01 14:00:00 |    3 
|           2
           5 | 2007-10-01 14:00:00 | 2007-10-01 16:00:00 |    1 
|           2
           6 | 2007-10-01 16:00:00 | 2007-10-01 18:00:00 |    2 
|           2
           7 | 2007-10-03 22:00:00 | 2007-10-03 22:35:00 |    1 
|           3
           8 | 2007-10-03 22:35:00 | 2007-10-03 23:00:00 |    2 
|           3
           9 | 2007-10-03 23:00:00 | 2007-10-04 04:00:00 |    1 
|           3
          10 | 2007-10-04 04:00:00 | 2007-10-04 05:30:00 |    2 
|           3
          11 | 2007-10-02 09:00:00 | 2007-10-02 10:00:00 |    1 
|           2
          12 | 2007-10-02 10:00:00 | 2007-10-02 10:30:00 |    2 
|           2
          13 | 2007-10-02 10:30:00 | 2007-10-02 12:30:00 |    1 
|           2
          14 | 2007-10-02 12:30:00 | 2007-10-02 14:00:00 |    3 
|           2
          15 | 2007-10-02 14:00:00 | 2007-10-02 16:00:00 |    1 
|           2
          16 | 2007-10-02 16:00:00 | 2007-10-02 18:00:00 |    2 
|           2
          17 | 2007-10-04 22:00:00 | 2007-10-04 22:35:00 |    1 
|           3
          18 | 2007-10-04 22:35:00 | 2007-10-04 23:00:00 |    2 
|           3
          19 | 2007-10-04 23:00:00 | 2007-10-05 04:00:00 |    1 
|           3
          20 | 2007-10-05 04:00:00 | 2007-10-05 05:30:00 |    2 
|           3
(20 lignes)

et l'exécution de la fonction retourne :

SELECT * FROM calcule_journees();
        debut        |         fin         | id_personne
---------------------+---------------------+-------------
 2007-10-01 09:00:00 | 2007-10-01 18:00:00 |           2
 2007-10-02 09:00:00 | 2007-10-02 18:00:00 |           2
 2007-10-03 22:00:00 | 2007-10-04 05:30:00 |           3
 2007-10-04 22:00:00 | 2007-10-05 05:30:00 |           3
(4 lignes)

Voilà, ceci n'est qu'une base de travail qui pourra fort probablement  être
optimisée :
- par la création d'un index composé (id_personne, debut) qui permettra 
d'éviter
    un seqscan lors du parcours ???
- par l'ajout de paramètres à cette fonction afin de limiter le nombre 
de tuples à traiter
    (personne ou groupe de personnes, fourchette de dates, ...)

Tout celà étant à adapter aux contraintes (temps-réel ou pas), et au 
contexte d'utilisation...

Si, toutefois, ce genre de solution n'est pas suffisant en termes 
d'efficience, peut-être
faudrait -t-il alors se tourner vers une solution type "vue 
matérialisée" ... mais c'est
une autre histoire !

PostgreSQL-ement.

François Figarola.

-- 
INTERNET CONSULT
Mas Guerido
6 rue Aristide Bergès
66330 CABESTANY
 
Tel   04.68.66.09.29
fax   04.68.66.99.50
* francois(dot)figarola(at)i-consult(dot)fr


In response to

pgsql-fr-generale by date

Next:From: Francis LeboutteDate: 2007-11-15 16:08:21
Subject: taille fichiers BD, RAM, performance
Previous:From: Xavier PoinsardDate: 2007-11-14 15:16:24
Subject: Re: [pgsql-fr-generale] SQL ou plpgsql pour recherche journée travail

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group