ORACLE, Arbre et période de recouvrement de date

ORACLE, Arbre et période de recouvrement de date - SQL/NoSQL - Programmation

Marsh Posté le 18-03-2011 à 11:03:58    

Bonjour à tous, voici un petit exposé de ma question :  
 
Base de donnée Oracle 9i
 
Objectif : calculer le nombre de jour en période entre le 06/04/2009 et le 31/03/2011  


CESCLAVE DDEB  DFIN
119479  06/04/2009 04/05/2009
119479  28/04/2009 30/06/2009
119479  01/07/2009 09/09/2009
119479  15/08/2009 30/11/2009
119479  02/12/2009 30/01/2010
119479  14/01/2010 28/02/2010
119479  01/02/2010 15/03/2010
119479  17/03/2010 12/04/2010
119479  09/04/2010 03/05/2010
119479  12/04/2010 03/05/2010
119479  13/04/2010 30/06/2010
119479  29/06/2010 30/11/2010
119479  18/10/2010 15/11/2010
119479  01/12/2010 17/01/2011
119479  16/12/2010 22/01/2011
119479  04/01/2011 31/03/2011


1ere idée : sum(DFIN - DDEB) = KO, ça compte des journées en double
2ème idée : créer des périodes de regroupement puis supprimer les périodes recouvertes par ces périodes de recouvrement, par exemple :  
06/04/2009 au 04/05/2009
28/04/2009 au 30/06/2009
 
Ces deux périodes se recouvrent, je pensais créer une période du 06/04/2009 au 30/06/2009, puis supprimer les deux périodes...
Ca fonctionne, sauf que me requête ne regroupe pas bien les périodes, par exemple, pour les périodes :  
 
17/03/2010 12/04/2010
09/04/2010 03/05/2010
12/04/2010 03/05/2010
13/04/2010 30/06/2010
29/06/2010 30/11/2010
18/10/2010 15/11/2010
 
on devrait créer un période du 17/03/2010 au 15/11/2010 puisque toutes les périodes se recouvrent une à une, mais ma requête me crée :  
 
17/03/2010 au 03/05/2010 qui recouvre :  
 
17/03/2010 12/04/2010
09/04/2010 03/05/2010
 
puis  
 
09/04/2010 au 30/06/2010 qui recouvre :  
 
09/04/2010 03/05/2010
13/04/2010 30/06/2010
 
puis  
 
13/04/2010 au 30/11/2010 qui recouvre :  
 
13/04/2010 30/06/2010
29/06/2010 30/11/2010
18/10/2010 15/11/2010
 
Du coup ça ne fonctionne pas (ou du moins pas du premier coup, ce qui veut dire que je devrais lancer mes requêtes un certain nombre de fois, nombre
que je ne connais pas...).
 
Voici ma requête :  


SELECT SR_CES, MIN(SR_DDEB), SR_DFIN
FROM  
     (
     SELECT PR.CESCLAVE AS SR_CES, PR.DDEB AS SR_DDEB, MAX(PR2.DFIN) AS SR_DFIN
     FROM XBA_T_BORNE_PROMO PR
     inner JOIN XBA_T_BORNE_PROMO PR2 ON PR2.CESCLAVE = PR.CESCLAVE
     WHERE 1=1
     AND  
     --le début de période tombe dans une période, la fin est en dehors de la période (sinon on est dans une période recouverte...)
     PR2.DDEB > PR.DDEB AND PR2.DDEB < PR.DFIN AND PR2.DFIN > PR.DFIN
     AND PR.CESCLAVE = 119479
     GROUP BY PR.CESCLAVE, PR.DDEB
     )
GROUP BY SR_CES, SR_DFIN;


