mysql - 获取按 12 周时间段分组的数据

Question

我有一个包含订单数据的表:

CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `price` decimal(6,3) unsigned NOT NULL,
  `created` datetime DEFAULT NULL,
  `paid` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

我需要获取 12 周内订单的价格总和，其中第一个周期从支付第一个订单的一周开始，下一个周期比前一个周期晚一周开始(这样可以比较中的数据) 12 周周期)。所以最终一定会有像 1-12、2-13 等这样的周期。

重要细节:我无法在查询中使用变量，因为此查询将在 BI 工具中使用，其中使用变量的查询会出现意外行为。

Answer 1

下一步是不是:

SELECT `periods`.*, SUM(`orders`.`price`) AS `revenue`
FROM (SELECT DISTINCT FROM_DAYS(TO_DAYS(`paid`) - MOD(TO_DAYS(`paid`) -1, 7)) AS `period_start`,
      (FROM_DAYS(TO_DAYS(`paid`) - MOD(TO_DAYS(`paid`) -1, 7)) + INTERVAL 12 WEEK) AS `period_end`
      FROM `orders`
) AS `periods`
LEFT JOIN `orders` ON DATE(`orders`.`paid`) BETWEEN `periods`.`period_start` AND `periods`.`period_end`
GROUP BY `periods`.`period_end`

小解释:首先我在子查询中定义12周的时间段，使用相同的表数据。通过这样做

DISTINCT FROM_DAYS(TO_DAYS(`paid`) - MOD(TO_DAYS(`paid`) -1, 7))

我将付费值移至周初。

有了这个

(FROM_DAYS(TO_DAYS(`paid`) - MOD(TO_DAYS(`paid`) -1, 7)) + INTERVAL 12 WEEK)

我在日期后添加 12 周表示一周的开始。

最终我得到这样的结果:

| period_start | period_end |
| 2015-07-19   | 2015-10-11 |
| 2015-07-26   | 2015-10-18 |

然后，我只需在期末连接订单表和组数据即可。

看起来它满足了我的需要。