mysql - 如何使用开始日期和结束日期计算每个月列中的数据数量

Question

我有一个包含类似信息的表，当用户选择开始日期 '2015-01-22' 和结束日期 '2015-07-31' 时，我想提取这样的数据。结果应该如下所示。

Month        Total Quantity    
January: 8
February: 6
March: 0
April: 0
May: 2
June: 18
July: 6

这是一个示例查询和 fiddle

CREATE TABLE orders
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_date DATE,
  product_id INT,
  quantity INT,
  customer_id INT
);
INSERT INTO orders (order_date, product_id, quantity, customer_id)
  VALUES
  ('2015-01-01', 1, 2, 123),
  ('2015-01-06', 3, 6, 123),
  ('2015-02-14', 2, 4, 123),
  ('2015-02-15', 2, 2, 123),
  ('2015-05-16', 1, 1, 456),
  ('2015-05-17', 1, 1, 456),
  ('2015-06-18', 1, 5, 789),
  ('2015-06-18', 3, 7, 123),
  ('2015-06-10', 3, 6, 123),
  ('2015-07-13', 1, 5, 456),
  ('2015-07-14', 1, 1, 456);

http://sqlfiddle.com/#!2/01ac19/1

结果应该是每月订单数量

Answer 1

首先，您 想要 需要的东西被称为“日历表”。毫无疑问，它们是您可以制作的最有用的分析表。它们各自的定义和数据填充各不相同，这里不会介绍，但出于我们的目的，我们将使用以下最小定义:

CREATE TABLE Calendar (calendarDate DATE PRIMARY KEY,
                       year INTEGER,
                       month INTEGER
                       dayOfMonth INTEGER);

...它充满了您期望的数据(插入从您的业务开始到 future 合理时间点的每个日期)。您还需要对此有索引 - 很多索引。

接下来，您需要考虑有关数据库的一些重要事项:如果使用函数输出作为条件，则它们不能使用索引。基本上，如果它不在 SELECT 中子句，使用函数(甚至通过一些隐式转换)会使查询变慢。所以，做类似 YEAR(order_date) 的事情应避免。
那么我们如何按年份或月份等进行汇总呢？通过范围查询。如果数据库有索引，那么查找范围的开头和结尾的成本非常低(并且也可以很好地并行化)。在我们的例子中，范围是 >= startOfMonth至< startOfNextMonth 。我们现在可以构建一个进程内范围表:

SELECT year, month, 
       calendarDate AS monthStart, 
       calendarDate + INTERVAL 1 MONTH AS nextMonthStart
FROM Calendar
WHERE dayOfMonth = 1
      AND calendarDate >= :queryStartRange
      AND calendarDate < :queryEndRange

...其中:表示月初值，留给读者作为练习。

现在，还记得我怎么说“无功能”吗？ calendarDate + INTERVAL 1 MONTH实际上很重要。然而，这并不重要。生成的表非常小(每年只有 12 行!)，好的 RDBMS 可以将内容放入内存中以获得更快的结果(因为仅命中索引将花费更长的时间)。

现在我们有了范围查询表，我们可以将其连接到 Orders (“事实”)表；

SELECT DRange.year, DRange.month, SUM(Orders.quantity) AS total_quantity
FROM (SELECT year, month, 
             calendarDate AS monthStart, 
             calendarDate + INTERVAL 1 MONTH AS nextMonthStart
      FROM Calendar
      WHERE dayOfMonth = 1
            AND calendarDate >= :queryStartRange
            AND calendarDate < :queryEndRange) AS DRange
JOIN Orders
  ON Orders.order_date >= DRange.monthStart
     AND Orders.order_date < DRange.nextMonthStart
GROUP BY DRange.year, DRange.month
ORDER BY DRange.year, DRange.month

Example Fiddle
(有趣的技巧:如果一个月没有订单，则使用 LEFT JOIN 而不是 JOIN 将为您带来空数量行 - 就像示例数据中的三月和四月)

那么这给我们带来了什么？对基础数据进行范围查询访问，这将使查询速度更快。如果由于某种原因 order_date变成时间戳，查询是完全安全的 - 我们将正确获取所有订单，并将它们放在正确的月份中。