Fonction analytique en SQL

Bonjour,

je dispose de la table suivante :
Code:

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

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:

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.

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:

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

Merci, cela fonctionne très bien :D