mysql - 如何使用 MySQL 扩展基于时间的聚合

Question

我们有一个包含 nr_of_items 和 date_created 时间戳的购买表。

我正试图找到一种方法，以可扩展的方式获得每月购买的总 nr_of_items 。客户端使用它来绘制图表，因此它应该能够查询至少 24 个月。目前我正在做常规的分组方式:

SELECT SUM(nr_of_items) FROM purchases
GROUP BY DATE(CONVERT_TZ(date_created,'UTC', "Europe/Amsterdam"))

但我看到这解释了

Using index condition; Using temporary; Using filesort

...这意味着它的可扩展性不是很好，假设这个表可能每月增长到数千行。

我研究了一种通过 month_index 存储每月总计的具体化方法，但客户可能配置了不同的时区，因此一个月不能映射到绝对时间戳，并且具体化到所有可能的时区感觉不太好想法(TZ 有时也会改变)。

这可以用 MySQL 解决吗？如何？或者我可以使用专门的基于时间的数据库吗？

Answer 1

你可以试试 Axibase Time Series Database它针对存储和分析时间序列数据进行了优化。它内置了对各种日历聚合的支持:

SELECT date_format(time, "yyyy-MMM-dd", "Europe/Amsterdam"),
  SUM(value), COUNT(value)
FROM "purchases"
  WHERE datetime >= CURRENT_YEAR 
    AND datetime < CURRENT_MONTH
GROUP BY PERIOD(1 MONTH, "Europe/Amsterdam")

更多详情:

• GROUP BY PERIOD聚合。
• JDBC Driver .

披露:我为 Axibase 工作。