Requête select

**sfoggy** · 23/09/2011, 09h49

Bonjour,

J'ai une table de la forme :

Code :

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

ID_EVE,ID_GROUP,ID_ACT_INI,ID_ACT_DST,DATE_DEBUT_EV,DATE_FIN_EV

J'ai une seconde table de la forme

Code :

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

ID_EVE DATE_DEBUT_PAUSE DATE_FIN_PAUSE

et on a si pause il y eu durant la ligne d'évènement.

Code :

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

DATE_DEBUT_EV < DATE_DEBUT_PAUSE < DATE_FIN_PAUSE < DATE_FIN_EV

plusieurs pauses sont possibles dans ce cas on a :

Code :

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

1
2
 
DATE_DEBUT_EV < DATE_DEBUT_PAUSE_1 < DATE_FIN_PAUSE_1 < DATE_DEBUT_PAUSE_2 < DATE_FIN_PAUSE_2 < .... < DATE_FIN_EV

J'aimerais obtenir le résultat suivant :

Code :

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

1
2
3
4
5
ID_EVE,ID_GROUP,ID_ACT_INI,ID_ACT_DST,DATE_DEBUT_EV,DATE_DEBUT_PAUSE_1
ID_EVE,ID_GROUP,ID_ACT_INI,ID_ACT_DST,DATE_FIN_PAUSE_1,DATE_DEBUT_PAUSE_2
 ...
ID_EVE,ID_GROUP,ID_ACT_INI,ID_ACT_DST,DATE_FIN_PAUSE_(N-1),DATE_DEBUT_PAUSE_(N)
ID_EVE,ID_GROUP,ID_ACT_INI,ID_ACT_DST,DATE_FIN_PAUSE_N,DATE_FIN_EV

Merci pour votre aide

**Waldar** · 23/09/2011, 14h25

Qu'avez-vous essayé comme requête ?

**sfoggy** · 23/09/2011, 16h12

Je traite actuellement par programme java et je souhaite tout faire dans une procédure stockée plus optimisée.

Je n'ai aucune idée sur la manière de procéder et le premier point est de savoir s'il est possible de le faire.

Cordialement

**Waldar** · 23/09/2011, 16h58

Une solution, j'ai supprimé du jeu de test les colonnes non essentielles :

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
With Tab1 as
(
select 1 as id_eve, trunc(sysdate) +  8/24 as dt_ev_deb, trunc(sysdate) + 18/24 as dt_ev_fin from dual union all
select 2          , trunc(sysdate) + 32/24             , trunc(sysdate) + 42/24              from dual
)
  ,  Tab2 as
(
select 1 as id_eve, trunc(sysdate) +  10.5/24 as dt_ps_deb, trunc(sysdate) +  10.75/24 as dt_ps_fin from dual union all
select 1          , trunc(sysdate) +  13  /24             , trunc(sysdate) +  14   /24              from dual union all
select 1          , trunc(sysdate) +  16  /24             , trunc(sysdate) +  16.5 /24              from dual union all
select 2          , trunc(sysdate) +  36  /24             , trunc(sysdate) +  37   /24              from dual
)
   , Tab as
(
select id_eve, dt_ev_deb as dt_deb, dt_ev_fin as dt_fin from Tab1
 union all
select id_eve, dt_ps_fin          , dt_ps_deb           from Tab2
)
  select id_eve
       , dt_deb
       , coalesce(lead(dt_fin) over(partition by id_eve order by dt_deb asc),
           first_value(dt_fin) over(partition by id_eve order by dt_deb asc)) as dt_fin
    from Tab
order by id_eve asc
       , dt_deb asc;
 
    ID_EVE DT_DEB              DT_FIN             
---------- ------------------- -------------------
         1 23/09/2011 08:00:00 23/09/2011 10:30:00
         1 23/09/2011 10:45:00 23/09/2011 13:00:00
         1 23/09/2011 14:00:00 23/09/2011 16:00:00
         1 23/09/2011 16:30:00 23/09/2011 18:00:00
         2 24/09/2011 08:00:00 24/09/2011 12:00:00
         2 24/09/2011 13:00:00 24/09/2011 18:00:00

