Forum Programmation.SQL [RESOLU]Limites du nombre d'enregistrements sous condition

Posté par (page perso) . Licence CC by-sa.
Tags :
2
27
août
2019

Bonsoir,

sous ce message un peu barbare se cache une question très simple.

J'ai cette vue MySQL:

    create or replace view galette_athletics_speed_chal as
    select a.id_adh AS id_adh,
           re.id_race AS id_race,
           point_for_race(re.time,re.gender,ro.distance, NULL) AS points,
           point_bonus(re.category) as bonus,
           if((month(ra.race_date) < 9),(year(ra.race_date) - 1),year(ra.race_date)) AS saison
    from 
          galette_athletics_results re join galette_adherents a on a.id_adh = re.id_adh
                                       join galette_athletics_races ra  on ra.id_race = re.id_race
                                       join galette_athletics_routes ro on ro.id_route = ra.id_route 
    where ro.is_neutralized = 0 and ro.is_challenge = 1 
    group by saison, a.id_adh, points, bonus, id_race
    order by `saison` desc, a.id_adh, points desc;

Elle fait parfaitement le boulot que je souhaite mais elle en fait trop. Je m'explique, dans l'absolu je n'ai besoin par id_adh que les 5 ou 6 enregistrements.

Les 5 ou 6 dépendent du sexe de l'id_adh (re.gender) et le choix des 5 ou 6 lignes et tout betement les lignes avec le plus grand nombre de points.

J'ai beaucoup cherché sur la toile et je coince.

Comment, à partir de cette vue feriez-vous pour réaliser cette opération ?

Je vous remercie pour votre aide !

  • # Exemple de dataset

    Posté par (page perso) . Évalué à 1 (+0/-0).

    Voila a quoi ressemblent les resultats de cette vue:

    id_adh id_race points bonus saison
    3 2070 0 2 2018
    3 2080 0 2 2018
    9 2094 4 0 2018
    9 2139 1 0 2018
    9 2150 1 0 2018
    9 2074 0 0 2018
    9 2130 0 0 2018
    9 2165 0 0 2018
    25 2094 10 0 2018
    25 2091 7 0 2018
    25 2165 6 0 2018
    45 2070 0 1 2018
    45 2080 0 1 2018
    45 2094 0 1 2018
    45 2107 0 1 2018
    45 2111 0 1 2018
    45 2152 0 1 2018
    45 2157 0 1 2018
    45 2173 0 1 2018
    45 2178 0 1 2018
    45 2189 0 1 2018
    47 2131 4 1 2018
    47 2152 1 1 2018
    47 2061 0 1 2018
    47 2174 0 1 2018
    47 2182 0 1 2018
    49 2091 0 2 2018
    50 2066 0 3 2018
    50 2174 0 3 2018
    53 2061 3 0 2018
    53 2066 4 0 2018
    53 2080 3 0 2018
    53 2091 7 0 2018
    53 2107 3 0 2018
    53 2138 0 0 2018
    53 2147 0 0 2018
    53 2152 0 0 2018
    53 2161 0 0 2018

    Par exemple, je ne veux coserver pour l'id 53 que ses 5 meilleurs résultats

  • # count + having

    Posté par . Évalué à 2 (+1/-0).

    Bonjour,

    Je propose de rajouter
    - un count(*) as counter dans la liste des champs après le select
    - une clause having avec une condition telle que (à adapter) (counter < 5 and re.gender = 1 ) or (counter < 6 and re.gender = 0 )

    Je ne suis pas sûr que cela fonctionne car je ne sais pas quand est calculé le count(*) : avant ou après le tri. (en fait plus j'y pense, plus je pense que cela ne doit pas fonctionner)

    Sinon, la solution est d'utiliser la même astuce en utilisant la requête proposée dans le message comme une sous requête (en rajoutant le re.gender dans les champs conservés)
    cela donnerait quelque chose comme :

    create or replace view galette_athletics_speed_chal as
    select id_adh,
           id_race,
           points,
           bonus,
           saison,
           count(*) as counter
    from ( 
    select a.id_adh AS id_adh,
               re.id_race AS id_race,
               re.gender AS gender,
               point_for_race(re.time,re.gender,ro.distance, NULL) AS points,
               point_bonus(re.category) as bonus,
               if((month(ra.race_date) < 9),(year(ra.race_date) - 1),year(ra.race_date)) AS saison
        from 
              galette_athletics_results re join galette_adherents a on a.id_adh = re.id_adh
                                           join galette_athletics_races ra  on ra.id_race = re.id_race
                                           join galette_athletics_routes ro on ro.id_route = ra.id_route 
        where ro.is_neutralized = 0 and ro.is_challenge = 1 
    
        order by `saison` desc, a.id_adh, points desc;
    )
    group by saison, id_adh, points, bonus, id_race
    having  (counter < 5 and gender = 1 ) or (counter < 6 and gender = 0 )

    Il me semble que la clause group by est inutile dans la sous-requête…

    Bonne journée et merci de donner un retour !

    • [^] # Re: count + having

      Posté par (page perso) . Évalué à 1 (+0/-0).

      Merci pour cette piste.

      Je viens de tester et le code ne fonctionne pas comme esperé. Enfin de ce que je vois.

      J'ai l'id 45 qui me remonte 9 lignes par exemple.

      C'est un debut, je vais voir pour comprendre comment ca fonctionne.

      • [^] # Re: count + having

        Posté par (page perso) . Évalué à 1 (+0/-0).

        Bon j'ai compris, counter vaut 1 tout le temps.

        Il doit y avoir un truc qui n'est pas branché au bon moment.

        • [^] # Re: count + having

          Posté par (page perso) . Évalué à 1 (+0/-0).

          J'ai un debut de truc:

          SELECT
          id_adh, saison, points, bonus,
          if (@prev=id_adh, @rn:=@rn+1, 1) as rn,
          @prev := id_adh
          FROM galette_athletics_speed_chal
          JOIN (SELECT @prev := NULL, @rn = 0) AS vars
          ORDER BY saison DESC, id_adh DESC

          mon soucis c'est que le @rn+1 me renvoie NULL:

          id_adh;saison;points;bonus;rn;@prev := id_adh
          289;2018;0;0;1;289
          288;2018;0;0;1;288
          282;2018;4;0;1;282
          282;2018;3;0;;282
          282;2018;3;0;;282
          282;2018;2;0;;282
          282;2018;0;0;;282
          282;2018;5;0;;282

          Mon idee c'est ensuite de remonter cette sous-requete dans une requete principale et de faire un WHERE RN <= conditionnel.

          • [^] # Re: count + having

            Posté par (page perso) . Évalué à 3 (+2/-0).

            Ok, j'ai fini par trouver mon erreur. L'initialisation de @rn était erronnée.

            Je suis donc arrivé à ce résultat:

            SELECT
            id_adh, saison, points ,bonus, rn
            

            FROM
            (
            SELECT
            id_adh, saison, points, bonus, gender,
            @rn := IF(@prev = id_adh, @rn+1, 1) AS rn,
            @prev := id_adh
            FROM galette_athletics_speed_chal
            JOIN (SELECT @prev := NULL, @rn := 0) AS vars
            ORDER BY saison DESC, id_adh DESC, points DESC
            ) AS sub
            WHERE (sub.gender = 'F' and rn <= 5) or (sub.gender = 'M' and rn <= 7)

            Qui me retourne exactement ce que je veux !

            Tres content.

        • [^] # Re: count + having

          Posté par . Évalué à 1 (+0/-0).

          Normal… il a besoin de du group by pour compter au sein du sous-groupe ! J'aurais pas du suggérer de le supprimer dans la sous-requète…

          • [^] # Re: count + having

            Posté par (page perso) . Évalué à 1 (+0/-0).

            Du coup, ca donnerait quoi ton idee ?
            La mienne fonctionne mais je ne peux pas la mettre dans une vue (a cause des variables utilisateur). Du coup, je passe par une procedure mais la, je galere pour l'appeler depuis Zend\Db.

            Un bete SELECT pour construire une VIEW m'irait bien mieux ^

            • [^] # Re: count + having

              Posté par . Évalué à 1 (+0/-0).

              Si je ne me trompe pas il faut rajouter

              group by saison, id_adh, points, bonus, id_race

              dans la sous-requete dans le code que je donne plus haut.

              • [^] # Re: count + having

                Posté par . Évalué à 1 (+0/-0).

                Non, non… laisse tomber ma "solution". Fausse piste !

              • [^] # Re: count + having

                Posté par (page perso) . Évalué à 1 (+0/-0).

                Je reviens.

                Donc ca donne des resultats mais que je ne trouve pas coherent. J'ai l'exemple d'une athled (id_adh=126) qui ne figure pas dans le classement final via ton code alors qu'elle est la meilleure.
                Je soupconne le count(*) de faire des choses etranges.

                Du coup, je me rabats sur mon code.

                Il faut que je trouve un moyen soit de:
                - faire un call de ma proc vian Zend\Db\Adapter
                - mapper le gros select que j'ai 'ecrit dans un objet Zend\Db\Sql

                Autant dire que pour le moment, je seche ^

  • # limit

    Posté par . Évalué à 4 (+2/-0).

    la clause limit ne serait elle pas ce que tu cherches ?

    ca limit le resultat à X valeur,
    donc une recherche avec order by result desc pour les resultats decroissants
    additionné d'un limit 5 ne prendra que les 5 premiers resultats

    evidemment il faut revoir ta requete,
    pour que le "limit 5" se fasse sur chaque adherent

    • [^] # Re: limit

      Posté par . Évalué à 2 (+1/-0).

      Je réponds à la place du demandeur, cela validera si j'ai bien compris le problème.

      La clause limite serait parfaite si il fallait limiter au mettre même nombre d'enregistrements pour tous les cas. Mais là, il faut limiter à 5 dans certains cas et à 6 dans d'autres… d'où le titre du message "Limite… …sous conditions"

      • [^] # Re: limit

        Posté par (page perso) . Évalué à 1 (+0/-0).

        c'est ca, en gros un "limit where" serait parfait.

      • [^] # Re: limit

        Posté par . Évalué à 2 (+0/-0).

        2 passes puisque 2 conditions

        une passe avec la limit 6 et la condition A
        une passe avec la limit 5 et la condition B

        dans le code de l'appli, faire une fonction
        recherche(limite,condition)
        qu'on appellera donc 2 fois
        devrait suffire
        au pire la requete se fera sur la condition la pire pour generer la vue,
        puis de cette vue on fera requete d'extraction.

  • # Et en utilisant la fonction RANK() ?

    Posté par . Évalué à 3 (+2/-0).

    Quelque chose comme :

    create or replace view galette_athletics_speed_chal as
      select * from (
        select a.id_adh AS id_adh,
               re.id_race AS id_race,
               point_for_race(re.time,re.gender,ro.distance, NULL) AS points,
               point_bonus(re.category) as bonus,
               if((month(ra.race_date) < 9),(year(ra.race_date) - 1),year(ra.race_date)) AS saison,
               RANK() OVER (PARTITION BY a.id_adh ORDER BY re.id_race) AS rank
        from 
              galette_athletics_results re join galette_adherents a on a.id_adh = re.id_adh
                                           join galette_athletics_races ra  on ra.id_race = re.id_race
                                           join galette_athletics_routes ro on ro.id_route = ra.id_route 
        where ro.is_neutralized = 0 and ro.is_challenge = 1 
        group by saison, a.id_adh, points, bonus, id_race
      ) AS t
      WHERE t.rank <= 6
      ORDER BY t.saison desc, t.id_adh, t.points desc;
  • # autre (meilleur ! ) idée.

    Posté par . Évalué à 1 (+0/-0).

    Bonjour,

    Je pense que la solution est d'utiliser UNION entre deux select (un pour chaque cas de gender) :

    (SELECT  FROM  WHERE gender='F' ORDER BY  LIMIT 5)
    UNION
    (SELECT  FROM  WHERE gender='M' ORDER BY  LIMIT 7);

    Non ?

    • [^] # Re: autre (meilleur ! ) idée.

      Posté par (page perso) . Évalué à 1 (+0/-0).

      J'ai le sentiment profond que cela n'y changera rien.

      La requete que j'ai fini par trouver fait vraiment le boulot. Son unique defaut est de m'interdire de l'utiliser dans une vue :(

      Maintenant je me bats comme un diable pour faire un simple CALL proceduer et pouvoir manipuler l'ensemble des lignes retournees.

      La, j'ai l'impression d'avoir 1 ligne contenant l'ensemble de mes infos mais je ne parviens pas trouver la methode pour les traiter :/

Envoyer un commentaire

Suivre le flux des commentaires

Note : les commentaires appartiennent à ceux qui les ont postés. Nous n'en sommes pas responsables.