Opération mathématique dans une requête

[Kynerion]

Bonjour,

Dans une table XXX j'ai plusieurs valeurs, dont deux numériques. Exemple:

| Groupe | Nombre | Capacité |

| alpha | 9 | 40 |

| beta | 12 | 24 |

(désolé pour le tableau... moche, j'espère qu'il est compris).

Et donc j'aurais besoin de diviser la capacité par le nombre et de classer les résultats par le quotient obtenu.

Dans mon monde merveilleux où MySQL serait intuitif (pour moi), la requête ressemblerait à ça:

SELECT * FROM XXX WHERE $quotient=(capacité/nombre) ORDER BY $quotient

Mais il faut croire que ce monde merveilleux n'existe pas...

Si vous avez compris mon appel de détresse, je veux bien de vos lumières. Merci bien!

[Julien L.]

Bonsoir, une hypothèse :

SELECT * FROM XXX ORDER BY (capacité*1/nombre*1)

Les *1 sont là pour s'assurer que le champ sera numérique.

Je ne saurais vous aider plus longuement ce soir.

Modifié 4 Avril 2006 par Julien L.

[Kynerion]

Je vais m'en contenter...

Merci beaucoup!!!

[Spidetra]

Tu as aussi cette syntaxe en utilisant des alias.

Attention : cette syntaxe marche avec mySQL, elle ne marche pas avec tout les SGBD.

SELECT f.foo, f.bar, (f.foo/f.bar) as quotient 
FROM foo f 
order by quotient;

[Kynerion]

Oui, merci, c'est également efficace. Maintenant je vais vraiment dévoiler le but de cette sélection parce que :

1. ça pourra resservir à d'autres

2. l'équation se révèle beaucoup plus compliquée que prévue

En effet, le but est de créer manuellement un classement des "meilleurs sujets du moment" d'un forum. Je précise "à la main" parce qu'il existe des mods de statistiques pour les forums, mais c'est plus gratifiant de le faire soi-même (et ça ne touche pas aux fichiers).

Le problème est donc de trouver l'équation MySQL qui permet de sélectionner ces sujets, selon 4 critères numériques contenus dans la table "sujets":

- la date de lancement du sujet, en timestamp

- la date du dernier message dans ce sujet, également en timestamp

- le nombre de lectures du sujet

- le nombre de messages du sujet

A partir de là je fais une première sélection puisque je ne prends que les sujets qui ont reçu une réponse dans le mois, à l'aide d'un:

WHERE dernier_message > ".(time()-60*60*24*30)."

L'idéal du sujet "super vivant", c'est donc un sujet lancé récemment (donc timestamp élevé du lancement), avec de nombreuses lectures et de nombreuses réponses (donc un quotient "lectures/réponses" faible).

J'ai tenté plusieurs choses:

ORDER BY ((lancement*1)/(lectures*1/réponses*1)) DESC

Mais pas satisfaisant : ça me mettait de très vieux sujets (donc très lus et très "répondus") qui ont juste reçu une réponse ce mois-ci.

Alors j'ai essayé de compliquer un peu la choses en rajoutant un facteur temps (la différence entre la date du dernier message et celle du lancement du sujet, qui doit être faible):

ORDER BY (((lancement*1)/(lectures*1/réponses*1))/(dernier_message*1-lancement*1)) DESC

Là encore ça mettait comme grand vainqueur un nouveau sujet lancé ce mois-ci, qui a reçu deux réponses très vite en début de mois et depuis plus rien.

Je sens que je me rapproche, mais il me manque un matheux...

EDIT : quelques corrections. Peut-être déjà inverser le quotient : "lectures/réponses" devient "réponses/lectures", donc le meilleur sujet sera celui dont ce quotient se rapproche de 1.

Ensuite prendre en compte la date du moment (time())avec :

- une faible différence entre la date actuelle et celle du dernier message

- une faible différence entre la date actuelle et celle du lancement du sujet

Enfin, autre(s) critère(s) :

- pour éviter les vieux sujets qui ressuscitent par une seule réponse, il faut un quotient faible entre le nombre de réponses ET la différence entre la date actuelle et celle du lancement du sujet

désolé si je complique, je me sers de ce sujet pour prendre des notes et améliorer l'équation.

Modifié 5 Avril 2006 par Kynerion

[robinsonvendredi]

D'après tes critères :

- un sujet le plus récent possible : MIN(Date du jour - date de lancement)

- un sujet fraichement répondu : MIN(Date du jour - date de dernière réponse)

Tu pourrais régler ta requête avec un coefficient entre ces deux critères

- avec le plus grand nombre de réponses possible : MAX(nombre de réponses)

Ce qui revient à minimiser :

(Coef*(Date du jour - date de lancement)+(Date du jour - date de dernière réponse))/

(nombre de réponses)

c'est juste un avis en passant...

[Kynerion]

Malheureusement non, cette requête a plutôt tendance à m'afficher des sujets assez différents et pas vraiment actifs (j'ai essayé en mettant et enlevant le "DESC", ça ne change rien).

Pour l'instant l'équation la plus aboutie est :

ORDER BY (((".(time() - dernier_message).") + (".(time() - lancement).")) * (réponses / lectures)) DESC

Mais il manque encore le facteur "pour éviter les vieux sujets qui ressuscitent par une seule réponse, il faut un quotient faible entre le nombre de réponses ET la différence entre la date actuelle et celle du lancement du sujet".

[Julien L.]

Je ne peux aider mais c'est (et ce serait) gentil de partager le développement et la réponse !

Modifié 5 Avril 2006 par Julien L.

[rportal]

Tu veux lister les sujets "super vivants"...

La definition (si j'ai bien lue) est un sujet:

- jeune

- qui possede beaucoup de reponse

- un ratio de visite faible par rapport aux reponses

On doit donc jouer avec les donnees suivantes:

- age = time - lancement

- ratio = reponse / visite ( <1 : represente % d'interet à repondre)

- v = reponse / age : vitesse moyenne de reponse : représente l'activité du sujet

Une premiere solution acceptable pourrait etre de definir un sujet "super vivants" comme un sujet dont l'activite est importante et dont l'interet à repondre est important.

Donc on a:

note = ratio * v

plus la note est forte plus le sujet est super vivant

Donc la formule serait:

note = reponse * reponse / visite / age

je vous laisse faire la requete SQL.

L'ordonnencement devrait être bon. Si cela ne colle pas, il faut que tu specifies un peu mieux ce que tu attends ou bien les cas où l'ordonnancement est mis en defaut.

Modifié 5 Avril 2006 par rportal

[Kynerion]

Merci pour ces éléments. Je posterai l'équation que j'ai retenue bien entendue; néanmoins cela avance et pour l'instant la requête que j'utilise est assez satisfaisante. Si vous voulez voir le classement, voici la page sur lequel il est affiché:

http://www.onlike.net/stats.php3

Je vais tester la formule de rportal et comparer les résultats avec la mienne.

[Kynerion]

Up, désolé pour le temps de réponse mais j'ai dû changer de méthode de classement car l'ancienne (classer uniquement les derniers sujets lancés) ne plaisait pas aux membres. Du coup il n'y a pas de filtre par date de lancement du sujet.

Voici donc le classement de ma requête, qui est en grande partie celui de rportal finalement :

ORDER BY ((num_replies*1/num_views*1)*((num_replies*1)/(".(time()-posted*1)."))) DESC LIMIT 10

Au préalable je ne sélectionne que les sujets ayant reçu une réponse dans les 5 derniers jours (à modifier selon l'activité de vos forums, de toute façon seuls 10 sujets sont classés). Evidemment du coup, les vieux sujets avec plein de réponses se retrouvent presque à coup sûr dans ce classement dès qu'ils en reçoivent une nouvelle, la division par l'âge du sujet n'ayant pas beaucoup d'influence avec eux. Tant pis.

[rportal]

Comment valides tu l'utilisation de la durée depuis le dernier post?

Au préalable je ne sélectionne que les sujets ayant reçu une réponse dans les 5 derniers jours.

<{POST_SNAPBACK}>

Bonne initiative...

Evidemment du coup, les vieux sujets avec plein de réponses se retrouvent presque à coup sûr dans ce classement dès qu'ils en reçoivent une nouvelle, la division par l'âge du sujet n'ayant pas beaucoup d'influence avec eux. Tant pis.

<{POST_SNAPBACK}>

Effectivement là tu ne divises pas par l'age du sujet mais par la duree depuis le dernier post. Du coup ta formule ne prend pas du tout en compte l'age du sujet.

Modifié 13 Avril 2006 par rportal

[Kynerion]

Effectivement là tu ne divises pas par l'age du sujet mais par la duree depuis le dernier post. Du coup ta formule ne prend pas du tout en compte l'age du sujet.

Si si :

(".(time()-posted*1).")

c'est l'âge du sujet. Pour la durée depuis le dernier post ça aurait été :

(".(time()-lastpost*1).")

C'est d'ailleurs justement cette dernière variable qu'il faudrait peut-être rajouter. Mais où ?

[rportal]

Non la duree depuis le dernier post ne sert à rien car elle n'a rien à voir avec l'activité d'un post. Par contre il serait interessant d'utiliser une vitesse de post un peu plus instantanée que la vitesse moyenne du post. Par exemple calculer la vitesse sur les 10 derniers posts... Du coup il te faudrait recuperer la date avant-avant-..10fois dernier post en timestamp. La tu obtiendrais des resultats un peu plus interessant car tu prendrais l'activité "quasi instantané" des sujets. du coup la formule pourrait se resumer à:

note = reponse / visite / age depuis 10 derniers post

Le 10* ne servant à rien pour discrimer (constante).

[Kynerion]

Hmmm.... du coup ça oblige à prendre en compte une autre table (la table des posts), et à aller chercher la date de l'ante-ante-ante-ante-ante-ante-ante-ante-pénultième post () dans tel sujet, sachant que tous les sujets n'ont pas forcément 10 posts...

je crois que je vais en rester là!

Plus tard je ferai un classement dynamique en Ajax à la Digg-spy, ça sera déjà bien.