Requête select [Résolu]

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

sfoggy · 26/09/2011, 10h41

J'ai adapté votre requête Waldar. La simplification faite sur l'action ne permettait pas d'avoir le résultat. Voici donc la requête :

Voici la requête donnant les infos souhaitées :

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
 
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, 1 AS action 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, 2 AS action 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, action, dt_ev_deb AS dt_deb, dt_ev_fin AS dt_fin FROM Tab1
  union ALL
  SELECT id_eve, action, dt_ps_fin          , dt_ps_deb           FROM Tab2
  )
  SELECT id_eve
       , dt_deb
       , coalesce(lead(dt_fin) over(partition BY id_eve, action ORDER BY dt_deb ASC),
           first_value(dt_fin) over(partition BY id_eve, action ORDER BY dt_deb ASC)) AS dt_fin
    FROM Tab
ORDER BY id_eve ASC
       , dt_deb ASC;

Merci pour votre aide.

Waldar · 26/09/2011, 12h34

Votre jeu de test est faux.
Vous avez mis le même id_eve pour deux événements différents.

Je maintiens donc ma solution.

sfoggy · 27/09/2011, 08h33

Le fonctionnement est bien celui-là, et en regardant plus avant dans les données, je trouve des choses comme cela

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
 
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 UNION ALL
  SELECT 71416 AS id_eve, 1 AS action, TO_DATE('21/09/2011 13:03:09','DD/MM/YYYY HH24:mi:ss') AS dt_ev_deb, TO_DATE('22/09/2011 12:31:22','DD/MM/YYYY HH24:mi:ss') AS dt_ev_fin FROM dual
  ),
  Tab2 AS
  (
    SELECT 71416 AS id_eve,1,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,2, 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 UNION ALL
    SELECT 71416 AS id_eve,1, TO_DATE('22/09/2011 09:45:00','DD/MM/YYYY HH24:mi:ss') AS dt_ps_deb, TO_DATE('22/09/2011 10:02:51','DD/MM/YYYY HH24:mi:ss') AS dt_ps_fin FROM dual
  ),
  Tab AS
  (
  SELECT id_eve, action, dt_ev_deb AS dt_deb, dt_ev_fin AS dt_fin FROM Tab1
  union ALL
  SELECT id_eve, NULL AS action, dt_ps_fin          , dt_ps_deb           FROM Tab2
  )
  SELECT id_eve
       , dt_deb                                                         ,
       lead(dt_fin) over(partition BY id_eve, action ORDER BY dt_deb ASC)
       , coalesce(lead(dt_fin) over(partition BY id_eve, action ORDER BY dt_deb ASC),
           first_value(dt_fin) over(partition BY id_eve, action ORDER BY dt_deb ASC)) AS dt_fin
    FROM Tab
ORDER BY id_eve ASC
       , dt_deb ASC;

et du coup, la solution est incorrecte.
id_eve n'est pas unique dans la table, il existe un autre champ pour clé

En reprenant votre solution initiale, je n'arrive pas à la solution attendue.

Code :

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

1
2
3
4
5
6
7
8
 
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 22.09.2011 12:31:22
 71416 21.09.2011 13:03:09 22.09.2011 09:45:00
 71416 22.09.2011 10:02:51 20.09.2011 17:29:36

Waldar · 28/09/2011, 12h47

Merci de communiquer un petit jeu de données réelles, dans votre dernier message vos données initiales n'ont ni queue ni tête.

sfoggy · 03/10/2011, 12h08

Bonjour,

voici un jeu de données pour un évènement

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
 
