Calcul de KPI à partir d'une BDD mysql

Version imprimable

Bonjour à tous,

j'utilise une base données mysql, ma table 'data' est remplie avec des données brutes que je voudrais exploiter pour faire du calcul de KPI et insérer les résultats dans d'autres tables. Le problème c'est que les formules sont très complexes et qui demande plusieurs cmd Select,mais toujours me fait apparaître une erreur de syntaxe, voici un exemple d'une seule formule:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
   select to_chart(startdate,'AAAA-MM-JJ HH:MM:SS') as Time, (1- (s1 + s2 + s3) / (s4 + s5 + s6))*100 as succ_rate
from
(   select MGW, startdate, sum(v1) as s1 ,sum(v2) as s2,sum(v3) as s3,sum(v4) as s4,sum(v5) as s5,sum(v6) as s6
    from
    (   select avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfAal2TermsRej' then valeur else null end) as v1,
        avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfIpTermsRej' then valeur else null end) as v2,
        avg(case when indicateur like '%TdmTermGrp%' and compteur = 'pmNrOfTdmTermsRej' then valeur else null end) as v3,
        avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfAal2TermsReq' then valeur else null end) as v4,
        avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfIpTermsReq' then valeur else null end) as v5,
        avg(case when indicateur like '%TdmTermGrp%' and compteur = 'pmNrOfTdmTermsReq' then valeur else null end) as v6,
        from data where SUBSTR(to_char(startdate,'AAAA-MM-JJ HH:MM:SS'),1,10) like $P{date_debut} and MGW like $P{par_mgw} 
group by startdate, MGW)

Merci d'avance pour votre aide.

Salut hafedhe.

Êtes-vous sûr de ne pas vous être trompé de forum ? La fonction 'to_chart' appartient à Oracle et non à MySql.
Je n'ai fait qu'analyser votre requête, mais je ne l'ai pas testée. Il y a des tas de choses qui ne vont pas avec votre select.

1) le format date+time ??? Pourquoi le mettre sous forme d'une chaîne de caractères ?
Avez-vous sous MySql utilisez le bon type 'datetime' ? Avez-vous eu des problèmes de conversions de date ?

2) êtes vous encore en mysql sous php ? Vous devriez songer à basculer soit vers mysqli, ou mieux vers du PDO.
Vous risquez d'avoir des problèmes d'injection SQL.

3) vu la présentation de votre requête, elle n'est pas lisible.
En la présentant correctement, on remarque les oublies, les erreurs et ce qui est en trop que je mets en rouge.
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 to_chart(startdate,'YYYY-MM-DD HH:MI:SS') as Time, (1 - (s1 + s2 + s3) / (s4 + s5 + s6)) * 100 as succ_rate from ( select MGW, startdate, sum(v1) as s1, sum(v2) as s2, sum(v3) as s3, sum(v4) as s4, sum(v5) as s5, sum(v6) as s6 from ( select avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfAal2TermsRej' then valeur else null end) as v1, avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfIpTermsRej' then valeur else null end) as v2, avg(case when indicateur like '%TdmTermGrp%' and compteur = 'pmNrOfTdmTermsRej' then valeur else null end) as v3, avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfAal2TermsReq' then valeur else null end) as v4, avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfIpTermsReq' then valeur else null end) as v5, avg(case when indicateur like '%TdmTermGrp%' and compteur = 'pmNrOfTdmTermsReq' then valeur else null end) as v6, from data where SUBSTR(to_char(startdate,'YYYY-MM-DD HH:MI:SS'),1,10) like $P{date_debut} and MGW like $P{par_mgw} group by startdate, MGW ) as x ) as y ;
a) J'ai un gros doute ??? Votre requête est faite pour de l'oracle. Je dis cela à cause de la fonction 'to_chart()' qui n'existe pas sous MySql.

Déjà, sous oracle, vous avez un problème de déclarative pour la conversion.
il ne faut pas mettre 'AAAA-MM-JJ HH:MM:SS' mais plutôt 'YYYY-MM-DD HH:MI:SS'.

Voici un [utl=http://www.sqlines.com/oracle-to-mysql/to_char_datetime]lien[/url] qui vous explique la conversion oracle mysql à ce sujet.

Si vous êtes en MySql, il faut utiliser ceci :

Code:

date_format(startdate, , '%Y-%m-%d %H:%i:%s');

b) il y a une virgule en trop.

c) il y a un problème avec le 'group by'. Quand on l'utilise, il faut mettre le nom des colonnes dans le select. Exemple :
Code:

1 2 3 select annee, mois, count(jour) from ... group by annee, mois;
C'est la norme qui impose cela. Inversement, MySql se permet quelques libertés à ce sujet, sauf à partir de la version 5.7.

d) après un from, mysql attend un nom de table. Si vous faites une sous-requête, il faut la nommer en mettant 'as x'.

@+

Merci beaucoup pour la réponse , je suis novice en BDD,en fait mon premier query c'était en oracle ,mais à cause de contraintes rencontrées avec l'outil spagobi , j'ai décidé de passer vers my sql.
donc si j'ai bien compris ,en mysql mon query devrait avoir cette forme:

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 date_format(startdate, , '%Y-%m-%d %H:%i:%s')  as Time,
       (1 - (s1 + s2 + s3) / (s4 + s5 + s6)) * 100 as succ_rate
