mysql - 从时间序列数据中获取高于阈值的时间量

Question

给定一个这样的表:

`sensor` int(11)
`reading` decimal(5,2)
`timestamp` datetime

表示温度数据并在值发生变化时记录一个条目，我如何找到高于给定值记录的时间量？

因此可能会有一堆读数，例如从 16 到 30，要求是找到 16 以上花费的时间。

Answer 1

两种解决方案

我提出两种解决方案:

• 一个查询，但由于子查询的原因，我不知道它对于大量数据是否非常有效；
• 一个函数。

架构

我使用此表测试了查询和函数:

CREATE TABLE `temperature` (
    `sensor` int(11) NOT NULL,
    `timestamp` datetime NOT NULL,
    `reading` decimal(5, 2) NOT NULL,
    PRIMARY KEY (`sensor`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

还有一些数据:

1;2014-09-18 17:00:00;15.0
1;2014-09-18 18:00:00;16.0
1;2014-09-18 19:00:00;15.0

解决方案 1:查询

SELECT SUM(elapsed_time)
FROM (
    SELECT
        (UNIX_TIMESTAMP((
            SELECT MIN(t2.timestamp)
            FROM temperature t2
            WHERE t1.sensor = t2.sensor AND t1.timestamp < t2.timestamp
        )) - UNIX_TIMESTAMP(t1.timestamp))
        AS elapsed_time
    FROM temperature t1
    WHERE t1.sensor = 1 AND t1.reading >= 16.0
) a;

解决方案 2:函数

该函数返回传感器高于某个值(以秒为单位)的时间量。

它将时间量初始化为 0。然后读取所需传感器的所有读数。如果温度高于您的要求，则会将距离下一次读数的时间加到总和中。最后，它返回超出传感器要求的时间。

DROP FUNCTION IF EXISTS getTotalTimeAbove;
DELIMITER $$
CREATE FUNCTION getTotalTimeAbove(sensor_id INTEGER, above DECIMAL(5, 2))
    RETURNS INTEGER
BEGIN
    DECLARE sum INTEGER DEFAULT 0;
    DECLARE curr_time DATETIME;
    DECLARE next_time DATETIME;
    DECLARE curr_reading INTEGER;
    DECLARE next_reading INTEGER;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR
        SELECT `timestamp`, `reading`
        FROM `temperature`
        WHERE `sensor` = sensor_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO next_time, next_reading;
        IF done THEN
            LEAVE read_loop;
        END IF;
        IF (curr_reading >= above) THEN
            SET sum = sum + (UNIX_TIMESTAMP(next_time) - UNIX_TIMESTAMP(curr_time));
        END IF;
        SET curr_time = next_time;
        SET curr_reading = next_reading;
    END LOOP;
    CLOSE cur;
    RETURN sum;
END
$$
DELIMITER ;

查询及其结果:

> SELECT getTotalTimeAbove(1, 16.0);
3600

奖金

您可以通过以下查询获得传感器的总记录时间:

SELECT UNIX_TIMESTAMP(MAX(`timestamp`)) - UNIX_TIMESTAMP(MIN(`timestamp`))
FROM `temperature`
WHERE `sensor` = 1