Calcul par tranche de 30 min entre deux dates

Version imprimable

01/04/2019, 09h15
pcano

Calcul par tranche de 30 min entre deux dates

Bonjour,

Je ne sais pas par quel bout attaquer ce problème là, aussi je sollicite un peu d'aide. J'ai une requête SQL qui me renvoie les données sous cette forme (simplifiée):

Intervention | Date-Heure début | Date-Heure fin | Matricule Agent | Nom Prénom
1020 | 01/01/2018 08:32 | 01/01/2018 09:40 | 123 | Jon Doe
1020 | 01/01/2018 08:32 | 01/01/2018 09:40 | 212 | Johnny English
1020 | 01/01/2018 08:32 | 01/01/2018 09:40 | 411 | Pierre Dupond
1021 | 01/01/2018 09:17 | 01/01/2018 10:20 | 520 | Tinitin Milou
1021 | 01/01/2018 09:17 | 01/01/2018 10:20 | 732 | Edouard Leclerc
1022 | 01/01/2018 10:32 | 01/01/2018 11:40 | 123 | Jon Doe
1022 | 01/01/2018 10:32 | 01/01/2018 11:40 | 212 | Johnny English
1022 | 01/01/2018 10:32 | 01/01/2018 11:40 | 411 | Pierre Dupond

Ce que je souhaiterais pouvoir savoir c'est le nombre de personne en intervention par tranche de 30 min. (peu importe le format d"expression de la tranche, date, numéro, minutes, timestamp ...).
Dans l'exemple ci dessous cela donnerait :

Tranche | Nbre d'intervenant
le 01/01/2018 de 08:00 à 08:29 | 0
le 01/01/2018 de 08:30 à 08:59 | 3
le 01/01/2018 de 09:00 à 09:29 | 5
le 01/01/2018 de 09:30 à 09:59 | 5
le 01/01/2018 de 10:00 à 10:29 | 2
le 01/01/2018 de 10:30 à 10:59 | 3
le 01/01/2018 de 11:00 à 11:29 | 3
le 01/01/2018 de 11:30 à 11:59 | 3

De plus, je dois pouvoir avoir ça sur toute une année, qui compte environ 18 000 interventions avec 2 à 6 participants par interventions.

Une idée de comment faire ?

Merci!

bonjour,

Il faut construire une table des tranches horaires puis faire une jointure avec cette table :

Code:

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
30
31
32
33
34
With Htab(Hseq, Hdeb, Hfin) as
    (select 1
          , cast('00:00:00' as time)
          , cast('00:29:59' as time)   
     union all
     select Hseq+1
          , Hdeb+30 minutes 
          , Hfin+30 minutes
     from Htab
     where Hseq < 48)
   , Tab1 (Xidt, Xnom, XTst) as
    (
     select 004, 'Tintin', cast('2019-04-01 00:05:16.000000' as timestamp)   
     union all
     select 005, 'Dupont', cast('2019-04-01 01:12:15.000000' as timestamp)   
     union all
     select 006, 'Durand', cast('2019-04-01 01:20:32.000000' as timestamp)   
     union all
     select 007, 'Martin', cast('2019-04-01 01:40:06.000000' as timestamp)   
     union all
     select 008, 'Tintin', cast('2019-04-01 02:09:25.000000' as timestamp)   
     union all
     select 009, 'Tagada', cast('2019-04-01 02:09:25.000000' as timestamp)   
     union all
     select 010, 'ABCDEF', cast('2019-04-01 02:09:25.000000' as timestamp)   
    )
 
select Hdeb
     , Hfin
     , count(distinct xnom)
from Htab
inner join Tab1
   on cast(substr(char(Xtst), 12, 8) as time) between Hdeb and Hfin    
group by Hdeb, Hfin

On obtient :

Code:

1
2
3
4
5
6
7
 
HDEB      HFIN      3            
---------------------------------
00:00:00  00:29:59  1            
01:00:00  01:29:59  2            
01:30:00  01:59:59  1            
02:00:00  02:29:59  3

Il faut ajouter une restriction sur la date (dans mon jeu d'essai, toutes les lignes sont sur le même jour)
et bien sur faire un regroupement sur l'identifiant de la personne et pas sur le nom, les homonymes étant possibles, là j'ai fait une solution à la va vite, mais le principe reste le même ;)

01/04/2019, 18h29
pcano

Bonjour,

Merci pour cette première approche, mais j'ai l'impression que la requête est basée sur une date-heure fixe par personne. Par exemple 'Tintin' à '2019-04-01 00:05:16'.
Or dans mon cas Tintin intervient de 00:05:16 à 01:45:00 et donc doit être comptabilisé :
sur la tranches 00:00 à 00:29:59
mais aussi
sur les tranches suivantes: 00:30 à 00:59:59 , 01:00 à 01:29:59 et 01:30 à 01:59:59

Une idée ?

Merci.
02/04/2019, 18h24
Waldar

Quel est votre SGBD ? Le support des périodes n'est pas la chose la plus répandue ni la plus homogène dans les différents dialectes.
03/04/2019, 11h18
pcano

