按日期对 MySQL 求和值，将今年与去年进行比较

Question

假设我有一张满桌子的订单。 每天可以有多个订单，总金额不同：

# Create orders table
CREATE TABLE orders (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `total` decimal(9,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

我想为这个表创建一堆随机测试数据，所以我创建了一个存储过程：

# Create test data for orders table
DELIMITER $$
CREATE PROCEDURE prepare_orders_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE j INT DEFAULT FLOOR(RAND() * 10 + 1); # Number of order entries in one day
                                                # randomized between 1 and 10 days
  # Create 2 years worth of data from 2014-01-01 to 2015-12-31
  WHILE i < (365*2) DO
    WHILE j > 0 DO
      INSERT INTO orders(order_date, total) 
      VALUES (DATE_ADD('2014-01-01', INTERVAL 1*i DAY), RAND() * 100);
      SET j = j - 1;
    END WHILE;
    SET j = FLOOR(RAND() * 10 + 1); #Random number of days from 1-10
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
CALL prepare_orders_tlano_data();

现在我有了数据，我想做的是按日期获取订单总额的总和，并将它们与上一年的订单总额总和进行比较。 上一年定义为当前日期之前的 52 周(我想查看由星期几确定的日期，例如：感恩节、黑色星期五、复活节等。 大多数情况下，将要比较的日期是休息一天(例如：2015-04-08与2015-09-08进行比较(。

我已经想出了一个解决方案，但我不认为它是最佳的。 这是因为我基本上是加入同一张桌子，我认为有一种更快的方法可以做到这一点。 这是该解决方案：

# Get totals for dates and dates from 52 weeks previous 
# (same weekday approximately 1 year in the past)
SELECT
    DATE(thisYear.order_date) AS ThisYearOrderDate,
    DATE(lastYear.order_date) AS LastYearOrderDate,
    YEAR(thisYear.order_date) as ThisYear,
    YEAR(lastYear.order_date) as LastYear,
    DAYNAME(thisYear.order_date) AS DayName,
    SUM(thisYear.total) AS ThisYearTotal,
    lastYear.total AS LastYearTotal
FROM orders thisYear 
    INNER JOIN (
        SELECT 
            order_date as order_date,
            SUM(total) as total
        FROM orders
        GROUP BY order_date
    ) lastYear
ON DATE_ADD(thisYear.order_date, INTERVAL -52 WEEK) = lastYear.order_date
GROUP BY thisYear.order_date;

下面是它将返回的内容的示例(结果会因存储过程中的随机数据而异(：

+--------------+--------------+----------+---------------+---------------+
| ThisYearDate | LastYearDate | DayName  | ThisYearTotal | LastYearTotal |
+--------------+--------------+----------+---------------+---------------+
| 2015-01-01   | 2014-01-02   | Thursday | 363.56        | 11.26         |
| 2015-01-02   | 2014-01-03   | Friday   | 137.62        | 189.76        |
| 2015-01-03   | 2014-01-04   | Saturday | 399.40        | 257.42        |
| 2015-01-04   | 2014-01-05   | Sunday   | 502.80        | 336.38        |
| 2015-01-05   | 2014-01-06   | Monday   | 107.59        | 466.79        |
+--------------+--------------+----------+---------------+---------------+

有人能想到不同的方法来实现这一目标吗？

编辑：
我看了一下@Used_By_Already的解决方案，并重写了一下，使其输出与我的表相同：

SELECT
    (CASE WHEN order_date < '2014-12-31'
         THEN DATE_ADD(order_date, INTERVAL 52 WEEK)
         ELSE order_date END) as ThisYearOrderDate,
    (CASE WHEN order_date < '2014-12-31'
         THEN order_date
         ELSE DATE_ADD(order_date, INTERVAL -52 WEEK) END) as LastYearOrderDate,
     DAYNAME(order_date) DayName,
     SUM(CASE WHEN order_date >= '2014-12-31'
         THEN total ELSE 0 END) as ThisYearTotal,
     SUM(CASE WHEN order_date < '2014-12-31'
         THEN total ELSE 0 END) as LastYearTotal
FROM orders
GROUP BY ThisYearOrderDate;

这运行良好，运行速度比我的解决方案快。 我唯一担心的是它需要这些日期过滤器，并且它只能在一年的范围内工作，因为 ThisYearOrderDate 和 LastYearOrderDate 之间的任何重叠都会导致一些误导性条目：

+--------------+--------------+-----------+---------------+---------------+
| ThisYearDate | LastYearDate | DayName   | ThisYearTotal | LastYearTotal |
+--------------+--------------+-----------+---------------+---------------+
| 2014-12-31   | 2014-01-01   | Wednesday | 18.01         | 253.56        |
| 2015-01-01   | 2014-01-02   | Thursday  | 363.56        | 11.26         |
| ...          | ...          | ...       | ...           | ...           |
| 2015-12-30   | 2014-12-31   | Wednesday | 380.71        | 0.00          |
| 2015-12-31   | 2015-01-01   | Thursday  | 400.36        | 0.00          |
+--------------+--------------+-----------+---------------+---------------+

Answer 1

在日期上使用大小写表达式并按此分组，并对两个总和(条件聚合(使用类似的大小写表达式。

select
      case when order_date < '2016-01-01' then order_date + INTERVAL 52 WEEKS else order_date end as an_order_date_pair
    , SUM(case then order_date  < '2016-01-01' then total end) as prev_yr
    , SUM(case then order_date >= '2016-01-01' then total end) as this_yr
from orders
group by
      case when order_date < '2016-01-01' then order_date + INTERVAL 52 WEEKS else order_date end

我无法在 sqlfiddle 的 MySQL 下运行它，因此以下内容是使用 Postgres 完成的，但您将看到语法与此需求非常相似：

SQL Fiddle PostgreSQL 9.3 Schema setup：

CREATE TABLE Orders
    (order_date timestamp , total decimal(12,3))
;
INSERT INTO Orders
    (order_date, total)
VALUES
    ('2014-01-02 00:00:00', 11.26),
    ('2014-01-03 00:00:00', 189.76),
    ('2014-01-04 00:00:00', 257.42),
    ('2014-01-05 00:00:00', 336.38),
    ('2014-01-06 00:00:00', 466.79),
    ('2015-01-01 00:00:00', 363.56),
    ('2015-01-02 00:00:00', 137.62),
    ('2015-01-03 00:00:00', 399.40),
    ('2015-01-04 00:00:00', 502.80),
    ('2015-01-05 00:00:00', 107.59)
;

查询 1：

select
      case when order_date < '2015-01-01'
          then order_date + INTERVAL '52 WEEKS'
          else order_date
      end as an_order_date_pair
    , SUM(case when order_date < '2015-01-01'
          then total else 0
       end) as prev_yr
    , SUM(case when order_date >= '2015-01-01'
          then total else 0
       end) as this_yr
from orders
group by
      case when order_date < '2015-01-01'
          then order_date + INTERVAL '52 WEEKS'
          else order_date
      end

结果：

|        an_order_date_pair | prev_yr | this_yr |
|---------------------------|---------|---------|
| January, 01 2015 00:00:00 |   11.26 |  363.56 |
| January, 05 2015 00:00:00 |  466.79 |  107.59 |
| January, 02 2015 00:00:00 |  189.76 |  137.62 |
| January, 04 2015 00:00:00 |  336.38 |   502.8 |
| January, 03 2015 00:00:00 |  257.42 |   399.4 |