Projeter sur 12 mois les données d'une table

**Dendrite** · 24/04/2012, 12h38

Bonjour,

J'ai la problématique suivante, pour passer d'une table à une vue.
La table salaire comporte des tuples (id/dep/prime/montant/debut/fin)
id étant l'id du salarié, dep son département dans l'entreprise, la prime le libellé de la prime, montant,début et fin étant intuitifs...

A partir de cette table, j'aimerais faire la vue annuelle (en sachant que chaque mois, le salarié peut changer de département et de montant) mais l'unicité d'un tuple se ferait sur la base 1 seul id, 1 seule prime (libellé), puis le dep de chaque mois, le montant de chaque mois...

La requête suivante fonctionne mais... est monstrueusement lente à l'éxécution.
Voyez-vous une autre façon de procéder ?
Ou bien dois-je forcément en passer par une boucle PHP pour que l'éxécution soit rapide, ce qui m'interdirait l'usage d'une vue ?

D'avance, merci de vos conseils d'experts !

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
create 
	or replace 
	view `v_salaire_2012` 
	as 
 
	select ind.nom,ind.prenom,salaire.id,salaire.prime,
		case when ("2012-01-31"  between salaire01.debut and salaire01.fin) then salaire01.lib else "X" end as lib_01,
		sum(case when ("2012-01-31"  between salaire.debut and salaire.fin) then salaire.montant else 0 end) as montant_01,
		case when ("2012-02-29"  between salaire02.debut and salaire02.fin) then salaire02.lib else "X" end as lib_02,
		sum(case when ("2012-02-28"  between salaire.debut and salaire.fin) then salaire.montant else 0 end) as montant_02,
		case when ("2012-03-31"  between salaire03.debut and salaire03.fin) then salaire03.lib else "X" end as lib_03,
		sum(case when ("2012-03-31"  between salaire.debut and salaire.fin ) then salaire.montant else 0 end) as montant_03,
		case when ("2012-04-30"  between salaire04.debut and salaire04.fin) then salaire04.lib else "X" end as lib_04,
		sum(case when ("2012-04-30"  between salaire.debut and salaire.fin ) then salaire.montant else 0 end) as montant_04,
		case when ("2012-05-31"  between salaire05.debut and salaire05.fin) then salaire05.lib else "X" end as lib_05,
		sum(case when ("2012-05-31"  between salaire.debut and salaire.fin  ) then salaire.montant else 0 end) as montant_05,
		case when ("2012-06-30"  between salaire06.debut and salaire06.fin) then salaire06.lib else "X" end as lib_06,
		sum(case when ("2012-06-30"  between salaire.debut and salaire.fin ) then salaire.montant else 0 end) as montant_06,
		case when ("2012-07-31"  between salaire07.debut and salaire07.fin) then salaire07.lib else "X" end as lib_07,
		sum(case when ("2012-07-31"  between salaire.debut and salaire.fin  ) then salaire.montant else 0 end) as montant_07,
		case when ("2012-08-31"  between salaire08.debut and salaire08.fin) then salaire08.lib else "X" end as lib_08,
		sum(case when ("2012-08-31"  between salaire.debut and salaire.fin  ) then salaire.montant else 0 end) as montant_08,
		case when ("2012-09-30"  between salaire09.debut and salaire09.fin) then salaire09.lib else "X" end as lib_09,
		sum(case when ("2012-09-30"  between salaire.debut and salaire.fin ) then salaire.montant else 0 end) as montant_09,
		case when ("2012-10-31"  between salaire10.debut and salaire10.fin) then salaire10.lib else "X" end as lib_10,
		sum(case when ("2012-10-31"  between salaire.debut and salaire.fin) then salaire.montant else 0 end) as montant_10,
		case when ("2012-11-30"  between salaire11.debut and salaire11.fin) then salaire11.lib else "X" end as lib_11,
		sum(case when ("2012-11-30"  between salaire.debut and salaire.fin) then salaire.montant else 0 end) as montant_11,
		case when ("2012-12-31"  between salaire12.debut and salaire12.fin) then salaire12.lib else "X" end as lib_12,
		sum(case when ("2012-12-31"  between salaire.debut and salaire.fin ) then salaire.montant else 0 end) as montant_12
	from salaire
	inner join ind on ind.id=salaire.id
	inner join salaire as salaire01 on salaire.id=salaire01.id
	inner join salaire as salaire02 on salaire.id=salaire02.id
	inner join salaire as salaire03 on salaire.id=salaire03.id
	inner join salaire as salaire04 on salaire.id=salaire04.id
	inner join salaire as salaire05 on salaire.id=salaire05.id
	inner join salaire as salaire06 on salaire.id=salaire06.id
	inner join salaire as salaire07 on salaire.id=salaire07.id
	inner join salaire as salaire08 on salaire.id=salaire08.id
	inner join salaire as salaire09 on salaire.id=salaire09.id
	inner join salaire as salaire10 on salaire.id=salaire10.id
	inner join salaire as salaire11 on salaire.id=salaire11.id
	inner join salaire as salaire12 on salaire.id=salaire12.id
 
	where "2012-01-31"  between salaire01.debut and salaire01.fin
	and "2012-02-28"  between salaire02.debut and salaire02.fin
	and "2012-03-31"  between salaire03.debut and salaire03.fin
	and "2012-04-30"  between salaire04.debut and salaire04.fin
	and "2012-05-31"  between salaire05.debut and salaire05.fin
	and "2012-06-30"  between salaire06.debut and salaire06.fin
	and "2012-07-31"  between salaire07.debut and salaire07.fin
	and "2012-08-31"  between salaire08.debut and salaire08.fin
	and "2012-09-30"  between salaire09.debut and salaire09.fin
	and "2012-10-31"  between salaire10.debut and salaire10.fin
	and "2012-11-30"  between salaire11.debut and salaire11.fin
	and "2012-12-31"  between salaire12.debut and salaire12.fin
	group by nom,prenom,salaire.id,salaire.prime
	order by salaire.id,field(salaire.prime,"primeA","primeB","primeC");

**al1_24** · 24/04/2012, 13h17

Serait-ce quelque chose comme ça que tu cherches à faire ?

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
SELECT  ind.nom
    ,   ind.prenom
    ,   salaire.id
    ,   salaire.prime
    ,   COALESCE(MAX(salaire01.dep), 'X')    AS dep_01
    ,   COALESCE(MAX(salaire01.lib), 'X')    AS lib_01
    ,   COALESCE(SUM(salaire01.montant), 0)  AS montant_01
    ,   COALESCE(MAX(salaire02.dep), 'X')    AS dep_02
    ,   COALESCE(MAX(salaire02.lib), 'X')    AS lib_02
    ,   COALESCE(SUM(salaire02.montant), 0)  AS montant_02
FROM    ind
    INNER JOIN
        salaire
        ON  ind.id    = salaire.id
    LEFT JOIN
        salaire AS salaire01
        ON  salaire.id      = salaire01.id
        ON  salaire.prime   = salaire01.prime
        AND '2012-01-31' BETWEEN salaire01.debut AND salaire01.fin
    LEFT JOIN
        salaire AS salaire02
        ON  salaire.id      = salaire02.id
        ON  salaire.prime   = salaire02.prime
        AND '2012-02-28'  BETWEEN salaire02.debut AND salaire02.fin
GROUP BY ind.nom
    ,   ind.prenom
    ,   salaire.id
    ,   salaire.prime
ORDER BY salaire.id
    ,   FIELD(salaire.prime, 'primeA', 'primeB', 'primeC')
;

**punkoff** · 24/04/2012, 13h23

bonjour,

plusieurs problème dans votre requête :
- vous faites n jointures qui sont, je pense, inutile (votre lenteur dexecution vient de là)
- vous utilisez mal group by : toute colonne dans la clause SELECT non présente dans la clause group by doit être encadrée par une fonction d'agrégation.

Votre syntaxe avec les sum / case est une bonne idée pour traiter le problème.

du coup, ceci devrait tourner :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SELECT ind.nom,ind.prenom,salaire.id,salaire.prime,
		case when ("2012-01-31"  BETWEEN salaire.debut AND salaire.fin) then salaire.lib else "X" end AS lib_01,
		sum(case when ("2012-01-31"  BETWEEN salaire.debut AND salaire.fin) then salaire.montant else 0 end) AS montant_01,
..........
........
FROM salaire
INNER JOIN ind ON ind.id=salaire.id
where salaire.debut > '2012-01-01' and salaire.fin <= '2012-12-31'
GROUP BY nom,prenom,salaire.id,salaire.prime
ORDER BY salaire.id,FIELD(salaire.prime,"primeA","primeB","primeC");

=> adaptez la condition sur les dates de la clause where, car je ne sais pas si fonctionnellement c'est juste pour votre cas.

**Dendrite** · 25/04/2012, 15h24

Merci de vos réponses.
Finalement, j'ai un peu changé mon fusil d'épaule, et j'ai fini par préférer 1 tuple=1 id + 1 type de prime + 1 dep
En cas de changement de montant, on ne change pas de tuple, mais en cas de changement de dep, on change de tuple.
Cette requête me fera une vue plus fidèle à ce que je cherchais, et gère parfaitement la proratisation des mois (complets ou non).
Sa performance est inférieure à une seconde...

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
SELECT  ind.nom
    ,ind.prenom
    ,salaire.id
    ,salaire.prime
	,salaire.dep
    ,sum(case when ("201201" >= DATE_FORMAT(debut, '%Y%m') and "201201" <= DATE_FORMAT(fin, '%Y%m')) then montant*(DATEDIFF(LEAST(fin,LAST_DAY("2012/01/01")),GREATEST(debut,"2012/01/01")) +1)/(DATEDIFF(LAST_DAY("2012/01/01"),"2012/01/01")+1) else 0 end) as montant_01
    ,sum(case when ("201202" >= DATE_FORMAT(debut, '%Y%m') and "201202" <= DATE_FORMAT(fin, '%Y%m')) then montant*(DATEDIFF(LEAST(fin,LAST_DAY("2012/02/01")),GREATEST(debut,"2012/02/01")) +1)/(DATEDIFF(LAST_DAY("2012/02/01"),"2012/02/01")+1) else 0 end) as montant_02
    ,sum(case when ("201203" >= DATE_FORMAT(debut, '%Y%m') and "201203" <= DATE_FORMAT(fin, '%Y%m')) then montant*(DATEDIFF(LEAST(fin,LAST_DAY("2012/03/01")),GREATEST(debut,"2012/03/01")) +1)/(DATEDIFF(LAST_DAY("2012/03/01"),"2012/03/01")+1) else 0 end) as montant_03
    ,sum(case when ("201204" >= DATE_FORMAT(debut, '%Y%m') and "201204" <= DATE_FORMAT(fin, '%Y%m')) then montant*(DATEDIFF(LEAST(fin,LAST_DAY("2012/04/01")),GREATEST(debut,"2012/04/01")) +1)/(DATEDIFF(LAST_DAY("2012/04/01"),"2012/04/01")+1) else 0 end) as montant_04
    ,sum(case when ("201205" >= DATE_FORMAT(debut, '%Y%m') and "201205" <= DATE_FORMAT(fin, '%Y%m')) then montant*(DATEDIFF(LEAST(fin,LAST_DAY("2012/05/01")),GREATEST(debut,"2012/05/01")) +1)/(DATEDIFF(LAST_DAY("2012/05/01"),"2012/05/01")+1) else 0 end) as montant_05
    ,sum(case when ("201206" >= DATE_FORMAT(debut, '%Y%m') and "201206" <= DATE_FORMAT(fin, '%Y%m')) then montant*(DATEDIFF(LEAST(fin,LAST_DAY("2012/06/01")),GREATEST(debut,"2012/06/01")) +1)/(DATEDIFF(LAST_DAY("2012/06/01"),"2012/06/01")+1) else 0 end) as montant_06
    ,sum(case when ("201207" >= DATE_FORMAT(debut, '%Y%m') and "201207" <= DATE_FORMAT(fin, '%Y%m')) then montant*(DATEDIFF(LEAST(fin,LAST_DAY("2012/07/01")),GREATEST(debut,"2012/07/01")) +1)/(DATEDIFF(LAST_DAY("2012/07/01"),"2012/07/01")+1) else 0 end) as montant_07
    ,sum(case when ("201208" >= DATE_FORMAT(debut, '%Y%m') and "201208" <= DATE_FORMAT(fin, '%Y%m')) then montant*(DATEDIFF(LEAST(fin,LAST_DAY("2012/08/01")),GREATEST(debut,"2012/08/01")) +1)/(DATEDIFF(LAST_DAY("2012/08/01"),"2012/08/01")+1) else 0 end) as montant_08
    ,sum(case when ("201209" >= DATE_FORMAT(debut, '%Y%m') and "201209" <= DATE_FORMAT(fin, '%Y%m')) then montant*(DATEDIFF(LEAST(fin,LAST_DAY("2012/09/01")),GREATEST(debut,"2012/09/01")) +1)/(DATEDIFF(LAST_DAY("2012/09/01"),"2012/09/01")+1) else 0 end) as montant_09
    ,sum(case when ("201210" >= DATE_FORMAT(debut, '%Y%m') and "201210" <= DATE_FORMAT(fin, '%Y%m')) then montant*(DATEDIFF(LEAST(fin,LAST_DAY("2012/10/01")),GREATEST(debut,"2012/10/01")) +1)/(DATEDIFF(LAST_DAY("2012/10/01"),"2012/10/01")+1) else 0 end) as montant_10
    ,sum(case when ("201211" >= DATE_FORMAT(debut, '%Y%m') and "201211" <= DATE_FORMAT(fin, '%Y%m')) then montant*(DATEDIFF(LEAST(fin,LAST_DAY("2012/11/01")),GREATEST(debut,"2012/11/01")) +1)/(DATEDIFF(LAST_DAY("2012/11/01"),"2012/11/01")+1) else 0 end) as montant_11
    ,sum(case when ("201212" >= DATE_FORMAT(debut, '%Y%m') and "201212" <= DATE_FORMAT(fin, '%Y%m')) then montant*(DATEDIFF(LEAST(fin,LAST_DAY("2012/12/01")),GREATEST(debut,"2012/12/01")) +1)/(DATEDIFF(LAST_DAY("2012/12/01"),"2012/12/01")+1) else 0 end) as montant_12
 
FROM    ind
    INNER JOIN
        salaire
        ON  ind.id    = salaire.id
GROUP BY ind.nom
    ,   ind.prenom
    ,   salaire.id
    ,   salaire.prime
    ,salaire.dep
ORDER BY nom,prenom,id,prime

Projeter sur 12 mois les données d'une table

Langage SQL

Discussions similaires

Partager

Partager