Classement de ligue 1

[lstephan]

Bonjour j aimerai générer un classement (type foot ligue 1) à partir de ma table matchs.

Le structure de la table est la suivante:


round_id (int) numero de la journée de championnat
Match_date (date) date de la rencontre
Matchpart1 (identifiant) equipe locale
Matchpart2 (identifiant) equipe visiteurs

Matchpart1_result (real) score equipe locale
Matchpart2_result (real) score equipe visiteurs

Quelqu'un pourrait il m aider?

D'avance merci

[StringBuilder]

Euh...

1/ C'est souvent qu'un match a pour résultat "1,125-0,66666" ?
2/ Ah, nan, ok, c'est pour dire si le match a pour résultat "1-2", alors tu stockes 1,2... Hmmmm vive les complications quand t'as 10-12... A moins que tu ne stockes 10 + 1/12 ? Ah ouais, dans ce cas... 10,083333333333333333333333333333333, c'est super lisible...
3/ Sauf que le type REAL, c'est un float, donc avec 2-0, ça donne 2... 2 n'est pas égal à 2, mais à 1,99999999998. D coup ça va te faire de très beaux scores genre 1 à 0,000000000010000000000200000000004 ce qui est complètement faux. Je sens que tu vas t'amuser à faire des round dans tous les sens... Tous ça pour... Perdre de la place (lol). Deux tinyint, ça fait 8+8 = 16 bits (soit 2 octets). Un REAL, c'est au minimum 32, voir 64 bits, donc 2 à 4 fois plus gros...
4/ Ah ben non, en relisant, tu veux stocker un INT dans un REAL...

Pour le reste, commence par poster ce que tu as déjà écrit comme requête, on va pas faire tes devoirs non plus...

[lstephan]

Hello,

Je sais je sais ma spécialité n'est pas le developpement MS Sql...mais bonStingbuilder tu l'as deja remarqué...

Je cherche juste à generer un classement et comptais sur quelques pistes simplement.

Mais bon pas grave...

enfin j avais juste réussi a faire cela pour calculer les points par match:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT     TOP (100) PERCENT dbo.jos_joomleague_matches.match_id, dbo.jos_joomleague_clubs.name AS Team, jos_joomleague_clubs_1.name AS Team2, 
                      dbo.jos_joomleague_matches.match_date, dbo.jos_joomleague_matches.matchpart1_result, dbo.jos_joomleague_matches.matchpart2_result, 
                      dbo.jos_joomleague_rounds.name, 
                      CASE WHEN dbo.jos_joomleague_matches.matchpart1_result > dbo.jos_joomleague_matches.matchpart2_result THEN 3 WHEN dbo.jos_joomleague_matches.matchpart1_result
                       = dbo.jos_joomleague_matches.matchpart2_result THEN 1 ELSE 0 END AS ptsclub1HOME, 
                      CASE WHEN dbo.jos_joomleague_matches.matchpart2_result > dbo.jos_joomleague_matches.matchpart1_result THEN 3 WHEN dbo.jos_joomleague_matches.matchpart2_result
                       = dbo.jos_joomleague_matches.matchpart1_result THEN 1 ELSE 0 END AS ptsclub2AWAY
FROM         dbo.jos_joomleague_rounds INNER JOIN
                      dbo.jos_joomleague_clubs AS jos_joomleague_clubs_1 INNER JOIN
                      dbo.jos_joomleague_matches INNER JOIN
                      dbo.jos_joomleague_clubs ON dbo.jos_joomleague_matches.matchpart1 = dbo.jos_joomleague_clubs.id ON 
                      jos_joomleague_clubs_1.id = dbo.jos_joomleague_matches.matchpart2 ON dbo.jos_joomleague_rounds.id = dbo.jos_joomleague_matches.round_id
ORDER BY dbo.jos_joomleague_matches.match_date

Apres je ne sais comment regrouper par equipe et faire la somme des points...

[StringBuilder]

Code :

SELECT top (100) percent ...

A quoi ça sert de préciser un "TOP" si c'est pour récupérer... 100% des lignes ?

J'ai jamais vu des clauses de jointures aussi mal écrites :

Code :

1
2
3
4
5
6
 
SELECT ...
FROM table1 t1
INNER JOIN table2 t2 ON t2.id = t1.ref_id
INNER JOIN table3 t3 ON t3.id = t2.ref_id
...

On fait un ON après chaque INNER JOIN, et non tous les INNER JOIN suivits d'un tas de ON qui s'appliquent sur on ne sait quoi (elle marche d'ailleurs cette syntaxe ?)

Aussi, utilise des alias pour nommer tes tables, là c'est trop illisible ces noms à ralonge...

Sinon, globalement, ta requête à l'air de faire à peu près ce qu'il faut faire.

Il manque une fonction de regroupement (sum() sur les scores) et un regroupement (group by <tous les champs sauf le sum()>)

Et ça devrait commencer à ressembler à quelquechose.

[lstephan]

Merci Stringbuilder,

C'est sur la syntaxe n'est surement pas la meilleure !!

Je vais donc tenter de mettre de l 'ordre dans tout cela en suivant tes conseils. Merci.

Le pire c 'est que la syntaxe fonctionne ( incroyable je sais)

Je vais me pencher sur tout ça.

Merci

[lstephan]

Re,

J'ai essayer de simplifier la requete pour la rendre plus visible:

Code :

1
2
3
4
5
6
SELECT     matchpart1, matchpart2, matchpart1_result, matchpart2_result, 
                      CASE WHEN dbo.jos_joomleague_matches.matchpart1_result > dbo.jos_joomleague_matches.matchpart2_result THEN 3 WHEN dbo.jos_joomleague_matches.matchpart1_result
                       = dbo.jos_joomleague_matches.matchpart2_result THEN 1 ELSE 0 END AS pointdomicile, 
                      CASE WHEN dbo.jos_joomleague_matches.matchpart2_result > dbo.jos_joomleague_matches.matchpart1_result THEN 3 WHEN dbo.jos_joomleague_matches.matchpart2_result
                       = dbo.jos_joomleague_matches.matchpart1_result THEN 1 ELSE 0 END AS pointvisiteur
FROM         dbo.jos_joomleague_matches

Apres je bloque car il faudrait que je fasse la somme des points par equipe

matchpart1 = equipe domicile
matchpart2 = equipe visiteur

Je ne sais si cela parait plus clair

Merci

[StringBuilder]

Le problème : Actuellement, vous avez deux colonnes, alors que vous n'en voulez qu'une.

La solution : UNION en prenant une première fois la première colonne, puis en prenant la seconde colonne.

Voici la requête avec la solution. Essayez de la comprendre, afin de continuer pour ramener les autres informations dont vous avez besoin. Même si en terme de "propreté" du code c'est pas forcément le mieux, en terme de design, je vous invite à mettre cette solution en sous-requête pour récupérer les autres informations (nom de l'équipe, etc.) plutôt que de dupliquer les jointures dans les deux membres du UNION.
Le mieux étant de passer par une vue qui fait le UNION, comme ça on simplifie la requête, et ça reste propre.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SELECT
      matchpart1 id, 
      CASE 
            WHEN dbo.jos_joomleague_matches.matchpart1_result > dbo.jos_joomleague_matches.matchpart2_result THEN 3 
            WHEN dbo.jos_joomleague_matches.matchpart1_result = dbo.jos_joomleague_matches.matchpart2_result THEN 1
            ELSE 0 
      END AS point
FROM         dbo.jos_joomleague_matches
union ALL
SELECT
      matchpart2 id, 
      CASE 
            WHEN dbo.jos_joomleague_matches.matchpart2_result > dbo.jos_joomleague_matches.matchpart1_result THEN 3 
            WHEN dbo.jos_joomleague_matches.matchpart2_result = dbo.jos_joomleague_matches.matchpart1_result THEN 1
            ELSE 0 
      END AS point
FROM         dbo.jos_joomleague_matches

Donc on peut partir sur l'utilisation de la vue suivante :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
CREATE VIEW v_points_equipe
SELECT
      matchpart1 AS equipe_id, 
      sum(CASE 
            WHEN dbo.jos_joomleague_matches.matchpart1_result > dbo.jos_joomleague_matches.matchpart2_result THEN 3 
            WHEN dbo.jos_joomleague_matches.matchpart1_result = dbo.jos_joomleague_matches.matchpart2_result THEN 1
            ELSE 0 
      END) AS points
FROM         dbo.jos_joomleague_matches
GROUP BY matchpart1
union ALL
SELECT
      matchpart2 AS equipe_id, 
      sum(CASE 
            WHEN dbo.jos_joomleague_matches.matchpart2_result > dbo.jos_joomleague_matches.matchpart1_result THEN 3 
            WHEN dbo.jos_joomleague_matches.matchpart2_result = dbo.jos_joomleague_matches.matchpart1_result THEN 1
            ELSE 0 
      END) AS points
FROM         dbo.jos_joomleague_matches
GROUP BY matchpart2

Ensuite il ne vous reste plus qu'à faire des jointures sur cette vue pour obetenir votre résultat très simplement.

[lstephan]

Merci beaucoup,

Je vais essayer de comprendre les requetes.

Je vais les executer sur ma base SQL SERVER.

Un grand merci.

Cela j espere va m aider a generer un classement pour mon site:
http://www.stadebrestois29.eu

[lstephan]

Re,

J ai testé les requetes et notamment celle ci:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
      matchpart1 AS equipe_id, 
      sum(CASE 
            WHEN dbo.jos_joomleague_matches.matchpart1_result > dbo.jos_joomleague_matches.matchpart2_result THEN 3 
            WHEN dbo.jos_joomleague_matches.matchpart1_result = dbo.jos_joomleague_matches.matchpart2_result THEN 1
            ELSE 0 
      END) AS points
FROM         dbo.jos_joomleague_matches
GROUP BY matchpart1
union ALL
SELECT
      matchpart2 AS equipe_id, 
      sum(CASE 
            WHEN dbo.jos_joomleague_matches.matchpart2_result > dbo.jos_joomleague_matches.matchpart1_result THEN 3 
            WHEN dbo.jos_joomleague_matches.matchpart2_result = dbo.jos_joomleague_matches.matchpart1_result THEN 1
            ELSE 0 
      END) AS points
FROM         dbo.jos_joomleague_matches
GROUP BY matchpart2

Mais elle me donne le meme resultat que celle la :

Code :

1
2
3
4
5
6
SELECT     matchpart1 AS equipe_id, 
                      SUM(CASE WHEN dbo.jos_joomleague_matches.matchpart1_result > dbo.jos_joomleague_matches.matchpart2_result THEN 3 
WHEN dbo.jos_joomleague_matches.matchpart1_result= dbo.jos_joomleague_matches.matchpart2_result THEN 1
 ELSE 0 END) AS points
FROM         dbo.jos_joomleague_matches
GROUP BY matchpart1

Je pensais qu'avec Union All cela regrouperait et sommerait sur chaque identifiant equipe.


Cela est normal?

Un grand merci en tout cas

[StringBuilder]

Normalement, la requête avec le UNION ALL (pense bien à mettre le ALL, sinon tu vas perdre des résultats !) tu dois avoir le double de lignes qu'avec l'autre requête.
En effet, ta requête retourne les points marqués par les équipes dans les matches à domicile.
Il faut rajouter aussi les points dans les matches à l'extérieur.

Du coup, je tique sur un truc...

La vue que je t'ai proposée n'est pas bonne : il faut faire la somme en dehors du UNION ALL, sinon tu as les points à domicile et à l'extérieur sur deux lignes...

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
 
CREATE VIEW v_points_equipe
AS
SELECT id equipe_id, sum(points) points
FROM (
SELECT
      matchpart1 id, 
      CASE 
            WHEN dbo.jos_joomleague_matches.matchpart1_result > dbo.jos_joomleague_matches.matchpart2_result THEN 3 
            WHEN dbo.jos_joomleague_matches.matchpart1_result = dbo.jos_joomleague_matches.matchpart2_result THEN 1
            ELSE 0 
      END AS point
FROM         dbo.jos_joomleague_matches
union ALL
SELECT
      matchpart2 id, 
      CASE 
            WHEN dbo.jos_joomleague_matches.matchpart2_result > dbo.jos_joomleague_matches.matchpart1_result THEN 3 
            WHEN dbo.jos_joomleague_matches.matchpart2_result = dbo.jos_joomleague_matches.matchpart1_result THEN 1
            ELSE 0 
      END AS point
FROM         dbo.jos_joomleague_matches
) tmp
GROUP BY id;

[lstephan]

Un grand merci en tout cas, je vais tester tout ça.

Vraiment geant.

Merci encore

A charge de revanche.

[Waldar]

Je digresse avec le sujet initial mais je réponds ça cette intervention :

Envoyé par StringBuilder

On fait un ON après chaque INNER JOIN, et non tous les INNER JOIN suivits d'un tas de ON qui s'appliquent sur on ne sait quoi (elle marche d'ailleurs cette syntaxe ?)

Oui, mais elle n'a de sens qu'en présence de jointures externe.

Un exemple ici :
http://www.developpez.net/forums/d11...nture-externe/

[StringBuilder]

Waldar > Je t'ai répondu dans l'autre topic

Pour moi les deux syntaxes sont équivalentes, et "la mienne" reste plus lisible (je trouve).

[serge0934]

juste pour info StringBuilder:

j'ai vu la même chose (les select top 100 percent) depuis que j'utilise le SSMS de 2008 R2 en completion automatique.
Ceci dit, pour les mises au point des requêtes sur des grosses tables, c'est pratique

[CinePhil]

D'accord (pour une fois !) avec StringBuilder !
C'est beaucoup plus clair et permet bien mieux de voir si on n'a pas oublié une condition de jointure lorsqu'il y a beaucoup de jointures.
Avec l'imbrication des JOIN, on retrouve le même travers qu'avec la syntaxe obsolète FROM un paquet de table WHERE un paquet de conditions de jointures.

[mikedavem]

Par contre je pense qu'avec des alias on aurait encore plus de lisibilité non ?

++

[CinePhil]

Envoyé par mikedavem

Par contre je pense qu'avec des alias on aurait encore plus de lisibilité non ?

++

Ça c'est sûr !
Dès qu'il y a plus d'une table dans une requête, j'utilise systématiquement des alias.

[lstephan]

Merci pour toutes les infos.

Pour ma part, novice en sql server je genere mes requetes à partir de Sql Management Studio, d'ou le fait peut etre qu aucun n 'alias ne paraisse.

Par alias, vous considerez que je dois rendre explicite les tables appelées dans la requete?

Je sais, je sais j en suis quà la base (SQL)


Mon but est bien sur de generer un classement complet (ligue1) avec nbre de points (general, domicile, exterieur et goal average)

D'avance merci,

[CinePhil]

Alias :
Plutôt que d'écrire dans toute la requête "dbo.jos_joomleague_rounds", on donne un alias au nom de la table :

Code :

FROM dbo.jos_joomleague_rounds r -- r est l'alias de la table dbo.jos_joomleague_rounds

ce qui simplifie grandement la lecture de la requête.

[lstephan]

Merci CinePhil pour ta réponse.

C'est vrai je pourrais donner des alias à mes tables ca rendrait tout ça plus clair.

Mais je pense que ma requete d 'origine n est surement pas la meilleure.

Je sais je demande beaucoup mais je cherche a generer donc un classement (type ligue 1) à partir en fait d'une table matchs recensant tous les matchs, scores par journées.

Tous les champs necessaires sont dans une seule table ( apres les autres infos je dois pouvoir les extraire facilement une fois le classement généré)

La table est la suivante :


je la nomme matchs pour plus de lisibilité

champs de la table:

match_date (date de la rencontre) (date)
matchpart1 (identifiant equipe domicile) (numeric)
matchpart2 (identifiant equipe visiteurs) (numeric)
matchpart1_result (score equipe domicile) (numeric)
matchpart2_result (score equipe visiteur) (numeric)
round_id (identifiant journée de championnat) (numeric)

A partir de ces champs, le but ultime serait de calculer les points par equipe (general, a domicile, a l exterieur et le goal average)

Mais je ne suis pas assez doué pour faire cela, alors peut etre est ce facilement faisable pour un pro SQL?

D'avance merci