La dernière idée que je vois, c'est l'utilisation des arbres (c'est un peu le cas en fait), sauf qu'au lieu d'avoir une égalité, il me faudrait
un between, mais je n'ai jamais utilisé ce type de requête, est ce que quelqu'un pourrait m'aider ?
 
Merci,

Reply

Marsh Posté le 18-03-2011 à 11:03:58   

Reply

Marsh Posté le 18-03-2011 à 12:47:54    

Salut,
 
Fait vite fait à l'arrache, un truc comme ca ne marcherait pas?
 

select count(*)
from  
  (select t1.possible_date,  
           case when (select count(*) from XBA_T_BORNE_PROMO where DDEB<=t1.possible_date and DFIN>=t1.possible_date)=0 then 0 else 1 as dans_une_periode
   from
     (select to_date('06/04/2009','dd-mon-yyyy')+rownum-1 possible_date
      from all_objects
      where rownum <= to_date('31/03/2011','dd-mon-yyyy')-to_date('06/04/2009','dd-mon-yyyy')+1
     ) t1
  ) t2
where dans_une_periode = 1;


Pas testé donc syntaxe pas garantie, et pas 100% sur que le case marche... La requete interne (t1) récupère toutes les dates entre ta date de début et ta date de fin, la requete du milieu (t2) checke si chaque date tombe dans une période ou pas, la requete externe compte celles qui tombent dans une période.


---------------
C'était vraiment très intéressant.
Reply

Marsh Posté le 18-03-2011 à 14:01:16    

Salut,
 
Merci d'avoir regardé, par contre je ne comprends pas tout... La requête ne me renvoit qu'un resultat, je me suis peut être mal exprimé dans mes exemples, mais j'ai des périodes différentes par CESCLAVE, par exemple :  
 


484779 05/04/2011 02/05/2011
484779 01/06/2011 30/06/2011
484803 21/10/2009 11/11/2009
485040 26/05/2009 29/06/2009
485040 11/11/2010 15/11/2010
485040 16/11/2010 22/11/2010
485040 17/12/2010 27/12/2010
485043 23/09/2009 19/10/2009
485043 24/09/2009 19/10/2009
485044 11/11/2010 15/11/2010


 
Ce qui est original, c'est que ta requête me retourne exactement le même resultat que ça :  
 


SELECT to_date('31/03/2011','dd/mm/yyyy') - to_date('06/04/2009','dd/mm/yyyy')+ 1  
FROM dual


 
Je pense que ça veut dire que toutes les journées entre le 06/04/2009 et le 31/03/2011 sont dans une période...
Peut-être qu'en comprenant un peu mieux ta syntaxe, je pourrai la faire évoluer vers un resultat par CESCLAVE, mais la je t'avoue que je ne vois pas trop comment faire...
 
Merci beaucoup,
 
PS : j'ai effectué 3 corrections dans les to_date, le format n'était pas bon, je remets donc ta requête avec ma correction...
 


SELECT COUNT(*)
FROM    
       (
       SELECT t1.possible_date,  
              CASE WHEN (SELECT COUNT(*) FROM  XBA_T_BORNE_PROMO WHERE DDEB<=t1.possible_date AND DFIN>=t1.possible_date)=0 THEN 0 ELSE 1 END AS dans_une_periode
       FROM  
            (
            SELECT to_date('06/04/2009','dd/mm/yyyy')+ROWNUM-1 possible_date
            FROM all_objects
            WHERE ROWNUM <= to_date('31/03/2011','dd/mm/yyyy')-to_date('06/04/2009','dd/mm/yyyy')+1
       ) t1
) t2
WHERE dans_une_periode = 1;


 
PS2 : je ne comprends pas trop comment ça fonctionne le ROWNUM dans ta requête...

Reply

Marsh Posté le 18-03-2011 à 15:27:46    

Re,
 
Oui ca ne renvoie qu'un résultat: le nombre de jours de la "période" totale qui tombe dans les périodes que tu as dans ta table. (c'est ce que j'avais compris que tu voulais). Effectivement si ca renvoie tous les jours, vérifies si les périodes de ta table recouvrent toute la période globale.
 
Si tu comprends pas trop, essaies d'exécuter les requetes incrémentalement.
 
ROWNUM c'est juste une variable "réservée" qui numérote les lignes renvoyées. La requete se fait sur la table all_objects - mais son contenu n'a pas d'importance, tout ce qui importe c'est qu'il y ait plus de lignes dedans que de dates dans ta période. Par ex si tu fais juste:

SELECT ROWNUM
FROM all_objects


Ca va te renvoyer: 1, 2, 3, 4, 5, etc.
 
Maintenant, si tu ajoutes un nombre à une date sous Oracle, ca ajoute le nombre de jours correspondant au nombre. Donc en faisant:

SELECT to_date('06/04/2009','dd/mm/yyyy')+ROWNUM-1 possible_date
FROM all_objects


Ca va te renvoyer: le premier jour de ta période, le second, le troisième, etc. Ensuite, la clause where limite le nombre de jours retournés pour ne pas dépasser la fin de ta période:

SELECT to_date('06/04/2009','dd/mm/yyyy')+ROWNUM-1 possible_date
FROM all_objects
WHERE ROWNUM <= to_date('31/03/2011','dd/mm/yyyy')-to_date('06/04/2009','dd/mm/yyyy')+1


Donc le tout va te donner une table intermédiaire qui liste toutes les dates de ta période globale.  
 
Ensuite, la requete t2 prend chaque date et regarde si elle tombe à l'intérieur d'au moins une période décrite dans ta table de périodes. La requete "rajoute" une colonne qui sera mise à 0 si la date n'est dans aucune période ou à 1 si elle est dans au moins une période.
 
Ensuite c'est facile, la requete "extérieure" prend tout ca et ne compte que les dates qui ont 1 dans la colonne.


---------------
C'était vraiment très intéressant.
Reply

Marsh Posté le 21-03-2011 à 12:37:57    

Salut,
 
Merci pour ces explications, voici ce que ça donne en faisant le calcul par article... Je me suis bien inspiré de ta requête, j'ai un peu trafiqué pour obtenir un "calendrier" par article, et j'ai rentré les bons paramètres de date de début et de date de fin, et voila le resultat :  
 


SELECT XBA.CARTICLE AS ART, COUNT(DISTINCT(XBA.DDEB_ANALYSE_MNC + CPT - 1)) AS NB_JOUR_PROMO
FROM XBA_TST_APPRO_AUTO XBA
inner JOIN  
      (
      SELECT ROWNUM AS CPT
      FROM XBA_TST_APPRO_AUTO
      WHERE ROWNUM <= to_date(SYSDATE, 'DD/MM/YYYY') -  
            (
            SELECT to_date(MIN(XBA2.DDEB_ANALYSE_MNC), 'DD/MM/YYYY')  
            FROM XBA_TST_APPRO_AUTO XBA2
            )
      ) ON 1 = 1
INNER JOIN XBA_T_BORNE_PROMO PRO ON PRO.CESCLAVE = XBA.CARTICLE AND (XBA.DDEB_ANALYSE_MNC + CPT - 1) >= PRO.DDEB AND (XBA.DDEB_ANALYSE_MNC + CPT - 1) <= PRO.DFIN
WHERE to_date(XBA.DDEB_ANALYSE_MNC + CPT - 1, 'DD/MM/YYYY') <= to_date(SYSDATE, 'DD/MM/YYYY')
GROUP BY XBA.CARTICLE
ORDER BY XBA.CARTICLE


 
La première jointure génère un calendrier qui commence à la plus petite date de début d'analyse (calculé avant et stocké dans ma table temporaire). Ce n'est pas optimisé parce que ça me donne un rownum max qui peut être très important si un article possède une date d'analyse très antérieure aux autres, mais ça ne peut pas arriver, je vais donc laisser comme ça, de plus les critères de la requête principale empêchent d'aller trop loin...  
La deuxième jointure permet de ne garder que les dates qui sont en période de promo, ce qui m'intéresse ici...  
Et la requête principale me fait un regroupement par article en comptant le nombre distinct de date du calendrier qui se trouvent en promo...
 
Bilan : 10,5 secondes pour 1400 articles... C'est le temps qu'il me fallait pour 1 article en utilisant les curseurs...  
 
Encore merci pour l'idée du pseudo calendrier...

Reply

Marsh Posté le 23-03-2011 à 12:30:07    

Il y a plus élegant que le rownum pour générer un calendrier en jouant sur le level / connect by.

 

Je n'ai pas de base Oracle sous le coude, mais de mémoire :
Select {date fin} - level + 1
from dual
connect by level <= {date fin - date debut} + 1

 

un exemple d'utilisation de cette méthode (qui n'est pas de moi =>  source ) donnant tous les vendredis entre 2 dates, facilement dérivables pour le cas qui nous intéresse  :

 
Code :
  1. WITH
  2. start_date AS (SELECT TO_DATE('1/1/2007','mm/dd/yyyy') dt FROM dual),
  3. end_date   AS (SELECT TO_DATE('12/31/2007','mm/dd/yyyy') dt FROM dual),
  4. counter AS (
  5.   SELECT LEVEL l
  6.   FROM dual
  7.   CONNECT BY LEVEL <= 365)
  8. SELECT
  9. s.dt+c.l dt
  10. FROM counter c,start_date s,end_date e
  11. WHERE s.dt + c.l <= e.dt
  12. AND TO_CHAR(s.dt+c.l,'fmDay') = 'Friday'
  13. AND s.dt+c.l < SYSDATE