from (
 
    select MGW,
           startdate,
           sum(v1) as s1,
           sum(v2) as s2,
           sum(v3) as s3,
           sum(v4) as s4,
           sum(v5) as s5,
           sum(v6) as s6
    from (
 
        select avg(case when indicateur like '%Vmgw%'       and compteur = 'pmNrOfAal2TermsRej' then valeur else null end) as v1,
               avg(case when indicateur like '%Vmgw%'       and compteur = 'pmNrOfIpTermsRej'   then valeur else null end) as v2,
               avg(case when indicateur like '%TdmTermGrp%' and compteur = 'pmNrOfTdmTermsRej'  then valeur else null end) as v3,
               avg(case when indicateur like '%Vmgw%'       and compteur = 'pmNrOfAal2TermsReq' then valeur else null end) as v4,
               avg(case when indicateur like '%Vmgw%'       and compteur = 'pmNrOfIpTermsReq'   then valeur else null end) as v5,
               avg(case when indicateur like '%TdmTermGrp%' and compteur = 'pmNrOfTdmTermsReq'  then valeur else null end) as v6,
               from data
               where SUBSTR(date_format(startdate, , '%Y-%m-%d %H:%i:%s'),1,10) like $P{date_debut}
               and                  MGW                                    like $P{par_mgw} 
               group by startdate, MGW
         ) as x
     ) as y
;

Malheureusement je reçoit encore une erreur :

Citation:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from data where SUBSTR(date_format(startdate, '%Y-%m-%d %H:%i:%s'),1,10) like $P' at line 21

01/01/2016, 10h34
Artemus24

Salut hafedhe.

Mettez votre requête entre les balise phpbb [ code ] et entre [/ code ] sans mettre des espaces à l'intérieur des crochets.

A peu de chose prêt oui. Vous avez toujours la virgule après 'v6'.
A vous de la tester sous MySql et de nous donner le compte rendu de l'exécution.

Pour le tester sous php, je vous conseille de basculer en PDO.
Vous risquez d'avoir des problèmes d'injection SQL avec la façon dont vous introduisez les paramètres dans votre requête.

La fonction substr existe bien sous MySql : http://dev.mysql.com/doc/refman/5.7/...unction_substr

Code:

SUBSTR(date_format(startdate, '%Y-%m-%d %H:%i:%s'),1,10) like $P' at line 21

Je ne voie pas trop l'intérêt de transformer votre date+time en chaîne de caractères. Le mieux est de ne rien mettre du tout.
Et comme vous voulez la tester, faites alors ceci :

Code:

date_format(startdate, '%Y-%m-%d %H:%i:%s') = $P

La variable $p doit contenir une date au bon format, par exemple : '2015-12-31 15:12:31'.

Comment est stocké votre colonne startdate ?
Il faut utiliser le type datetime et non une chaîne de caractères.

@+
01/01/2016, 10h48
hafedhe

ma colonne startdate est stocké sous la forme datetime exemple : 2015-12-26 00:00:00

Merci pour votre aide

En suivant vos consigne j'ai apporté le modifications demandés:

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
 
select date_format(startdate, '%Y-%m-%d %H:%i:%s') as Time,
(1 - (s1 + s2 + s3) / (s4 + s5 + s6)) * 100 as succ_rate
from (
 
select MGW,
startdate,
sum(v1) as s1,
sum(v2) as s2,
sum(v3) as s3,
sum(v4) as s4,
sum(v5) as s5,
sum(v6) as s6
from (
 
select avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfAal2TermsRej' then valeur else null end) as v1,
avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfIpTermsRej' then valeur else null end) as v2,
avg(case when indicateur like '%TdmTermGrp%' and compteur = 'pmNrOfTdmTermsRej' then valeur else null end) as v3,
avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfAal2TermsReq' then valeur else null end) as v4,
avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfIpTermsReq' then valeur else null end) as v5,
avg(case when indicateur like '%TdmTermGrp%' and compteur = 'pmNrOfTdmTermsReq' then valeur else null end) as v6
from data
where startdate like $P{date_debut}
and MGW like $P{par_mgw}
group by startdate, MGW
) as x
) as y
;

Maintenant j'ai une autre erreur sous phpmyadmin : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{date_debut} and MGW like $P{par_mgw} group by startdate, MGW ) as x ) as y' at line 22

01/01/2016, 14h41
Artemus24

Salut hafedhe.

Citation:

Envoyé par hafedhe

ma colonne startdate est stocké sous la forme datetime exemple : 2015-12-26 00:00:00

D'accord ! Vous avez utilisez le bon type pour stocker la date et l'heure. Remplacez ceci :

Code:

date_format(startdate, '%Y-%m-%d %H:%i:%s') as Time,

tout simplement par :

Code:

startdate as time,

Citation:

Envoyé par hafedhe

Maintenant j'ai une autre erreur sous phpmyadmin : #1064

Votre requête, vous l'avez récupéré depuis un programme php. "$P{date_debut}" et "$P{par_mgw}", sont des variables provenant de php.
Si vous désirez que votre requête fonctionne sous phpmyadmin, remplacé ces variables par leur valeur respective.

P.S.: n'ouvrez pas un second sujet sur le même thème.

@+