+- JeuWeb - Crée ton jeu par navigateur (https://jeuweb.org)
+-- Forum : Discussions, Aide, Ressources... (https://jeuweb.org/forumdisplay.php?fid=38)
+--- Forum : Programmation, infrastructure (https://jeuweb.org/forumdisplay.php?fid=51)
+--- Sujet : [Résolu] Requête SQL qui évalue la possession de chaque Vol sur chaque Territoire (/showthread.php?tid=2149)
[Résolu] Requête SQL qui évalue la possession de chaque Vol sur chaque Territoire - Sephi-Chan - 26-03-2008
Salut à tous,
Dans le cadre de Seelies, je dois lancer à intervalle régulier (de l'ordre de la demi-heure) une requête qui détermine le niveau de possession de chaque Vol sur chaque territoire.
Je n'arrive cependant pas à pondre la requête qui me convient, la plus optimisée. C'est pourquoi je fais appel à vous pour m'aider à la mettre au point.
Voici mes tables simplifiées (pour ne garder que l'essentiel) :
Territories a écrit :Flights
flight_id
Seelies a écrit :seelies
seelie_id
territory_id : Territoire sur lequel se trouve la Seelie.
importance : Indice de représentation. Une Seelie puissante a un indice plus grand, alors qu'une Seelie jeune n'est pas très représentative.
Territories a écrit :territories
territory_id
Flight has Seelie a écrit :flight_has_seelies
/* On a une cardinalité 1, n car les Seelies pourront à terme avoir plusieurs Vols, des Vols secrets, notamment. */
flight_id
seelie_id
Flight has Territory a écrit :flight_has_territory
flight_id
territory_id
possession : pourcentage de possession du territoire.
L'objectif est donc de déterminer un pourcentage de possession pour chaque territoire et chaque Vol.
Par exemple, si sur un même territoire T1 il y a deux Seelies du Vol A qui ont respectivement une importance de 10 et de 30 ainsi qu'une Seelie du Vol B qui a une importance de 20.
Il faut que la (ou les) requête modifie la valeur de possession de chaque entrée de la table flight_has_territory pour avoir un pourcentage. (P(T, A) = (∑(importances(A, T1)) * ∑(importances(T1)) / 100) )
Possession de T1 (en %) pour le Vol A = (30 + 10) * ((30 + 10 + 20) / 100)
Citation :A : T1 : 66%
B : T1 : 34%
La difficulté, c'est que je veux que les arrondis soient faits en faveur du Vol le moins représenté. Et je n'arrive pas à le faire.
J'espère que vous pourrez m'aider et je vous en remercie d'avance.
Sephi-Chan
RE: Requête SQL qui évalue la possession de chaque Vol sur chaque Territoire - uriak - 26-03-2008
Déjà juste une question, pourquoi une seelie n'a pas de champ flight (avec éventuellement un champ has_flight) ce qui te force à faire une jointure de plus ?
Code :
SELECT flight_id, territory_id, SUM(SELECT importance FROM Seelies JOIN Flight_has_seelies AS FHS ON Seelie.seelie_id=FAS.seelie_id WHERE FHS.flight_id=flights.flight_id AND Seelies.territory_id=territories.territory_id) AS Possession FROM flights, Territoriesha je me rends compte que tu veux une mise à jour, pas de problème. je pars du principe que tu crées le champ de la table flight_has territory avant, hein, par exemple quand une Seelies se déplace.
Code :
UPDATE flight_has_territory as FHT SET possession = SUM(SELECT importance FROM Seelies JOIN Flight_has_seelies AS FHS ON Seelie.seelie_id=FHS.seelie_id WHERE FHS.flight_id=FHT.flight_id AND Seelies.territory_id=FHT.territory_id)RE: Requête SQL qui évalue la possession de chaque Vol sur chaque Territoire - uriak - 26-03-2008
Bon je viens de lire ton édition. Il me semble raisonnable de remplir dans la table la somme de l'importance et de n'effectuer ce traitement en pourcentage qu'au moment de son utilisation... plus de contrôle en php sur les arrondis, je pense.
RE: Requête SQL qui évalue la possession de chaque Vol sur chaque Territoire - Shakkah - 26-03-2008
Sephi-Chan a écrit :La difficulté, c'est que je veux que les arrondis soient faits en faveur du Vol le moins représenté. Et je n'arrive pas à le faire.
Tu sais avant le calcul le Vol le moins représenté ou pas ?
Pour arrondir tu utilise ROUND ou autre chose ?
Si tu utilise mysql, essaye de voir si tu peux pas faire ce que tu veux avec TRUNCATE ou FLOOR (http://dev.mysql.com/doc/refman/5.0/fr/mathematical-functions.html)
RE: Requête SQL qui évalue la possession de chaque Vol sur chaque Territoire - Sephi-Chan - 26-03-2008
Merci à vous deux !
J'avais déjà fais la requête qui me modifiait les valeurs absolues de possession. Je n'arrive par contre pas à créer des valeurs en pourcentage. Au lieu de me sortir respectivement 66,66 et 33,33, il me sort 24 et 12. Et je ne comprends pas pourquoi ! :/
Je préfère faire ça en SQL directement plutôt qu'en PHP, quitte à perdre l'arrondit.
Sephi-Chan
RE: Requête SQL qui évalue la possession de chaque Vol sur chaque Territoire - uriak - 26-03-2008
Tu peux envisager deux requêtes...
La première calcule la somme totale, et tu ajoutes un champ relative dans cette dernière table
du coup, ça donne
Code :
UPDATE flight_has_territory as FHT set relative = (FLOOR(FHT.possession*100/SUM(SELECT possession FROM flight_has_territory as FHT2 where FHT2.territory_id=FHT.terriroty_id)))Assure-toi juste qu'une entrée ne peux jamais avoir une somme totale de contrôle de zéro
RE: Requête SQL qui évalue la possession de chaque Vol sur chaque Territoire - Sephi-Chan - 26-03-2008
Oups, j'avais fais le boulet en me trompant dans mon opération !
Bon, voilà la requête qui m'affiche les pourcentage :
Code PHP :
<?php
SELECT
@possession_abs := (
SELECT SUM(S.importance)
FROM seelies S
JOIN Flight_has_seelie AS FHS ON S.seelie_id = FHS.seelie_id
WHERE FHS.flight_id = FHT.flight_id AND S.territory_id = FHT.territory_id
) AS possession_abs,
@possession_totale := (
SELECT SUM(S2.importance)
FROM seelies S2
WHERE S2.territory_id = FHT.territory_id
) AS possession_totale,
ROUND(@possession_abs * 100 / @possession_totale) AS possession_pct
FROM flight_has_territory FHT;
Et à la sauce UPDATE, voilà ce que ça donne :
Code PHP :
<?php
UPDATE flight_has_territory FHT
SET possession = ROUND(
(
SELECT SUM(S.importance)
FROM seelies S
JOIN Flight_has_seelie AS FHS ON S.seelie_id = FHS.seelie_id
WHERE FHS.flight_id = FHT.flight_id AND S.territory_id = FHT.territory_id
)
/ (
SELECT SUM(S2.importance)
FROM seelies S2
WHERE S2.territory_id = FHT.territory_id
)
* 100
);
J'ai finalement opté pour un simple ROUND(), tant pis pour cette petite différence avec le programme prévu : je peux bien faire quelques concessions pour que la requête reste simple. Je compenserais ça ailleurs.
Si quelqu'un toutefois trouve une solution pour faire ce que je voulais à la base en une seule requête, à savoir arrondir le pourcentage en faveur du plus petit pourcentage, je suis preneur.
Sephi-Chan
RE: Requête SQL qui évalue la possession de chaque Vol sur chaque Territoire - Shakkah - 26-03-2008
Sephi-Chan a écrit :Possession de T1 (en %) pour le Vol A = (30 + 10) * ((30 + 10 + 20) / 100)
Citation :A : T1 : 66%
B : T1 : 34%
ça serais pas mieux Vol A = (30 + 10) * (100 / (30 + 10 + 20)) ?
EDIt: J'étais entrain de faire le calcul qd j'ai posté j'avais pas vu que tu avais trouvé l'erreur.
RE: Requête SQL qui évalue la possession de chaque Vol sur chaque Territoire - uriak - 26-03-2008
il faudrait une forme de récursivité pour ton cas, Sephi, c'est pas simple et à mon avis, lourd.
Tu peux envisager une seconde passe où tu compares une entrée aux autres et si elle est la plus faible ET que la somme est inférieure à 100, alors tu lui ajoutes 1.
Mais pour le coup je ne sais pas ce qui se passe en SQL quand on se met à changer des variables utilisées dans la requête même. Quelqu'un pour nous dire deux mots là dessus ?