Comment effectuer une itération sur une plage de dates dans PL/SQL

J'ai besoin d'écrire un rapport qui fournit un résumé des totaux sur une table avec des plages de dates pour chaque enregistrement.

table data:
option   start_date   end_date
opt1     6/12/2009    6/19/2009
opt1     6/3/2009     6/13/2009
opt2     6/5/2009     6/6/2009

Ce que je veux sortir est essentiellement ceci:

date       option    count
6/1/2009   opt1      0
6/1/2009   opt2      0
6/2/2009   opt1      0
6/2/2009   opt2      0
6/3/2009   opt1      0
6/3/2009   opt2      1

Je vais avoir un moment difficile de déterminer comment itérer sur une plage de dates. Je suis sûr que c'est un simple curseur qui pourrait être créé pour cela, mais je suis à une perte. De préférence en PL/SQL

Mise à JOUR:

J'ai fini à l'aide de l'exemple ici pour accomplir ce que je voulais faire. Cela crée une fonction qui génère un tableau de dates.

InformationsquelleAutor Adam Carr | 2009-06-12

Vous aurez besoin d'une sorte de calendrier de la boucle à travers une gamme de date. J'ai construit à l'aide de la se connecter par niveau truc. Vous pouvez ensuite rejoindre le calendrier avec vos données (jointure croisée puisque vous voulez une suite, même quand il n'y a pas d'option pour cette journée):

SQL> WITH calendar AS (
  2     SELECT to_date(:begin_date, 'mm/dd/yyyy') + ROWNUM - 1 c_date
  3       FROM dual
  4      CONNECT BY LEVEL <= to_date(:end_date, 'mm/dd/yyyy') 
                             - to_date(:begin_date, 'mm/dd/yyyy') + 1
  5  )
  6  SELECT c_date "date", d_option "option", COUNT(one_day)
  7    FROM (SELECT c.c_date, d.d_option,
  8                  CASE
  9                     WHEN c.c_date BETWEEN d.start_date AND d.end_date THEN
 10                      1
 11                  END one_day
 12             FROM DATA d, calendar c)
 13   GROUP BY c_date, d_option
 14  ORDER BY 1,2;

date        option COUNT(ONE_DAY)
----------- ------ --------------
01/06/2009  opt1                0
01/06/2009  opt2                0
02/06/2009  opt1                0
02/06/2009  opt2                0
03/06/2009  opt1                1
03/06/2009  opt2                0
04/06/2009  opt1                1
04/06/2009  opt2                0
05/06/2009  opt1                1
05/06/2009  opt2                1
06/06/2009  opt1                1
06/06/2009  opt2                1

12 rows selected

Cela fait exactement ce que je voulais... même mieux que l'article que j'ai la référence ci-dessus. Merci!
+1 - votre solution est plus efficace que la mienne ci-dessous avec l'étape supplémentaire de créer de la gauche de la jointure de la table de base. Pas sûr de savoir comment il serait dans le cas où le tableau est indexé.

InformationsquelleAutor Vincent Malgrat

14

Une solution que j'utilise, c'est de convertir la date en un nombre entier de gamme que vous pouvez utiliser dans une boucle for, puis de le convertir à une date à faire des choses avec elle. Vous ne pouvez pas faire toutes les jointures ou quoi que ce soit de cette façon, mais c'est beaucoup plus petite solution que celles déjà posté le:
```
declare
  start_date number;
  end_date number;
  business_date varchar2(8);
begin
  start_date := to_number(to_char(to_date('2013-04-25', 'yyyy-MM-dd'), 'j'));
  end_date := to_number(to_char(to_date('2013-05-31', 'yyyy-MM-dd'), 'j'));
  for cur_r in start_date..end_date loop
    business_date := to_char(to_date(cur_r, 'j'), 'yyyy-MM-dd');
    dbms_output.put_line(business_date);
  end loop;
end;
```
- fonctionne très bien 🙂 a Noter qu'il devrait être: business_date varchar2(10); au lieu de varchar2(8);
- Entreprise date est erronée. Il devrait être varchar2(10). Veuillez mettre à jour pour les autres utilisateurs!
InformationsquelleAutor drakkanraz

Comme un complément à d'autres techniques, d'une façon que je itérer sur les dates est la suivante:

/* List of days for the past year, starting with today at midnight */
SELECT TRUNC(SYSDATE) + 1 - LEVEL AS today,
       TRUNC(SYSDATE) + 2 - LEVEL AS tomorrow
FROM DUAL
CONNECT BY LEVEL <= 365

InformationsquelleAutor Sarah Vessels

3

Ici est une réponse basée sur une réponse ci-dessus:
Il utilise un début et date de fin:

Il répertorie tous les jours de 07/01/2013 à 07/31/2013. Facilement adaptable à n'importe quel intervalle de date.
```
SELECT to_date('07/01/2013', 'mm/dd/yyyy') + LEVEL - 1 AS today
FROM dual
CONNECT BY LEVEL <= to_date('07/31/2013', 'mm/dd/yyyy') - to_date('07/01/2013', 'mm/dd/yyyy') + 1;
```
InformationsquelleAutor bozone

Ce type de requête est mieux traitée si vous avez un deuxième "utilitaire" de la table, que vous pouvez utiliser pour à peu près n'importe quelle requête où vous avez besoin de convertir des plages dans des seaux. L'utilité de la table n'est rien de plus qu'une liste de nombres:

CREATE TABLE Iterator (Counter NUMBER);

COUNTER
-------
      0
      1
      2
      3 
...
    100 (or however many rows you want to include)

SI nous supposons que vous souhaitez afficher les 30 jours, par exemple

SELECT   TO_DATE('6/1/2009', 'MM/DD/YYYY') + i.counter thedate
       , i.My_option
       , count(y.My_option)
    FROM ( SELECT DISTINCT
                  i2.Counter
                , y.My_option
             FROM iterator i2
                , YourTable y
            WHERE i2.Counter < 5
         ) i
           LEFT OUTER JOIN yourtable y 
                           ON  TO_DATE('6/1/2009', 'MM/DD/YYYY') + i.counter 
                               >= y.start_date
                           AND TO_DATE('6/1/2009', 'MM/DD/YYYY') + i.counter 
                               <  y.end_date
                           AND y.My_option = i.My_option
GROUP BY TO_DATE('6/1/2009', 'MM/DD/YYYY') + i.counter
       , i.My_option
ORDER BY 1
       , 2;

L'idée est que vous créez un produit Cartésien entre votre itérateur table et votre table avec la plage, puis de filtrer tous les cas où votre gamme de conditions ne sont pas remplies. Vous pouvez l'utiliser dans de nombreux endroits, et est l'un des meilleurs exemples pourquoi il est préférable de modéliser vos données avec des plages d'opposition à intervalles discrets - parce que vous pouvez toujours convertir facilement à intervalles discrets à l'aide de cette technique.

edit: j'ai vraiment ne devriez pas utiliser ENTRE pour la plage de dates des requêtes - je l'ai changé pour >= <

InformationsquelleAutor Steve Broberg

declare 
v_curr_date  date;
for i in to_number(to_char(p_date_from ,'j')) .. to_number(to_char(p_date_to 
,'j')) loop

 v_curr_date = to_date(to_char(i),'j'); 
 --make any operation on v_curr_date (like insert into table)

end loop;    

end;

InformationsquelleAutor Yasser Zilal

Vous devez vous connecter pour publier un commentaire.