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

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