Finding ranges of continous values-CodePudding

With a MySQL table with timestamps.

|Timestamps             |
|-----------------------|
|2021-08-01 14:00:00.000|
|2021-08-01 14:00:00.100|
|2021-08-01 14:00:00.200|
|2021-08-01 14:00:00.300|
|2021-08-01 14:00:00.600|
|2021-08-01 14:00:00.700|
|2021-08-01 14:00:00.800|
|2021-08-01 14:00:01.000|

I would like to get the time intervals of the continuous data. Continuous is defined by a frequency, in this example, it can be 10Hz The desired result would be

|Start                   | End                    |
|------------------------|------------------------|
|2021-08-01 14:00:00.000 | 2021-08-01 14:00:00.300|
|2021-08-01 14:00:00.600 | 2021-08-01 14:00:00.800|

I am using MySQL version 5.7.35 and cant use WITH and other functions. Can this be done fast? There are ca. 100000 elements per table for now.

CodePudding user response：

Use :

CREATE TABLE test_tbl (
              my_data timestamp(3)  
                       );

INSERT INTO test_tbl VALUES
('2021-08-01 14:00:00.000'),
('2021-08-01 14:00:00.100'),
('2021-08-01 14:00:00.200'),
('2021-08-01 14:00:00.300'),
('2021-08-01 14:00:00.600'),
('2021-08-01 14:00:00.700'),
('2021-08-01 14:00:00.800'),
('2021-08-01 14:00:01.000');


SELECT 
       MIN(my_data) start,
       MAX(my_data) end 
  FROM 
     ( SELECT *
            , CASE WHEN right(my_data,3) = @prev 100 THEN @i:=@i ELSE @i:=@i 100 END row_num
            , @prev:=right(my_data,3) prev
         FROM test_tbl
            , (SELECT @prev:= null,@i:=0) vars 
              ORDER BY row_num
     ) x 
 GROUP  BY row_num;

Demo:https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/98

CodePudding user response：

Insert speed

Rule of Thumb -- "Don't worry about performance if you are doing less than 100 writes per second." (You are asking about only 10/sec, correct?) (There are techniques to get past 100.)

Non-windowing solution (pre 8.0 or 10.2)

-- Sample data

CREATE TABLE gaps (
    ts timestamp(3),
    PRIMARY KEY(ts)    -- Some index on ts is important here
);

INSERT INTO gaps VALUES
('2021-08-01 14:00:00.000'),
('2021-08-01 14:00:00.100'),
('2021-08-01 14:00:00.200'),
('2021-08-01 14:00:00.300'),
('2021-08-01 14:00:00.600'),
('2021-08-01 14:00:00.700'),
('2021-08-01 14:00:00.800'),
('2021-08-01 14:00:01.000'),  -- By itself
('2021-08-01 14:00:01.300');

-- Add a sequence number (via AUTO_INCREMENT); Caveat: This depends on auto_increment_increment = 1

DROP TEMPORARY TABLE IF EXISTS gaps2;
CREATE TEMPORARY TABLE gaps2 (
    id INT AUTO_INCREMENT,
    PRIMARY KEY(id) )
SELECT  val
    FROM (
        ( SELECT a.ts as val
            FROM gaps AS a
            LEFT JOIN gaps AS b  ON a.ts = b.ts   interval 0.1 second
            WHERE b.ts IS NULL
        )
        UNION ALL
        ( SELECT  b.ts as val
            FROM gaps AS b
            LEFT JOIN gaps AS a  ON b.ts = a.ts - interval 0.1 second
            WHERE a.ts IS NULL
        )
        ORDER BY val
         ) AS cd;
         
select * from gaps2;
 ---- ------------------------- 
| id | val                     |
 ---- ------------------------- 
|  1 | 2021-08-01 14:00:00.000 |
|  2 | 2021-08-01 14:00:00.300 |
|  3 | 2021-08-01 14:00:00.600 |
|  4 | 2021-08-01 14:00:00.800 |
|  5 | 2021-08-01 14:00:01.000 |
|  6 | 2021-08-01 14:00:01.000 |
|  7 | 2021-08-01 14:00:01.300 |
|  8 | 2021-08-01 14:00:01.300 |
 ---- -------------------------

-- Pivot:

SELECT  CEIL(id/2) AS pair,
        MIN(val) AS 'run_start',
        MAX(val) AS 'run_end'
    FROM gaps2
    GROUP BY pair
    ORDER BY pair;

 ------ ------------------------- ------------------------- 
| pair | run_start               | run_end                 |
 ------ ------------------------- ------------------------- 
|    1 | 2021-08-01 14:00:00.000 | 2021-08-01 14:00:00.300 |
|    2 | 2021-08-01 14:00:00.600 | 2021-08-01 14:00:00.800 |
|    3 | 2021-08-01 14:00:01.000 | 2021-08-01 14:00:01.000 |
|    4 | 2021-08-01 14:00:01.300 | 2021-08-01 14:00:01.300 |
 ------ ------------------------- -------------------------