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

[François Figarola <francois(dot)figarola(at)i-consult(dot)fr>]

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