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
|
# view tmp_ecarts_types
cursor_creation_tmp_ecarts_types = connexion_creation.cursor()
cursor_creation_tmp_ecarts_types.execute('''DROP VIEW IF EXISTS tmp_ecarts_types''')
cursor_creation_tmp_ecarts_types.execute('''CREATE VIEW tmp_ecarts_types AS SELECT a.reference, a.intitule
AS contrat, b.periode, b.variance, round(sqrt(b.variance), 2) AS ecart_type
FROM clients a , (SELECT m.reference, m.periode, m.phase1, m.phase2, m.phase3,
round(cast(pow(abs(m.moyenne - m.Taux1),2)/3 AS real)) AS valeur1,
round(cast(pow(abs(m.moyenne - m.Taux2),2)/3 AS real)) AS valeur2,
round(cast(pow(abs(m.moyenne - m.Taux3),2)/3 AS real)) AS valeur3,
round(pow(abs(m.moyenne - m.Taux1),2)/3
+ pow(abs(m.moyenne - m.Taux2),2)/3
+ pow(abs(m.moyenne - m.Taux3),2)/3,2) AS variance
FROM (SELECT reference, periode, E_phase1 AS phase1, E_phase2 AS phase2, E_phase3 AS phase3, totalisateur AS total,
round(cast(E_phase1 AS float)/(totalisateur)*100, 2) AS Taux1,
round(cast(E_phase2 AS float)/(totalisateur)*100, 2) AS Taux2,
round(cast(E_phase3 AS float)/(totalisateur)*100, 2) AS Taux3,
round(CASE WHEN reference <>"" THEN
(round(cast(E_phase1 AS float)/(totalisateur)*100)
+ round(cast(E_phase2 AS float)/(totalisateur)*100)
+ round(cast(E_phase3 AS float)/(totalisateur)*100))/3 ELSE 0 END,2) AS moyenne
FROM tmp_ecarts_phases
ORDER BY reference, periode) AS m
ORDER BY reference, periode) AS b
WHERE a.reference = b.reference
ORDER BY a.reference, b.periode ''') |
Partager