mysql - 多个 GROUP BY 并按 SUM'd 组值排序

Question

我正在为我们的时间跟踪应用编写报告。每次条目都与一个项目和一项服务相关。这是一个按项目和服务对时间条目进行分组的简化查询。

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

这将导致这样的表格:

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

虽然我尝试实现的是按 SUM 的项目分钟数排序的可能性。不应将 »Business Card« 项目放在首位，而应将 »Microsite« 项目放在首位，因为它有更多的时间。

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

我发现获取列 »project_minutes« 的唯一方法是先创建一个表并将其与自身连接。我提出的查询:

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;    

由于 mySQL 错误(？)，我什至无法创建临时表: http://www.google.com/search?&q=site:bugs.mysql.com+reopen+temporary+table

我的问题:

1. 实现像 »project_minutes« 这样的列的最佳方法是什么，该列汇总一组分钟并将结果添加为额外的列？是否有我不知道的巧妙的 SQL 技巧？
2. 如果您没有找到解决我的第一个问题的方法，您认为为每个查询创建一个额外的表是否有意义？它比在代码中手动执行此逻辑更快吗？我们使用 Rails，以防万一。

非常感谢您的帮助!

更新

感谢您到目前为止的回复。我将它们总结为要点以获得更好的概述: http://gist.github.com/553560

我说的对吗，除了每个组按语句查询一次 time_entries 表之外没有其他方法吗？如果是，您是否因为以下事实而发现性能问题:

1. 表 time_entries 是迄今为止行数最多的表(约 400 万)
2. 用户最多可以按 6 列进行分组。看看这个截图: http://dl.dropbox.com/u/732913/time_entries_grouped_by_customer_project_service_user.png

Answer 1

像这样的事情应该做你想做的:

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

哪里 ilv1.date_at=ilv2.date_at AND ilv1.project_name=ilv2.project_name 按 ilv2.minutes 排序；

(你真的，真的需要所有这些外连接——它们会严重影响性能)

使用基于原始查询的物化 View (以及如上所述的具有不同分组的两次传递查询)可能会更有效。但是中途之家可能会两次使用相同的查询基础查询并将一个包含在合并 block 中，例如

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 按 ilv2.minutes 排序；

C.