Décomposer des périodes

Version imprimable

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:

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:

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

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:

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:

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.

22/10/2012, 12h29
milka

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
22/10/2012, 13h22
skuatamad

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 ?
22/10/2012, 13h58
milka

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

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:

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

Salut !

J'ai voulu essayer aussi pour rigoler :

Code:

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

24/10/2012, 17h18
milka

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 ?
24/10/2012, 19h37
skuatamad

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.
25/10/2012, 00h08
Waldar

Citation:

Envoyé par milka

J'étais partie sur cette idée mais perfs désastreuses.

Je le suspectais car je n'ai pas fait dans la finesse, je reconstruis jour par jour une ligne par matricule par contrat, forcément ça fait du volume.

Citation:

Envoyé par milka

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 ?

Oui, avec un exists / not exists de la table contrat vers suspension ça limitera déjà l'algorithme.
25/10/2012, 09h28
pacmann

Si tu veux traiter tous les contrats sur toutes les dates, il va falloir lire les deux tables en entier... à partir de là, ma solution consiste "quasiment juste en une jointure", je ne vois pas trop comment on pourrait faire moins en terme d'accès.

Une autre solution à envisager sera un union sur les 2 tables + un tri + l'utilisation de la clause MODEL pour bricoler, pas sûr que ce soit beaucoup mieux cela dit.

Sinon d'accord avec Skuat évidemment : le plan d'exécution serait un bon indice...

Bonjour à tous

J'ai adapté la requête au véritable schéma de données.
et voici son plan d'exécution :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=969084 Card=2095 Bytes=75420)
  SORT (ORDER BY) (Cost=969084 Card=2095 Bytes=75420)
    HASH (GROUP BY) (Cost=969084 Card=2095 Bytes=75420)
      VIEW (Cost=962843 Card=27547155 Bytes=991697580)
        WINDOW (SORT) (Cost=962843 Card=27547155 Bytes=1983395160)
          WINDOW (SORT) (Cost=962843 Card=27547155 Bytes=1983395160)
            HASH JOIN (RIGHT OUTER) (Cost=19190 Card=27547155 Bytes=1983395160)
              VIEW (Cost=17 Card=1162 Bytes=33698)
                MERGE JOIN (Cost=17 Card=1162 Bytes=46480)
                  SORT (JOIN) (Cost=6 Card=35 Bytes=1120)
                    TABLE ACCESS (FULL) OF TB_SUSPENSIONS (TABLE) (Cost=5 Card=35 Bytes=1120)
                  FILTER
                    SORT (JOIN) (Cost=11 Card=13149 Bytes=105192)
                      INDEX (FAST FULL SCAN) OF PK_TB_CALENDRIER (INDEX (UNIQUE)) (Cost=9 Card=13149 Bytes=105192)
              VIEW (Cost=18892 Card=27547155 Bytes=1184527665)
                MERGE JOIN (PARTITION OUTER) (Cost=18892 Card=27547155 Bytes=1101886200)
                  SORT (JOIN) (Cost=11 Card=13149 Bytes=105192)
                    INDEX (FAST FULL SCAN) OF PK_TB_CALENDRIER (INDEX (UNIQUE)) (Cost=9 Card=13149 Bytes=105192)
                  FILTER
                    SORT (PARTITION JOIN) (Cost=12 Card=3373 Bytes=107936)
                      TABLE ACCESS (FULL) OF TB_CONTRATS (TABLE) (Cost=11 Card=3373 Bytes=107936)

25/10/2012, 10h35
milka

Citation:

Envoyé par Waldar

Oui, avec un exists / not exists de la table contrat vers suspension ça limitera déjà l'algorithme.

A quel endroit dois-je mettre la clause "exists" ?
26/10/2012, 15h45
milka

Bonjour,

une petite idée pour les perfs ?

merci et bon week-end

Code:

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
WITH
tb_contrats AS
(
SELECT '000210' matricule, CAST('10/01/2000' AS DATE) deb, CAST('31/12/2035' AS DATE) fin, 'CDI' type_contrat FROM Dual 
Union ALL
SELECT '000340' matricule, CAST('10/07/2003' AS DATE) deb, CAST('31/12/2035' AS DATE) fin, 'CDI' type_contrat FROM Dual 
Union ALL
SELECT '004580' matricule, CAST('01/01/2010' AS DATE) deb, CAST('15/02/2010' AS DATE) fin, 'CDD' type_contrat FROM Dual 
Union ALL
SELECT '004580' matricule, CAST('01/06/2010' AS DATE) deb, CAST('30/05/2011' AS DATE) fin, 'CDD' type_contrat FROM Dual
),
tb_suspensions AS -- périodes intérieures non chevauchantes
(
SELECT '000210' matricule, CAST('18/01/2012' AS DATE) deb, CAST('17/06/2014' AS DATE) fin FROM Dual 
Union ALL
SELECT '004580' matricule, CAST('12/10/2010' AS DATE) deb, CAST('30/11/2010' AS DATE) fin FROM Dual
),
T0 AS
(
SELECT  C.*,
        C.matricule AS MAT, 
		CASE WHEN C.deb > S.deb THEN C.deb ELSE S.deb END AS DEBUT,
        CASE WHEN C.fin < S.fin THEN C.fin ELSE S.fin END AS FIN
FROM    tb_contrats AS C
        LEFT OUTER JOIN tb_suspensions AS S
		      ON C.matricule = S.matricule
			     AND C.deb < S.deb 
				 AND C.fin > S.fin
)
SELECT matricule, deb, DEBUT - 1 as fin , type_contrat
FROM   T0
WHERE  deb < DEBUT
UNION  ALL
SELECT matricule, deb, fin, type_contrat
FROM   T0
WHERE  DEBUT IS NULL
UNION  ALL
SELECT matricule, DEBUT, FIN, type_contrat + ' SUSPENSION'
FROM   T0
WHERE  DEBUT IS NOT NULL
UNION  ALL
SELECT matricule,  day +1, fin, type_contrat 
FROM   T0
WHERE  fin > FIN
ORDER BY 1, 2

OK si les périodes de suspensions sont intérieures aux périodes contractuelles, non chevauchantes et qu'il n'y en n'a qu'une par contrat ou période contractuelles, ce qui est le cas de votre jeu d'essai.

A +