Message édité par E-Nyar le 23-03-2011 à 12:35:48

---------------
Don't fuck me, I'm anonymous.
Reply

Marsh Posté le 23-03-2011 à 15:11:47    

Salut,
 
J'aurai aussi préféré cette solution, par contre j'ai un problème sur ma base, que je ne sais pas contourner :  


SELECT LEVEL
FROM dual
CONNECT BY LEVEL <= 365


 
Cette requête ne me sort que 100 lignes... J'ai un peu cherché, j'ai vu pas mal d'exemples du SELECT LEVEL FROM DUAL CONNECT BY LEVEL < XXX, avec des exemples allant jusqu'à 10000, mais chez moi ça reste bloqué à 100...
 
Je suis aussi tombé sur un message qui indiquait que ce type de requête était très couteux, apparemment l'accès à la table DUAL pénalise beaucoup...
 
La solution préconisée était de créer une table contenant un champ I allant de 1 à N et de faire une jointure dessus...
 
Si quelqu'un peut me dire pourquoi mon SELECT LEVEL FROM DUAL bloque à 100, ça m'intéresse...
 
Merci,

Reply

Marsh Posté le 23-03-2011 à 17:06:41    

La table "dual" n'existe pas en tant que tel. C'est un table virtuelle (table du schema SYS avec 1 seule colonne nommée dummy, vide) qui permet de manipuler les objets Oracle sans avoir de table physique.
Pour ce qui est du message limité à 100 et problème de ressouces, je dirais à vue de nez que ça vient du requêteur (Toad je suppose) et pas de la base, ou alors des droits utilisateurs qui limite le nombre de résultat d'une requête à 100.


