Discussion :

[Herman]

Hello à tous,

J'ai besoin de votre avis pour de l'optimisation de requêtes. J'ai déjà jeté un coup d'oeil à http://sqlpro.developpez.com/cours/optimiser/ mais je ne trouve pas mon bonheur.

Je vous donne tout de même le contexte même si je pense qu'en lisant simplement les requêtes il est facile de comprendre la chose : : une base de joueurs de basket (table JOUEUR), avec une liste d'équipe (table EQUIPE), des matchs d'une équipe contre une autre (table MATCH), et une table avec les tirs tenté/réussi de chaque joueur pendant les matchs (table TIR_PENDANT_MATCH).

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
17
18
19
Table JOUEUR:
JOUEUR_ID
NOM_JOUEUR

Table EQUIPE:
EQUIPE_ID

Table MATCH:
MATCH_ID
EQUIPE_ID_1
EQUIPE_ID_2

Table TIR_PENDANT_MATCH:
TIR_ID
JOUEUR_ID
EQUIPE_ID
MATCH_ID
NOMBRE_POINT_SHOOT_TENTE /* = 1 si lancer franc, =2 si shoot à 2 points, =3 si shoot à 3 points */
TIR_REUSSI_FLAG /* = 1 si shoot réussi, = 0 si shoot raté $/

Requêtes :
1/ Qui sont les joueurs n'ayant jamais marqué ?
2/ Quel joueurs a le plus marqué dans la saison ?
3/ Quel joueur a marqué le plus par match (donc ratio du total des points marqués par nombre de match joué) ?

J'ai mes requêtes de mon côté, mais je pense qu'il y a 10 000 moyens de faire chacune (au niveau des jointures, des sous-req, etc...) et j'aurai aimé avoir votre avis sur ce qui est le plus optimisé :

1/

Code :

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

1
2
3
4
5
SELECT NOM_JOUEUR
FROM JOUEUR a
INNER JOIN TIR_PENDANT_MATCH b
ON a.JOUEUR_ID = b.JOUEUR_ID
HAVING sum(CASE WHEN TIR_REUSSI_FLAG = '0' THEN '0' ELSE '1' END) = '0'

2/

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
SELECT a.JOUEUR_ID, NOM_JOUEUR, POINTS_MARQUE
FROM JOUEUR a
INNER JOIN 
	(SELECT JOUEUR_ID, SUM(NOMBRE_POINT_SHOOT_TENTE) AS POINTS_MARQUE
	FROM TIR_PENDANT_MATCH
	WHERE TIR_REUSSI_FLAG = '1'
	GROUP BY JOUEUR_ID) b
ON a.JOUEUR_ID = b. JOUEUR_ID
WHERE b.POINTS_MARQUE = (SELECT MAX(sub.POINTS_MARQUE) 
						FROM
							(SELECT JOUEUR_ID, SUM(NOMBRE_POINT_SHOOT_TENTE) AS POINTS_MARQUE
							FROM TIR_PENDANT_MATCH
							WHERE TIR_REUSSI_FLAG = '1'
							GROUP BY JOUEUR_ID) sub
						)

- Il doit y avoir moyen de faire quelque chose avec un RANK() OVER (PARTITION BY JOUEUR_ID ORDER BY SUM(NOMBRE_POINT_SHOOT_TENTE)) AS Partition_point
- Si il y a 2 joueurs ex aequo, je crois que ça ne prend pas le cas en compte

3/

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
SELECT a.JOUEUR_ID,NOM_JOUEUR, MOYENNE_POINT
FROM JOUEUR a
INNER JOIN 
	(SELECT JOUEUR_ID 
	, SUM(NOMBRE_POINT_SHOOT_TENTE*TIR_REUSSI_FLAG)/count(DISTINCT MATCH_ID) AS MOYENNE_POINT
	FROM TIR_PENDANT_MATCH
	GROUP BY JOUEUR_ID) b
ON a.JOUEUR_ID = b.JOUEUR_ID
WHERE MOYENNE_POINT = (SELECT MAX(MOYENNE_POINT)
					   FROM 
							(SELECT JOUEUR_ID 
							,SUM(NOMBRE_POINT_SHOOT_TENTE*TIR_REUSSI_FLAG)/count(DISTINCT MATCH_ID) AS MOYENNE_POINT
							FROM TIR_PENDANT_MATCH
							GROUP BY JOUEUR_ID) b
						)

- Si il y a 2 joueurs ex aequo, pas sur que ça ressorte les deux

Merci d'avance pour votre aide

[tatayo]

Bonjour,
Pour la première, il y a beaucoup plus simple: un joueur qui n'a jamais marqué, c'est celui qui n'a aucun tir réussi.
Donc en SQL:

Code sql :

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

1
2
3
4
5
6
 
SELECT NOM_JOUEUR
FROM JOUEUR a
left outer JOIN TIR_PENDANT_MATCH b
ON a.JOUEUR_ID = b.JOUEUR_ID and b.TIR_REUSSI_FLAG = '1'
where b.joueur_id is null

Surtout que dans ta requête, tu fais la somme de chaine, puis tu compares le résultat (qui est un numérique) avec une chaine...
D'ailleurs, vu que ton flag ne peut prendre que 2 valeurs, pourquoi ne pas prendre un booléen ?


Tatayo.

[Herman]

Yep, très vrai, je peux update vers un BOOLEAN. Est-ce qu'au niveau performance ça y change quelque chose ? Ou c'est plutôt pour rester cohérent avec le sens du champ ?

Merci pour la proposition d'optimisation de la requête 1, j'étais également parti sur du LEFT pour commencer à optimiser, Thanks!

[Herman]

En fait la 2/ et la 3/ n'étaient pas du tout correctes. Ça donne çà en MySQL :

Requête 2/

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
SELECT a.JOUEUR_ID, NOM_JOUEUR, POINTS_MARQUE
FROM JOUEUR a
INNER JOIN 
	(SELECT JOUEUR_ID, SUM(NOMBRE_POINT_SHOOT_TENTE) as POINTS_MARQUE
	FROM TIR_PENDANT_MATCH
	WHERE TIR_REUSSI_FLAG = '1'
	GROUP BY JOUEUR_ID) b
ON a.JOUEUR_ID = b. JOUEUR_ID
WHERE b.POINTS_MARQUE = (SELECT MAX(sub.POINTS_MARQUE) 
						FROM
							(SELECT JOUEUR_ID, SUM(NOMBRE_POINT_SHOOT_TENTE) as POINTS_MARQUE
							FROM TIR_PENDANT_MATCH
							WHERE TIR_REUSSI_FLAG = '1'
							GROUP BY JOUEUR_ID) sub
						)

Du coup pour celle-ci, ça me parait très étonnant que je doive parcourir 2 fois la table TIR_PENDANT_MATCH :/

Requête 3:

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
SELECT a.JOUEUR_ID,NOM_JOUEUR, MOYENNE_POINT
FROM JOUEUR a
INNER JOIN 
	(SELECT JOUEUR_ID 
	, SUM(NOMBRE_POINT_SHOOT_TENTE*TIR_REUSSI_FLAG)/count(distinct MATCH_ID) as MOYENNE_POINT
	FROM TIR_PENDANT_MATCH
	GROUP BY JOUEUR_ID) b
ON a.JOUEUR_ID = b.JOUEUR_ID
WHERE MOYENNE_POINT = (SELECT MAX(MOYENNE_POINT)
					   FROM 
							(SELECT JOUEUR_ID 
							,SUM(NOMBRE_POINT_SHOOT_TENTE*TIR_REUSSI_FLAG)/count(distinct MATCH_ID) as MOYENNE_POINT
							FROM TIR_PENDANT_MATCH
							GROUP BY JOUEUR_ID) b
						)

J'ai toujours besoin d'aide pour optimiser tout ça

[Herman]

Ça n'inspire personne ?

[aieeeuuuuu]

Bonjour

Vous pouvez en effet utiliser RANK()

Pour la 2/ :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
WITH CTE AS (
    SELECT 
        NOM_JOUEUR
        ,RANK() OVER(ORDER BY SUM(NOMBRE_POINT_SHOOT_TENTE) DESC) AS Rang
    FROM JOUEUR J
    INNER JOIN TIR_PENDANT_MATCH T
        ON T.JOUEUR_ID = J.JOUEUR_ID
    WHERE TIR_REUSSI_FLAG 
    GROUP BY NOM_JOUEUR
)
SELECT NOM_JOUEUR
FROM CTE
WHERE Rang = 1

S'il y a deux exæquo, les deux ressortiront (sinon, vous pouvez utiliser ROW_NUMBER() au lieu de RANK() )


Et donc pour la 3/, il suffit de diviser les points par le nombre de matchs distincts :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
WITH CTE AS (
    SELECT 
        NOM_JOUEUR
        ,RANK() OVER(ORDER BY SUM(NOMBRE_POINT_SHOOT_TENTE) / COUNT(DISTINCT ) DESC) AS Rang
    FROM JOUEUR J
    INNER JOIN TIR_PENDANT_MATCH T
        ON T.JOUEUR_ID = J.JOUEUR_ID
    WHERE TIR_REUSSI_FLAG 
    GROUP BY NOM_JOUEUR
)
SELECT NOM_JOUEUR
FROM CTE
WHERE Rang = 1

Attention, si un joueur a joué un match pendant lequel il n'a pas marqué de point, son ratio sera faussé... mais avec votre modèle, vous ne pouvez pas faire autrement

[StringBuilder]

Euh... Pour la seconde... Je suis con ou un bête ORDER BY + LIMIT suffit ?

Code SQL :

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

1
2
3
4
5
6
7
 
SELECT J.JOUEUR_ID, J.JOUEUR_NOM, COUNT(T.NOMBRE_POINT_SHOOT_TENTE) BUTS
FROM JOUEUR J
INNER JOIN TIR_PENDANT_MATCH T ON T.JOUEUR_ID = J.JOUEUR_ID AND TIR_REUSSI_FLAG = '1'
GROUP BY J.JOUEUR_ID, J.JOUEUR_NOM
ORDER BY BUTS DESC
LIMIT 1,1;

Quant à la troisième :

Code SQL :

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

1
2
3
4
5
6
7
8
9
 
SELECT J.JOUEUR_ID, J.JOUEUR_NOM, SUM(T.NOMBRE_POINT_SHOOT_TENTE) / COUNT(DISTINCT M.MATCH_ID) RATIO
FROM JOUEUR J
LEFT JOIN TIR_PENDANT_MATCH T ON T.JOUEUR_ID = J.JOUEUR_ID AND TIR_REUSSI_FLAG = '1'
INNER JOIN EQUIPE E ON E.EQUIPE_ID = J.EQUIPE_ID
INNER JOIN MATCH M ON M.EQUIPE1_ID = E.EQUIPE_ID OR M.EQUIPE2_ID = E.EQUIPE_ID
GROUP BY J.JOUEUR_ID, J.JOUEUR_NOM
ORDER BY RATIO DESC
LIMIT 1,1;

[al1_24]

La clause LIMIT n'est pas prise en charge par tous les SGBD et Herman n'a pas précisé celui qu'il utilisait...

[StringBuilder]

Il a parlé de MySQL il me semble.

En fait la 2/ et la 3/ n'étaient pas du tout correctes. Ça donne çà en MySQL :