Aller au contenu


Photo

Group By et Order


  • Veuillez vous connecter pour répondre
8 réponses à ce sujet

#1 Julie SUP !

Julie SUP !

    Newbie

  • Members
  • Pip
  • 7 messages

Posté 14 novembre 2013 - 05:10

Bonjour,

J'ai une requête sur laquelle je fais un group by sur deux colonnes, et je n'arrive pas à la trier comme je veux.
Disons que j'ai une table avec des données avec une date, une clé étrangère vers la table1 (disons des associations), une clé étrangère vers la table2 (disons des races de chats) et un nombre. J'aimerais pouvoir récupérer la liste des associations avec pour chacune d'entre elle la liste des races de chats avec leur nombre (SUM(nomber) étant donné que c'est sur plusieurs dates).
J'ai donc un truc comme ça :

SELECT a.name, r.name, SUM(s.nombre) AS nb
FROM stats s
JOIN association a ON a.id = s.associationId
JOIN race r ON r.id = s.raceId
GROUP BY a.id, r.id

Le problème, c'est que j'aimerais trier ces résultats en ayant d'abord les associations qui ont le plus grand nombre (donc le résultat de la requête avec GROUP BY a.id uniquement) puis ensuite, au sein de cette association, trier les races en fonction de leur nombre. La deuxième partie est facile, mais c'est le tri par le nombre total pour l'association que je n'arrive pas à faire...
J'ai pensé à ajouter un :

ORDER BY (
SELECT SUM(s2.nombre) AS total 
FROM stats s2
JOIN association a2 ON a2.id = s2.associationId
WHERE a2.id = a.id
GROUP BY a2.id
)

Cette sous requête dans le order ne fonctionne pas. J'ai une erreur comme quoi plusieurs lignes sont retounées

J'ai aussi essayé d'ajouter dans mon select un truc comme ça : (afin de pouvoir mettre un ORDER BY total après)

(SELECT SUM(s2.nombre) AS total
FROM stats s2
JOIN association a2 ON a2.id = s2.associationId
WHERE a2.id = a.id
GROUP BY a2.id
) AS total

Ca fonctionne mais ca prend 1000 ans vu que ça fait une sous requête par ligne...

Donc je me demandais s'il y avait un moyen simple de faire ça ? (sachant ma table stats risquent de faire quelques centaines de millions de lignes assez rapidement et que c'est pas possible que les requêtes prennent plus de 10 secondes...)

Merci !



#2 Pierre VETTER

Pierre VETTER

    Member

  • Full Professors
  • 17 messages
  • LocationStrasbourg

Posté 14 novembre 2013 - 06:38

Bonjour Julie,

 

Ton post était dans la catégorie PL/SQL, je l'ai donc déplacé ici puisqu'il porte uniquement sur du SQL.

 

SELECT a.name, r.name, SUM(s.nombre) AS nb

FROM stats s
JOIN association a ON a
.id = s.associationId
JOIN race r ON r
.id = s.raceId
GROUP BY a
.id, r.id

 

Dans tous les cas, ça ne marchera pas.

Dès lors qu'on utilise une fonction de groupe dans la clause SELECT, toutes les autres colonnes de cette clause doivent être présentes dans la clause GROUP BY.

Il faudrait donc :

GROUP BY a.name,r.name

Après, je ne suis pas certain de comprendre ta question.

 

A tout hasard, essaie ceci :

SELECT a.name, r.name, SUM(s.nombre) AS nb
FROM stats s
JOIN association a ON a.id = s.associationId
JOIN race r ON r.id = s.raceId
GROUP BY ROLLUP a.name, r.name

Si ça ne te rapproches pas de ce que tu cherches, peux tu nous donner un MCD (ou DDL) qu'on y voit plus claire?



#3 Julie SUP !

Julie SUP !

    Newbie

  • Members
  • Pip
  • 7 messages

Posté 15 novembre 2013 - 10:38

Bonjour Pierre,

 

Merci pour ta réponse mais je ne pense pas que le ROLLUP m'aide beaucoup ici :/

Je vais essayer de mieux expliquer :)

 

J'ai 3 tables : 

table association, avec les colonnes id et name

table race, avec les colonnes id et name

table stats, avec les colonnes id, date, associationId, raceId, nombre

 

J'aimerais obtenir le nombre en fonction de l'association, puis de la race (en gros, group by associationId, raceId) avec les résultats triés en ayant d'abord les associations qui ont le plus grand nombre au total, puis un tri en fonction des races qui ont le plus grand nombre.

En gros il me faudrait un résultat comme ça : 

 