Bonjour,

C'est de l'oracle 10G

Cdlt

Essayez ainsi :

Code:

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- Données
create table pcano
( Intervention  smallint
, dt_deb        date
, dt_fin        date
, id_agent      smallint
, nm_agent      varchar2(20)
);
 
insert all
    into pcano values (1020, to_date('01/01/2018 08:32', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 09:40', 'dd/mm/yyyy hh24:mi'), 123, 'Jon Doe')
    into pcano values (1020, to_date('01/01/2018 08:32', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 09:40', 'dd/mm/yyyy hh24:mi'), 212, 'Johnny English')
    into pcano values (1020, to_date('01/01/2018 08:32', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 09:40', 'dd/mm/yyyy hh24:mi'), 411, 'Pierre Dupond')
    into pcano values (1021, to_date('01/01/2018 09:17', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 10:20', 'dd/mm/yyyy hh24:mi'), 520, 'Tinitin Milou')
    into pcano values (1021, to_date('01/01/2018 09:17', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 10:20', 'dd/mm/yyyy hh24:mi'), 732, 'Edouard Leclerc')
    into pcano values (1022, to_date('01/01/2018 10:32', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 11:40', 'dd/mm/yyyy hh24:mi'), 123, 'Jon Doe')
    into pcano values (1022, to_date('01/01/2018 10:32', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 11:40', 'dd/mm/yyyy hh24:mi'), 212, 'Johnny English')
    into pcano values (1022, to_date('01/01/2018 10:32', 'dd/mm/yyyy hh24:mi'), to_date('01/01/2018 11:40', 'dd/mm/yyyy hh24:mi'), 411, 'Pierre Dupond')
select * from dual;
 
 
-- Requête
with cte_bornes (dt_min, dt_max) as
(
select trunc(min(dt_deb), 'dd') + (floor((min(dt_deb) - trunc(min(dt_deb), 'dd'))*48)-1)/48
     , trunc(max(dt_fin), 'dd') + (floor((max(dt_fin) - trunc(max(dt_fin), 'dd'))*48)+1)/48
  from pcano
)
  ,  cte_period (per_min, per_max) as
(
    select dt_min + (level - 1) * 30/60/24
         , dt_min + level       * 30/60/24
      from cte_bornes
connect by level <= (dt_max - dt_min)*48
)
    select per.per_min, per.per_max
         , count(tbl.Intervention)
      from cte_period per
 left join pcano      tbl on (tbl.dt_deb, tbl.dt_fin) overlaps (per.per_min, per.per_max)
  group by per.per_min, per.per_max
  order by per.per_min;
 
PER_MIN              PER_MAX              COUNT(TBL.INTERVENTION)
-------------------  -------------------  -----------------------
2018-01-01 08:00:00  2018-01-01 08:30:00                        0
2018-01-01 08:30:00  2018-01-01 09:00:00                        3
2018-01-01 09:00:00  2018-01-01 09:30:00                        5
2018-01-01 09:30:00  2018-01-01 10:00:00                        5
2018-01-01 10:00:00  2018-01-01 10:30:00                        2
2018-01-01 10:30:00  2018-01-01 11:00:00                        3
2018-01-01 11:00:00  2018-01-01 11:30:00                        3
2018-01-01 11:30:00  2018-01-01 12:00:00                        3

Ben, la réponse d'escartefique me semble tout à fait pertinente !
Il suffit de s'en inspirer.

Il me semble qu'Oracle 10g il n'existe pas encore les CTE.

En s'inspirant de la solution de Waldar, il me semble aussi que l'opérateur Overlap n'est pas non plus disponible en 10g.

Du coup la clause FROM devrait ressembler à
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 FROM ( select dt_min + (level - 1) * 30/60/24 as per_min , dt_min + level * 30/60/24 as per_max from ( select trunc(min(dt_deb), 'dd') + (floor((min(dt_deb) - trunc(min(dt_deb), 'dd'))*48)-1)/48 as dt_min , trunc(max(dt_fin), 'dd') + (floor((max(dt_fin) - trunc(max(dt_fin), 'dd'))*48)+1)/48 as dt_max from pcano )cte_bornes connect by level <= (dt_max - dt_min)*48 )cte_period inner join pcano on pcano.dt_deb < cte_period.per_max and pcano.dt_fin > cte_period.per_min

03/04/2019, 16h11
Waldar

Citation:

Envoyé par Michel.Priori

Il me semble qu'Oracle 10g il n'existe pas encore les CTE.

Les CTE sont supportées mais pas intégralement - il ne faut pas lister les colonnes ; effectivement la requête proposée nécessite cet ajustement.
OVERLAPS existe depuis la 9i il me semble, mais en tant que fonction non documentée.
03/04/2019, 16h15
Michel.Priori

En tous les cas, merci Waldar d'avoir proposé une réponse avec un CONNECT BY.

C'est vrai qu'avec l'utilisation des CTE récursives j'avais largement mis de coté cette écriture qui reste d'une limpidité magistrale.