MySQL - 使用 DATETIME 日期和间隔计算每天 7 天的移动总和

Question

最终尝试获取数量列的每日滚动总和(其中“每日总和”是过去 7 天的总和)，即使该日期范围内没有数据。像这样:

我已经找到并尝试了以下建议，但没有成功。我认为我的问题是因为我将日期存储为日期时间，并且可能有些日子没有条目。通常对 SQL 很陌生，如果我误用了任何术语，我们深表歉意。

在this SQL Fiddle我试过 the technique here它看起来像是使用相关子查询来计算总和，但由于某种原因它实际上不起作用，它只是不断对先前的行求和。

SELECT DATE(purchase_date), SUM(quantity),
   (SELECT SUM(quantity) 
   FROM all_orders as all_orders_past 
   WHERE datediff(all_orders_past.purchase_date, all_orders.purchase_date) <= 7
   ) as mvgSum
FROM all_orders
GROUP BY DATE(purchase_date)
ORDER BY DATE(purchase_date) DESC

在this SQL Fiddle我试过这个technique here它使用自连接并且应该处理日期间隔，但我看到一些非常奇怪的结果，其中 7 天总和为 1，而它肯定应该更多。

SELECT DATE(all_orders.purchase_date), SUM(all_orders.quantity), SUM(all_orders_past.quantity)
FROM all_orders
JOIN all_orders as all_orders_past 
  on all_orders_past.purchase_date between all_orders.purchase_date - 6 and all_orders.purchase_date
GROUP BY 1
ORDER BY DATE(all_orders.purchase_date) DESC;

似乎需要将两者结合起来，但我不确定那会是什么样子。

为了便于引用的数据集:

