megarequête , sql et PHP

Version imprimable

Voir 40 message(s) de cette discussion en une page

23/05/2012, 13h47
redoran

megarequête , sql et PHP

Re; j'ai une requête qui affiche dans:
Première ligne du tableau:
extraction de l'année,
ensuite fait la somme des enregistrement de 18 colonnes pour une année XXXX,
ensuite fait la somme des colonnes impaires Total IP et colonnes paires Total P,
ensuite la somme de Total P et Total IP dans Total GL.
deuxième ligne du tableau:
pourcentage de chaque colonne par rapport au Total GL.
exemple :
DSG || cl1 ||cl2 ||cl3.........||cl18 ||Total IP||Total P||Total GL
AAA || 12 ||05 ||20..........||......|| 32 || 05 || 37
% || 32.43 ||13.51 ||.............||......|| 86.49 || 13.51 ||----

premièrement espérant que les développeur du site nous permettent d’insérer des tableaux. merci :mouarf:
ma question:
en matière d’optimisation avec un nombre conséquent d'enregistrements sous forme de chiffres.,
est il souhaitable de faire ce genre de mega requête sur le plan statistique bien sûr?
faire partagé les calcules entre mysql et PHP ?
23/05/2012, 13h49
stealth35

y'a cl1 cl2 cl3 jusqu’à cl18 ?
23/05/2012, 14h50
CinePhil

Les traitements de masse sont en principe plus rapides dans le SGBD qu'en PHP car un SGBD est fait pour ça.

Commence par montrer la requête que tu as faite.

Ceci dit, si tu as la possibilité de changer le modèle de données, ce serait souhaitable !

Re, Requête: c'est une bombe a gaz :calin:

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
 
