ORACLE, Arbre et période de recouvrement de date

Recherche :

Mot : Pseudo : Filtrer
Bas de page
Auteur	Sujet : ORACLE, Arbre et période de recouvrement de date

Tibar

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,

Publicité

lasnoufle

La seule et unique!

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.

Tibar

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...

lasnoufle

La seule et unique!

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.

Tibar

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...

E-Nyar

I ain't no nice guy after all

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 :

WITH
start_date AS (SELECT TO_DATE('1/1/2007','mm/dd/yyyy') dt FROM dual),
end_date AS (SELECT TO_DATE('12/31/2007','mm/dd/yyyy') dt FROM dual),
counter AS (
SELECT LEVEL l
FROM dual
CONNECT BY LEVEL <= 365)
SELECT
s.dt+c.l dt
FROM counter c,start_date s,end_date e
WHERE s.dt + c.l <= e.dt
AND TO_CHAR(s.dt+c.l,'fmDay') = 'Friday'
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.

Tibar

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,

E-Nyar

I ain't no nice guy after all

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.

Tibar

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.

SQLpro

expert SGBD, SQL, modélisation

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 +

---------------
__________________

Publicité

Tibar

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,

FORUM HardWare.fr

Programmation

SQL/NoSQL

ORACLE, Arbre et période de recouvrement de date

Sujets relatifs
UPDATE JOIN Oracle	Dump d'une base oracle
Date vide validée par schéma XSD	Noméro de jours de l'année a partir d'une date
pb récupération de données sous oracle	Connexion Oracle 11g en oci
Calendrier - Selection de date depuis un pop-up	Problème comparaison de date
Lancer Oracle JPUBLISHER avec ANT (build.xml) ?	[C++] Date limite d'utilisation d'un logiciel
Plus de sujets relatifs à : ORACLE, Arbre et période de recouvrement de date

Page générée en 0.060 secondes