How to query data of changes in a column in MySQL

All we need is an easy explanation of the problem, so here it is.

I want to write a complex query for me.

My example records:

|   id   |   seed   |         run_date    |   value   |
|-----------------------------------------------------|
|    1   |   12     | 2019-01-22 09:00:00 |     0     |
|    2   |   14     | 2019-01-22 09:01:00 |     0     |
|    3   |   15     | 2019-01-22 09:02:33 |     1     |
|    4   |   19     | 2019-01-22 09:04:00 |     1     |
|    5   |   22     | 2019-01-22 09:12:00 |     1     |
|    6   |   28     | 2019-01-22 09:16:00 |     0     |
|    7   |   30     | 2019-01-22 09:18:00 |     0     |
|    8   |   36     | 2019-01-22 09:19:00 |     0     |
|    9   |   44     | 2019-01-22 09:20:00 |     1     |
|   10   |   58     | 2019-01-22 09:21:00 |     1     |
|   11   |   80     | 2019-01-22 09:24:12 |     1     |
|   12   |   120    | 2019-01-22 09:25:00 |     1     |
|   13   |   128    | 2019-01-22 09:26:00 |     1     |
|   14   |   136    | 2019-01-22 09:28:00 |     0     |

I want the difference between the ones.

Simply:

min(run_date)   max(run_date)      difference

id=3             id=5                seed2-seed1 = 22-15 = 7

id=9             id=13               128-44 = 84

Where should I start? How should I do? Anybody have any idea?

Mysql version: 5.7.24 – MySQL Community Server (GPL)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Maybe I can update mysql version. If I will update how can I do it?

In MySQL 8+ the query will be

with 
cte0 as ( select *, 
                 lag(value) over (order by run_date) lg, 
                 lead(value) over (order by run_date) ld
          from test),
cte1 as ( select *, 
                 row_number() over (order by run_date) rn
          from cte0
          where value = 1 
            and (lg = 0 or lg is null)),
cte2 as ( select *, 
                 row_number() over (order by run_date) rn
          from cte0
          where value = 1 
            and (ld = 0 or ld is null))
select cte1.id `min(run_date)`, 
       cte2.id `max(run_date)`, 
       cte2.seed - cte1.seed difference
from cte1, 
     cte2
where cte1.rn = cte2.rn;
min(run_date) | max(run_date) | difference
------------: | ------------: | ---------:
            3 |             5 |          7
            9 |            13 |         84

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply