0

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)

  • Specify MySQL version. – Akina Jan 22 at 11:43
  • 5.7.24 - MySQL Community Server (GPL) @Akina – Tota1907 Jan 22 at 11:46
  • It's bad, no window functions... you will need 2 subqueries used 3 table copies each for to find 1's ranges... I recommend to use stored procedure with cursor and temporary table - it will be less expensible. – Akina Jan 22 at 11:58
  • Maybe I can update mysql version. If I will update how can I do it? @Akina – Tota1907 Jan 22 at 12:19
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.