Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Is there a way to analyze a range of values in a specifc timeframe?

I have a table in MySQL 8 called "temperature" with the fields IDDevice, Data, Temperature.
I need to analyze each device and return a list of temperatures that have been out of the range 2-8 degrees in a 20 minutes time frame.
Also, out of range must be consecutive, meaning that if inside that range I have a correct temperature I do not want to receive those rows.
Can you help me write the query?
I tried something like this but it’s not working correctly:

SELECT iddispositivo, Data, Temperatura, is_out_of_range, consecutive_out_of_range
  FROM (SELECT iddispositivo, data, temperatura,
               IF(Temperatura < 2 OR Temperatura > 8, 1, 0) AS is_out_of_range,
               SUM(IF(Temperatura < 2 OR Temperatura > 8, 1, 0)) OVER (PARTITION BY iddispositivo ORDER BY Data) AS consecutive_out_of_range
          FROM temperature
         WHERE iddispositivo = 1750
           AND data >= date_sub(now(), interval 1 day)
       ) AS temp_data
 WHERE 1 = 1
   AND consecutive_out_of_range > (SELECT COUNT(*) 
                                     FROM temperature AS t2
                                    WHERE t2.iddispositivo = temp_data.iddispositivo
                                      AND data >= date_sub(now(), interval 1 day)
                                      AND t2.Data BETWEEN DATE_SUB(temp_data.Data, INTERVAL 20 MINUTE) AND temp_data.Data)
   AND is_out_of_range = 1;

Thanks

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    In case anyone has the same problem, this query seems to work correctly:

    WITH FilteredData AS (
    SELECT 
        iddispositivo, 
        Data, 
        Temperatura,
        CASE 
            WHEN Temperatura < 2 OR Temperatura > 8 THEN 1
            ELSE 0
        END AS is_out_of_range
    FROM 
        temperature
    WHERE 
        iddispositivo = 1750
        AND Data >= DATE_SUB(NOW(), INTERVAL 1 DAY)
),
RankedData AS (
    SELECT 
        iddispositivo, 
        Data, 
        Temperatura,
        is_out_of_range,
        ROW_NUMBER() OVER (ORDER BY Data) -
        ROW_NUMBER() OVER (PARTITION BY is_out_of_range ORDER BY Data) AS grp
    FROM 
        FilteredData
),
ConsecutiveOutOfRange AS (
    SELECT 
        iddispositivo, 
        MIN(Data) AS start_time, 
        MAX(Data) AS end_time, 
        COUNT(*) AS num_readings,
        TIMESTAMPDIFF(MINUTE, MIN(Data), MAX(Data)) AS duration
    FROM 
        RankedData
    WHERE 
        is_out_of_range = 1
    GROUP BY 
        iddispositivo, grp
)
SELECT 
    iddispositivo, 
    start_time, 
    end_time, 
    duration
FROM 
    ConsecutiveOutOfRange
WHERE 
    duration >= 20
ORDER BY 
    start_time;

  2. 0

    in MSSql its possible using

    GROUP BY
    TIMESTAMPDIFF(MINUTE, ‘1970-01-01’, TimeValue) – TIMESTAMPDIFF(MINUTE, ‘1970-01-01’, LAG(TimeValue) OVER (PARTITION BY IDDevice ORDER BY Data)) – 1 > 20

    In mySQL, the best option would be to write a stored procedure

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search