Message édité par E-Nyar le 23-03-2011 à 17:08:02

---------------
Don't fuck me, I'm anonymous.
Reply

Marsh Posté le 23-03-2011 à 17:41:04    

Salut,
 
Merci pour la réponse... Je travaille avec PL/SQL Développer, je ne sais pas si ça a un lien avec ma limite des 100...  
 
Je vais creuser un peu, sinon je genererai une table qui va de 1 à 1000, ça me suffira.

Reply

Marsh Posté le 23-03-2011 à 17:53:04    

Commencez par utiliser un opérateur de packing d'intervalle comme ceux que j'ai donné sous forme de requête SQL dans cette étude : http://blog.developpez.com/sqlpro/ [...] -en-sql-1/
Dès lors vous n'aurez plus qu'a faire une SUM de la différence entre debut en fin en jour.
 
A +


---------------
__________________
Reply

Marsh Posté le 23-03-2011 à 17:53:04   

Reply

Marsh Posté le 23-03-2011 à 19:20:07    

Bonsoir,
 
Tout d'abord, merci pour ce cours...  
 
J'étais parti sur la solution 3 en plusieurs étapes (c'est à dire commencer par créer les périodes qui en recouvrent totalement d'autres, puis supprimer les périodes recouvertes, puis enfin calculer la somme(ddebut - dfin), mais ma requête de génération des périodes les plus larges ne fonctionnait pas, et je comprends pourquoi en voyant la syntaxe de la T0 dans la solution 3)...
 
Je vais essayer de voir ce que je peux adapter à Oracle, je pense que je vais devoir me limiter aux requêtes 1, 2 ou 3, ce qui me fait un peu peur quand je sais que j'ai environ 70000 périodes distinctes pour environ 30000 articles (soit environ 2 articles par période), mais que je dois faire ce calcul pour 3 types de période différents (saison, rupture, promotion)...
 
Ca me rassure un peu de voir que l'idée du regroupement de période n'était pas mauvaise en soit...
 
Je ne sais pas trop vers quelle solution m'orienter... Est-ce que l'idée dont on discute sur ce topic (la table qui permet de parcourir toutes les dates possibles) est vraiment mauvaise ?
 
Merci,

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed