mysql - 如何按小时对结果进行分组，其中包括没有记录的小时数

Question

我有一个包含所有订单的表格，我正在尝试按小时分隔订单。如果特定小时没有记录，查询将忽略该小时，但我想要实现的是报告该小时的“0”。

我还加入了一个包含所有时间的临时表。

SELECT sum(orders.price), hour(orders.time) as hour
FROM orders 
RIGHT JOIN dummy_time as dummy
ON hour(orders.time) = dummy.time
WHERE state = 1
AND (date(orders.time) = '2014-06-17' or orders.time is null)
GROUP BY hour

您可以在SQLFiddle中查看我的查询

Answer 1

要获取 dummy_time 中的所有行，请将您的条件从 WHERE 移至您的 RIGHT JOIN。此外，从 dummy.time 中选择小时，这样您将获得所有小时。

使用 COALESCE 获取订单没有记录的 0 值。

SELECT COALESCE(sum(orders.price),0), dummy.time as hour
FROM orders 
RIGHT JOIN dummy_time as dummy
ON hour(orders.time) = dummy.time
AND orders.state = 1
AND orders.time BETWEEN '2014-06-17 00:00:00' AND '2014-06-17 23:59:59'
GROUP BY dummy.time

http://www.sqlfiddle.com/#!2/c7adb/2

下面查询的查询计划看起来比上面的更糟糕，但是因为您报告说 JOIN 似乎是缓慢的主要原因，所以值得一试。下面的查询在执行 JOIN 之前减少了行集。

SELECT 
  COALESCE(t1.orders_sum,0),
  t2.time
FROM
(
  SELECT 
    sum(orders.price) orders_sum, 
    hour(orders.time) orders_hour
  FROM orders 
  WHERE orders.state = 1
  AND orders.time BETWEEN '2014-06-17 00:00:00' AND '2014-06-17 23:59:59'
  GROUP BY hour(orders.time)
) t1 RIGHT JOIN dummy_time t2 ON t1.orders_hour = t2.time

http://www.sqlfiddle.com/#!2/0775b/1

此外，请确保您的表已编入索引

CREATE INDEX test_index1 ON orders (state,time);
CREATE INDEX test_index2 ON dummy_time (time);