ID_EVE    ID_ACTEUR  ID_ACTION   DEBUT                       FIN
--------- ----------- ---------- -------------------------- -------------------
     70556         21          1        12/09/2011 16:13:22 13/09/2011 16:04:58
     70556         21          2        13/09/2011 16:04:58 13/09/2011 16:07:24
     70556         21          1        21/09/2011 15:30:37 21/09/2011 15:58:48
     70556         21          2        21/09/2011 15:58:48 21/09/2011 16:01:51
     70556         21          1        21/09/2011 16:01:51 21/09/2011 16:41:34
     70556         21          2        21/09/2011 16:41:34 21/09/2011 17:26:40
     70556         21          5        21/09/2011 17:26:40 21/09/2011 17:26:42
     70556         21          6        21/09/2011 17:26:42 21/09/2011 17:26:44
     70556         21          7        21/09/2011 17:26:44 03/10/2011 12:01:52
     70556         21          1        30/08/2011 15:19:56 30/08/2011 15:20:02
     70556         21          2        30/08/2011 15:20:02 30/08/2011 15:23:35
     70556         21          1        30/08/2011 15:23:35 30/08/2011 15:46:48
     70556         21          2        30/08/2011 15:46:48 12/09/2011 16:13:22
     70556         82          1        13/09/2011 16:07:24 15/09/2011 10:33:49
     70556         82          2        15/09/2011 10:33:49 21/09/2011 15:30:37
 
 
ID_EVE          DEBUT_PAUSE          FIN_PAUSE
------------- -------------------- -------------------
        70556   15/09/2011 11:23:35 15/09/2011 16:27:54
        70556   16/09/2011 17:12:36 19/09/2011 11:58:51
        70556   19/09/2011 12:00:19 21/09/2011 15:29:38

En espérant que cela soit plus parlant pour vous.

Merci de attention

Waldar · 03/10/2011, 14h44

D'accord, en fait par événement les périodes doivent être toujours complètement décrites ?

Sur ce jeu de test, est-ce bien le résultat suivant que vous souhaitez obtenir ?

    ID_EVE  ID_ACTEUR  ID_ACTION DT_DEB              DT_FIN             
---------- ---------- ---------- ------------------- -------------------
     70556         21          1 30/08/2011 15:19:56 30/08/2011 15:20:02
     70556         21          2 30/08/2011 15:20:02 30/08/2011 15:23:35
     70556         21          1 30/08/2011 15:23:35 30/08/2011 15:46:48
     70556         21          2 30/08/2011 15:46:48 12/09/2011 16:13:22
     70556         21          1 12/09/2011 16:13:22 13/09/2011 16:04:58
     70556         21          2 13/09/2011 16:04:58 13/09/2011 16:07:24
     70556         82          1 13/09/2011 16:07:24 15/09/2011 10:33:49
     70556         82          2 15/09/2011 10:33:49 15/09/2011 11:23:35
     70556         82          0 15/09/2011 11:23:35 15/09/2011 16:27:54
     70556         82          2 15/09/2011 16:27:54 16/09/2011 17:12:36
     70556         82          0 16/09/2011 17:12:36 19/09/2011 11:58:51
     70556         82          2 19/09/2011 11:58:51 19/09/2011 12:00:19
     70556         82          0 19/09/2011 12:00:19 21/09/2011 15:29:38
     70556         82          2 21/09/2011 15:29:38 21/09/2011 15:30:37
     70556         21          1 21/09/2011 15:30:37 21/09/2011 15:58:48
     70556         21          2 21/09/2011 15:58:48 21/09/2011 16:01:51
     70556         21          1 21/09/2011 16:01:51 21/09/2011 16:41:34
     70556         21          2 21/09/2011 16:41:34 21/09/2011 17:26:40
     70556         21          5 21/09/2011 17:26:40 21/09/2011 17:26:42
     70556         21          6 21/09/2011 17:26:42 21/09/2011 17:26:44
     70556         21          7 21/09/2011 17:26:44 03/10/2011 12:01:52

En gras les données rajoutées (avec 0 en id_action pour les pauses).

sfoggy · 03/10/2011, 15h13

Tout à fait.

Waldar · 03/10/2011, 15h33