$cbd->exec("SET lc_time_names = 'fr_FR'");
    $tbord=$cbd->prepare('SELECT MONTHNAME(datedec)AS Mois ,MONTH(datedec)AS nummois, YEAR(datedec) AS an,
    sum(m)as m,sum(f)as f,sum(m1)as m1,sum(f1)as f1,sum(m2)as m2,sum(f2)as f2,sum(m3)as m3,sum(f3)as f3,sum(m4)as m4,sum(f4)as f4,
    sum(m5)as m5,sum(f5)as f5,sum(m6)as m6,sum(f6)as f6,sum(m7)as m7,sum(f7)as f7,sum(m8)as m8,sum(f8)as f8,
    (sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)) As Total_M ,
    (sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)) As Total_F ,
    (sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)) AS Total ,
 ROUND(((sum(m)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pm,
									ROUND(((sum(f)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pf,
									ROUND(((sum(m1)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pm1,
									ROUND(((sum(f1)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pf1,
									ROUND(((sum(m2)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pm2,
									ROUND(((sum(f2)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pf2,
									ROUND(((sum(m3)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pm3,
									ROUND(((sum(f3)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pf3,
									ROUND(((sum(m4)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pm4,
									ROUND(((sum(f4)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pf4,
									ROUND(((sum(m5)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pm5,
									ROUND(((sum(f5)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pf5,
									ROUND(((sum(m6)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pm6,
									ROUND(((sum(f6)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pf6,
									ROUND(((sum(m7)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pm7,
									ROUND(((sum(f7)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pf7,
									ROUND(((sum(m8)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pm8,
									ROUND(((sum(f8)*100)/((sum(m)+sum(m1)+sum(m2)+sum(m3)+sum(m4)+sum(m5)+sum(m6)+sum(m7)+sum(m8)+sum(f)+sum(f1)+sum(f2)+sum(f3)+sum(f4)+sum(f5)+sum(f6)+sum(f7)+sum(f8)))),2) AS pf8
 
 
 
FROM `conteneur` 
WHERE YEAR(datedec)=:an');
 
 
try {
	// On envois la requète
	$tbord->bindParam(':an', $_POST['an'] , PDO::PARAM_INT); 
	$tbord->execute();
	// On récupère en 1er toutes les données
	$datalist = $tbord->fetchAll(PDO::FETCH_ASSOC);
	// libèré la connexion du serveur
	$tbord->closeCursor();	
 
 
	}
catch( Exception $e ){
echo 'Erreur d\'affichage : ', $e->getMessage();
}
/*----------------- fin affichage */

désolé j'ai pas pu l’édenté sous l’éditeur de texte du forum :oops:

23/05/2012, 15h09
stealth35

c'est parce que ta table n'est pas correct mais je te l'avais déjà dis
23/05/2012, 15h44
redoran

Re; Stealth36 oui je me rappel bien est je té répondu que c'est suite a l’étude de l'existant et au exigence du propriétaire.
avec cette composition on évite de faire des PIVOTS et GROUP BY par paramètre A et paramètre B....
23/05/2012, 15h46
stealth35

c'est de la bidouille et du-coup tu vois bien que ça coince, tant que ta base ne sera pas correct ça ne marchera pas
23/05/2012, 16h19
redoran

Citation:

que ça coince

apparemment sa marche très bien , seulement ma question étais sur le plan optimisation est temps d’exécution par rapport a un nombre important d'enregistrements.
23/05/2012, 16h23
stealth35

Ca coince dans le sens ou tu ne pourras simplifié ta requête, et le serveur a mon avis doit en prendre un coup, elle met combien de temps la requête ?
23/05/2012, 16h33
redoran

sur phpMyadmin:
Affichage des lignes 0 - 0 ( 1 total, Traitement en 0.0150 sec) pour 22 enregistrements en local.
23/05/2012, 16h36
stealth35

bah ça va pourquoi tu veux optimiser ça

Juste un petit truc redoran, je me suis amusé à indenter le début de ta requête et tu as un niveau de parenthèses en trop il me semble dans le ROUND :

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
SELECT MONTHNAME(datedec)AS Mois,
	MONTH(datedec)AS nummois, 
	YEAR(datedec) AS an,
    sum(m) as m, sum(f) as f, sum(m1) as m1, sum(f1) as f1, sum(m2) as m2, sum(f2) as f2, sum(m3) as m3, sum(f3) as f3, sum(m4) as m4, sum(f4)as f4,
    sum(m5) as m5, sum(f5) as f5, sum(m6) as m6, sum(f6) as f6, sum(m7) as m7, sum(f7) as f7, sum(m8) as m8, sum(f8)as f8,
    (
		sum(m) + sum(m1) + sum(m2) + sum(m3) + sum(m4) + sum(m5) + sum(m6) + sum(m7) + sum(m8)
	) As Total_M,
    (
		sum(f) + sum(f1) + sum(f2) + sum(f3) + sum(f4) + sum(f5) + sum(f6) + sum(f7) + sum(f8)
	) As Total_F,
    (
		sum(m) + sum(m1) + sum(m2)  +sum(m3) + sum(m4) + sum(m5) + sum(m6) + sum(m7) + sum(m8) 
			+ sum(f) + sum(f1) + sum(f2) + sum(f3) + sum(f4) + sum(f5) + sum(f6) + sum(f7) + sum(f8)
	) AS Total,
	ROUND
	(
		(
			(sum(m)*100) / 
			(
				(
					sum(m) + sum(m1) + sum(m2) + sum(m3) + sum(m4) + sum(m5) + sum(m6) + sum(m7) + sum(m8) + 
						sum(f) + sum(f1) + sum(f2) + sum(f3) + sum(f4) + sum(f5) + sum(f6) + sum(f7) + sum(f8)
				)
			)
		)
	,2) AS pm,

Au passage, remarque qu'elle pique un peu moins les yeux comme ça ta requête non ?

23/05/2012, 16h45
redoran

j'ai besoin d'un avis , j'ai l'habitude de faire ce type de requêtes en monposte [delphi+access] et sa marche bien.
en site web dynamique là je manque d'expérience. :oops:
quelque soit la requête , normalement les SGBDR ont un processus de traitement de requêtes c'est a dire la capacité de transformé la requête écrite en requête optimisée , si c'est le cas est ce que mysql possède ce processus et comment peut ont accédé a cette écriture?
23/05/2012, 16h49
stealth35

en faisant des sous requete ça doit le faire
23/05/2012, 16h52
redoran

Citation:

en faisant des sous requête ça doit le faire

, justement pour cela que je parle de transformation , parce que les SGBDR gèrent différemment les sous requêtes afin de réduire le temps de traitement.
23/05/2012, 16h54
stealth35

t'as moyen d'envoyer la table ça va être dur de tester sans
23/05/2012, 17h01
redoran

1 pièce(s) jointe(s)

la table conteneur Pièce jointe 95636
23/05/2012, 17h06
stealth35

le but final c'est de récupérer uniquement les moyennes ?

j'arrive à ça

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
SELECT 
    MONTHNAME(c.datedec) AS Mois ,
    MONTH(c.datedec)     AS nummois, 
    YEAR(c.datedec)      AS an,
    ROUND(((SUM(c.m)  * 100) / f.Total), 2) AS pm,
    ROUND(((SUM(c.m1) * 100) / f.Total), 2) AS pm1,
    ROUND(((SUM(c.m2) * 100) / f.Total), 2) AS pm2,
    ROUND(((SUM(c.m3) * 100) / f.Total), 2) AS pm3,
    ROUND(((SUM(c.m4) * 100) / f.Total), 2) AS pm4,
    ROUND(((SUM(c.m5) * 100) / f.Total), 2) AS pm5,
    ROUND(((SUM(c.m6) * 100) / f.Total), 2) AS pm6,
    ROUND(((SUM(c.m7) * 100) / f.Total), 2) AS pm7,
    ROUND(((SUM(c.m8) * 100) / f.Total), 2) AS pm8,
    ROUND(((SUM(c.f)  * 100) / f.Total), 2) AS pf,
    ROUND(((SUM(c.f1) * 100) / f.Total), 2) AS pf1,
    ROUND(((SUM(c.f2) * 100) / f.Total), 2) AS pf2,
    ROUND(((SUM(c.f3) * 100) / f.Total), 2) AS pf3,
    ROUND(((SUM(c.f4) * 100) / f.Total), 2) AS pf4,
    ROUND(((SUM(c.f5) * 100) / f.Total), 2) AS pf5,
    ROUND(((SUM(c.f6) * 100) / f.Total), 2) AS pf6,
    ROUND(((SUM(c.f7) * 100) / f.Total), 2) AS pf7,
    ROUND(((SUM(c.f8) * 100) / f.Total), 2) AS pf8
FROM `conteneur` c, (
    SELECT (Total_M + Total_F) AS Total FROM(
        SELECT  
            (m + m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8) AS Total_M ,
            (f + f1 + f2 + f3 + f4 + f5 + f6 + f7 + f8) AS Total_F 
        FROM (
            SELECT
                SUM(m)  AS m,
                SUM(f)  AS f,
                SUM(m1) AS m1,
                SUM(f1) AS f1,
                SUM(m2) AS m2,
                SUM(f2) AS f2,
                SUM(m3) AS m3,
                SUM(f3) AS f3,
                SUM(m4) AS m4,
                SUM(f4) AS f4,
                SUM(m5) AS m5,
                SUM(f5) AS f5,
                SUM(m6) AS m6,
                SUM(f6) AS f6,
                SUM(m7) AS m7,
                SUM(f7) AS f7,
                SUM(m8) AS m8,
                SUM(f8) AS f8
            FROM `conteneur`
            ) c
        ) t 
    ) f

ps :
- sinon ton closeCursor sert a rien, un et fetch suffit
- y'a aucune logique (de nommage) entre Mois, nummois et an

23/05/2012, 23h49
redoran

RE, désolé pour le retard !! faute de connexion...
ta requête affiche seulement les pourcentage et fait:
Affichage des lignes 0 - 0 ( 1 total, Traitement en 0.0017 sec) plus rapide que la mienne :

Affichage des lignes 0 - 0 ( 1 total, Traitement en 0.0150 sec) pour 22 enregistrements en local.
mais ta pas l'affichage des sommes !!!!!

Voir 40 message(s) de cette discussion en une page