sql - 如何正确连接表并使用 group by 来汇总 MySQL 中的数据？

Question

我在 MySQL 中工作，在构建一个汇总两个表中的列的查询时遇到了一些麻烦。我想比较每天的请求数量，一个包含每小时记录的表和一个包含每日聚合的表，每天。理想情况下，每个的总和是相同的。

这是架构:

每小时表:

CREATE TABLE requests_hourly (
 customer_id INT,
 date DATETIME,
 requests BIGINT,
 req_type SMALLINT );

每日表

CREATE TABLE requests_daily (
 customer_id INT,
 date DATE,
 requests BIGINT,
 req_type SMALLINT );

2010 年 6 月两个表的 req_type 无法使用 SQL 获取所有请求

SELECT
 SUM(h.requests),
 SUM(d.requests),
 h.req_type
FROM requests_hourly h
LEFT OUTER JOIN requests_daily d ON d.req_type = h.req_type
WHERE h.date >= '2010-06-01 00:00:00'
 AND h.date < '2010-07-01 00:00:00'
 AND d.date >= '2010-06-01 00:00:00'
 AND d.date < '2010-07-01 00:00:00'
GROUP BY h.req_type;

我感觉错误出在 JOIN 中。预先感谢您的帮助!

回答

我感谢 Peter 的回答，但确实需要稍作修改。所以这是 MySQL SQL 代码:

SELECT *
FROM
  (SELECT SUM(requests) AS 'Daily Request Sum', req_type
   FROM requests_daily
   WHERE date BETWEEN '2010-06-01 00:00:00' AND '2010-07-01 00:00:00'
   GROUP BY req_type, date) d
  INNER JOIN
  (SELECT SUM(requests) AS 'Hourly Request Sum', req_type
   FROM requests_hourly
   WHERE date BETWEEN '2010-06-01 00:00:00' AND '2010-07-01 00:00:00'
   GROUP BY req_type, DATE(date)) h
USING (req_type, date)

Answer 1

根据我的查询分析器，这是迄今为止最快/最有效的方法:

SELECT *
FROM
  (SELECT SUM(requests) AS 'Daily Request Sum', req_type
   FROM requests_daily
   WHERE date BETWEEN '2010-06-01 00:00:00' AND '2010-07-01 00:00:00'
   GROUP BY req_type)
  INNER JOIN
  (SELECT SUM(requests) AS 'Hourly Request Sum', req_type
   FROM requests_hourly
   WHERE date BETWEEN '2010-06-01 00:00:00' AND '2010-07-01 00:00:00'
   GROUP BY req_type)
USING (req_type, date)

它遵循简单的逻辑，即通过限制大小并在操作发生之前预先安排要连接的表中的索引，可以显着减少通过将两个表的索引 (req_type) 相互匹配而导致的相对“昂贵”的窃听，尤其是因为您没有唯一索引 (req_type)，这将强制数据库执行搜索而不是查找。

希望这对您有所帮助。