Calcul de durée exacte à une réunion

**hugobob** · 26/08/2010, 13h10

Bonjour,

J'aimerais pouvoir calculer la durée réelle des membres lors d'une reunion.

Explication:

j'ai une table un peu comme ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
24/08/2010
Membre1
            Heure Action                             SENS
               7:49                                     ENTREE
               9:08                                     SORTIE
              11:05                                    ENTREE
              12:41                                    SORTIE
              14:25                                    ENTREE
              18:11                                    SORTIE
 
Membre2
            Heure Action
               7:45                                    ENTREE
              10:30                                    SORTIE
              10:41                                    ENTREE
              10:47                                    SORTIE
              10:48                                    ENTREE
              15:31                                    SORTIE
              17:09                                    ENTREE
              18:29                                    SORTIE

J'aimerais pouvoir calculer la durée réelle des membres lors de cette reunion ie

Pour le membre2
(10:30-7:45)+(10:41-10:47)+(15:31-10:48) +(18:29-17:09)= 2:45+0:06+5:17+1:20=9:28

Pour le membre1
(9:08-7:49)+(12:41-11:05)+(18:11-14:25) = 1:03+1:36+3:24=6:03

**xdescamp** · 26/08/2010, 13h58

La fonction analytique LEAD devrait faire l'affaire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
with ma_table as (
    select 'Membre1' membre, to_date ('24082010 07:49','DDMMYYYY HH24:MI') heure_action, 'ENTREE' sens from dual union all
    select 'Membre1' membre, to_date ('24082010 09:08','DDMMYYYY HH24:MI') heure_action, 'SORTIE' sens from dual union all
    select 'Membre1' membre, to_date ('24082010 11:05','DDMMYYYY HH24:MI') heure_action, 'ENTREE' sens from dual union all
    select 'Membre1' membre, to_date ('24082010 12:41','DDMMYYYY HH24:MI') heure_action, 'SORTIE' sens from dual union all
    select 'Membre1' membre, to_date ('24082010 14:25','DDMMYYYY HH24:MI') heure_action, 'ENTREE' sens from dual union all
    select 'Membre1' membre, to_date ('24082010 18:11','DDMMYYYY HH24:MI') heure_action, 'SORTIE' sens from dual union all
    select 'Membre2' membre, to_date ('24082010 07:45','DDMMYYYY HH24:MI') heure_action, 'ENTREE' sens from dual union all
    select 'Membre2' membre, to_date ('24082010 10:30','DDMMYYYY HH24:MI') heure_action, 'SORTIE' sens from dual union all
    select 'Membre2' membre, to_date ('24082010 10:41','DDMMYYYY HH24:MI') heure_action, 'ENTREE' sens from dual union all
    select 'Membre2' membre, to_date ('24082010 10:47','DDMMYYYY HH24:MI') heure_action, 'SORTIE' sens from dual union all
    select 'Membre2' membre, to_date ('24082010 10:48','DDMMYYYY HH24:MI') heure_action, 'ENTREE' sens from dual union all
    select 'Membre2' membre, to_date ('24082010 15:31','DDMMYYYY HH24:MI') heure_action, 'SORTIE' sens from dual union all
    select 'Membre2' membre, to_date ('24082010 17:09','DDMMYYYY HH24:MI') heure_action, 'ENTREE' sens from dual union all
    select 'Membre2' membre, to_date ('24082010 18:29','DDMMYYYY HH24:MI') heure_action, 'SORTIE' sens from dual
    )
select membre,
       sum(heure_action_suivante - heure_action) duree
from (select membre, heure_action, sens,
             lead(heure_action) over (partition by membre order by heure_action asc) heure_action_suivante
      from ma_table)
where sens = 'ENTREE'
group by membre;
 
 
MEMBRE       DUREE
------- ----------
Membre1 ,278472222
Membre2 ,370833333

Il ne reste plus qu'à formater le résultat (exprimé en jours).

**Vincent Rogier** · 26/08/2010, 17h00

je pense qu'il y a beaucoup plus simple sans fonction analytique...

Soit la table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
create table reunion
(
  membre varchar2(20), 
  action varchar2(10), 
  heure date
);

les membres et actions sont sous forme de string, c'est plus simple pour la demo.

Le jeu de test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
insert into reunion values ('membre1', 'entree', to_date('26082010090000', 'DDMMYYYYHH24MISS'));
insert into reunion values ('membre1', 'sortie',  to_date('26082010090500', 'DDMMYYYYHH24MISS'));
insert into reunion values ('membre1', 'entree', to_date('26082010091000', 'DDMMYYYYHH24MISS'));
insert into reunion values ('membre1', 'sortie',  to_date('26082010092000', 'DDMMYYYYHH24MISS'));
 
