Plusieurs GROUP BY's et tri par valeurs de groupe SUM's

Question

Je travaille sur les rapports de notre application de suivi du temps. Chaque entrée de temps est liée à un projet et à un service. Voici une requête simplifiée pour regrouper les entrées de temps par projet et service.

SELECT                    
  projects.name as project_name,
  services.name as service_name,
  SUM(minutes) AS minutes 
FROM `time_entries`             
JOIN `projects` ON `projects`.id = `time_entries`.project_id 
JOIN `services` ON `services`.id = `time_entries`.service_id 
GROUP BY 
  time_entries.project_id, 
  time_entries.service_id    
ORDER BY
  max(minutes)   DESC

Il en résultera un tableau comme celui-ci :

+---------------+--------------+---------+
| project_name  | service_name | minutes |
+---------------+--------------+---------+
| Business Card | Consulting   |    4800 |
| Microsite     | Coding       |    3200 |
| Microsite     | Consulting   |    2400 |
| Microsite     | Design       |    2400 |
| Business Card | Design       |     800 |
+---------------+--------------+---------+

Ce que j'essaie d'obtenir, c'est la possibilité de trier par la somme des minutes du projet. Ce n'est pas le projet "Carte de visite" qui devrait être en tête, mais le projet "Microsite", parce qu'il a plus de minutes.

+---------------+--------------+-----------------+---------+
| project_name  | service_name | project_minutes | minutes |
+---------------+--------------+-----------------+---------+
| Microsite     | Coding       |            8000 |    3200 |
| Microsite     | Consulting   |            8000 |    2400 |
| Microsite     | Design       |            8000 |    2400 |
| Business Card | Consulting   |            5600 |    4800 |
| Business Card | Design       |            5600 |     800 |
+---------------+--------------+-----------------+---------+

La seule façon que j'ai trouvée pour obtenir la colonne "project_minutes" est de créer d'abord une table et de la joindre à elle-même. La requête que j'ai trouvée :

DROP TABLE IF EXISTS group2;    
CREATE TABLE group2     SELECT                     
  projects.id as project_id,
  projects.name as project_name,
  services.name as service_name,
  SUM(minutes) AS minutes 
FROM `time_entries`             
JOIN `projects` ON `projects`.id = `time_entries`.project_id 
JOIN `services` ON `services`.id = `time_entries`.service_id 
GROUP BY 
  time_entries.project_id, 
  time_entries.service_id    
ORDER BY
  max(minutes)   DESC
LIMIT 0, 30;

SELECT 
  project_name, service_name, project_minutes, minutes
FROM  
  group2
LEFT JOIN 
  (
    SELECT project_id as project_id, sum(minutes) AS project_minutes
      FROM group2
     GROUP BY project_id         
  ) as group1  on group1.project_id = group2.project_id
ORDER BY 
  project_minutes DESC, 
  minutes DESC;    

Je ne suis même pas capable de créer une table temporaire à cause d'un bug mySQL ( ?): http://www.google.com/search?&q=site:bugs.mysql.com+reopen+temporary+table

Mes questions :

1. Quelle serait la meilleure façon d'obtenir une colonne comme "project_minutes" qui additionne les minutes d'un groupe et ajoute le résultat en tant que colonne supplémentaire ? Existe-t-il une astuce SQL que je ne connais pas ?
2. Si vous ne voyez pas de solution à ma première question, pensez-vous qu'il soit judicieux de créer une table supplémentaire pour chaque requête ? Est-ce plus rapide que de faire cette logique manuellement après dans le code ? Nous utilisons Rails, au cas où cela ferait une différence.

Merci beaucoup pour votre aide !

MISE À JOUR

Merci pour vos réponses jusqu'à présent. Je les résume pour avoir une meilleure vue d'ensemble : http://gist.github.com/553560

Ai-je raison de dire qu'il n'y a pas d'autre moyen que d'interroger la table time_entries une fois pour chaque instruction group by ? Si oui, constatez-vous des problèmes de performance en raison des faits suivants :

1. La table time_entries est de loin celle qui contient le plus grand nombre de lignes (~4 millions).
2. L'utilisateur peut regrouper jusqu'à 6 colonnes. Jetez un coup d'œil à cette capture d'écran : http://dl.dropbox.com/u/732913/time_entries_grouped_by_customer_project_service_user.png

Answer 1

Ce type de produit devrait permettre d'obtenir les résultats escomptés :

SELECT ilv1.date_at, ilv1.project_name, ilv1.service_name, ilv1.minutes
FROM 
( SELECT                             
  te1.date_at,
  p1.name as project_name,
  s1.name as service_name,
  SUM(minutes) AS minutes 
FROM time_entries te1             
LEFT OUTER JOIN projects p1 ON p1.id = te1.project_id 
LEFT OUTER JOIN services s1 ON s1.id = te1.service_id 
GROUP BY 
  te1.project_id, 
  te1.service_id) AS ilv1,
( SELECT                             
  te2.date_at,
  p2.name as project_name,
  SUM(minutes) AS minutes 
FROM time_entries te1             
LEFT OUTER JOIN projects p1 ON p1.id = te1.project_id  
GROUP BY 
  te1.project_id) AS ilv2

WHERE ilv1.date_at=ilv2.date_at AND ilv1.project_name=ilv2.project_name ORDER BY ilv2.minutes ;

(Est-ce vraiment le cas ? vraiment n'a pas besoin de toutes ces jointures externes - elles vont nuire considérablement aux performances)

Il sera probablement beaucoup plus efficace d'utiliser la vue matérialisée sur la base de votre requête originale (et d'une requête en deux passes avec des regroupements différents comme ci-dessus). Mais une solution intermédiaire consisterait à utiliser deux fois la même requête de base et d'en envelopper une dans un bloc de consolidation, par exemple

SELECT ilv1.date_at, ilv1.project_name, ilv1.service_name, ilv1.minutes
FROM 
 (....) ilv1,
 (SELECT ilv3.date_at, ilv3.project_name, sum(ilv3.minutes) as minutes 
  FROM (...copy of ilv1) ilv3
  GROUP BY ilv3.date_at, ilv3.project_name
 ) ilv2
WHERE ilv1.date_at=ilv2.date_at

AND ilv1.project_name=ilv2.project_name ORDER BY ilv2.minutes ;

C.

Answer 2

Je suppose que l'identifiant du projet dans les entrées temporelles est toujours NOT NULL, et que l'identifiant des services peut être nul.

Select t.date, t.project_name, t.service_name, p.minutes as Project_minutes, t.minutes
FROM
(SELECT                             
  time_entries.date_at,
  time_entries.project_Id,
  projects.name as project_name,
  services.name as service_name,
  SUM(minutes) AS minutes 
FROM time_entries             
JOIN projects ON projects.id = time_entries.project_id 
LEFT JOIN services ON services.id = time_entries.service_id 
GROUP BY 
  time_entries.date_at
  time_entries.project_id, 
  time_entries.service_id    
) t
JOIN
  (Select date_at, project_Id, Sum(minutes) minutes
  from time_entries
  group by date_at, project_id) p
ON (p.date_at = t.date_at AND p.project_id = t.project_id)