asso2 | race2 | 46
asso2 | race1 | 36
asso1 | race1 | 53
asso1 | race2 | 10
asso3 | race1 | 26
asso3 | race2 | 9
 
Merci beaucoup pour l'aide ! (La je suis en train de voir ce que je peux faire avec un JOIN d'une sous requête qui récupérerait le nombre total de l'asso, je sais pas si je vais réussir à faire marcher ça ! )


#4 Pierre VETTER

Pierre VETTER

    Member

  • Full Professors
  • 17 messages
  • LocationStrasbourg

Posté 15 novembre 2013 - 12:45

Bonjour Julie,

 

C'est plus claire  ;)

 

La je suis en train de voir ce que je peux faire avec un JOIN d'une sous requête qui récupérerait le nombre total de l'asso, je sais pas si je vais réussir à faire marcher ça ! 

 

C'est en effet la piste qui me semble la meilleure puisque tu tries et affiches en fonction de clauses GROUP BY différentes.

 

Tu devrais aboutir à quelque chose du genre :

SELECT a.name, r.name, SUM(s.nombre) AS nb1
FROM stats s
JOIN association a ON a.id = s.associationId
JOIN race r ON r.id = s.raceId
JOIN (   SELECT a2.name, SUM(s2.nombre) AS nb2
         FROM association a2 JOIN stats s2
	 ON a2.id = s2.associationId
	 GROUP BY a2.name)
ON a.id = a2.id
GROUP BY a.name, r.name
ORDER BY nb2, nb1

 

 

 



#5 Julie SUP !

Julie SUP !

    Newbie

  • Members
  • Pip
  • 7 messages

Posté 15 novembre 2013 - 03:03

Merci, c'était ma conclusion :)

 

Bon, mon problème c'est que ma requête prend 30 secondes à s’exécuter (alors que la requête dans la sous requête prend 3s, et la sous requête prend 3s :( )

(Mon exemple est bien simplifié ici, en réalité j'ai des JOIN partout et ma table de stats en est à plus de 15 millions de ligne et elle devrait grossir de façon assez rapide encore ! )

 

Donc je vais plutôt "dupliquer" mes tables de statistiques pour les simplifier, avoir moins de données et réduire le temps de traitement des requêtes!

 

Merci en tout cas !



#6 Pierre VETTER

Pierre VETTER

    Member

  • Full Professors
  • 17 messages
  • LocationStrasbourg

Posté 15 novembre 2013 - 04:57

Du coup ça devient un problème d'optimisation.

Sur quel SGBD/Version es tu ?



#7 Julie SUP !

Julie SUP !

    Newbie

  • Members
  • Pip
  • 7 messages

Posté 15 novembre 2013 - 06:44

MySQL

 

Mais je risque d'avoir du mal à optimiser ma requête pour descendre en dessous de 5 secondes.

Surtout que mes requêtes qui font 30 secondes, c'est loin d'être celles qui renvoient le plus de données et j'en ai d'autres qui vont prendre beaucoup plus de temps. (et la taille de la table va continuer d'augmenter ! )

Bon après, il y a peut-être moyen d'optimiser autrement qu'avec les index ? Ça ne peut pas faire de mal de toute façon :)

 

Mais je pense vraiment que passer par des tables beaucoup plus petites vont aider :)



#8 Fabien ROYER

Fabien ROYER

    Newbie

  • Anciens
  • Pip
  • 2 messages
  • Cursus:Alumni

Posté 18 novembre 2013 - 04:18

Bonjour Julie,

 

Stock cette requête dans une table temporaire :

SELECT a2.name, SUM(s2.nombre) AS nb2
FROM association a2 JOIN stats s2
     ON a2.id = s2.associationId
     GROUP BY a2.name

Tu gagnera beaucoup en rapidité.

 

A+



#9 Julie SUP !

Julie SUP !

    Newbie

  • Members
  • Pip
  • 7 messages

Posté 19 novembre 2013 - 11:16

Merci Fabien,

 

Le problème avec cette solution, c'est que ma sous requête ne sera jamais la même. Ici, j'ai simplifié les choses au maximum, mais il y aura forcément des conditions sur les dates (qui vont changer à chaque fois) et des conditions sur d'autres colonnes de ma table de statistiques (qui a en réalité bien plus de colonnes que dans mon exemple).

La je suis en train de mettre en place une solution avec d'autres tables de statistiques qui ont moins de colonnes, donc moins d'informations et ça devrait donc pouvoir être beaucoup plus rapide :)

 

Mais merci quand même, je prend note de toutes ces alternatives :)






1 utilisateur(s) li(sen)t ce sujet

0 membre(s), 1 invité(s), 0 utilisateur(s) anonyme(s)