Discussion :

[herve91]

Bonjour,

je dispose de la table suivante :

Code :

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

1
2
3
4
5
CREATE TABLE T (
  ID NUMBER(8),
  EQT VARCHAR2(10),
  HORLOGE DATE,
  STATUT NUMBER(1));

Par exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
 1 xx 20/08/2015 10:00:00 1
 2 xx 20/08/2015 10:10:00 1
 3 xx 20/08/2015 10:12:00 0
 4 xx 20/08/2015 10:20:00 0
 5 xx 20/08/2015 10:24:00 1
 6 xx 20/08/2015 10:30:00 1
 7 xx 20/08/2015 10:32:00 1
 8 yy 20/08/2015 11:10:00 1
 9 yy 20/08/2015 11:20:00 1
10 yy 20/08/2015 11:30:00 1

J'ai besoin de détecter les intervalles pour lesquelles le STATUT vaut 1 sur au moins 3 lignes consécutives, pour chaque EQT.
La requête correspondante devrait renvoyer les informations suivantes :

Code :

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

1
2
3
4
 
EQT HORLOGE DEBUT       HORLOGE FIN
xx  20/08/2015 10:24:00 20/08/2015 10:32:00
yy  20/08/2015 11:10:00 20/08/2015 11:30:00

Je pense qu'il faut utiliser les fonctions analytiques d'Oracle.
Qu'en pensez-vous ? Une idée ?
Merci d'avance.

[skuatamad]

Une approche un peu brut, on peut aussi faire du WITH recursif.
J'ai enrichi le jeu de test pour prendre en compte le fait que c'est au moins 3 statut à 1 consécutif et qu'il peut y avoir plusieurs séquences de statut à 1 pour un EQT.

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
  with t as (
select 1 as id,  'xx' as EQT, to_date('20/08/2015 10:00:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 2 as id,  'xx' as EQT, to_date('20/08/2015 10:10:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 3 as id,  'xx' as EQT, to_date('20/08/2015 10:12:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 0 as STATUT from dual union all
select 4 as id,  'xx' as EQT, to_date('20/08/2015 10:20:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 0 as STATUT from dual union all
select 5 as id,  'xx' as EQT, to_date('20/08/2015 10:24:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 6 as id,  'xx' as EQT, to_date('20/08/2015 10:30:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 7 as id,  'xx' as EQT, to_date('20/08/2015 10:32:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 8 as id,  'yy' as EQT, to_date('20/08/2015 11:10:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 9 as id,  'yy' as EQT, to_date('20/08/2015 11:20:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 10 as id, 'yy' as EQT, to_date('20/08/2015 11:30:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 11 as id,  'xx' as EQT, to_date('20/08/2015 10:25:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 12 as id,  'xx' as EQT, to_date('20/08/2015 10:26:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 13 as id,  'xx' as EQT, to_date('20/08/2015 10:27:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 14 as id,  'xx' as EQT, to_date('20/08/2015 10:35:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 0 as STATUT from dual union all
select 15 as id,  'xx' as EQT, to_date('20/08/2015 10:37:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 0 as STATUT from dual union all
select 16 as id,  'xx' as EQT, to_date('20/08/2015 10:38:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 17 as id,  'xx' as EQT, to_date('20/08/2015 10:39:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 18 as id,  'xx' as EQT, to_date('20/08/2015 10:40:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 19 as id,  'yy' as EQT, to_date('20/08/2015 11:25:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 20 as id,  'yy' as EQT, to_date('20/08/2015 11:27:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual union all
select 21 as id,  'yy' as EQT, to_date('20/08/2015 11:32:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 0 as STATUT from dual union all
select 22 as id,  'yy' as EQT, to_date('20/08/2015 11:35:00', 'dd/mm/yyyy hh24:mi:ss') as HORLOGE, 1 as STATUT from dual
),
       list_t as (
select id, eqt, horloge, statut
     , lead(statut,1) over (partition by eqt order by horloge) as next_statut
     , lead(statut,2) over (partition by eqt order by horloge) as next_statut_2
     , lag (statut,1) over (partition by eqt order by horloge) as prev_statut
     , lag (statut,2) over (partition by eqt order by horloge) as prev_statut_2
  from t
),
       test_deb_fin as (
select id, eqt, horloge
     , case when coalesce(prev_statut,0) = 0 and statut = 1 and next_statut = 1 and next_statut_2 = 1
            then 'debut'
            when coalesce(next_statut,0) = 0 and statut = 1 and prev_statut = 1 and prev_statut_2 = 1
            then 'fin'
        end as borne
  from list_t
),
       liste_borne as (
select eqt
     , case when borne = 'debut' then horloge end as HORLOGE_DEBUT
     , lead(horloge) over (partition by eqt order by horloge) as HORLOGE_FIN
  from test_deb_fin
 where borne is not null
)
select * 
  from liste_borne
 where HORLOGE_DEBUT is not null
 order by eqt
 
EQ HORLOGE_DEBUT       HORLOGE_FIN
-- ------------------- -------------------
xx 20/08/2015 10:24:00 20/08/2015 10:32:00
xx 20/08/2015 10:38:00 20/08/2015 10:40:00
yy 20/08/2015 11:10:00 20/08/2015 11:30:00

[herve91]

Merci, cela fonctionne très bien