[Résolu] Requête SQL qui évalue la possession de chaque Vol sur chaque Territoire

[Sephi-Chan]

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) :

Flights
flight_id

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
territory_id

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
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)

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

[uriak]

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, Territories

ha 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)

[uriak]

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.

[Shakkah]

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/m...tions.html)

[Sephi-Chan]

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

[uriak]

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

[Sephi-Chan]

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; 

[Shakkah]

Possession de T1 (en %) pour le Vol A = (30 + 10) * ((30 + 10 + 20) / 100)

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.

[uriak]

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 ?