[MYSQL]classement tounois

classement tounois [MYSQL] - SQL/NoSQL - Programmation

Marsh Posté le 18-01-2010 à 16:25:55    

Bonjour une petite demande d'aide dans une requete SQL un poil complexe  pour réaliser un classement de type jeu "RISK"
 
j'ai la table suivante  => table_base
nom   pays    ville     nombre_d'unité
bob    france  paris   3
toto   france  paris   4
bob    france    lile     3
toto   italie      rome 3
toto   espagne madrid 3
 
 
et j'aimerais pouvoir facilement sortir que classement niveau ville (déterminé en fonction du nombre d'unité par chque personne dans la ville) => vue_classement_ville
nom  pays  ville        classement
toto  france  paris      2
bob   france  paris      1
bob   france  lile          1
toto   italie    rome      1
toto   espagne madrid 1
 
ensuite niveau pays (1 ville = 1 point) => vue_classement_pays
nom  pays      classement
bob   france    1
toto   france    2
toto   italie      1
toto   espagne 1
 
enfin niveau monde (1pays = 1 point) => vue_classement_monde
nom  classement
toto  1
bob   2
 
 
a première vue faire ces vues me semblaient super simple mais je bloque sur pas mal de choses au final :(
 
  => transformer un total d'unité en un classement , en gros faire un ORDER BY sur ma table_base et replacer le nombre d'unité par la place dans la liste  
  => le GROUP BY selon de critères de diférences :/ pour les villes pays
 
Bref d'un truc d'apparence simple je bloque sur beaucoup :/
 
 

Reply

Marsh Posté le 18-01-2010 à 16:25:55   

Reply

Marsh Posté le 20-01-2010 à 11:14:19    

Pour ta premiere question a vu de nez je dirais que tu vas etre obligé de passer par une procedure stockée, en SQL pur je ne vois pas comment c'est jouable...
 
Pour ta seconde question il faut etre plus precis, comprend pas

Reply

Marsh Posté le 20-01-2010 à 13:43:10    

ben pour la 1er ouep c'est ce que j'avais fait une requête sur le nombre d'unité classé par nombre d'unité. Et ensuite je remplis la vue_classement_ville (qui donc est une table :p) selon la position de chacun
 
et l'affaire est bouclé
 
mais je me demandais si je pouvais pas m'en sortir avec une operation genre un SWITCH sur (nombre_d'unité - MAX(nombre_d'unité)) si il vaut 0 = 1 er place si il vaut 1 = 2em place si il vaut 2=place  
 
 
dans le 2em cas le soucis c'est qu'il existe des villes homonyme dans les pays, genre Paris au Texas et en France.  
 
donc je pensais a faire un SELECT avec une clause GROUP BY (CONCAT(country,city)) avec un trie ascendant sur SUM(classement)  
car effectivement apres un SUM des classement sur chacune des villes du pays celui qui a le moins de point est forcément le gagnant du pays
 
par contre le souci c'est qu'on a le disciminant joueur et en faisant un GROUP BY (CONCAT(country,city)) on ne prend pas en compte les joueurs.
 
 
 

Reply

Marsh Posté le 25-01-2010 à 17:26:17    

Pour le premier point je dirais de partir sur quelquechose comme ça (à vérifier au niveau de la syntaxe, je n'ai pas de MySQL sous le coude):

 

Select pays, ville, nom, max(rang)
from
(
select T1.pays, T1.ville, T1.nom, count(*) as rang
from table_base as T1,
join table_base as T2 on T1.pays = T2.pays and T1.ville = T2.ville and T1.nbr_unite >= T2.nbr_unite
group by T1.pays, T1.ville, T1.nom
)
group by pays, ville, nom

 

Le principe est que pour chaque ligne de la table_base, on va compter le nombre de ligne pour la même clé (pays/ville) ayant moins (>=)  d'unité par un pseudo  produit cartésien, on récupère ensuite le maximum de ce nombre pour nous donner le rang.


Message édité par E-Nyar le 25-01-2010 à 17:34:17

---------------
Don't fuck me, I'm anonymous.
Reply

Marsh Posté le 26-01-2010 à 14:19:16    

Juste comme ça, dans ton exemple, il y a une erreur (qui m'a bien emmerdé !)
 
En effet, pour le palmarès par pays, en France, bob et toto sont ex aequo. En effet, bob contrôle paris, et toto contrôle lille. Avec chacun une ville, ils ont le même rang...
 
J'ai donc ajouté "marseille" à mon exemple, histoire de ne plus avoir ce cas particulier (ceci dit, il ne pose pas de problème...)
 
Attention, la syntaxe est pour SQL Server 2008. Elle diffère de MySQL.
J'espère pour toi que MySQL support les fonctions de partitionnement... Sinon t'as plus qu'à abandonner ce simili de résidu de SGBD et télécharger SQL Server Express, qui est autrement mieux foutu...
 

Code :
  1. DROP VIEW rang_mondial
  2. go
  3.  
  4.  
  5. DROP VIEW rang_par_pays
  6. go
  7.  
  8.  
  9. DROP VIEW rang_par_ville
  10. go
  11.  
  12. DROP INDEX uix_base_ville ON base_ville
  13. go
  14.  
  15. DROP TABLE base_ville
  16. go
  17.  
  18. CREATE TABLE base_ville
  19. (
  20.  joueur varchar(50) NOT NULL,
  21.  pays varchar(50) NOT NULL,
  22.  ville varchar(50) NOT NULL,
  23.  unites int NOT NULL
  24. )
  25. go
  26.  
  27. CREATE UNIQUE INDEX uix_base_ville ON base_ville (pays, joueur, ville)
  28. go
  29.  
  30. CREATE VIEW rang_par_ville
  31. AS
  32. SELECT pays, ville, joueur, RANK() over (partition BY pays, ville ORDER BY unites DESC) Rang
  33. FROM base_ville
  34. go
  35.  
  36. CREATE VIEW rang_par_pays
  37. AS
  38. SELECT pays, joueur, RANK() over (partition BY pays ORDER BY count(*) DESC) Rang
  39. FROM rang_par_ville
  40. WHERE rang = 1
  41. GROUP BY pays, joueur
  42. go
  43.  
  44. CREATE VIEW rang_mondial
  45. AS
  46. SELECT joueur, RANK() over (partition BY NULL ORDER BY count(rang) DESC) Rang
  47. FROM rang_par_pays
  48. WHERE rang = 1
  49. GROUP BY joueur
  50. go
  51.  
  52. INSERT INTO base_ville (joueur, pays, ville, unites) VALUES ('bob', 'france', 'paris', 3);
  53. INSERT INTO base_ville (joueur, pays, ville, unites) VALUES ('toto', 'france', 'paris', 4);
  54. INSERT INTO base_ville (joueur, pays, ville, unites) VALUES ('bob', 'france', 'lille', 3);
  55. INSERT INTO base_ville (joueur, pays, ville, unites) VALUES ('bob', 'france', 'marseille', 1);
  56. INSERT INTO base_ville (joueur, pays, ville, unites) VALUES ('toto', 'italie', 'rome', 3);
  57. INSERT INTO base_ville (joueur, pays, ville, unites) VALUES ('toto', 'espagne', 'madrid', 3);
  58.  
  59.  
  60. SELECT *
  61. FROM rang_par_ville;
  62.  
  63. SELECT *
  64. FROM rang_par_pays;
  65.  
  66. SELECT *
  67. FROM rang_mondial;


 
Résultat :


 
(1 ligne(s) affectée(s))
 
(1 ligne(s) affectée(s))
 
(1 ligne(s) affectée(s))
 
(1 ligne(s) affectée(s))
 
(1 ligne(s) affectée(s))
 
(1 ligne(s) affectée(s))
pays                                               ville                                              joueur                                             Rang
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------
espagne                                            madrid                                             toto                                               1
france                                             lille                                              bob                                                1
france                                             marseille                                          bob                                                1
france                                             paris                                              toto                                               1
france                                             paris                                              bob                                                2
italie                                             rome                                               toto                                               1
 
(6 ligne(s) affectée(s))
 
pays                                               joueur                                             Rang
-------------------------------------------------- -------------------------------------------------- --------------------
espagne                                            toto                                               1
france                                             bob                                                1
france                                             toto                                               2
italie                                             toto                                               1
 
(4 ligne(s) affectée(s))
 
joueur                                             Rang
-------------------------------------------------- --------------------
toto                                               1
bob                                                2
 
(2 ligne(s) affectée(s))
 


 
Et voilà.

Reply

Marsh Posté le 26-01-2010 à 17:09:23    

MySql accepte les fonctions analytiques ? (ça fait un bout de temps que je n'y ai touché ^^)


---------------
Don't fuck me, I'm anonymous.
Reply

Marsh Posté le 26-01-2010 à 17:20:14    

Ben ça rejoint mon dernier commentaire : si ça passe pas, faudra penser à travailler sur un SGBD moins pourrave ;)

Reply

Marsh Posté le 26-01-2010 à 17:29:58    

Access ? :whistle:


---------------
Don't fuck me, I'm anonymous.
Reply

Marsh Posté le 27-01-2010 à 10:48:02    


je vais voir de ce pas ce que peux fournir mysql en tant que fonctions de partinionement.
 
sinon on passe à access [:henri gaud-macias:3]

Reply

Marsh Posté le 27-01-2010 à 10:54:57    

aprioris il y a ce qu'il faut dans mysql... oufffff !
 
http://dev.mysql.com/doc/refman/5. [...] types.html
 
ca vas le faire ca , par contre je connaissait pas du tout
en tout cas merci du filon Buzz

Reply

Marsh Posté le 27-01-2010 à 10:54:57   

Reply

Marsh Posté le 28-01-2010 à 15:00:01    

une IA a écrit :

aprioris il y a ce qu'il faut dans mysql... oufffff !

 

http://dev.mysql.com/doc/refman/5. [...] types.html

 

ca vas le faire ca , par contre je connaissait pas du tout
en tout cas merci du filon Buzz

 

en fait c'est mort le partitionnement est simplement un tableau dont on fait une répartition en amont
néamoins j'ai réussit à m'en sortir :p

 
Code :
  1. SET @rank=0;
  2. SELECT @rank:=@rank+1 AS rank,city,country,player,COUNT(*) AS units
  3. FROM base_ville GROUP BY country,city,player
  4. ORDER BY COUNT (*)  DESC
 

ensuite en mixant les distinct et les group by ca fonctionne bien :)
en gros pour faire le top au niveau ville, je group sur country,player et je fait un count sur distinct(city) et hop :)


Message édité par une IA le 28-01-2010 à 15:37:25
Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed