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 :

Décomposer des périodes


Sujet :

SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre confirmé
    Profil pro
    Administrateur de base de données
    Inscrit en
    Mai 2003
    Messages
    115
    Détails du profil
    Informations personnelles :
    Âge : 44
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Mai 2003
    Messages : 115
    Par défaut Décomposer des périodes
    Bonjour,

    Je cherche la requête sql qui me permettrait de décomposer une période en plusieurs sous-périodes en fonction des évènements périodiques d'une autre table.
    Exemple : décomposition d'un contrat en fonction des périodes de suspensions éventuelles.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
     
    with
    tb_contrats as
    (
    select '000210' matricule, '10/01/2000' deb, '31/12/2035' fin, 'CDI' type_contrat FROM Dual Union all
    select '000340' matricule, '10/07/2003' deb, '31/12/2035' fin, 'CDI' type_contrat FROM Dual Union all
    select '004580' matricule, '01/01/2010' deb, '15/02/2010' fin, 'CDD' type_contrat FROM Dual Union all
    select '004580' matricule, '01/06/2010' deb, '30/05/2011' fin, 'CDD' type_contrat FROM Dual
    ),
    tb_suspensions as
    (
    select '000210' matricule, '18/01/2012' deb, '17/06/2014' fin FROM Dual Union all
    select '004580' matricule, '12/10/2010' deb, '30/11/2010' fin FROM Dual
    )
    Résultat attendu :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     
    MATRICULE DEB        FIN        TYPE_CONTRAT 
    --------- ---------- ---------- ------------ 
    000210    10/01/2000 17/01/2012 CDI          
    000210    18/01/2012 17/06/2014 CDI SUSPENSION
    000210    18/01/2014 31/12/2035 CDI          
    000340    10/07/2003 31/12/2035 CDI          
    004580    01/01/2010 15/02/2010 CDD          
    004580    01/06/2010 11/10/2010 CDD          
    004580    12/10/2010 30/11/2010 CDD SUSPENSION
    004580    01/12/2010 30/05/2011 CDD
    Merci d'avance pour votre aide précieuse.
    Faut que je prenne des vitamines là ...

  2. #2
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 954
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 954
    Par défaut
    Voici une approche, j'espère que c'est correcte et malgré tout performant.
    J'ai considéré qu'il fallait récupérer pour les suspensions le dernier type_contrat en court côté tb_contrats (j'ai rajouté un test pour l'illustrer) :
    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
      WITH tb_contrats AS (
    SELECT '000210' matricule, to_date('10/01/2000','dd/mm/yyyy') deb, to_date('31/12/2035','dd/mm/yyyy') fin, 'CDI' type_contrat FROM Dual Union ALL
    SELECT '000340' matricule, to_date('10/07/2003','dd/mm/yyyy') deb, to_date('31/12/2035','dd/mm/yyyy') fin, 'CDI' type_contrat FROM Dual Union ALL
    SELECT '004580' matricule, to_date('01/01/2010','dd/mm/yyyy') deb, to_date('15/02/2010','dd/mm/yyyy') fin, 'CDD' type_contrat FROM Dual Union ALL
    SELECT '004580' matricule, to_date('01/06/2010','dd/mm/yyyy') deb, to_date('30/05/2011','dd/mm/yyyy') fin, 'CDD' type_contrat FROM Dual Union ALL
    SELECT 'test'   matricule, to_date('01/10/2012','dd/mm/yyyy') deb, to_date('01/11/2012','dd/mm/yyyy') fin, 'CDD' type_contrat FROM Dual Union ALL
    SELECT 'test'   matricule, to_date('02/11/2012','dd/mm/yyyy') deb, to_date('31/12/2012','dd/mm/yyyy') fin, 'CDI' type_contrat FROM Dual
    ),
           tb_suspensions AS (
    SELECT '000210' matricule, to_date('18/01/2012','dd/mm/yyyy') deb, to_date('17/06/2014','dd/mm/yyyy') fin FROM Dual Union ALL
    SELECT '004580' matricule, to_date('12/10/2010','dd/mm/yyyy') deb, to_date('30/11/2010','dd/mm/yyyy') fin FROM Dual Union ALL
    SELECT 'test'   matricule, to_date('08/10/2012','dd/mm/yyyy') deb, to_date('10/10/2012','dd/mm/yyyy') fin FROM Dual Union ALL
    SELECT 'test'   matricule, to_date('24/10/2012','dd/mm/yyyy') deb, to_date('26/10/2012','dd/mm/yyyy') fin FROM Dual Union ALL
    SELECT 'test'   matricule, to_date('07/11/2012','dd/mm/yyyy') deb, to_date('10/11/2012','dd/mm/yyyy') fin FROM Dual Union ALL
    SELECT 'test'   matricule, to_date('13/11/2012','dd/mm/yyyy') deb, to_date('20/11/2012','dd/mm/yyyy') fin FROM Dual
    ),
           t_union as (
    select matricule, deb as dte, type_contrat, 'deb' as type_date, 'contrat' as src
      from tb_contrats
     union all
    select matricule, fin       , type_contrat, 'fin'             , 'contrat'
      from tb_contrats
    union all
    select matricule, deb       , NULL        , 'deb'             , 'suspension'
      from tb_suspensions
     union all
    select matricule, fin       , NULL        , 'fin'             , 'suspension'
      from tb_suspensions
    ),
           res as (
    select matricule
         , dte as date_deb
         , case when type_date = 'fin' and src = 'contrat'
                then NULL 
                else lead(dte) over (partition by matricule order by dte) 
            end as date_fin 
         , (select max(type_contrat) keep (dense_rank first order by dte desc)
              from t_union t2
             where t2.matricule = t.matricule
               and t2.dte <= t.dte
               and t2.src = 'contrat'
               and t2.type_date = 'deb') || case when type_date = 'deb' and src = 'suspension'
                                                 then ' SUSPENSION'
                                                 else NULL
                                             end as type_contrat
      from t_union t
    )     
    select * 
      from res
     where date_fin is not null
    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
    MATRIC DATE_DEB   DATE_FIN   TYPE_CONTRAT
    ------ ---------- ---------- --------------
    000210 10/01/2000 18/01/2012 CDI
    000210 18/01/2012 17/06/2014 CDI SUSPENSION
    000210 17/06/2014 31/12/2035 CDI
    000340 10/07/2003 31/12/2035 CDI
    004580 01/01/2010 15/02/2010 CDD
    004580 01/06/2010 12/10/2010 CDD
    004580 12/10/2010 30/11/2010 CDD SUSPENSION
    004580 30/11/2010 30/05/2011 CDD
    test   01/10/2012 08/10/2012 CDD
    test   08/10/2012 10/10/2012 CDD SUSPENSION
    test   10/10/2012 24/10/2012 CDD
    test   24/10/2012 26/10/2012 CDD SUSPENSION
    test   26/10/2012 01/11/2012 CDD
    test   02/11/2012 07/11/2012 CDI
    test   07/11/2012 10/11/2012 CDI SUSPENSION
    test   10/11/2012 13/11/2012 CDI
    test   13/11/2012 20/11/2012 CDI SUSPENSION
    test   20/11/2012 31/12/2012 CDI
     
    18 rows selected.
    Reste à gérer les date_deb et date_fin à afficher avec des +1 ou -1 car l'exemple de résultat proposé me semble inconsistent.

    En cas de problème de perf, merci de préciser la gestion d'affichage du type_contrat, et dans la mesure du possible filtrer au maximum les tables lors de l'UNION.

  3. #3
    Membre confirmé
    Profil pro
    Administrateur de base de données
    Inscrit en
    Mai 2003
    Messages
    115
    Détails du profil
    Informations personnelles :
    Âge : 44
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Mai 2003
    Messages : 115
    Par défaut
    Bonjour et merci pour la réponse.

    C'est vrai que l'union est couteux car j'ai pas mal de données.
    J'ai souvent ce travail de période à mettre en place dans des requêtes et je souhaitais trouver la solution la plus performante tout de suite pour l'implémenter ailleurs.

    Si vous avez une autre idée d'algo ...

    Merci

  4. #4
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 954
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 954
    Par défaut
    Quelle version d'oracle ? si 11GR2, peut être avec un WITH recursif.
    Sinon fonction pipelined ou vue matérialisée pour stocker le résultat de l'UNION.

    Par ailleurs, quelle est la gestion d'affichage du type_contrat ? est ce qu'un même matricule peut passer de CDD à CDI ?

  5. #5
    Membre confirmé
    Profil pro
    Administrateur de base de données
    Inscrit en
    Mai 2003
    Messages
    115
    Détails du profil
    Informations personnelles :
    Âge : 44
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Mai 2003
    Messages : 115
    Par défaut
    Dans le cas des contrats, il s'agit d'une 10gr2. J'ai d'autres bases en 11gr2.

    Je ne comprends pas ta question au sujet de la "gestion d'affichage".
    Mais oui, le même matricule peux passer de CDD à CDI

  6. #6
    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
    Je ne sais pas si ce sera plus performant que la solution de skuatamad, mais je vous donne une autre piste donc ça vaut peut-être le coup de l'essayer.

    Implémentez ce calendrier, dès lors vous pouvez faire cette requête :
    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
    WITH tb_contrats AS
    (
    SELECT '000210' matricule, to_date('10/01/2000','dd/mm/yyyy') deb, to_date('31/12/2035','dd/mm/yyyy') fin, 'CDI' type_contrat FROM Dual Union ALL
    SELECT '000340' matricule, to_date('10/07/2003','dd/mm/yyyy') deb, to_date('31/12/2035','dd/mm/yyyy') fin, 'CDI' type_contrat FROM Dual Union ALL
    SELECT '004580' matricule, to_date('01/01/2010','dd/mm/yyyy') deb, to_date('15/02/2010','dd/mm/yyyy') fin, 'CDD' type_contrat FROM Dual Union ALL
    SELECT '004580' matricule, to_date('01/06/2010','dd/mm/yyyy') deb, to_date('30/05/2011','dd/mm/yyyy') fin, 'CDD' type_contrat FROM Dual
    )
      ,  tb_suspensions AS 
    (
    SELECT '000210' matricule, to_date('18/01/2012','dd/mm/yyyy') deb, to_date('17/06/2014','dd/mm/yyyy') fin FROM Dual Union ALL
    SELECT '004580' matricule, to_date('12/10/2010','dd/mm/yyyy') deb, to_date('30/11/2010','dd/mm/yyyy') fin FROM Dual
    )
      ,  SR as
    (
        select ctr.matricule, cl1.cal_jour
             , ctr.type_contrat || case when ssp.matricule is not null then ' SUSPENSION' end as type_contrat
             , row_number() over(partition by ctr.matricule                                  order by cl1.cal_jour asc)
             - row_number() over(partition by ctr.matricule, ctr.type_contrat, ssp.matricule order by cl1.cal_jour asc) as grp
          from tb_contrats ctr partition by (ctr.matricule)
    right join calendrier cl1 
            on cl1.cal_jour between ctr.deb and ctr.fin
     left join tb_suspensions ssp
    inner join calendrier cl2
            on cl2.cal_jour between ssp.deb and ssp.fin
            on ssp.matricule = ctr.matricule
           and cl2.cal_jour  = cl1.cal_jour
    )
      select matricule
           , min(cal_jour) as date_deb
           , max(cal_jour) as date_fin
           , type_contrat
        from SR
       where type_contrat is not null
    group by matricule
           , type_contrat
           , grp
    order by matricule     asc
           , min(cal_jour) asc;
     
    MATRICULE DATE_DEB   DATE_FIN   TYPE_CONTRAT 
    --------- ---------- ---------- --------------
    000210    10/01/2000 17/01/2012 CDI          
    000210    18/01/2012 17/06/2014 CDI SUSPENSION
    000210    18/01/2014 31/12/2035 CDI          
    000340    10/07/2003 31/12/2035 CDI          
    004580    01/01/2010 15/02/2010 CDD          
    004580    01/06/2010 11/10/2010 CDD          
    004580    12/10/2010 30/11/2010 CDD SUSPENSION
    004580    01/12/2010 30/05/2011 CDD

  7. #7
    Membre Expert Avatar de pacmann
    Homme Profil pro
    Consulté Oracle
    Inscrit en
    Juin 2004
    Messages
    1 626
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 44
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Consulté Oracle
    Secteur : Distribution

    Informations forums :
    Inscription : Juin 2004
    Messages : 1 626
    Par défaut
    Salut !

    J'ai voulu essayer aussi pour rigoler :

    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
     
    WITH tb_contrats AS
    (
    SELECT '000210' matricule, to_date('10/01/2000','dd/mm/yyyy') deb, to_date('31/12/2035','dd/mm/yyyy') fin, 'CDI' type_contrat FROM Dual Union ALL
    SELECT '000340' matricule, to_date('10/07/2003','dd/mm/yyyy') deb, to_date('31/12/2035','dd/mm/yyyy') fin, 'CDI' type_contrat FROM Dual Union ALL
    SELECT '004580' matricule, to_date('01/01/2010','dd/mm/yyyy') deb, to_date('15/02/2010','dd/mm/yyyy') fin, 'CDD' type_contrat FROM Dual Union ALL
    SELECT '004580' matricule, to_date('01/06/2010','dd/mm/yyyy') deb, to_date('30/05/2011','dd/mm/yyyy') fin, 'CDD' type_contrat FROM Dual
    )
      ,  tb_suspensions AS 
    (
    SELECT '000210' matricule, to_date('18/01/2012','dd/mm/yyyy') deb, to_date('17/06/2014','dd/mm/yyyy') fin FROM Dual Union ALL
    SELECT '004580' matricule, to_date('12/10/2010','dd/mm/yyyy') deb, to_date('30/11/2010','dd/mm/yyyy') fin FROM Dual
    ), t_res_temp as (
    select tc.matricule, tc.deb, tc.fin, type_contrat, ts.deb as debs, ts.fin as fins
         , case when lag(ts.fin, 1) over(partition by tc.matricule order by ts.deb) between tc.deb and tc.fin then 
           lag(ts.fin, 1) over(partition by tc.matricule order by ts.deb) end finsp
         , case when lead(ts.deb, 1) over(partition by tc.matricule order by ts.deb) between tc.deb and tc.fin then 
           lead(ts.deb, 1) over(partition by tc.matricule order by ts.deb)  end debsn 
    from tb_contrats tc
      left outer join tb_suspensions ts on tc.matricule = ts.matricule
        and ts.fin between tc.deb and tc.fin                  
      )
      , t_res as (
    select t_res_temp.*, case when fins is null then 'nosus' 
                              when fins is not null and finsp is not null and debsn is not null then 'midsus'
                              when fins is not null and finsp is null and debsn is not null then 'firstsus'
                              when fins is not null and finsp is not null and debsn is null then 'lastsus'
                              else 'uniqsus' end nseg
    from t_res_temp
    )
    select matricule, case when n = 1 and nseg in ('nosus', 'firstsus', 'uniqsus') then deb
                           when n = 1 and nseg in ('lastsus', 'midsus') then debs                       
                           when n = 2 and nseg in ('firstsus', 'uniqsus') then debs
                           when n = 2 and nseg in ('lastsus', 'midsus') then fins + 1                                               
                           when n = 3 then fins + 1 end debr                       
                    , case when n = 1 and nseg = 'nosus' then fin
                           when n = 1 and nseg in ('firstsus', 'uniqsus') then debs - 1 
                           when n = 1 and nseg in ('lastsus', 'midsus') then fins                       
                           when n = 2 and nseg in ('firstsus', 'uniqsus') then fins
                           when n = 2 and nseg = 'lastsus' then fin
                           when n = 2 and nseg = 'midsus' then debsn - 1                                               
                           when n = 3 and nseg = 'firstsus' then debsn - 1 
                           when n = 3 and nseg = 'uniqsus' then fin end finr 
                    , type_contrat
                    , case when n = 1 and nseg in ('midsus', 'lastsus') or n = 2 and nseg in ('uniqsus', 'firstsus')                        
                      then 'SUSPENSION' end susps
                    --, deb, fin, debs, fins, debsn, finsp, n, nseg
    from t_res
      join (select level n 
            from dual 
             connect by level <= 3) nums on nums.n <= case when nseg in ('nosus') then 1 
                                                           when nseg in ('midsus', 'lastsus') then 2
                                                           when nseg in ('uniqsus', 'firstsus') then 3 end 
    order by matricule, deb, debs, n
    L'idée c'est d'y aller à la jointure externe entre contrats et suspensions, puis faire une jointure avec max 3 lignes (découpage de la période provoquée par la période de suspension).

    Le code est dégueulasse, désolé j'ai un peu galéré, ça fait longtemps que j'ai plus fait de SQL

    Côté performances, il faut voir quel est le besoin précis. S'il s'agit de sortir ce genre de rapport pour un numéro de matricule précis, il faut modifier la requête pour l'intégrer aux bons endroits...

  8. #8
    Membre confirmé
    Profil pro
    Administrateur de base de données
    Inscrit en
    Mai 2003
    Messages
    115
    Détails du profil
    Informations personnelles :
    Âge : 44
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Mai 2003
    Messages : 115
    Par défaut
    Waldar,

    merci pour ta réponse.
    J'étais partie sur cette idée mais perfs désastreuses.

    Pour améliorer les perfs, je pensais à décomposer le contrat en dates uniquement lorsqu'il y a une suspension pendant le contrat. Mais je ne vois pas comment faire. Une idée ?

  9. #9
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 954
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 954
    Par défaut
    Je ne suis pas rentrée dans le détail des solutions proposées par Waldar et pacmann, mais si les perfs sont désastreuse, d'abord analyser pourquoi (plan, trace étendue...)

    Ensuite est ce vraiment nécessaire de travailler sur toutes les années et tous les contrats ?

    Enfin pour ma requête j'utiliserais volontier une vue matérialisée (voir peut être matérialisée sur une IOT) pour les UNION, ce qui permettrait d'avoir une structure plus simple à interroger pour ce besoin.

Discussions similaires

  1. Gérer des périodes
    Par nathou38400 dans le forum Requêtes et SQL.
    Réponses: 2
    Dernier message: 23/10/2008, 15h02
  2. comment créer des périodes ?
    Par cocofighter dans le forum Langage SQL
    Réponses: 3
    Dernier message: 29/01/2008, 18h08
  3. requête sur des périodes délimitées par des dates
    Par gvdmoort dans le forum Requêtes
    Réponses: 1
    Dernier message: 12/10/2007, 12h09
  4. Réponses: 5
    Dernier message: 14/09/2007, 20h44
  5. [Conception] Prix en fonctions des périodes
    Par developpertun dans le forum PHP & Base de données
    Réponses: 7
    Dernier message: 05/04/2006, 12h26

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