Demande d'aide pour script de nettoyage

Version imprimable

Bonjour à tous (et à toutes),

Voilà mon petit problème : tous les jours (sauf arrêt des tâches automatiques), je récupère des infos d'un autre système afin de tracer d'éventuels changements d'état de mes enregistrements. Chaque fois qu'un état est modifié, je stocke la date et l'état.

Malheureusement, un bug a fait que le stockage avait lieu systématiquement (même quand l'état n'a pas changé).
Je me retrouve donc avec une base à nettoyer :mrgreen:

Ci-dessous, un extrait de la table à nettoyer (avec en rouge les lignes à supprimer) :
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 ORDER_ID DATE STATE 47087 07/07/2010 15:31 0 47087 13/07/2010 05:08 5 47087 14/07/2010 06:04 5 47087 15/07/2010 06:46 5 47087 21/07/2010 05:07 5 47087 22/07/2010 05:00 5 47087 23/07/2010 05:13 9 47087 24/07/2010 05:14 9 47087 25/07/2010 05:27 9 47087 26/07/2010 05:11 9 47087 27/07/2010 05:08 15 47088 07/07/2010 15:31 0 47088 13/07/2010 05:06 5 47088 14/07/2010 06:55 5 47088 15/07/2010 05:54 5 47088 21/07/2010 05:11 5 47088 22/07/2010 05:03 5 47088 23/07/2010 05:13 9 47088 24/07/2010 05:02 9 47088 25/07/2010 05:01 9 47088 26/07/2010 05:11 9 47088 27/07/2010 05:21 15 47089 07/07/2010 15:31 0 47089 13/07/2010 05:06 10 47089 14/07/2010 06:01 10 47089 15/07/2010 06:14 10 47089 21/07/2010 05:01 10 47089 22/07/2010 05:11 10 47089 23/07/2010 05:13 10 47089 24/07/2010 05:05 10 47089 25/07/2010 05:04 10 47089 26/07/2010 05:11 10 47089 27/07/2010 05:16 14
Je me suis lancé dans une procédure Oracle qui, à l'heure actuelle, ressemble à çà :
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 DECLARE -- Parcourir chaque ligne de commande CURSOR iterOrderIds_cur IS SELECT DISTINCT order_id FROM state_history; -- Stocker l'id de la commande courante currentOrderId_rec iterOrderIds_cur%ROWTYPE; -- Parcourir chaque état pour une ligne de commande CURSOR iterOrderStates_cur (orderId_in NUMBER) IS SELECT DISTINCT hist_at_state FROM state_history WHERE order_id = orderId_in; -- Stocker l'état courant currentState_rec iterOrderStates_cur%ROWTYPE; BEGIN -- Parcourir l'historique des états pour chaque ligne de commande FOR currentOrderId_rec IN iterOrderIds_cur LOOP dbms_output.put_line(currentOrderId_rec.order_id); -- Pour chaque ligne de commande, parcourir les états AT de l'historique FOR currentState_rec IN iterOrderStates_cur(currentOrderId_rec.order_id) LOOP dbms_output.put_line(currentState_rec.hist_at_state); -- A SUIVRE... END LOOP; END LOOP; END;
C'est loin d'être terminé et voilà ce que je comptais faire :
- pour chaque état d'une ligne de commande, rechercher l'enregistrement avec la date la plus récente (SELECT ...)
- supprimer les autres lignes (DELETE ...)

Mais çà me parait laborieux et je me demande si je suis sur la bonne piste ou bien si une solution plus simple existe ?
Par exemple en utilisant des fonctions sur les dates ?

Je suis donc preneur de conseils et/ou des commentaires qui pourraient éclairer ma lanterne et m'aider à partir dans le bon sens :ccool:

Merci d'avance ;)

Cordialement.
S.

J'ai un peu plus simple !

Jeu de test :

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
create table t
(
    order_id    number(5),
    dt          date,
    state       number(2)
);
 
insert into t (order_id, dt, state)
select 47087, to_date('07/07/2010 15:31', 'dd/mm/yyyy hh24:mi'),  0 from dual union all
select 47087, to_date('13/07/2010 05:08', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47087, to_date('14/07/2010 06:04', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47087, to_date('15/07/2010 06:46', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47087, to_date('21/07/2010 05:07', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47087, to_date('22/07/2010 05:00', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47087, to_date('23/07/2010 05:13', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47087, to_date('24/07/2010 05:14', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47087, to_date('25/07/2010 05:27', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47087, to_date('26/07/2010 05:11', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47087, to_date('27/07/2010 05:08', 'dd/mm/yyyy hh24:mi'), 15 from dual union all
select 47088, to_date('07/07/2010 15:31', 'dd/mm/yyyy hh24:mi'),  0 from dual union all
select 47088, to_date('13/07/2010 05:06', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47088, to_date('14/07/2010 06:55', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47088, to_date('15/07/2010 05:54', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47088, to_date('21/07/2010 05:11', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47088, to_date('22/07/2010 05:03', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47088, to_date('23/07/2010 05:13', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47088, to_date('24/07/2010 05:02', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47088, to_date('25/07/2010 05:01', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47088, to_date('26/07/2010 05:11', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47088, to_date('27/07/2010 05:21', 'dd/mm/yyyy hh24:mi'), 15 from dual union all
select 47089, to_date('07/07/2010 15:31', 'dd/mm/yyyy hh24:mi'),  0 from dual union all
select 47089, to_date('13/07/2010 05:06', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('14/07/2010 06:01', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('15/07/2010 06:14', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('21/07/2010 05:01', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('22/07/2010 05:11', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('23/07/2010 05:13', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('24/07/2010 05:05', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('25/07/2010 05:04', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('26/07/2010 05:11', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('27/07/2010 05:16', 'dd/mm/yyyy hh24:mi'), 14 from dual;
 
commit;

Suppression :

Code:

1
2
3
4
5
6
7
8
9
10
11
delete from t
where rowid in (With SR as
                (
                select rowid rd, order_id, dt, state,
                       lag(state) over(partition by order_id order by dt asc) as lg
                  from t
                )
                select rd
                  from sr
                 where state = lg)
-- 22 rows deleted.

Données restantes :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select * from t
order by 1, 2;
 
ORDER_ID	DT			STATE
47087		07/07/2010 15:31:00	0
47087		13/07/2010 05:08:00	5
47087		23/07/2010 05:13:00	9
47087		27/07/2010 05:08:00	15
47088		07/07/2010 15:31:00	0
47088		13/07/2010 05:06:00	5
47088		23/07/2010 05:13:00	9
47088		27/07/2010 05:21:00	15
47089		07/07/2010 15:31:00	0
47089		13/07/2010 05:06:00	10
47089		27/07/2010 05:16:00	14

Super Waldar :ccool:
Merci bien, je vais tester çà.

Pour info, j'ai trouvé la solution ci-dessous entre temps.
Elle a le mérite de fonctionner même si elle est beaucoup moins esthétique :cry:

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
59
DECLARE
 
    -- Stocker l'identifiant de l'historique
    var_histId state_history.hist_id%type;
 
    -- Parcourir l'historique de chaque ligne de commande
    CURSOR cur_iterOrderIds
    IS
        SELECT DISTINCT order_id
          FROM state_history;
 
    -- Stocker l'identifiant de la ligne de commande courante
    rec_currentOrderId cur_iterOrderIds%ROWTYPE;
 
    -- Parcourir l'ensemble des états pour une ligne de commande donnée
    CURSOR cur_iterOrderStates (orderId_in NUMBER)
    IS 
        SELECT DISTINCT hist_state
          FROM state_history
         WHERE order_id = orderId_in;
 
    -- Stocker l'état courant
    rec_currentState cur_iterOrderStates%ROWTYPE;
 
BEGIN
    -- Parcourir l'historique de chaque ligne de commande
    FOR rec_currentOrderId IN cur_iterOrderIds
    LOOP
 
        dbms_output.put_line(rec_currentOrderId.order_id);
 
        -- Pour chaque ligne de commande, parcourir les états AT de l'historique
        FOR rec_currentState IN cur_iterOrderStates(rec_currentOrderId.order_id)
        LOOP
 
            -- Sélectionner le plus ancien pour un état donnée
            SELECT hist_id into var_histId
              FROM state_history
            WHERE order_id = rec_currentOrderId.order_id
               AND hist_state = rec_currentState.hist_state
               AND hist_state_date = (SELECT min(hist_state_date)
                                           FROM state_history
                                          WHERE order_id = rec_currentOrderId.order_id
                                            AND hist_state = rec_currentState.hist_state
               );
 
            -- Supprimer les autres
            DELETE FROM state_history
             WHERE order_id = rec_currentOrderId.order_id
               AND hist_state = rec_currentState.hist_state
               AND hist_id != var_histId;
 
        END LOOP;
 
        commit;
 
    END LOOP;
 
END;