[9i] Intervalles de dates

**Magnus** · 27/05/2010, 14h04

Bonjour,

J'essaye depuis plusieurs jours d'écrire LA requête PL/SQL qui va bien pour répondre à la problématique suivante.
Dans une table où je stocke 1 préférence et 1 intervalle, je souhaite les connaître les dates où je change de préférence sachant que je dois toujours récupérer la préférence minimale :

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
 
INSERT INTO t (preference, start_date, end_date)
  VALUES   (996,
            TO_DATE ('12/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
            TO_DATE ('10/12/2009 23:59:59', 'MM/DD/YYYY HH24:MI:SS'));
 
INSERT INTO t (preference, start_date, end_date)
  VALUES   (990,
            TO_DATE ('09/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
            TO_DATE ('07/31/2010 23:59:59', 'MM/DD/YYYY HH24:MI:SS'));
 
INSERT INTO t (preference, start_date, end_date)
  VALUES   (984,
            TO_DATE ('05/08/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
            TO_DATE ('05/12/2010 23:59:59', 'MM/DD/YYYY HH24:MI:SS'));
 
INSERT INTO t (preference, start_date, end_date)
  VALUES   (983,
            TO_DATE ('05/30/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
            TO_DATE ('06/15/2010 23:59:59', 'MM/DD/YYYY HH24:MI:SS'));

Je cherche donc à obtenir :

Code :

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

1
2
3
4
5
6
 
01/09/2009 -- on passe de 996 à 990
08/05/2010 -- on passe de 990 à 984
12/05/2010 -- on passe de 984 à 990 (à nouveau)
30/05/2010 -- on passe de 990 à 983
15/06/2010 -- on passe de 983 à 990 (à nouveau)

J'essaye de m'inspirer de plusieurs articles dont ceux de AskTom mais je n'arrive pas à comprendre la logique des requêtes récursives/analytiques :
http://asktom.oracle.com/pls/asktom/...76000346581356

Pourriez-vous m'aider à écrire ou m'expliquer comment Tom en est arrivé à écrire la requête au début de cet article (j'arrive à comprendre celle d'Anthony un peu plus loin dans ce même article ceci dit) ?

Merci.

C.

PS : je vais être absent la semaine prochaine mais n'hésitez pas à me poser des questions ou faire des propositions et j'y répondrai dès mon retour.

**dgi77** · 27/05/2010, 14h50

Voilà une requête que j'ai basée sur ma compréhension de ton jeu de test.
Je n'ai pas de base sous la main, mais j'espère que ça pourra correspondre à tes besoins :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT t1.start_date, t2.preference, t1.preference FROM t t1, t t2
WHERE t1.start_date > t2.start_date
AND t1.start_date < t2.end_date
UNION
SELECT t1.end_date, t1.preference, t2.preference FROM t t1, t t2
WHERE t1.start_date > t2.start_date
AND t1.end_date < t2.end_date

**Magnus** · 09/06/2010, 10h36

Salut dgi77 et merci pour ta réponse.

T'as proposition est intéressante mais ne satisfait pas exactement ma problématique.
Voici la requête que j'ai réussi à écrire qui semble couvrir tous mes cas de tests :

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
  SELECT   CASE WHEN veille.preference > courante.preference THEN date_courante ELSE date_veille END date_changement,
           a.*,
           veille.preference mini_pref_veille,
           courante.preference mini_pref_courante
    FROM   (SELECT   TO_DATE (:date_debut, 'DD/MM/YYYY') + ROWNUM - 2 date_veille,
                     TO_DATE (:date_debut, 'DD/MM/YYYY') + ROWNUM - 1 date_courante
              FROM   all_objects
             WHERE   ROWNUM <= TO_DATE (:date_fin, 'DD/MM/YYYY') - TO_DATE (:date_debut, 'DD/MM/YYYY') + 1) a,
           t courante,
           t veille
   WHERE   courante.code_generique = :code_generique
       AND veille.code_generique = :code_generique
       AND courante.start_date <= a.date_courante
       AND veille.start_date <= a.date_veille
       AND NVL (courante.end_date, a.date_courante) >= a.date_courante
       AND NVL (veille.end_date, a.date_veille) >= a.date_veille
       AND NOT EXISTS (SELECT   NULL
                         FROM   t mini
                        WHERE   mini.code_generique = :code_generique
                            AND mini.start_date <= a.date_courante
                            AND mini.ROWID <> courante.ROWID
                            AND mini.preference < courante.preference
                            AND NVL (mini.end_date, a.date_courante) >= a.date_courante
                            )
       AND NOT EXISTS (SELECT   NULL
                         FROM   t mini
                        WHERE   mini.code_generique = :code_generique
                            AND mini.start_date <= a.date_veille
                            AND mini.ROWID <> veille.ROWID
                            AND mini.preference < veille.preference
                            AND NVL (mini.end_date, a.date_veille) >= a.date_veille
                            )
       AND courante.rowid <> veille.rowid
ORDER BY   date_courante

J'ai encore quelques tests unitaires à effectuer mais je suis surtout "inquiet" des performances de cette table de calendrier générée à la volée (j'ai trouvé cette idée à plusieurs reprises sur le bloc de Tom KYTE).

Avez-vous déjà utilisé ce genre de tables ?
Les performances sont-elles au RV ?

**Magnus** · 21/06/2010, 09h46

J'ai résolu tout seul ma problématique.
Des fois que mon thread serve un jour à un autre utilisateur, voici la requête finale (qui est un poil plus compliquée que la précédente) :

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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
SELECT   t.session_id,
         t.code_produit,
         t.code_generique,
         t.date_changement,
         t.pref_cour,
         t.pref_prec,
         t.pref_suiv,
         ancien_ac.code_remplacement,
         nouvel_ac.code_remplacement
  FROM   mytable ancien_ac,
         mytable nouvel_ac,
         (  SELECT   courante.session_id,
                     courante.code_produit,
                     courante.code_generique,
                     courante.preference pref_cour,
                     precedente.preference pref_prec,
                     suivante.preference pref_suiv,
                     (SELECT   ROWID
                        FROM   mytable milieu_precedent
                       WHERE   courante.session_id = milieu_precedent.session_id
                           AND courante.code_produit = milieu_precedent.code_produit
                           AND courante.code_generique = milieu_precedent.code_generique
                           AND milieu_precedent.start_date <=
                                 cal.date_courante - (cal.date_courante - cal.date_precedente) / 2
                           AND NVL (milieu_precedent.end_date,
                                    cal.date_courante - (cal.date_courante - cal.date_precedente) / 2 + 1) >
                                 cal.date_courante - (cal.date_courante - cal.date_precedente) / 2
                           AND NOT EXISTS
                                 (SELECT   NULL
                                    FROM   mytable mini
                                   WHERE   mini.session_id = milieu_precedent.session_id
                                       AND mini.code_produit = milieu_precedent.code_produit
                                       AND mini.code_generique = milieu_precedent.code_generique
                                       AND mini.start_date <=
                                             cal.date_courante - (cal.date_courante - cal.date_precedente) / 2
                                       AND mini.ROWID <> milieu_precedent.ROWID
                                       AND mini.preference < milieu_precedent.preference
                                       AND NVL (mini.end_date,
                                                cal.date_courante - (cal.date_courante - cal.date_precedente) / 2 + 1) >
                                             cal.date_courante - (cal.date_courante - cal.date_precedente) / 2))
                       id_milieu_precedent,
                     (SELECT   ROWID
                        FROM   mytable milieu_suivant
                       WHERE   courante.session_id = milieu_suivant.session_id
                           AND courante.code_produit = milieu_suivant.code_produit
                           AND courante.code_generique = milieu_suivant.code_generique
                           AND milieu_suivant.start_date <=
                                 cal.date_courante + (cal.date_suivante - cal.date_courante) / 2
                           AND NVL (milieu_suivant.end_date,
                                    cal.date_courante + (cal.date_suivante - cal.date_courante) / 2 + 1) >
                                 cal.date_courante + (cal.date_suivante - cal.date_courante) / 2
                           AND NOT EXISTS
                                 (SELECT   NULL
                                    FROM   mytable mini
                                   WHERE   mini.session_id = milieu_suivant.session_id
                                       AND mini.code_produit = milieu_suivant.code_produit
                                       AND mini.code_generique = milieu_suivant.code_generique
                                       AND mini.start_date <=
                                             cal.date_courante + (cal.date_suivante - cal.date_courante) / 2
                                       AND mini.ROWID <> milieu_suivant.ROWID
                                       AND mini.preference < milieu_suivant.preference
                                       AND NVL (mini.end_date,
                                                cal.date_courante + (cal.date_suivante - cal.date_courante) / 2 + 1) >
                                             cal.date_courante + (cal.date_suivante - cal.date_courante) / 2))
                       id_milieu_suivant,
                     cal.date_courante date_changement
              FROM   (SELECT   *
                        FROM   (SELECT   session_id,
                                         code_produit,
                                         code_generique,
                                         LAG(date_courante)
                                           OVER (PARTITION BY session_id, code_produit, code_generique
                                                 ORDER BY date_courante)
                                           date_precedente,
                                         date_courante,
                                         LEAD(date_courante)
                                           OVER (PARTITION BY session_id, code_produit, code_generique
                                                 ORDER BY date_courante)
                                           date_suivante
                                  FROM   (SELECT   DISTINCT
                                                   session_id, code_produit, code_generique, start_date date_courante
                                            FROM   mytable
                                           WHERE   session_id = (SELECT   MAX (session_id)
                                                                   FROM   mycalendrier)
                                          UNION
                                          SELECT   DISTINCT
                                                   session_id,
                                                   code_produit,
                                                   code_generique,
                                                   NVL (end_date,
                                                        LEAST (:l_fin_horizon, TO_DATE ('31/12/2999', 'DD/MM/YYYY')))
                                            FROM   mytable
                                           WHERE   session_id = (SELECT   MAX (session_id)
                                                                   FROM   mycalendrier)))
                       WHERE   date_precedente IS NOT NULL
                           AND date_suivante IS NOT NULL) -- mycalendrier
                                                         cal,
                     mytable courante,
                     mytable precedente,
                     mytable suivante
             WHERE   courante.session_id = cal.session_id
                 AND courante.code_produit = cal.code_produit
                 AND courante.code_generique = cal.code_generique
                 AND courante.session_id = precedente.session_id
                 AND courante.code_produit = precedente.code_produit
                 AND courante.code_generique = precedente.code_generique
                 AND courante.session_id = suivante.session_id
                 AND courante.code_produit = suivante.code_produit
                 AND courante.code_generique = suivante.code_generique
                 AND courante.code_generique = :code_generique
                 AND (cal.date_courante = courante.start_date
                   OR (courante.end_date IS NOT NULL
                   AND cal.date_courante = courante.end_date))
                 AND precedente.start_date <= cal.date_precedente
                 AND suivante.start_date <= cal.date_suivante
                 AND NVL (courante.end_date, cal.date_courante + 1) >= cal.date_courante
                 AND NVL (precedente.end_date, cal.date_precedente + 1) >= cal.date_precedente
                 AND NVL (suivante.end_date, cal.date_suivante + 1) >= cal.date_suivante
                 -- recherche d'une éventuelle préférence de la date courante strictement inférieure
                 AND NOT EXISTS (SELECT   NULL
                                   FROM   mytable mini
                                  WHERE   mini.session_id = courante.session_id
                                      AND mini.code_produit = courante.code_produit
                                      AND mini.code_generique = courante.code_generique
                                      AND mini.start_date <= cal.date_courante
                                      AND mini.ROWID <> courante.ROWID
                                      AND mini.preference < courante.preference
                                      AND NVL (mini.end_date, cal.date_courante + 1) >= cal.date_courante)
                 -- recherche d'une éventuelle préférence de la date précédente strictement inférieure
                 AND NOT EXISTS (SELECT   NULL
                                   FROM   mytable mini
                                  WHERE   mini.session_id = precedente.session_id
                                      AND mini.code_produit = precedente.code_produit
                                      AND mini.code_generique = precedente.code_generique
                                      AND mini.start_date <= cal.date_precedente
                                      AND mini.ROWID <> precedente.ROWID
                                      AND mini.preference < precedente.preference
                                      AND NVL (mini.end_date, cal.date_precedente + 1) >= cal.date_precedente)
                 -- recherche d'une éventuelle préférence de la date précédente strictement inférieure
                 AND NOT EXISTS (SELECT   NULL
                                   FROM   mytable mini
                                  WHERE   mini.session_id = suivante.session_id
                                      AND mini.code_produit = suivante.code_produit
                                      AND mini.code_generique = suivante.code_generique
                                      AND mini.start_date <= cal.date_suivante
                                      AND mini.ROWID <> suivante.ROWID
                                      AND mini.preference < suivante.preference
                                      AND NVL (mini.end_date, cal.date_suivante + 1) >= cal.date_suivante)
                 AND (courante.ROWID <> precedente.ROWID
                   OR courante.ROWID <> suivante.ROWID)
                 AND cal.session_id = -- p_session_id
                                     (SELECT   MAX (session_id)
                                        FROM   mycalendrier)
                 AND date_courante BETWEEN :p_debut_horizon AND :p_fin_horizon
          ORDER BY   courante.code_generique DESC, date_courante, date_precedente) t
 WHERE   t.id_milieu_precedent = ancien_ac.ROWID(+)
     AND t.id_milieu_suivant = nouvel_ac.ROWID(+)

[9i] Intervalles de dates

PL/SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager