1

Imaginons que je veuille faire un site où les utilisateurs peuvent exprimer leur amour pour une ville.
Un nouvel arrivant sur le site peut donner trois noms de villes et le site doit proposer une dizaine d'utilisateurs qui aiment le plus ces trois villes.

Exemple, j'arrive sur le site et je précise les 3 villes: ville1, ville2 et ville3.
Et dans la base on a quelque chose du genre:

            User1 |  User2  |  User3  |  ...
----------------------------------------------
Ville1   |  20    |  10     |    0    |
----------------------------------------------
Ville2   |  50    |   0     |  1000   |
----------------------------------------------
Ville3   |  40    |  40     |    0    |


User1 aime la ville Ville1 avec 20 coeurs et User3 ne l'aime pas.
User2 aime la ville Ville3 avec 40 coeurs, etc..

Donc il faut que je sorte un classement des users en fonction de ces 3 villes.
Dans un cas comme celui-ci, je voudrais privilégier User1 qui semble avoir des gouts communs à l'utilisateur arrivant plutot que User3, qui malgré le fait qu'il aime beaucoup plus la Ville2 que les autres, n'aime pas du tout Ville1 et Ville3.

Comment je peux faire ça de façon efficace? On peut mettre en cache des infos redondantes avec des structures supplémentaire qui mettent 1 jour à être calculé ou n'importe quoi d'autre, la seule vraie contrainte c'est le query pour un nouvel arrivant qui doit être efficace. C'est pas forcément du SQL, je peux très bien faire un programme en C qui s'executera sur le serveur.

Du coté des SGBD, peut-on utiliser ORDER BY expression dans mysql? Si oui, peut-on mettre quelque chose de non-linéaire dans "expression"? Si oui, est-ce que c'est efficace? Y a-t-il une manière de faire une recherche vraiment personnalisé avec sa propre définition de ranking?
Tout ce qui passe pas par le port 80, c'est de la triche.

2

Tu es sûr d'être limité à trois villes et pas plus ? Parce que ça change beaucoup de choses ; si c'est fixe et à vie, alors ta solution est correcte - sauf que la représentation doit être inversée, les noms de colonnes seront les villes et les users avec les notes seront les lignes d'enregistrements - si tu as un nombre de villes pouvant varier, alors tu as tout intérêt à faire avec trois tables :

Table villes

Denom_ville	|	Code_ville
----------------+--------------------
Agde		|	00001
...		|	...

Table Users

User_id		|	e-mail		....
----------------+---------------------	....
onur		| onur@yn.com

Table notes


User_id		|	Code_ville	|	Note
----------------+-----------------------+-------------------
onur		|	00001		|	40

(et encore, ça c'est si ton identifiant utilisateur ne varie pas dans le temps ; sur un site comme yN, on peut changer son pseudo, donc le user_id n'est pas la même chose que le pseudo).

Sinon, je ne comprends pas trop... tu veux faire une valence sur les opinions des users en pénalisant ceux qui sont trop extrêmes ? Dans ce cas, le SQL seul ne te servira pas (ou alors peut-être avec un SQL qui supporte des fonctions statistiques avancées, mais je ne les connais pas du tout).
Là, le plus simple (AMHA), c'est de déterminer la requête qui te permet de trouver les gens qui sont à pénaliser (mais ça veut déjà dire que tu sais concrètement ce que tu vas pénaliser) et de créer un champ dans ta table note (ou dans ta table user, ce qui évite d'avoir de l'information redondate, mais qui ne supporte pas d'avoir plusieurs sondages en cours) qui déterminerait la pénalité d'une personne (à bien y réfléchir, je pense que c'est mieux que tout soit dans la table notes, ça te permet d'utiliser ce champ directement pour y mettre un coefficient de pénalité).
Lorsque l'utilisateur note ses trois villes, le champ "pénalité" est automatiquement calculé (comme ça, plus besoin de le faire pendant la requête). Et de cette façon tu peux pondérer directement les notes. Mais à ce moment là, tu ne peux plus faire tout ton traitement avec du SQL et tu es obligé de passer par un algo pour trier l'info.

Pour répondre, oui ORDER BY expr fonctionne avec MySQL. Si ce que tu appelles "non linéraire" est {0;4;4;5;5.6;10}, alors oui, ça fonctionne.

Mais je pense que ta méthode n'est pas pertinente (mettre en place des correcteurs de ce type montre que le sondage n'est pas pensé convenablement... une solution possible, AMA, serait de faire comme pour les jeux de rôle : les gens ont 100 points à distribuer sur les n villes, et il faut que tu penses à des règles du type :
- il ne doit pas y avoir plus de n villes ayant 0
- une seule ville ne peut pas avoir plus de la moitié des points (ou du quart, ou du tiers, ou de ce que tu veux)
- ...
avatar

3

Hmm. Merci. Mais ça m'aide pas tellement.