insert into reunion values ('membre2', 'entree', to_date('26082010093000', 'DDMMYYYYHH24MISS'));
insert into reunion values ('membre2', 'sortie',  to_date('26082010094000', 'DDMMYYYYHH24MISS'));
insert into reunion values ('membre2', 'entree', to_date('26082010095000', 'DDMMYYYYHH24MISS'));
insert into reunion values ('membre2', 'sortie',  to_date('26082010101500', 'DDMMYYYYHH24MISS'));

La requete codée à l'arrache donnant le nombre de minutes de présence :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
select 
    "MEMBRE",
        (
        SUM("SORTIE" - to_date('0101001', 'DDMMYYYY')) - 
        SUM("DEBUT"  - to_date('0101001', 'DDMMYYYY'))
         ) *1440 as "DUREE MN"
from
(
    select 
        membre as "MEMBRE", heure as "DEBUT", NULL as "SORTIE"
    from
        reunion  
    where
        action = 'entree'
    union
    select 
        membre as "MEMBRE", NULL as "DEBUT", heure as "SORTIE"
    from
        reunion  
    where
        action = 'sortie'
)
group by
    membre;

résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
MEMBRE                 DUREE MN
-------------------- ----------
membre2                      35
membre1                      15

**xdescamp** · 26/08/2010, 17h54

Envoyé par Vincent Rogier

je pense qu'il y a beaucoup plus simple sans fonction analytique...

Plus simple, ça se discute. Je pense que ça dépend des personnes, mais personnellement je ne trouve pas plus simple d'expliquer pourquoi on fait 2 soustractions avec une date arbitraire.

En plus, du point de vu des performances, la fonction analytique permet de n'accéder à la table qu'une seule fois alors que l'autre méthode nécessite 2 accès.

**Vincent Rogier** · 26/08/2010, 18h22

effectivement, il y avait plus simple et l'union n'est pas nécessaire (codée à l'arrache que j'avais dis...)

La solution la plus simple possible :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
select
   "MEMBRE",
        (
           SUM("FIN" - to_date('0101001', 'DDMMYYYY')) -
           SUM("DEBUT"  - to_date('0101001', 'DDMMYYYY'))
         )  *1440 as "DUREE MN"
from
(
   select
           membre as "MEMBRE",
           decode(action, 'entree', heure, NULL) as "DEBUT" ,
           decode(action, 'sortie', heure,  NULL) as "FIN"
   from
           reunion
)
group by
   "MEMBRE";

Et question plan d'exécution, la version "simple"' est plus avantageuse :

Version simple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     8 |   224 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |         |     8 |   224 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| REUNION |     8 |   224 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Version Analytique :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
 
--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     8 |   296 |     5  (40)| 00:00:01 |
|   1 |  HASH GROUP BY       |         |     8 |   296 |     5  (40)| 00:00:01 |
|*  2 |   VIEW               |         |     8 |   296 |     4  (25)| 00:00:01 |
|   3 |    WINDOW SORT       |         |     8 |   224 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| REUNION |     8 |   224 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Pourquoi faire compliqué quand on peut faire simple.....

**Vincent Rogier** · 26/08/2010, 18h27

Et puisque tu n'aimes pas les dates arbitraires, on n'a qu'a l'enlever... :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
    select 
        "MEMBRE",
        (SUM("FIN"-sysdate) - SUM("DEBUT"-sysdate))*1440 as "DUREE MN"
    from
    (
        select 
            membre as "MEMBRE",  
            decode(action, 'entree', heure, NULL) as "DEBUT" , 
            decode(action, 'sortie', heure,  NULL) as "FIN"
        from
            reunion 
    )
    group by
        "MEMBRE";

**xdescamp** · 27/08/2010, 09h16

SYSDATE reste une date arbitraire, mais au final j'achète ta solution.

**c.langlet** · 27/08/2010, 10h44

Bonjour,

Je ne veux pas pinailler, c'est juste pour ma culture personnel.

Quand est évalué sysdate dans la requête ?
-Au démarrage -> alors pas de problème
-A chaque itération -> alors ne vaut-il pas mieux utiliser trunc(sysdate) ?
(bien évidemment dans le cas ou les secondes sont importantes et que la requête dure un certain temps)

**Vincent Rogier** · 27/08/2010, 10h50

le plus sur c'est une une constante (date fixe dans la première version de la requete) mais pour répondre à ta question, sysdate est évaluée une seule fois par occurence.. mais effectivement le trunc(sydate) est mieux.

Pour t'en assurer, prend une table énorme et fais un 'select rowid, sysdate from table'...

**c.langlet** · 27/08/2010, 11h45

Merci pour la reponse,
j'ai essayé

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select rowid,to_char(sysdate,'HH24:MI:SS')

sur une grosse table et les secondes ne bouge pas , l'evaluation doit donc se faire en debut de requete.

Calcul de durée exacte à une réunion

SQL Oracle

Discussions similaires

Partager

Partager