mySQL多数据解析查询

Question

我正在尝试根据每单位时间的数据点选择两种不同分辨率的数据。现在我只是运行两个查询并使用 UNION 将它们连接起来。为了获得我想要的数字分辨率，我使用它来实现每分钟 1 个数据点:

GROUP BY UNIX_TIMESTAMP(`datetime`) DIV 60

只是想知道是否有更有效的方法来做到这一点？

SELECT (UNIX_TIMESTAMP(`datetime`)*1000) as `dt`, `value1`, `value2`
    FROM `table`
    WHERE `datetime` BETWEEN '2017-01-01' AND '2018-01-01'
    GROUP BY UNIX_TIMESTAMP(`datetime`) DIV 240
UNION
SELECT (UNIX_TIMESTAMP(`datetime`)*1000) as `dt`, `value1`, `value2`
    FROM `table`
    WHERE `datetime` BETWEEN '2017-01-01' AND '2018-01-01'
    AND TIME(`datetime`) BETWEEN TIME('12:00:00') AND TIME('13:00:00')
    GROUP BY UNIX_TIMESTAMP(`datetime`) DIV 60
ORDER BY `dt` ASC;

Answer 1

这是我遇到的另一种选择。这个似乎更快一点，并返回所选时间的实际值，而不是 mySQL 只是从每个时间组中挑选一个。

在这个表上 datetime 是一个索引。

SELECT a.`datetime`, a.`value1`, a.`value2`
    FROM `table` a
INNER JOIN
(
    SELECT `datetime`
        FROM `table`
        WHERE DATE(`datetime`) BETWEEN '2017-01-01' AND '2018-01-01'
        GROUP BY UNIX_TIMESTAMP(`datetime`) DIV 240
    UNION
    SELECT `datetime`
        FROM `table`
        WHERE DATE(`datetime`) BETWEEN '2017-01-01' AND '2018-01-01'
        AND TIME(`datetime`) BETWEEN '12:00:00' AND '13:00:00'
        GROUP BY UNIX_TIMESTAMP(`datetime`) DIV 60
    ORDER BY `datetime`
) b on a.`datetime` = b.`datetime`;