Enregistrements consécutifs sur un champs dans MYSQL

[sureau]

Bonjour, pour un site de jeu en ligne ( voir le www ), j'aurais voulu ressortir des données du genre : nombre de victoires et défaites consécutives, nombre de coups parfait consécutif etc...
Pour cela, j'ai une table ACTION qui enregistre tout ce qui se passe dans une partie.
Pour synthétiser, elle contient comme champs
1 - idDuJoueur
2 - DateHeure de la partie
3 - victoire (boolean true or false)

idDuJoueur et la DateHeure constituent la clé primaire

Donc nous pouvons avoir plusieurs parties d'un joueur le même jour et plusieurs parties à la même dateHeure, mais pas du même joueur.

De qu'elle façon pourrais je ressortir le nombre d'enregistrements consécutifs où victoire est true (nombre de victoires d'affilées).

Les joueurs jouant beaucoup (plusieurs centaines de parties pour de très nombreux joueurs (et en milliers pour les toxicos de la belote), je souhaiterais faire ce traitement directement sur mysql plutôt que de charger tous les enregistrements pour les retraiter ensuite.

Avez vous une piste?

[Mitsu]

Tu veux juste compter le nombre de victoire? je comprends pas le problème tu fait un
SELECT COUNT(*) WHERE victoire=true

[sureau]

En fait je voudrais les victoires consécutives (2 d'affilées etc...), en gros le nombre maximum d'enregistrements ayant victoires=true successives sans victoires=false entre 2 enregistrements.

[ADIDASman]

Si tu n'as pas peur des requêtes complexes, en voici une qui devrait fonctionner :

Code: Tout sélectionner

select plage.id_du_joueur, plage.defaite, plage.defaite_suivante, count(*) as victoires_consecutives
from
(
(
select id_du_joueur, '0001-01-01 00:00:00' as defaite, min(date_heure) as defaite_suivante
from action
where victoire = 0
group by id_du_joueur, defaite
)
union
(
select a1.id_du_joueur, a1.date_heure as defaite, a2.date_heure as defaite_suivante
from action as a1, action as a2
where a1.id_du_joueur = a2.id_du_joueur
and a1.victoire = 0
and a2.victoire = 0
and a2.date_heure = (
select min(a3.date_heure)
from action as a3
where a3.id_du_joueur = a1.id_du_joueur
and a3.victoire = 0
and a3.date_heure > a1.date_heure)
)
union
(
select id_du_joueur, max(date_heure) as defaite, '9999-12-31 23:59:59' as defaite_suivante
from action
where victoire = 0
group by id_du_joueur
)
) plage,
action
where plage.id_du_joueur = action.id_du_joueur
and plage.defaite < action.date_heure
and plage.defaite_suivante > action.date_heure
group by plage.id_du_joueur, plage.defaite, plage.defaite_suivante

Ce n'est pas super facile à comprendre mais ce que tu veux faire n'est pas super simple non plus

Voici donc quelques explications...

Tout d'abord, on construit une "table intermédiaire" que j'ai appelée ici plage. Elle contient les informations suivantes :

id_du_joueur : l'identifiant du joueur
defaite : la première défaite du joueur
defaite_suivante : la défaite suivant la première

Et ainsi de suite...

Pourquoi est-elle constituée à l'aide d'UNION ?
Tout simplement car il faut prendre en compte une éventuelle séquence de victoires en début ou en fin de "vie" d'un joueur !

Le reste de la requête est relativement simple : on compte pour chaque "plage", le nombre de victoires correspondant.

Enjoy

Edit : Hors Sujet : Sympa le concept de ton site de belote en ligne !

[YoyoS]

Utiliser la clause HAVING !

[ADIDASman]

Utiliser la clause HAVING !

Il est en effet possible de placer quelques HAVING au lieu notamment des "where victoire = 0"...
Je suis loin d'être un expert en benchmarks MySQL et je ne sais pas si les performances seront meilleures.

[zeb]

Utiliser la clause HAVING !

et un aspro, t'a vu la requête

[sureau]

Ma fois, je pensais pas ça si complexe, mais je suis bluffé par la solution.

La requête ne marche pas (forcemment sinon ça aurait été trop facile), et MYSQL retourne cette erreur : "Every derived table must have its own alias "

J'ai remplacer les UNION par des UNION ALL mais toujours le même problème. Bref, le plus gros a été fait, je ne connaissais pas cette méthode que je trouve très bien pensé Reste plus qu'à débuguer.

Un grand merci

[ADIDASman]

Ah ben m*** alors...
Etant donné que je trouvais le problème intéressant à résoudre, j'ai créé une table action ayant la même structure que la tienne pour faire des tests.
La requête que je t'ai posté ci-dessus marche impeccable chez moi !
Je ne me serais pas permis de le faire sans préciser la mention "non testée" si ça n'était pas le cas.

Pour info, les tests ont été faits sur du MySQL 5.0.45.

A priori, l'erreur que tu rencontres doit venir du fait que certaines tables n'ont pas été "aliasées".

Tu peux essayer ceci qui fonctionne parfaitement chez moi (j'ai rajouté des as act3, as act4 et as act5) :

Code: Tout sélectionner

select plage.id_du_joueur, plage.defaite, plage.defaite_suivante, count(*) as victoires_consecutives
from
(
(
select act1.id_du_joueur, '0001-01-01 00:00:00' as defaite, min(act1.date_heure) as defaite_suivante
from action as act1
where act1.victoire = 0
group by act1.id_du_joueur, defaite
)
union
(
select a1.id_du_joueur, a1.date_heure as defaite, a2.date_heure as defaite_suivante
from action as a1, action as a2
where a1.id_du_joueur = a2.id_du_joueur
and a1.victoire = 0
and a2.victoire = 0
and a2.date_heure = (
select min(a3.date_heure)
from action as a3
where a3.id_du_joueur = a1.id_du_joueur
and a3.victoire = 0
and a3.date_heure > a1.date_heure)
)
union
(
select act4.id_du_joueur, max(act4.date_heure) as defaite, '9999-12-31 23:59:59' as defaite_suivante
from action as act4
where act4.victoire = 0
group by act4.id_du_joueur
)
) plage,
action as act5
where plage.id_du_joueur = act5.id_du_joueur
and plage.defaite < act5.date_heure
and plage.defaite_suivante > act5.date_heure
group by plage.id_du_joueur, plage.defaite, plage.defaite_suivante

Bon courage et tiens nous au courant.