IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Calcul de durée exacte à une réunion


Sujet :

SQL Oracle

  1. #1
    Membre actif Avatar de hugobob
    Profil pro
    FOI
    Inscrit en
    septembre 2005
    Messages
    169
    Détails du profil
    Informations personnelles :
    Localisation : Gabon

    Informations professionnelles :
    Activité : FOI

    Informations forums :
    Inscription : septembre 2005
    Messages : 169
    Points : 203
    Points
    203
    Par défaut Calcul de durée exacte à une réunion
    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

  2. #2
    Membre averti Avatar de xdescamp
    Homme Profil pro
    Inscrit en
    octobre 2008
    Messages
    300
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : octobre 2008
    Messages : 300
    Points : 442
    Points
    442
    Par défaut
    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).

  3. #3
    Rédacteur
    Avatar de Vincent Rogier
    Profil pro
    Inscrit en
    juillet 2007
    Messages
    2 373
    Détails du profil
    Informations personnelles :
    Âge : 43
    Localisation : France

    Informations forums :
    Inscription : juillet 2007
    Messages : 2 373
    Points : 5 306
    Points
    5 306
    Par défaut
    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
    Vincent Rogier.

    Rubrique ORACLE : Accueil - Forum - Tutoriels - FAQ - Livres - Blog

    Vous voulez contribuer à la rubrique Oracle ? Contactez la rubrique !

    OCILIB (C Driver for Oracle)

    Librairie C Open Source multi-plateformes pour accéder et manipuler des bases de données Oracle

  4. #4
    Membre averti Avatar de xdescamp
    Homme Profil pro
    Inscrit en
    octobre 2008
    Messages
    300
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : octobre 2008
    Messages : 300
    Points : 442
    Points
    442
    Par défaut
    Citation Envoyé par Vincent Rogier Voir le message
    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.

  5. #5
    Rédacteur
    Avatar de Vincent Rogier
    Profil pro
    Inscrit en
    juillet 2007
    Messages
    2 373
    Détails du profil
    Informations personnelles :
    Âge : 43
    Localisation : France

    Informations forums :
    Inscription : juillet 2007
    Messages : 2 373
    Points : 5 306
    Points
    5 306
    Par défaut
    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.

    Rubrique ORACLE : Accueil - Forum - Tutoriels - FAQ - Livres - Blog

    Vous voulez contribuer à la rubrique Oracle ? Contactez la rubrique !

    OCILIB (C Driver for Oracle)

    Librairie C Open Source multi-plateformes pour accéder et manipuler des bases de données Oracle

  6. #6
    Rédacteur
    Avatar de Vincent Rogier
    Profil pro
    Inscrit en
    juillet 2007
    Messages
    2 373
    Détails du profil
    Informations personnelles :
    Âge : 43
    Localisation : France

    Informations forums :
    Inscription : juillet 2007
    Messages : 2 373
    Points : 5 306
    Points
    5 306
    Par défaut
    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";
    Vincent Rogier.

    Rubrique ORACLE : Accueil - Forum - Tutoriels - FAQ - Livres - Blog

    Vous voulez contribuer à la rubrique Oracle ? Contactez la rubrique !

    OCILIB (C Driver for Oracle)

    Librairie C Open Source multi-plateformes pour accéder et manipuler des bases de données Oracle

  7. #7
    Membre averti Avatar de xdescamp
    Homme Profil pro
    Inscrit en
    octobre 2008
    Messages
    300
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : octobre 2008
    Messages : 300
    Points : 442
    Points
    442
    Par défaut
    SYSDATE reste une date arbitraire, mais au final j'achète ta solution.

  8. #8
    Membre averti
    Profil pro
    Inscrit en
    août 2005
    Messages
    316
    Détails du profil
    Informations personnelles :
    Âge : 51
    Localisation : France

    Informations forums :
    Inscription : août 2005
    Messages : 316
    Points : 388
    Points
    388
    Par défaut
    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)

  9. #9
    Rédacteur
    Avatar de Vincent Rogier
    Profil pro
    Inscrit en
    juillet 2007
    Messages
    2 373
    Détails du profil
    Informations personnelles :
    Âge : 43
    Localisation : France

    Informations forums :
    Inscription : juillet 2007
    Messages : 2 373
    Points : 5 306
    Points
    5 306
    Par défaut
    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'...
    Vincent Rogier.

    Rubrique ORACLE : Accueil - Forum - Tutoriels - FAQ - Livres - Blog

    Vous voulez contribuer à la rubrique Oracle ? Contactez la rubrique !

    OCILIB (C Driver for Oracle)

    Librairie C Open Source multi-plateformes pour accéder et manipuler des bases de données Oracle

  10. #10
    Membre averti
    Profil pro
    Inscrit en
    août 2005
    Messages
    316
    Détails du profil
    Informations personnelles :
    Âge : 51
    Localisation : France

    Informations forums :
    Inscription : août 2005
    Messages : 316
    Points : 388
    Points
    388
    Par défaut
    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.

Discussions similaires

  1. calcul de durée sur une suite de données
    Par madousn dans le forum Requêtes
    Réponses: 4
    Dernier message: 24/06/2008, 10h04
  2. [MySQL] Calculer la somme d'une durée sous la forme Heure:Minute:Seconde
    Par radhwene dans le forum PHP & Base de données
    Réponses: 1
    Dernier message: 23/06/2007, 10h49
  3. [8.5] calcul de moyenne sur une durée
    Par rihiveli dans le forum SAP Crystal Reports
    Réponses: 5
    Dernier message: 27/04/2007, 11h06
  4. Réponses: 7
    Dernier message: 25/04/2007, 15h38
  5. Réponses: 3
    Dernier message: 19/12/2006, 17h43

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo