Créer un tableau dynamique complexe

ebouilleur · 30-07-2012 14:30:32

Bonjour,

Je dois créer un tableau dynamique via des informations de ma bdd, mais je ne sais pas trop comment m'y prendre pour faire sa proprement.
Je vous montre ça, ca vous permettra de bien comprendre.

le tableau finalement que je souhaiterai :
tableau

Ma base de donnée qui est de la forme :

CREATE TABLE IF NOT EXISTS `stats_clos` (

  `nb_intervention` int(10) unsigned NOT NULL,

  `nom_tech` varchar(50) NOT NULL,

  `equipe` varchar(150) NOT NULL,

  `priorite` smallint(5) unsigned NOT NULL,

  `jour` date NOT NULL,

  KEY `nb_intervention` (`nb_intervention`,`priorite`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Comment faire pour avoir un tableau comme ci-dessus, avec un code propre (pas 50 requetes sql j'entend). (Pour info, un user peut être dans plusieurs équipes)

Voici le "début" de code qui ne sert pas a grand chose ... :

            <h1>Clôture par site, par techniciens et par priorité</h1>

            <?php   

            $sql1="SELECT * FROM stats_clos WHERE jour='2012-07-06' ORDER BY equipe DESC";     

            $req1 = mysql_query($sql1) or die('Erreur SQL stats_svsi_clos : <br />'.$sql1.'<br>'.mysql_error());                   

            while($array_res = mysql_fetch_array($req1)){                            

                $equipe = $array_res['equipe'];

            }                

            ?>             

            <table style="text-align:center">

              <tr>

                <td style="width:100px" class="cellule_red"><b>Groupe d'affectation</b></td>

                <td style="width:100px" class="cellule_red"><b>Technicien</b></td>

                <td style="width:100px" class="cellule_red"><b>Urgente</b></td>

                <td style="width:100px" class="cellule_red"><b>Express</b></td>

                <td style="width:100px" class="cellule_red"><b>Courte</b></td>

                <td style="width:100px" class="cellule_red"><b>Moyenne</b></td>

                <td style="width:100px" class="cellule_red"><b>Longue</b></td>

                <td style="width:100px" class="cellule_red"><b>Total</b></td>

              </tr>

            </table>

Je ne sais pas trop si je dois traiter toutes les infos dans mon while avant mon tableau ou dans la tableau (je pense que ca doit etre fait dans la tableau sinon ca ne sera pas propre)
un gros coup de main svp?
merci

Dernière modification par ebouilleur (30-07-2012 14:33:24)

Jc · 30-07-2012 23:59:42

Bonjour,

Un revenant!
Alors oui, c'est plus propre de peupler ton tableau avec une seule boucle à partir de ton resultset. Il te faut donc une seule requête.
Une question ta table stats n'a pas de clé primaire? donc pas d'id de log?
Je suis un peu pris jusqu'à la fin du mois, j'essaye de te faire cela au plus tôt. Mais on est quand même dans un cas où les vues indexées ont leur légitimité et dans un cas où mysql devrait montrer ses limites. Interessant en tout cas à tester pour voir en myisam les performances de retour. Tu as combien de lignes sur ta table?

++

Dernière modification par Jc (31-07-2012 00:00:32)

Maljuna Kris · 31-07-2012 00:28:55

Saluton,
Penser éventuellement à l'option WITH ROLLUP du GROUP BY qui fera presque tout le travail.

ebouilleur · 31-07-2012 07:54:22

Salut,

Oui je reviens de vacances ^^ (et j'ai pas mal de boulot, j'ai meme pas le temps de travailler pour moi )
non je n'ai pas mis de clé id, mais je peux en rajouter s'il le faut.
En fait le contenu de cette table est est "résumé" d'une autre bcp plus grosse dont j'en extrait ce qui m'intéresse. La table est minuscule pour le moment, mais va gagner environ 50 enregistrements par jour.
je ne connais pas WITH ROLLUP, mais je vais y jetter un oeil
Merci

ebouilleur · 31-07-2012 14:05:01

OK, donc si j'ai bien compris, le ROLLUP me permet de faire les calcules des lignes Total de mon tableau, en faisant une requete du genre :

SELECT nb_intervention, nom_tech, equipe, priorite, SUM( nb_intervention )

FROM stats_svsi_clos

WHERE jour = '2012-7-30'

GROUP BY equipe, nom_tech, priorite

WITH ROLLUP

a priori ça fonctionne bien

J'essaye deja dans un 1er temsp d'afficher les lignes pour une meme equipe, sans total ni rien, et je bloque, je n'obtient pas ce que je voudrais... est ce que je dois bien faire qqchose du genre :

SELECT s1.nb_intervention, s2.nb_intervention, s3.nb_intervention, s4.nb_intervention, s1.nom_tech, s1.equipe

FROM stats_svsi_clos AS s1

LEFT JOIN stats_svsi_clos AS s2 ON (s1.jour=s2.jour)

LEFT JOIN stats_svsi_clos AS s3 ON (s1.jour=s3.jour)

LEFT JOIN stats_svsi_clos AS s4 ON (s1.jour=s4.jour)

WHERE s1.priorite='1' AND s2.priorite='2' AND s3.priorite='3' AND s4.priorite='4' AND s1.jour='2012-7-30' AND s1.equipe='Paris' 

GROUP BY s1.equipe, s1.nom_tech

Dernière modification par ebouilleur (31-07-2012 16:03:29)

Jc · 01-08-2012 07:14:00

Bonjour,

En fait, tu n'es pas sur la bonne voie. Pour construire la bonne requête du premier coup, il faut d'abord que tu tiennes compte de sa signature (conforme au tableau) puis de l'effet du rollup. En gros il te faut faire un group by equipe (tu n'as pas le choix pour celle-ci) et puis sur chaque colonne pour laquelle tu souhaites qu'un total soit calculé automatiquement.
Ensuite tu ne peux pas te permettre d'avoir un retour de type NULL sur une ligne pour une colonne aggrégée car sinon ton cela va flinguer tes SUM(). Donc évite un LEFT JOIN comme tu l'as fait.
Il te faut donc pour éviter les valeurs NULL dans tes calculs d'aggrégation, que dans chacune de tes requêtes dérivées il y ait un pré calcul qui te retourne la valeur 0 en l'absence d'information pour chaque colonne de manière à ne pas avoir d'effet NULL sur tes jointures.

L'idée est là:

SELECT t0.equipe, t0.nom_tech, t1.calc AS urgent, t2.calc AS express, t3.calc AS courte, t4.calc AS moyenne, t5.calc AS longue, t6.calc as total FROM

stats_clos AS t0 INNER JOIN 

(SELECT equipe, nom_tech, SUM(nb_intervention) AS calc FROM stats_clos WHERE priorite=1 GROUP BY equipe,nom_tech,nb_intervention ORDER BY equipe) AS t1 ON (t0.equipe=t1.equipe AND t0.nom_tech=t1.nom_tech)

INNER JOIN

(SELECT equipe, nom_tech, SUM(nb_intervention) AS calc FROM stats_clos WHERE priorite=2 GROUP BY equipe,nom_tech,nb_intervention ORDER BY equipe) AS t2 ON (t0.equipe=t2.equipe AND t0.nom_tech=t2.nom_tech)

INNER JOIN

(SELECT equipe, nom_tech, SUM(nb_intervention) AS calc FROM stats_clos WHERE priorite=3 GROUP BY equipe,nom_tech,nb_intervention ORDER BY equipe) AS t3 ON (t0.equipe=t3.equipe AND t0.nom_tech=t3.nom_tech)

INNER JOIN

(SELECT equipe, nom_tech, SUM(nb_intervention) AS calc FROM stats_clos WHERE priorite=4 GROUP BY equipe,nom_tech,nb_intervention ORDER BY equipe) AS t4 ON (t0.equipe=t4.equipe AND t0.nom_tech=t4.nom_tech)

INNER JOIN

(SELECT equipe, nom_tech, SUM(nb_intervention) AS calc FROM stats_clos WHERE priorite=5 GROUP BY equipe,nom_tech,nb_intervention ORDER BY equipe) AS t5 ON (t0.equipe=t5.equipe AND t0.nom_tech=t5.nom_tech)

INNER JOIN

(SELECT equipe, nom_tech, SUM(nb_intervention) AS calc FROM stats_clos GROUP BY equipe,nom_tech,nb_intervention ORDER BY equipe) AS t6 ON (t0.equipe=t6.equipe AND t0.nom_tech=t6.nom_tech)

GROUP BY t0.equipe, t0.nom_tech, urgent, express , courte, moyenne, longue, total WITH ROLLUP

Donc le problème niveau perf, ce sont les jointures sur des VARCHAR, pour lesquelles tu es un peu beaucoup victime de la dénormalisation de ta table. En gros pour les perfs, cette requête devrait être une vue dénormalisée de tes tables. Car construire une requête sur un modèle dénormalisé c'est tout sauf performant sur un moteur transactionnel. Pour cela que le fait d'être sur MyISAM, me rends curieux sur les perfs...

Attention car il va falloir très certainement que tu modifies encore cette requête par rapport justement aux valeurs NULL.

Note: j'ai rajouté nom_tech dans l'aggregation pour être conforme à la norme SQL.

Dernière modification par Jc (01-08-2012 14:00:21)

ebouilleur · 01-08-2012 15:23:31

Salut,

Tu as modifié quoi a 14h? juste le LEFT JOIN qui est devenu INNER JOIN ou autre chose?

Sinon je suis dessus depuis 14h, il y avait des truc a corrigé car ta requete (tel quelle) me sortait 500 lignes (alors que j'ai en gros 50 enregistrements ^^

Bon le mieux que j'ai pour le moment c'est avec ca :

SELECT t0.equipe, t0.nom_tech, t1.calc AS urgent, t2.calc AS express, t3.calc AS courte, t4.calc AS moyenne, t5.calc AS longue, t6.calc as total FROM

stats_svsi_clos AS t0 INNER JOIN

(SELECT equipe, nom_tech, SUM(nb_intervention) AS calc FROM stats_svsi_clos WHERE jour='2012-7-30' AND priorite=1 GROUP BY equipe,nom_tech,nb_intervention ORDER BY equipe) AS t1 ON (t0.equipe=t1.equipe AND t0.nom_tech=t1.nom_tech)

INNER JOIN

(SELECT equipe, nom_tech, SUM(nb_intervention) AS calc FROM stats_svsi_clos WHERE jour='2012-7-30' AND priorite=2 GROUP BY equipe,nom_tech,nb_intervention ORDER BY equipe) AS t2 ON (t0.equipe=t2.equipe AND t0.nom_tech=t2.nom_tech)

INNER JOIN

(SELECT equipe, nom_tech, SUM(nb_intervention) AS calc FROM stats_svsi_clos WHERE jour='2012-7-30' AND priorite=3 GROUP BY equipe,nom_tech,nb_intervention ORDER BY equipe) AS t3 ON (t0.equipe=t3.equipe AND t0.nom_tech=t3.nom_tech)

INNER JOIN

(SELECT equipe, nom_tech, SUM(nb_intervention) AS calc FROM stats_svsi_clos WHERE jour='2012-7-30' AND priorite=4 GROUP BY equipe,nom_tech,nb_intervention ORDER BY equipe) AS t4 ON (t0.equipe=t4.equipe AND t0.nom_tech=t4.nom_tech)

INNER JOIN

(SELECT equipe, nom_tech, SUM(nb_intervention) AS calc FROM stats_svsi_clos WHERE jour='2012-7-30' AND priorite=5 GROUP BY equipe,nom_tech,nb_intervention ORDER BY equipe) AS t5 ON (t0.equipe=t5.equipe AND t0.nom_tech=t5.nom_tech)

INNER JOIN

(SELECT equipe, nom_tech, SUM(nb_intervention) AS calc FROM stats_svsi_clos WHERE jour='2012-7-30' GROUP BY equipe,nom_tech ORDER BY equipe) AS t6 ON (t0.equipe=t6.equipe AND t0.nom_tech=t6.nom_tech)

WHERE t0.jour='2012-7-30' 

GROUP BY t0.equipe, t0.nom_tech, urgent, express , courte, moyenne, longue, total WITH ROLLUP

J'ai je crois juste supprimé le "nb_intervention" du GROUP BY de la reque t6
MAIS je n'ai le résultat que pour 1 seul tech sur 1 seul site...
Alors que chaque requête lancé indépendamment me donne bien pour tous les tech et tous les sites, en image :

Dernière modification par ebouilleur (01-08-2012 15:25:29)

Jc · 01-08-2012 16:27:39

Bonjour,

Merci pour ton retour, je pense avoir saisi mon erreur (difficile de faire cela à la volée de tête sans jeu de test).

Après réflexion je pense que pour sortir le tableau en l'état on est un peu victime de ton choix de format de table.

SELECT equipe, nom_tech, SUM(nb_intervention) AS calc FROM stats_svsi_clos WHERE jour='2012-7-30' GROUP BY equipe,nom_tech,nb_intervention WITH ROLLUP ORDER BY equipe,nom_tech,nb_intervention

 

est bien mais oblige à retravailler la sortie pour ton tableau, car on aura les priorités en ligne et non en colonne.

A méditer.

Jc · 02-08-2012 01:00:08

Oups j'oubliais, retire le ORDER BY c'est incompatible avec le GROUP BY avec l'option ROLLUP.

ebouilleur · 07-08-2012 08:07:27

Je crois que je vais essayer de changer ma table pour que le traitement soit plus simple...

Jc · 18-08-2012 13:13:16

Bonjour,

En fait ma requête initiale est bonne, la seule chose c'est qu'elle nécessite beaucoup plus de filtrage que ce que j'ai fait. Mais tu as raison, sur un moteur OLTP travailler sur des structures normalisées pour sortir des stats est toujours la meilleure option.

++

PHP|Débutant :: Forums

Advertisement

#1 30-07-2012 14:30:32

Créer un tableau dynamique complexe

#2 30-07-2012 23:59:42

Re : Créer un tableau dynamique complexe

#3 31-07-2012 00:28:55

Re : Créer un tableau dynamique complexe

#4 31-07-2012 07:54:22

Re : Créer un tableau dynamique complexe

#5 31-07-2012 14:05:01

Re : Créer un tableau dynamique complexe

#6 01-08-2012 07:14:00

Re : Créer un tableau dynamique complexe

#7 01-08-2012 15:23:31

Re : Créer un tableau dynamique complexe

#8 01-08-2012 16:27:39

Re : Créer un tableau dynamique complexe

#9 02-08-2012 01:00:08

Re : Créer un tableau dynamique complexe

#10 07-08-2012 08:07:27

Re : Créer un tableau dynamique complexe

#11 18-08-2012 13:13:16

Re : Créer un tableau dynamique complexe

Pied de page des forums