create table all_orders (
    id INT,
    purchase_date DATETIME,
    quantity VARCHAR(1),
    item_status VARCHAR(9),
    asin VARCHAR(10)
);
insert into all_orders (id, purchase_date, quantity, item_status, asin) values 
    (1, '2018-11-17 15:17:58', 1, 'Shipped', 'B072BPWXPG'),
     (2, '2018-11-16 18:16:48', 1, 'Shipped', 'B072BPWXPG'),
     (3, '2018-11-21 02:13:01', 2, 'Shipped', 'B072BPWABC'),
     (4, '2018-11-08 03:33:16', 3, 'Shipped', 'B072BPWXPG'),
     (5, '2018-11-17 15:39:35', 1, 'Cancelled', 'B072BPWXPG'),
     (6, '2018-11-07 13:51:54', 1, 'Shipped', 'B072BPWABC'),
     (7, '2018-11-20 02:53:52', 2, 'Shipped', 'B072BPWABC'),
     (8, '2018-11-12 20:46:48', 1, 'Shipped', 'B072BPWXPG'),
     (9, '2018-11-11 07:00:45', 3, 'Shipped', 'B072BPWXPG'),
     (10, '2018-11-09 18:19:24', 2, 'Shipped', 'B072BPWXPG'),
     (11, '2018-11-09 03:35:05', 1, 'Shipped', 'B072BPWABC'),
     (12, '2018-11-05 17:02:46', 1, 'Shipped', 'B072BPWABC'),
     (13, '2018-11-15 18:37:25', 2, 'Shipped', 'B072BPWXPG'),
     (14, '2018-11-10 22:32:01', 1, 'Shipped', 'B072BPWABC'),
     (15, '2018-11-21 04:48:22', 3, 'Shipped', 'B072BPWXPG'),
     (16, '2018-11-08 10:39:01', 2, 'Cancelled', 'B072BPWXPG'),
     (17, '2018-11-14 13:23:56', 3, 'Cancelled', 'B072BPWXPG'),
     (18, '2018-11-12 01:19:53', 1, 'Shipped', 'B072BPWXPG'),
     (19, '2018-11-06 02:55:06', 1, 'Shipped', 'B072BPWXPG'),
     (20, '2018-11-18 22:07:01', 1, 'Shipped', 'B072BPWABC'),
     (21, '2018-11-09 12:31:32', 1, 'Shipped', 'B072BPWXPG'),
     (22, '2018-11-17 01:51:43', 2, 'Shipped', 'B072BPWXPG'),
     (23, '2018-11-10 23:40:26', 1, 'Shipped', 'B072BPWABC'),
     (24, '2018-11-18 18:05:37', 1, 'Shipped', 'B072BPWXPG'),
     (25, '2018-11-07 10:40:41', 2, 'Shipped', 'B072BPWXPG'),
     (26, '2018-11-12 20:56:32', 1, 'Shipped', 'B072BPWXPG'),
     (27, '2018-11-07 16:09:02', 1, 'Shipped', 'B072BPWXPG'),
     (28, '2018-11-13 17:28:32', 1, 'Cancelled', 'B072BPWXPG'),
     (29, '2018-11-12 23:14:54', 1, 'Shipped', 'B072BPWXPG'),
     (30, '2018-11-17 06:47:50', 1, 'Shipped', 'B072BPWABC'),
     (31, '2018-11-11 14:12:13', 1, 'Shipped', 'B072BPWXPG'),
     (32, '2018-11-12 23:42:08', 1, 'Shipped', 'B072BPWXPG'),
     (33, '2018-11-13 01:01:27', 3, 'Shipped', 'B072BPWXPG'),
     (34, '2018-11-13 17:02:58', 1, 'Shipped', 'B072BPWXPG'),
     (35, '2018-11-13 18:44:41', 1, 'Cancelled', 'B072BPWABC'),
     (36, '2018-11-04 07:04:12', 2, 'Shipped', 'B072BPWABC'),
     (37, '2018-11-04 04:56:10', 1, 'Shipped', 'B072BPWABC'),
     (38, '2018-11-13 14:55:40', 1, 'Shipped', 'B072BPWXPG'),
     (39, '2018-11-09 00:54:42', 1, 'Shipped', 'B072BPWXPG'),
     (40, '2018-11-21 03:11:28', 3, 'Shipped', 'B072BPWABC'),
     (41, '2018-11-14 08:19:59', 3, 'Shipped', 'B072BPWXPG'),
     (42, '2018-11-04 01:04:29', 3, 'Shipped', 'B072BPWXPG'),
     (43, '2018-11-21 22:49:16', 1, 'Shipped', 'B072BPWABC'),
     (44, '2018-11-09 17:29:48', 1, 'Shipped', 'B072BPWXPG'),
     (45, '2018-11-08 09:05:29', 2, 'Shipped', 'B072BPWXPG'),
     (46, '2018-11-20 23:47:21', 1, 'Cancelled', 'B072BPWXPG'),
     (47, '2018-11-19 14:16:29', 3, 'Shipped', 'B072BPWABC'),
     (48, '2018-11-14 13:05:33', 1, 'Shipped', 'B072BPWXPG'),
     (49, '2018-11-21 20:41:09', 2, 'Cancelled', 'B072BPWXPG'),
     (50, '2018-11-11 18:41:56', 3, 'Shipped', 'B072BPWXPG'),
     (51, '2018-11-11 05:34:07', 1, 'Shipped', 'B072BPWABC'),
     (52, '2018-11-10 06:16:35', 2, 'Shipped', 'B072BPWXPG'),
     (53, '2018-11-09 01:10:37', 1, 'Shipped', 'B072BPWXPG'),
     (54, '2018-11-07 18:01:34', 1, 'Shipped', 'B072BPWXPG'),
     (55, '2018-11-18 13:33:35', 2, 'Shipped', 'B072BPWXPG'),
     (56, '2018-11-12 06:25:56', 1, 'Cancelled', 'B072BPWABC'),
     (57, '2018-11-15 14:13:01', 2, 'Shipped', 'B072BPWABC'),
     (58, '2018-11-04 02:32:48', 2, 'Shipped', 'B072BPWXPG'),
     (59, '2018-11-09 12:48:53', 1, 'Shipped', 'B072BPWXPG'),
     (60, '2018-11-11 02:40:51', 1, 'Cancelled', 'B072BPWXPG'),
     (61, '2018-11-14 15:01:13', 1, 'Shipped', 'B072BPWXPG'),
     (62, '2018-11-12 06:29:30', 2, 'Cancelled', 'B072BPWXPG'),
     (63, '2018-11-04 05:53:07', 2, 'Cancelled', 'B072BPWXPG'),
     (64, '2018-11-18 21:33:31', 1, 'Cancelled', 'B072BPWXPG'),
     (65, '2018-11-16 06:57:31', 1, 'Shipped', 'B072BPWABC'),
     (66, '2018-11-20 11:59:19', 2, 'Shipped', 'B072BPWXPG'),
     (67, '2018-11-12 00:43:23', 1, 'Shipped', 'B072BPWXPG'),
     (68, '2018-11-12 21:58:53', 1, 'Cancelled', 'B072BPWABC'),
     (69, '2018-11-15 18:29:35', 1, 'Shipped', 'B072BPWXPG'),
     (70, '2018-11-15 09:10:53', 3, 'Shipped', 'B072BPWXPG'),
     (71, '2018-11-20 07:01:51', 1, 'Shipped', 'B072BPWABC'),
     (72, '2018-11-06 12:52:26', 1, 'Cancelled', 'B072BPWXPG'),
     (73, '2018-11-19 10:49:55', 1, 'Shipped', 'B072BPWXPG'),
     (74, '2018-11-20 21:26:19', 2, 'Shipped', 'B072BPWABC'),
     (75, '2018-11-10 07:35:08', 2, 'Cancelled', 'B072BPWABC'),
     (76, '2018-11-09 01:32:33', 1, 'Shipped', 'B072BPWXPG'),
     (77, '2018-11-17 07:08:10', 1, 'Shipped', 'B072BPWXPG'),
     (78, '2018-11-07 23:51:43', 1, 'Shipped', 'B072BPWABC'),
     (79, '2018-11-09 20:28:27', 1, 'Shipped', 'B072BPWABC'),
     (80, '2018-11-06 04:01:45', 3, 'Cancelled', 'B072BPWXPG'),
     (81, '2018-11-15 02:33:34', 2, 'Cancelled', 'B072BPWXPG'),
     (82, '2018-11-15 03:55:42', 2, 'Shipped', 'B072BPWXPG'),
     (83, '2018-11-21 23:10:38', 2, 'Shipped', 'B072BPWABC'),
     (84, '2018-11-15 18:11:32', 2, 'Shipped', 'B072BPWXPG'),
     (85, '2018-11-12 09:41:02', 1, 'Shipped', 'B072BPWXPG'),
     (86, '2018-11-13 12:34:34', 1, 'Shipped', 'B072BPWXPG'),
     (87, '2018-11-07 06:10:33', 1, 'Shipped', 'B072BPWXPG'),
     (88, '2018-11-11 02:26:23', 1, 'Cancelled', 'B072BPWXPG'),
     (89, '2018-11-13 04:01:40', 3, 'Shipped', 'B072BPWXPG'),
     (90, '2018-11-17 17:27:18', 3, 'Cancelled', 'B072BPWXPG');

Answer 1

您可以执行以下操作:

SELECT a.purchase_date, a.quantity, sum(p.quantity) as 'past_quantity'
FROM (
  SELECT DATE(purchase_date) as purchase_date , sum(quantity) as quantity
  FROM all_orders
  GROUP BY 1
  UNION
  SELECT cdate, 0
  FROM calendar_dates
  WHERE cdate NOT IN (
    SELECT DATE(purchase_date)
    FROM all_orders
  ) AND cdate between (SELECT MIN(purchase_date) FROM all_orders)
    AND (SELECT MAX(purchase_date) FROM all_orders)
) AS a
  JOIN all_orders p on DATE(p.purchase_date) between 
    DATE_ADD(DATE(a.purchase_date), INTERVAL -6 day) and DATE(a.purchase_date)
WHERE a.purchase_date between '2018-11-10' and '2018-11-12'
GROUP BY a.purchase_date, a.quantity

因此，您要做的是首先获取各个日期(一行/日期)的总和，然后加入它以获得过去几天的总和。

对于丢失的日期，最简单的方法(也是获得最佳性能的方法)是创建一个包含所有日期的日历表(所有日期均由您定义，并将其包含在查询中。

参见 SQL Fiddle