**ORA-007** · 23/09/2011, 17h48

En fait tu veux ressortir toutes les périodes d'activités ?

En PL :

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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
 
DROP TABLE TEST_T1;
DROP TABLE TEST_T2;
 
CREATE TABLE TEST_T1
AS
SELECT 1 AS ID_EVE,TO_DATE('10/09/2011','DD/MM/YYYY') AS DATE_DEBUT_EV, TO_DATE('23/09/2011','DD/MM/YYYY') AS DATE_FIN_EV FROM DUAL UNION ALL
SELECT 2 AS ID_EVE,TO_DATE('01/07/2011','DD/MM/YYYY') AS DATE_DEBUT_EV, TO_DATE('30/07/2011','DD/MM/YYYY') AS DATE_FIN_EV FROM DUAL
;
 
CREATE TABLE TEST_T2
AS
SELECT 1 AS ID_EVE, TO_DATE('15/09/2011','DD/MM/YYYY') AS DATE_DEBUT_PAUSE, TO_DATE('16/09/2011','DD/MM/YYYY') AS DATE_FIN_PAUSE FROM DUAL UNION ALL
SELECT 1 AS ID_EVE, TO_DATE('18/09/2011','DD/MM/YYYY') AS DATE_DEBUT_PAUSE, TO_DATE('20/09/2011','DD/MM/YYYY') AS DATE_FIN_PAUSE FROM DUAL UNION ALL
SELECT 1 AS ID_EVE, TO_DATE('21/09/2011','DD/MM/YYYY') AS DATE_DEBUT_PAUSE, TO_DATE('22/09/2011','DD/MM/YYYY') AS DATE_FIN_PAUSE FROM DUAL UNION ALL
SELECT 2 AS ID_EVE, TO_DATE('15/07/2011','DD/MM/YYYY') AS DATE_DEBUT_PAUSE, TO_DATE('18/09/2011','DD/MM/YYYY') AS DATE_FIN_PAUSE FROM DUAL UNION ALL
SELECT 2 AS ID_EVE, TO_DATE('20/07/2011','DD/MM/YYYY') AS DATE_DEBUT_PAUSE, TO_DATE('23/09/2011','DD/MM/YYYY') AS DATE_FIN_PAUSE FROM DUAL UNION ALL
SELECT 2 AS ID_EVE, TO_DATE('25/07/2011','DD/MM/YYYY') AS DATE_DEBUT_PAUSE, TO_DATE('27/09/2011','DD/MM/YYYY') AS DATE_FIN_PAUSE FROM DUAL 
;
 
DECLARE
 
CURSOR c_TEST_T1 IS
SELECT *
FROM TEST_T1;
 
CURSOR c_TEST_T2(l_id_eve NUMBER) IS
SELECT *
FROM TEST_T2
WHERE TEST_T2.id_eve = l_id_eve
ORDER BY DATE_DEBUT_PAUSE 
;
 
TYPE r_periodes IS RECORD ( ID_EVE TEST_T1.ID_EVE%TYPE
                          , DT_DEB_ACTIVITE DATE
                          , DT_FIN_ACTIVITE DATE
                          );
 
TYPE t_periodes IS TABLE OF r_periodes INDEX BY BINARY_INTEGER;
 
l_periodes t_periodes;
 
BEGIN
 
l_periodes.DELETE;
 
FOR  l_TEST_T1 IN c_TEST_T1 
LOOP
    l_periodes(l_periodes.COUNT+1).id_eve        := l_TEST_T1.id_eve;
    l_periodes(l_periodes.COUNT).DT_DEB_ACTIVITE := l_TEST_T1.DATE_DEBUT_EV;
 
    FOR  l_TEST_T2 IN c_TEST_T2(l_TEST_T1.id_eve)
    LOOP
        l_periodes(l_periodes.COUNT).DT_FIN_ACTIVITE  := l_TEST_T2.DATE_DEBUT_PAUSE;
        l_periodes(l_periodes.COUNT+1).id_eve        := l_TEST_T1.id_eve;   
        l_periodes(l_periodes.COUNT).DT_DEB_ACTIVITE := l_TEST_T2.DATE_FIN_PAUSE;              
    END LOOP;
    l_periodes(l_periodes.COUNT).DT_FIN_ACTIVITE := l_TEST_T1.DATE_FIN_EV;
