mysql - 选择特定时间段的烛台数据(最大值、最小值、开盘价、收盘价)

Question

我正在处理烛台图表数据。我有一个数据库表(MySql)

created                  value
2017-09-25 17:55:04    3322.09
2017-09-25 18:00:03    3317.49
2017-09-25 18:05:02    3316.12
2017-09-25 18:10:03    3325.16
2017-09-25 18:15:04    3326.01
2017-09-25 18:20:03    3330.49
2017-09-25 18:25:04    3331.49
2017-09-25 18:30:04    3335.24
2017-09-25 18:35:03    3347.13
2017-09-25 18:40:03    3343.21
2017-09-25 18:45:05    3341.78
2017-09-25 18:50:03    3339.64
2017-09-25 18:55:04    3336.12
2017-09-25 19:00:04    3327.73
2017-09-25 19:05:03    3327.96
2017-09-25 19:10:04    3325.93

我正在尝试为任何时间段(例如 15 分钟或每 4 小时、每 6 天等)选择最大值、最小值、开盘值、收盘值

到目前为止我有

SELECT p.created, HOUR(`DATE`), MAX(p.value), MIN(p.value) 
FROM price 
GROUP BY DATE(`DATE`),HOUR(`DATE`)

没那么复杂。它为我提供每小时的最大值和最小值，因此对于分钟和“完整”时段(如天、周、月)，我可以使用 MONTH、DAY 等函数。

知道如何针对任何时间段(比如 28 分钟或 31 小时等)构建查询吗？此外，这适用于最大值和最小值，但我还需要时间段的开盘值(第一个)和收盘值(最后一个)。

Answer 1

要对任意时间段进行分组，您可以采用 created 列的 UNIX 时间值(即自 UNIX 纪元以来的秒数)并除以所需时间段的大小，只取结果商的整数部分(并丢弃余数)。因此，每 28 分钟分组一次:

SELECT   FROM_UNIXTIME(28*60 * (UNIX_TIMESTAMP(created) DIV (28*60))) grp_id
         MIN(value)    min_value,
         MAX(value)    max_value,
         MIN(created)  min_created,
         MAX(created)  max_created
FROM     price
GROUP BY grp_id

您会注意到我还选择了 min_created 和 max_created，它们会产生每组中第一个和最后一个值的时间。然后您可以再次将结果连接回 price 表以获得相关记录的 value(这本质上是“groupwise maximum/minimum”问题的一个实例) :

SELECT t.grp_id,
       t.min_value,
       t.max_value,
       price_min.value first_value,
       price_max.value last_value
FROM (
  SELECT   FROM_UNIXTIME(28*60 * (UNIX_TIMESTAMP(created) DIV (28*60))) grp_id
           MIN(value)    min_value,
           MAX(value)    max_value,
           MIN(created)  min_created,
           MAX(created)  max_created
  FROM     price
  GROUP BY grp_id
) t JOIN price price_min ON price_min.created = t.min_created
    JOIN price price_max ON price_max.created = t.max_created

查看sqlfiddle .