IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Requête récursive Oracle 11g


Sujet :

SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre émérite
    Profil pro
    Inscrit en
    Août 2008
    Messages
    861
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 861
    Par défaut Requête récursive Oracle 11g
    Bonjour à tous,

    Je souhaiterais s'il vous plaît avoir vos avis et vos lumières sur une requête assez complexe, du moins pour moi.
    Merci déjà aux personnes qui auront la patience de me lire, je m'aperçois après relecture que c'est assez touffu!

    L'environnement est Oracle 11gR2. Je ne peux pas modifier le modèle de données, une appli de saisie/consultation se base directement dessus (pas de vues intermédiaires), donc je peux seulement ajouter des tables au besoin. (ce point reste peut être négociable avec l'appli si la modif est minime et vraiment bénéfique)

    Je vais tacher d'expliquer brievement la problématique, en espérant être assez clair.
    Je m'excuse de ne pas présenter tel quel le modèle, comme pour à peu près tout le monde en entreprise, ça m'est interdit.
    Ceci dit je crois que c'est pas plus mal pour la compréhension

    Départements :
    t_departement (departement_id, nom_departement, activite_departement, site_departement)

    Projets :
    Un projet appartient à un seul département et peut être composé de sous-projets (auto-référencement).
    On peut en théorie avoir jusqu'à 5 niveaux de sous-projet. Un projet principal a un parent_id null.
    t_projet (projet_id, parent_projet_id, departement_id, reference_projet, code_projet, projet_type1_id, projet_type2_id, projet_type3_id)

    Un projet est d'un certain type (qui va lui donner quelques attributs spécifiques), on a une sorte d'héritage mal modélisé (sur les trois clé étrangères dans t_projet deux seront forcément non renseignées, et un type_projet ne peut être référencé qu'une seule fois :/ )
    t_projet_type1 (projet_type1_id, nom_projet_type1, ...)
    t_projet_type2 (projet_type2_id, nom_projet_type2, ...)
    t_projet_type3 (projet_type3_id, nom_projet_type3, ...)


    Chaque projet_type a 0-n contrats rattachés, qui correspondent à un montant sur une période (qui peut être passée ou future) :
    t_contrat_type1 (contrat_type1_id, projet_type1_id, debut_contrat_type1, fin_contrat_type1, montant_contrat_type1)
    t_contrat_type2 (contrat_type2_id, projet_type2_id, debut_contrat_type2, fin_contrat_type2, montant_contrat_type2)
    t_contrat_type3 (contrat_type3_id, projet_type3_id, debut_contrat_type3, fin_contrat_type3, montant_contrat_type3)


    Chaque projet ou sous projet a un budget sur une période, les périodes pour un projet_id ne peuvent pas se chevaucher :
    t_budget (t_budget_id, projet_id, debut_budget, fin_budget, montant_budget)

    Une table calendrier (simplifiée) :
    t_calendrier (jour)

    On souhaite pouvoir fournir les indicateurs suivants à une date d donnée (présente, passée ou future) :
    1) i1 : Dépense courante d'un projet ou sous-projet : c'est la somme des montant_contrat qui lui sont rattachés et qui sont actifs à cette date (debut_contrat <= d < fin_contrat)
    2) i2 : Budget restant pour un projet : c'est la différence entre le montant_budget (actif à la date d) qui lui est rattaché et la dépense courante. Pour un sous-projet, ce sera le minimum entre son budget restant et celui de ses projets parents.
    3) i3 : Dépense courante d'un projet ou sous-projet, sous projets inclus : c'est la somme des montant_contrat qui lui sont rattachés, ou qui sont rattachés à l'un de ces sous-projet, et qui sont actifs à cette date (debut_contrat <= d < fin_contrat)
    4) i4 : Budget restant pour un projet, sous-projets inclus : c'est la différence entre le montant_budget qui lui est rattaché et la dépense courante de lui et tous ses sous-projets. Pour un sous-projet, ce sera le min entre son budget restant et celui de ses predecesseurs.

    L'utilisateur doit saisir au minimum une date (ou une période, auquel cas on lui renverra la valeur pour chaque jour de cette période). Il peut aussi filtrer sur un pays, un département, un projet, une activité... classique quoi.
    L'outil de restitution utilisé est BO.

    Donc l'idée serait de sortir une (ou plusieurs) vue côté Oracle avec la structure suivante :
    v_indicateurs (projet_id, jour, i1, i2, i3, i4)

    Les volumétries à terme sont de l'ordre de 10 000 projets et 40 000 sous projets. En moyenne 15 contrats par projet.
    Stocker tous les résultats à l'avance dans un modèle type datawarehouse parait compliqué, car les données sont susceptibles de changer tous les jours, il faudrait donc recalculer chaque jour tous les indicateurs pour tous les projets et toutes les dates du calendrier...
    Je penche plutôt pour du calcul à la volée, mais j'ai quelques inquiétudes côté performances.

    Suis-je passé à côté de quelque chose, auriez vous des idées quant à la solution à adopter?
    Voici un début de requête, où i2 est incomplet car on doit pouvoir le comparer avec celui de ces predecesseur et ne garder que le plus petit. Sans parler de i3 et i4. Avez-vous une piste pour ce faire svp? Je me penche sur la récursivité, mais je ne suis pas très à l'aise avec et ne sais pas si j'arriverai à mes fins.
    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
    SELECT 
          p.projet_id,
          cal.jour,
          sum(coalesce(ct1.montant_contrat1, ct2.montant_contrat2, ct3.montant_contrat3,0)) AS i1,
          avg(coalesce(b.montant_budget,0))-sum(coalesce(ct1.montant_contrat1, ct2.montant_contrat2, ct3.montant_contrat3,0)) AS i2
    FROM t_projet p
    CROSS JOIN t_calendrier cal
    LEFT JOIN t_budget b
         ON b.projet_id = p.projet_id
         AND cal.jour BETWEEN b.debut_budget AND b.fin_budget
    LEFT JOIN t_projet_type1 pt1
         JOIN t_contrat_type1 ct1
              ON pt1.projet_type1_id = ct1.projet_type1_id
      ON pt1.projet_type1_id = p.projet_type1_id
      AND cal.jour >= ct1.debut_contrat1 AND cal.jour < ct1.fin_contrat1
    LEFT JOIN t_projet_type2 pt2
         JOIN t_contrat_type2 ct2
              ON pt2.projet_type2_id = ct2.projet_type2_id
      ON pt2.projet_type2_id = p.projet_type2_id
      AND cal.jour >= ct2.debut_contrat2 AND cal.jour < ct2.fin_contrat2
    LEFT JOIN t_projet_type3 pt3
         JOIN t_contrat_type3 ct3
              ON pt3.projet_type3_id = ct3.projet_type3_id
      ON pt3.projet_type3_id = p.projet_type3_id
      AND cal.jour >= ct3.debut_contrat1 AND cal.jour < ct3.fin_contrat1
     
     
    -- Version avec UNION
    SELECT 
          p.projet_id,
          cal.jour,
          sum(coalesce(ct1.montant_contrat1,0)) AS i1,
          avg(coalesce(b.montant_budget,0))-sum(coalesce(ct1.montant_contrat1,0)) AS i2
    FROM t_projet p
    CROSS JOIN t_calendrier cal
    LEFT JOIN t_budget b
         ON b.projet_id = p.projet_id
         AND cal.jour BETWEEN b.debut_budget AND b.fin_budget
    JOIN t_projet_type1 pt1
         ON pt1.projet_type1_id = p.projet_type1_id
    LEFT JOIN t_contrat_type1 ct1
         ON pt1.projet_type1_id = ct1.projet_type1_id
         AND cal.jour >= ct1.debut_contrat1 AND cal.jour < ct1.fin_contrat1
    UNION ALL     
    SELECT 
          p.projet_id,
          cal.jour,
          sum(coalesce(ct2.montant_contrat2,0)) AS i1,
          avg(coalesce(b.montant_budget,0))-sum(coalesce(ct2.montant_contrat2,0)) AS i2
    FROM t_projet p
    CROSS JOIN t_calendrier cal
    LEFT JOIN t_budget b
         ON b.projet_id = p.projet_id
         AND cal.jour BETWEEN b.debut_budget AND b.fin_budget
    JOIN t_projet_type2 pt2
         ON pt2.projet_type2_id = p.projet_type2_id
    LEFT JOIN t_contrat_type2 ct2
         ON pt2.projet_type2_id = ct2.projet_type2_id
         AND cal.jour >= ct2.debut_contrat1 AND cal.jour < ct2.fin_contrat1
    UNION ALL
    SELECT 
          p.projet_id,
          cal.jour,
          sum(coalesce(ct3.montant_contrat3,0)) AS i1,
          avg(coalesce(b.montant_budget,0))-sum(coalesce(ct3.montant_contrat3,0)) AS i2
    FROM t_projet p
    CROSS JOIN t_calendrier cal
    LEFT JOIN t_budget b
         ON b.projet_id = p.projet_id
         AND cal.jour BETWEEN b.debut_budget AND b.fin_budget
    JOIN t_projet_type3 pt3
         ON pt3.projet_type3_id = p.projet_type3_id
    LEFT JOIN t_contrat_type3 ct3
         ON pt3.projet_type3_id = ct3.projet_type3_id
         AND cal.jour >= ct3.debut_contrat1 AND cal.jour < ct3.fin_contrat1
    Si y'a besoin de précisions supplémentaires n'hésitez pas, et merci à toute personne qui saura me donner un coup de main!

  2. #2
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 454
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 454
    Par défaut
    Un petit jeu de données ce serait super !

Discussions similaires

  1. Requête récursive dans oracle
    Par yservant dans le forum SQL
    Réponses: 1
    Dernier message: 31/01/2013, 23h40
  2. Réponses: 1
    Dernier message: 14/12/2012, 09h41
  3. Problème de requête récursive sous Oracle 10
    Par boulgom12 dans le forum Oracle
    Réponses: 1
    Dernier message: 13/12/2006, 09h54
  4. Problème concaténation requête avec ORACLE
    Par kobe dans le forum Bases de données
    Réponses: 2
    Dernier message: 16/08/2005, 11h57
  5. Requête récursive
    Par tirixil dans le forum Bases de données
    Réponses: 3
    Dernier message: 07/03/2005, 16h11

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo