分组为最接近的小时间隔 30 分钟-解网

问：

您好，我正在尝试将间隔分组 30 分钟到最接近的小时。我能够按 30 分钟分组，但我无法使最近一个小时的逻辑工作。我附上了一个快照，您可以从中了解我想要什么。

  select SUBSTRING_INDEX(GROUP_CONCAT(CAST(price AS CHAR) ORDER BY `timestamp`), ',', 1 ) as open,
                    max(price) high,
                    min(price) low,
                    SUBSTRING_INDEX(GROUP_CONCAT(CAST(price AS CHAR) ORDER BY `timestamp` desc), ',', 1 ) as close,
                    coinrace.watch_list.symbol,
                    timestamp
                    from coinrace.watch_quote
                    join coinrace.watch_list on coinrace.watch_list.watch_id = coinrace.watch_quote.watch_id
                    where (`timestamp` between '2015-12-03' and '2015-12-10')
                    and coinrace.watch_quote.serial_number = 1
                    and coinrace.watch_quote.BuyOrSell='buy'
                    and coinrace.watch_list.symbol='MCOEUR'
                    group by  UNIX_TIMESTAMP(timestamp) div (30*60)

sql mysql-workbench

不要与日期/时间/时间戳值一起使用 - 有时会得到错误的结果。您应该改用独占上限（）。您可以构造一个用于分组的查询内虚拟表。不应包含不属于 - 您将获得不确定的结果。我不确定，但每组最好的 n 个选项之一可能更适合 /。BETWEEN<GROUP BYopenclose

答：

0赞 Vojtěch Dohnal 12/18/2015 #1

也许您不仅应该选择，还应该选择您按（）分组的值，只是您还应该将其转换回可读的日期时间值：timestampUNIX_TIMESTAMP(timestamp) div 1800

from_unixtime((UNIX_TIMESTAMP(timestamp) div 1800)*1800)

因此，查询将如下所示：

  select SUBSTRING_INDEX(GROUP_CONCAT(CAST(price AS CHAR) ORDER BY `timestamp`), ',', 1 ) as open,
                    max(price) high,
                    min(price) low,
                    SUBSTRING_INDEX(GROUP_CONCAT(CAST(price AS CHAR) ORDER BY `timestamp` desc), ',', 1 ) as close,
                    coinrace.watch_list.symbol,
                    from_unixtime((UNIX_TIMESTAMP(timestamp) div 1800)*1800) as timestamp
                    from coinrace.watch_quote
                    join coinrace.watch_list on coinrace.watch_list.watch_id = coinrace.watch_quote.watch_id
                    where (`timestamp` between '2015-12-03' and '2015-12-10')
                    and coinrace.watch_quote.serial_number = 1
                    and coinrace.watch_quote.BuyOrSell='buy'
                    and coinrace.watch_list.symbol='MCOEUR'
                    group by  UNIX_TIMESTAMP(timestamp) div (30*60)

分组为最接近的小时间隔 30 分钟

Grouping into interval of 30 minutes to its nearest hour

评论

评论