Voici quelques éclaircissements. Oui les utilisateurs peuvent voter plus de 3 villes. Ils peuvent voter n villes avec n >= 0. J'ai le même type de tables que les 3 tables que tu as faites. Du coup, je peux pas calculer une pénalité à l'avance puisque la pénalité dépendra des 3 villes que le nouvel arrivant aura mis.

En fait, je ne veux pas pénaliser les extremes (enfin ça revient peut-être à ça?) je veux plutôt favoriser ceux qui ont des votes étendus sur les trois villes choisies par le nouvel arrivant. En gros, si on avait:

            User1 |  User2  | ...
-----------------------------
Ville1   |  10    |   0     |   
-----------------------------
Ville2   |  10    |  30     |  
----------------------------------------------
Ville3   |  10    |   0     |   


Je voudrais privilégier l'User1.
D'où ma question sur les expressions et la non-linéarité. Ca serait un truc du genre:
ORDER BY coef1*voteVille1*voteVille2*voteVille3 
+ coef2*voteVille1*voteVille2 
+ coef2*voteVille1*voteVille3 
+ coef2*voteVille2*voteVille3
+ coef3*voteVille1
+ coef3*voteVille2
+ coef3*voteVille3


Donc si ce genre de choses n'est pas efficace, il faudrait peut-etre que je fasse une table Notes en plus avec des combinaisons de villes...
Table notes


User_id		|	Code_ville1	|	Code_ville2	|	Note
----------------+-----------------------+-------------------
onur		|	00001		|	00002		|	40
----------------+-----------------------+-------------------
onur		|	00001		|	00003		|	20
----------------+-----------------------+-------------------
onur		|	00001		|	 ... 		|	
----------------+-----------------------+-------------------
onur		|	00001		|	  n  		|	50
----------------+-----------------------+-------------------
onur		|	00002		|	00003		|	4
----------------+-----------------------+-------------------
onur		|	00002		|	00004		|	72
----------------+-----------------------+-------------------
onur		|	00002		|	 ... 		|	
----------------+-----------------------+-------------------
onur		|	00002		|	  n  		|	28


...et ce, pour chaque user.

Tout ce qui passe pas par le port 80, c'est de la triche.

4

Ah euuh à mon avis, tu ne peux pas faire un ORDER BY de ce type. Tu vas devoir passer par un tableau à trier en fonction de ce que tu as calculé (ou alors il y a des extension SQL que je ne connais pas, ce qui est aussi possible).

Passer par des tables créées spécialement par users est (AMHA) une hérésie conceptuelle. Mais bon, on a vu pire (quoique ?! grin).

Mais je persiste à penser que les modalités de vote (classement) ne sont pas pertinentes (mais ça, normalement, ce n'est pas à toi, développeur, de les décider... c'est au responsable du sondage de les définir "comme s'il devait le faire en vrai, avec des bouts de papier").
avatar

5

L'idée c'est que quand t'arrives sur le site et que tu dis les 3 noms de villes, ça te donne des users qui aiment ces trois villes. "Aimes les 3 villes" ne veut rien dire. Disons qu'il y a pas de règle, c'est moi qui les définit en fonction de ce qui ferait plus plaisir à l'arrivant. J'ai l'impression que voir un user qui aime un peu les 3 villes que tu as mis a plus de "valeur" qu'un user qui aime vraiment beaucoup une des villes.

C'est vrai que même mathématiquement je vois pas ce qu'il faudrait faire, quelle est la métrique.
Tout ce qui passe pas par le port 80, c'est de la triche.

6

Bah il faut que tu crées des règles de notation, je ne vois que ça, comme j'ai indiqué... comme ça, tu as un filtrage au niveau saisie et pas au niveau interprétation, c'est sacrément plus souple et plus fiable.
avatar

7

Ben, tu dois faire une transformation non linéaire sur tes scores. Par exemple, un logarithme (du coup 0 -> -inf, 1 -> 0 etc., donc tu élimines tout de suite ceux qui n'aiment pas du tout une des 3 villes et tu ne donnes pas un score absurde à celui qui rentre 1000 pour une des villes). Une moyenne géométrique des 3 scores pourrait aussi marcher (ça serait d'ailleurs équivalent pour des fins de classement à une moyenne arithmétique sur les logarithmes). Et tu pourrais aussi envisager des transformations plus complexes.

Cela dit, à mon avis, il faudrait aussi donner à tes utilisateurs 1. un score maximal (sinon il y aura toujours une triple nouille qui mettra 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 juste pour être lourde) et 2. une idée de ce que signifient les scores (genre 0 = aime pas / pas d'opinion, 50 = aime un peu, 100 = aime beaucoup) pour normaliser un peu dès la saisie.
avatar
Mes news pour calculatrices TI: Ti-Gen
Mes projets PC pour calculatrices TI: TIGCC, CalcForge (CalcForgeLP, Emu-TIGCC)
Mes chans IRC: #tigcc et #inspired sur irc.freequest.net (UTF-8)

Liberté, Égalité, Fraternité

8

Ok, en fait j'ai pas tellement la main sur la saisie mais disons que c'est difficile pour un user de "voter" 1000 pour une ville (disons que c'est pas un vote explicite que les users font, c'est en fonction de leur comportement que je détermine ça). Il peut y avoir des rapport 1/1000 d'un user à l'autre mais pas 1/10000000 quoi (ou alors le mec passe sa vie sur le site).

Sinon le logarithme me parait une bonne idée en effet. Je biaiserais peut-être la fonction log avec log(0)=0 parce que sinon ça fait une trop grosse pénalité. Par exemple pour le cas suivant:

            User1 |  User2  | ...
-----------------------------
Ville1   |  2    |   0     |   
-----------------------------
Ville2   |  2    |  30     |  
----------------------------------------------
Ville3   |  2    |   0     |   


(avec log en base 2, User2 se fait jeter alors que je le préfère à User1).

Maintenant reste à savoir comment je peux avoir une requete efficace. Je précalcule les logScore = log(score) et ensuite un ORDER BY logScoreVille1 + logScoreVille2 + logScoreVille3. Mais est-ce que ça va être efficace? Sinon, faut que je réfléchisse à une structure avec de la redondance pour calculer cette expression rapidement.



Tout ce qui passe pas par le port 80, c'est de la triche.

9

onur (./8) :
(avec log en base 2, User2 se fait jeter alors que je le préfère à User1).

Pourtant c'est contradictoire avec ton premier exemple, le User 2 ici est comme le User 3 du post de départ, le User 1 est comparable dans les 2 posts.

Je proposais le logarithme exactement pour ça, là tu inverses complètement tes attentes, donc je ne comprends plus ce que tu veux.
avatar
Mes news pour calculatrices TI: Ti-Gen
Mes projets PC pour calculatrices TI: TIGCC, CalcForge (CalcForgeLP, Emu-TIGCC)
Mes chans IRC: #tigcc et #inspired sur irc.freequest.net (UTF-8)

Liberté, Égalité, Fraternité

10

Oui non le logarithme est très bien. Mais ça pénalise trop les 0 je trouve. L'idée n'est pas d'avoir à tout prix les gens qui ont voté plusieurs villes mais un compromis.

Un exemple avec log modifié (f(x) = log(x) pour x!=0, et =0 pour x=0) avec log = log en base 2
            User1 |  User2  | ...
-----------------------------
Ville1   |  2    |   0     |   
-----------------------------
Ville2   |  2    |  50     |  
----------------------------
Ville3   |  2    |   0     |   


On préfère User2

            User1 |  User2  | ...
-----------------------------
Ville1   |  3    |   0     |   
-----------------------------
Ville2   |  3    |  50     |  
----------------------------
Ville3   |  3    |   0     |   


On préfère toujours User2


            User1 |  User2  | ...
-----------------------------
Ville1   |  4    |   0     |   
-----------------------------
Ville2   |  4    |  50     |  
----------------------------
Ville3   |  4    |   0     |   


On préfère User1.

Maintenant faut trouver la bonne base de logarithme pour le "compromis". Et une façon efficace de calculer la somme dans le ORDER BY si mysql ne le fait pas bien.

Tout ce qui passe pas par le port 80, c'est de la triche.

11

Bon, VERDICT:... ça marche plutot pas mal smile

J'ai une table précalculée user_id, ville_id, lgvote. ("lg" comme log, ici j'ai pris log2(x+2))
Avec la requete suivante j'arrive à avoir les utilisateurs qui aiment soit la ville 2543 OU exclusif 328, soit les deux villes 2543, 328.

SELECT
  user_name,user_id,
  SUM(lgvote) as smlne
FROM
  stats_uss
WHERE
  ville_id = 2543 OR ville_id = 328
GROUP BY
  user_name,user_id
ORDER BY smlne DESC
LIMIT 30

Maintenant je voudrais ajouter une fonctionnalité qui permettrait de pondérer les villes qu'on aime, càd la même requete que dessus, mais avec la possibilité de dire qu'on aime 9 fois plus la ville 2543 que la ville 328: en gros smlne renverait 0.9*lgVoteVille2543 + 0.1*lgVoteVille328 au lieu de lgVoteVille2543 + lgVoteVille328 .
Comment on fait ça en SQL?
Tout ce qui passe pas par le port 80, c'est de la triche.

12

ben tu definis une fonction, non?
avatar
I'm on a boat motherfucker, don't you ever forget

13

oui, et dans ce cas l'idée d'envisager la mise en place d'une procédure stockée est justifiée...
avatar
Webmaster du site Ti-FRv3 (et aussi de DevLynx)
Si moins de monde enculait le système, alors celui ci aurait plus de mal à nous sortir de si grosses merdes !
"L'erreur humaine est humaine"©Nil (2006) // topics/6238-moved-jamais-jaurais-pense-faire-ca

14

Ok merci les gars, je vais regarder ça.
Tout ce qui passe pas par le port 80, c'est de la triche.