Progressively Calculate a Time Difference Between Multiple MySQL Rows depending on one of the fields in the row

user_2162712 0
Viewed 77

If I have a sql table where each row has a start time and end time and these times progressively

CREATE TABLE mytable
    (`id` int, `start` decimal(6,3),`end` decimal(6,3),`include` varchar(20))
;
    
INSERT INTO mytable
    (`id`, `start`, `end`, `include`)
VALUES
    ( 1 , '0.0' , '2.0' , 'yes'),
    ( 2 , '2.1' , '5.0' , 'no'),
    ( 3 , '5.001' , '8.0' , 'yes'),
    ( 4 , '8.001' , '10.2' , 'yes'),
    ( 5 , '10.3' , '12.5' , 'yes'),
    ( 6 , '12.5' , '15.6' , 'no'),
    ( 7 , '15.7' , '20.3' , 'yes'),
    ( 8 , '20.4' , '22.4' , 'yes'),
    ( 9 , '22.5' , '34.5' , 'yes'),
    ( 10 , '34.6' , '45.5' , 'no')
;

I would like to progressively calculate the length between the first start time and the last end time within each group of rows that have INCLUDE = 'YES' and Restart the calculation once one of the rows has an INCLUDE = "NO" (and exclude the NO rows).

So essentially GROUP BY the include field and possibly subtracting the max(end) from min(start) within that GROUP.

While I know I could do this programmatically would like to see if it is possible to do so within a query.

ID     start     end     include
1      0.0     2.0        yes
2      2.1     5.0        no
3      5.1     8.0        yes
4      8.1     10.2       yes
5      10.3    12.4       yes
6      12.5    15.6       no
7      15.7    20.3       yes
8      20.4    22.4       yes
9      22.5    34.5       yes
10     34.6    45.5       no

So, the results in this case after my query is run should be:

2.0 (2.0 - 0.00 in Row 1)
7.3 (12.4 in the END column in row 5 minus 5.1 in the START column in row 3)
18.8 (34.5 in the end column in row 9 minus 15.7 in the START column in row 7)

Any row that has a NO in the INCLUDE column will reset things.

Here is something I have tried:

SELECT (Max(end) - Min(start)) AS time FROM mytable WHERE include = 'yes' GROUP BY include ORDER BY end ASC

but this just gives me a single value.

https://www.db-fiddle.com/f/rTix3jp1KjhiPwABzWyHvv/0

mysql
1 Answers

This is a standard 'gaps-and-islands' problem; I didn't answer sooner because there's a clique of regulars who normally take on this kind of problem.

Anyway, something like this should work - although the 'regulars' can probably write it more efficiently(/correctly) than me...

DROP TABLE IF EXISTS mytable;

CREATE TABLE mytable
    (`id` int, `start` decimal(6,3),`end` decimal(6,3),`include` varchar(20))
;
    
INSERT INTO mytable
    (`id`, `start`, `end`, `include`)
VALUES
    (  1 ,  0.0 ,  2.0 , 'yes'),
    (  2 ,  2.1 ,  5.0 , 'no'),
    (  3 ,  5.1 ,  8.0 , 'yes'),
    (  4 ,  8.1 , 10.2 , 'yes'),
    (  5 , 10.3 , 12.5 , 'yes'),
    (  6 , 12.5 , 15.6 , 'no'),
    (  7 , 15.7 , 20.3 , 'yes'),
    (  8 , 20.4 , 22.4 , 'yes'),
    (  9 , 22.5 , 34.5 , 'yes'),
    ( 10 , 34.6 , 45.5 , 'no')
;

SELECT MAX(end) - MIN(start) result
  FROM
     ( SELECT *, ROW_NUMBER() OVER (ORDER BY include, id) - ROW_NUMBER() OVER (ORDER BY id) n FROM mytable ) a
 WHERE include = 'yes'
 GROUP
    BY n;
    
+--------+
| result |
+--------+
| 18.800 |
|  7.400 |
|  2.000 |
+--------+
user_15296730
Related