L'idée reste grosso modo la même que dans la première requête :

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
WITH Tab1 AS
(
SELECT 70556 AS id_eve, 21 AS id_acteur, 1 AS id_action, to_date('12/09/2011 16:13:22', 'dd/mm/yyyy hh24:mi:ss') AS debut,  to_date('13/09/2011 16:04:58', 'dd/mm/yyyy hh24:mi:ss') AS fin FROM dual union ALL
SELECT 70556          , 21             , 2             , to_date('13/09/2011 16:04:58', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('13/09/2011 16:07:24', 'dd/mm/yyyy hh24:mi:ss')        FROM dual union ALL
SELECT 70556          , 21             , 1             , to_date('21/09/2011 15:30:37', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('21/09/2011 15:58:48', 'dd/mm/yyyy hh24:mi:ss')        FROM dual union ALL
SELECT 70556          , 21             , 2             , to_date('21/09/2011 15:58:48', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('21/09/2011 16:01:51', 'dd/mm/yyyy hh24:mi:ss')        FROM dual union ALL
SELECT 70556          , 21             , 1             , to_date('21/09/2011 16:01:51', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('21/09/2011 16:41:34', 'dd/mm/yyyy hh24:mi:ss')        FROM dual union ALL
SELECT 70556          , 21             , 2             , to_date('21/09/2011 16:41:34', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('21/09/2011 17:26:40', 'dd/mm/yyyy hh24:mi:ss')        FROM dual union ALL
SELECT 70556          , 21             , 5             , to_date('21/09/2011 17:26:40', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('21/09/2011 17:26:42', 'dd/mm/yyyy hh24:mi:ss')        FROM dual union ALL
SELECT 70556          , 21             , 6             , to_date('21/09/2011 17:26:42', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('21/09/2011 17:26:44', 'dd/mm/yyyy hh24:mi:ss')        FROM dual union ALL
SELECT 70556          , 21             , 7             , to_date('21/09/2011 17:26:44', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('03/10/2011 12:01:52', 'dd/mm/yyyy hh24:mi:ss')        FROM dual union ALL
SELECT 70556          , 21             , 1             , to_date('30/08/2011 15:19:56', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('30/08/2011 15:20:02', 'dd/mm/yyyy hh24:mi:ss')        FROM dual union ALL
SELECT 70556          , 21             , 2             , to_date('30/08/2011 15:20:02', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('30/08/2011 15:23:35', 'dd/mm/yyyy hh24:mi:ss')        FROM dual union ALL
SELECT 70556          , 21             , 1             , to_date('30/08/2011 15:23:35', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('30/08/2011 15:46:48', 'dd/mm/yyyy hh24:mi:ss')        FROM dual union ALL
SELECT 70556          , 21             , 2             , to_date('30/08/2011 15:46:48', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('12/09/2011 16:13:22', 'dd/mm/yyyy hh24:mi:ss')        FROM dual union ALL
SELECT 70556          , 82             , 1             , to_date('13/09/2011 16:07:24', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('15/09/2011 10:33:49', 'dd/mm/yyyy hh24:mi:ss')        FROM dual union ALL
SELECT 70556          , 82             , 2             , to_date('15/09/2011 10:33:49', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('21/09/2011 15:30:37', 'dd/mm/yyyy hh24:mi:ss')        FROM dual
)
  ,  Tab2 AS
(
SELECT 70556 AS id_eve, to_date('15/09/2011 11:23:35', 'dd/mm/yyyy hh24:mi:ss') AS debut_pause, to_date('15/09/2011 16:27:54', 'dd/mm/yyyy hh24:mi:ss') AS fin_pause FROM dual union ALL
SELECT 70556          , to_date('16/09/2011 17:12:36', 'dd/mm/yyyy hh24:mi:ss')               , to_date('19/09/2011 11:58:51', 'dd/mm/yyyy hh24:mi:ss')              FROM dual union ALL
SELECT 70556          , to_date('19/09/2011 12:00:19', 'dd/mm/yyyy hh24:mi:ss')               , to_date('21/09/2011 15:29:38', 'dd/mm/yyyy hh24:mi:ss')              FROM dual
)
  ,  Tab AS
(
SELECT id_eve, id_acteur, id_action, debut AS dt_deb, fin AS dt_fin FROM Tab1
 UNION ALL
SELECT id_eve, NULL     , NULL     , fin_pause      , debut_pause   FROM Tab2
 UNION ALL
SELECT id_eve, NULL     , NULL     , debut_pause    , fin_pause     FROM Tab2
)
  SELECT id_eve
       , coalesce(id_acteur, last_value(id_acteur IGNORE nulls) over(partition BY id_eve ORDER BY dt_deb ASC)) AS id_acteur
       , coalesce(id_action, case when dt_fin < dt_deb then last_value(id_action IGNORE nulls) over(partition BY id_eve ORDER BY dt_deb ASC) else 0 end) AS id_action
       , dt_deb                                                         
       , case
           when lead(id_action, 1, id_action) over(partition BY id_eve ORDER BY dt_deb ASC) IS NULL
             OR dt_fin < dt_deb
           then lead(dt_deb) over(partition BY id_eve ORDER BY dt_deb ASC)
           else dt_fin
         end AS dt_fin
    FROM Tab
ORDER BY id_eve ASC
       , dt_deb ASC;

L'algorithme ne doit par contre fonctionner qu'avec des données "propres", pas de chevauchement de période de pause ou multi acteur, et surement d'autres limitations.

sfoggy · 03/10/2011, 17h30

Le chevauchement des périodes est impossible ainsi que le changement d'acteur.

Je jette un coup d' oeil pour comprendre le fonctionnement.

Merci pour la solution.

23/09/2011, 14h25	#2
Waldar Modérateur Fabien Ingénieur d'études en décisionnel Inscription : septembre 2008 Messages : 5 686 Détails du profil Informations personnelles : Nom : Fabien Âge : 34 Localisation : France, Yvelines (Île de France) Informations professionnelles : Activité : Ingénieur d'études en décisionnel Secteur : Arts - Culture Informations forums : Inscription : septembre 2008 Messages : 5 686 Points : 10 431 Points : 10 431	Qu'avez-vous essayé comme requête ? __________________ Email : http://scr.im/waldar
	00

26/09/2011, 12h34	#8
Waldar Modérateur Fabien Ingénieur d'études en décisionnel Inscription : septembre 2008 Messages : 5 686 Détails du profil Informations personnelles : Nom : Fabien Âge : 34 Localisation : France, Yvelines (Île de France) Informations professionnelles : Activité : Ingénieur d'études en décisionnel Secteur : Arts - Culture Informations forums : Inscription : septembre 2008 Messages : 5 686 Points : 10 431 Points : 10 431	Votre jeu de test est faux. Vous avez mis le même id_eve pour deux événements différents. Je maintiens donc ma solution. __________________ Email : http://scr.im/waldar
	00

28/09/2011, 12h47	#10
Waldar Modérateur Fabien Ingénieur d'études en décisionnel Inscription : septembre 2008 Messages : 5 686 Détails du profil Informations personnelles : Nom : Fabien Âge : 34 Localisation : France, Yvelines (Île de France) Informations professionnelles : Activité : Ingénieur d'études en décisionnel Secteur : Arts - Culture Informations forums : Inscription : septembre 2008 Messages : 5 686 Points : 10 431 Points : 10 431	Merci de communiquer un petit jeu de données réelles, dans votre dernier message vos données initiales n'ont ni queue ni tête. __________________ Email : http://scr.im/waldar
	00

23/09/2011, 16h12	#3
sfoggy Membre à l'essai Inscription : septembre 2009 Messages : 75 Détails du profil Informations forums : Inscription : septembre 2009 Messages : 75 Points : 22 Points : 22	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
	00

03/10/2011, 15h13	#13
sfoggy Membre à l'essai Inscription : septembre 2009 Messages : 75 Détails du profil Informations forums : Inscription : septembre 2009 Messages : 75 Points : 22 Points : 22	Tout à fait.
	00

03/10/2011, 17h30	#15
sfoggy Membre à l'essai Inscription : septembre 2009 Messages : 75 Détails du profil Informations forums : Inscription : septembre 2009 Messages : 75 Points : 22 Points : 22	Le chevauchement des périodes est impossible ainsi que le changement d'acteur. Je jette un coup d' oeil pour comprendre le fonctionnement. Merci pour la solution.
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email