END LOOP;
 
FOR i in l_periodes.FIRST .. l_periodes.LAST
LOOP
     DBMS_OUTPUT.PUT_LINE (l_periodes(i).id_eve 
                              || '-' 
                              || l_periodes(i).DT_DEB_ACTIVITE 
                              || '-' 
                              ||l_periodes(i).DT_FIN_ACTIVITE );
END LOOP;
 
END;
/
 
1-10/09/11-15/09/11
1-16/09/11-18/09/11
1-20/09/11-21/09/11
1-22/09/11-23/09/11
2-01/07/11-15/07/11
2-18/09/11-20/07/11
2-23/09/11-25/07/11
2-27/09/11-30/07/11

Après, Quid des periodes qui se chevauchent ...

**sfoggy** · 26/09/2011, 10h17

Bonjour,

c'est en effet le but recherché ORA-007. Je teste avec l'ensemble des données nécessaires, car je dois calculer le temps d'activité par évènement et par action. Je regarde comment adapter votre solution.

Pour Waldar, la requête ne donne pas le résultat attendu :

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
 
WITH 
  Tab1 AS
  (
  SELECT 71416 AS id_eve, 1 AS action, TO_DATE('19/09/2011 17:29:33','DD/MM/YYYY HH24:mi:ss') AS dt_ev_deb, TO_DATE('20/09/2011 17:29:36','DD/MM/YYYY HH24:mi:ss') AS dt_ev_fin FROM dual UNION ALL
  SELECT 71416 AS id_eve, 2          , TO_DATE('20/09/2011 17:29:36','DD/MM/YYYY HH24:mi:ss') AS dt_ev_deb, TO_DATE('21/09/2011 13:03:09','DD/MM/YYYY HH24:mi:ss') AS dt_ev_fin FROM dual
  ),
  Tab2 AS
  (
    SELECT 71416 AS id_eve, TO_DATE('19/09/2011 17:29:51','DD/MM/YYYY HH24:mi:ss') AS dt_ps_deb, TO_DATE('20/09/2011 13:02:51','DD/MM/YYYY HH24:mi:ss') AS dt_ps_fin FROM dual UNION ALL
    SELECT 71416 AS id_eve, TO_DATE('20/09/2011 17:45:00','DD/MM/YYYY HH24:mi:ss') AS dt_ps_deb, TO_DATE('21/09/2011 12:02:51','DD/MM/YYYY HH24:mi:ss') AS dt_ps_fin FROM dual
  ),
  Tab AS
  (
  SELECT id_eve, dt_ev_deb AS dt_deb, dt_ev_fin AS dt_fin FROM Tab1
  union ALL
  SELECT id_eve, dt_ps_fin          , dt_ps_deb           FROM Tab2
  )
  SELECT id_eve
       , dt_deb
       , coalesce(lead(dt_fin) over(partition BY id_eve ORDER BY dt_deb ASC),
           first_value(dt_fin) over(partition BY id_eve ORDER BY dt_deb ASC)) AS dt_fin
    FROM Tab
ORDER BY id_eve ASC
       , dt_deb ASC;
 
 
ID_EVE DT_DEB              DT_FIN             
 71416 19.09.2011 17:29:33 19.09.2011 17:29:51
 71416 20.09.2011 13:02:51 21.09.2011 13:03:09
 71416 20.09.2011 17:29:36 20.09.2011 17:45:00
 71416 21.09.2011 12:02:51 20.09.2011 17:29:36

Merci pour vos réponses

Requête select

PL/SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager