Requête -> tableau croisé et autres besoins statistiques

**Perplexe** · 04/02/2010, 13h34

Bonjour,
Désolé pour le titre approximatif. Si après lecture, vous avez une proposition de titre plus explicite pour que ceux qui ont la même question puissent trouver ce thread avec la fonction recherche, n'hésitez pas.

Soit une table "visites" dans laquelle sont enregistrées les donnés d'emploi des visiteurs :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Table "VISITES":
+-------+----------+---------+----------+-------+
|vis_num|  vis_date|categorie|anciennete|salaire|
+-------+----------+---------+----------+-------+
|      1|2010-01-01|  employe|         0|   1000|
|      2|2010-01-01|  employe|         4|   1200|
|      3|2010-01-01|    cadre|         2|   2200|
|      4|2010-01-01|  employe|         2|   1100|
|      5|2010-01-05| cadresup|         0|   3500|
|      6|2010-01-05|  employe|        10|   1500|
|      7|2010-01-06|    cadre|         0|   2000|
|      8|2010-01-09|  employe|         4|   1250|
+-------+----------+---------+----------+-------

Je cherche à construire une requête qui, sans traitement ultérieur, retourne un résultat de ce genre :

Code :

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

1
2
3
4
5
6
7
8
9
 
+-----------+-------+-------+-------+-------+-------+---------+-------+
| anciennete|tot_vis|employe|emp_vis|  cadre|cad_vis| cadresup|csu_vis|
+-----------+-------+-------+-------+-------+-------+---------+-------+
|          0|      3|   1000|      1|   2000|      1|     3500|      1|
|          2|      2|   1100|      1|   2200|      1|         |       |
|          4|      2|   1225|      2|       |       |         |       |
|         10|      1|   1500|      1|       |       |         |       |
+-----------+-------+-------+-------+-------+-------+---------+-------+

Si la requête en question existe, est-il possible de la compléter pour donner ceci :

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
 
+-----------+-------+-------+-------+-------+-------+---------+-------+
| anciennete|tot_vis|employe|emp_vis|  cadre|cad_vis| cadresup|csu_vis|
+-----------+-------+-------+-------+-------+-------+---------+-------+
|          0|      3|   1000|      1|   2000|      1|     3500|      1|
|          1|       |       |       |       |       |         |       |
|          2|      2|   1100|      1|   2200|      1|         |       |
|          3|       |       |       |       |       |         |       |
|          4|      2|   1225|      2|       |       |         |       |
|          5|       |       |       |       |       |         |       |
|          6|       |       |       |       |       |         |       |
|          7|       |       |       |       |       |         |       |
|          8|       |       |       |       |       |         |       |
|          9|       |       |       |       |       |         |       |
|         10|      1|   1500|      1|       |       |         |       |
+-----------+-------+-------+-------+-------+-------+---------+-------+

Et à partir de là, calculer les écarts moyens entre valeurs pour remplir les cases vides à l'aide d'extrapolations:

Ecart moyen par année d'ancienneté :

EMPLOYES:
De 0 à 2 ans : 100 soient : 100/2 = 50
De 0 à 4 ans : 225 soient : 225/4 = 56,25
De 0 à 10 ans: 500 soient : 500/10 = 50
De 2 à 4 ans : 125 soient : 125/2 = 62,5
De 2 à 10 ans: 400 soient : 400/8 = 50
De 4 à 10 ans: 275 soient : 275/6 = 45,8

Calcul de EMPLOYE @ 1 AN d'ANCIENNETE :

R = (salaire moyen employés @ ancienneté moyenne) + (salaire moyen employés * ((écart ancienneté moyenne -> ancienneté cible) * écart moyen annuel))
R = 1210 + (1210 * ((-3) * 0,0433))
R = 1210 + (1210 * -0,1299)
R = 1210 + -157,179
R = 1052,82 arrondis à 1053

Ecart annuel moyen employés : 52,425
Salaire moyen employés : 1210
Ecart moyen/salaire moyen : 4,33%
Ancienneté moyenne échantillon : 4 ans

CADRES:
De 0 à 2 ans : 200 soient : 200/2 = 100

Ecart annuel moyen cadres : 100
Salaire moyen cadres : 2100
Ecart moyen/salaire moyen : 4,76%
Ancienneté moyenne échantillon : 1 an

CADRES SUPÉRIEURS:
Ecart annuel moyen cadres supérieurs : ? (*)
Salaire moyen cadres supérieurs : 3500
Ecart moyen/salaire moyen : ? (*)
Ancienneté moyenne échantillon : 0 an

(*) en l'absence de valeur, calculs à réaliser sur la base tous visiteurs (cf. ).

TOUS VISITEURS:
Ecart annuel moyen tous_visiteurs : 4,545%
Salaire moyen tous_visiteurs : 1718
Ecart moyen/salaire moyen tous_visiteurs : 78,083
Ancienneté moyenne échantillon tous_visiteurs : 2,75 an

Ecart de salaire moyen tous_visiteurs à salaire moyen cadresup :
a/Ramener le salaire moyen tous_visiteurs à l'ancienneté moyenne cadresup :
R = (((ancienneté moyenne cadresup - ancienneté moyenne tous_visiteurs) * (ecart annuel moyen tous_visiteurs)) * salaire moyen tous_visiteurs) + salaire moyen tous_visiteurs
R = (((0 - 2,75) * 0,0454) * 1718) + 1718
R = ((-2,75 * 0,0454) * 1718) + 1718
R = (-0,119 * 1718) + 1718
R = 1513,429

b/Comparer le salaire moyen cadresup Vs salaire moyen tous_visiteurs, à anciennetés égales :
R1 = Salaire moyen cadresup / salaire moyen tous_visiteurs
R1 = 3500/1513,429
R1 = 2,3126

c/Déduire les chiffres manquants à partir du résultat :
cadresup @ x ans d'ancienneté = (salaire moyen tous_visiteurs à X ans d'ancienneté) * R2 :

Pour enfin aboutir à ceci:

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
 
+-----------+-------+-------+-------+-------+-------+-------+---------+-------+
| anciennete|tot_vis|ts_visi|employe|emp_vis|  cadre|cad_vis| cadresup|csu_vis|
+-----------+-------+-------+-------+-------+-------+-------+---------+-------+
|          0|      3|   1513|   1000|      1|   2000|      1|     3500|      1|
|          1|      0|   1582|   1053|      0|   2100|      0|     3659|      0|
|          2|      2|   1651|   1100|      1|   2200|      1|     3818|      0|
|          3|      0|   1720|   1158|      0|   2282|      0|     3977|      0|
|          4|      2|   1788|   1225|      2|   2373|      0|     4136|      0|
|          5|      0|   1857|   1262|      0|   2464|      0|     4294|      0|
|          6|      0|   1926|   1315|      0|   2555|      0|     4453|      0|
|          7|      0|   1994|   1367|      0|   2646|      0|     4612|      0|
|          8|      0|   2063|   1420|      0|   2737|      0|     4771|      0|
|          9|      0|   2132|   1472|      0|   2827|      0|     4930|      0|
|         10|      1|   2201|   1500|      1|   2918|      0|     5089|      0|
+-----------+-------+-------+-------+-------+-------+-------+---------+-------+

Dernière question : existe-t'il dans MySql des fonctions statistiques avancées spécifiquement faites pour ça (en clair, boucher les trous dans un tableau en fonction des valeurs existantes) ?

Merci d'avance pour vos suggestions.