MySQL: How to count the number of deviations?

Question

I've got a table with two columns: timestamp and integer value (let's name these date and value). The second value gets swings between 0 and ~290 and then goes back to 0, so on the chart it looks like a waves (details are viewed on screenshot).

How do I count the number of these drops to 0 in the set?

Expected result is a number of drops from high point to zero (as you can see on the picture). MySQL version is 5.5.37-0+wheezy1.

Data sample:

data                                  value
2017-10-10 00:00:00                   270
2017-10-10 00:00:01                   270
2017-10-10 00:00:02                   270
2017-10-10 00:00:03                   265
2017-10-10 00:00:04                   263
2017-10-10 00:00:05                   184
2017-10-10 00:00:06                   87
2017-10-10 00:00:07                   23
2017-10-10 00:00:08                   0
2017-10-10 00:00:09                   0
2017-10-10 00:00:10                   0
2017-10-10 00:00:11                   0
2017-10-10 00:00:12                   24
2017-10-10 00:00:13                   87
2017-10-10 00:00:14                   189
2017-10-10 00:00:15                   241
2017-10-10 00:00:16                   267
2017-10-10 00:00:17                   267
2017-10-10 00:00:18                   265
2017-10-10 00:00:19                   266

Welcome to Stackoverflow (SO) you may want to visit the site's help link in the footnote. It is expected at SO that you attempt the solution then ask for help as this in't a free coding service. — Paul Maxwell, Oct 10 '17 at 05:14
Not quite useful - 0 values could last for some time before raising again to higher point. — Corewood, Oct 10 '17 at 05:53
Your question lacks information; "sample data" and "expected result" (a result derived from the supplied data) will normally be sufficient to adequately define an SQL related question. We also prefer some attempt at the solution yourself as we don't regard SO as a free coding service. — Paul Maxwell, Oct 10 '17 at 06:44
Expected result is a number of drops from high point to zero (as you can see on the picture). MySQL version is 5.5.37-0+wheezy1. — Corewood, Oct 10 '17 at 06:51

score 0 · Answer 1 · answered Oct 10 '17 at 05:23

Did you mean to say "columns" where you said "rows"? Assuming so, you may want to edit your question.

As for the question itself:

Assuming you have more than one zero in a row and want to only count zeros preceded by a higher value, you could probably adapt this existing answer on how to detect previous values in MySQL.

https://stackoverflow.com/a/20849340/4315597

Sorry for providing only a link as an answer -- this would be a comment, but I don't yet have the reputation to comment.

Thanks for noticing, you're completely right about columns. About your answer - I'll check this out and respond as soon as possible. — Corewood, Oct 10 '17 at 05:52

score 0 · Accepted Answer · answered Oct 10 '17 at 07:00

0

See this working at SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Table1
    (`ts` datetime, `value` int)
;

INSERT INTO Table1
    (`ts`, `value`)
VALUES
    ('2017-01-01 00:00:00', 270),
    ('2017-01-01 00:00:00', 270),
    ('2017-01-01 00:00:00', 270),
    ('2017-01-01 00:00:00', 0),
    ('2017-01-01 00:00:00', 270),
    ('2017-01-01 00:00:00', 270),
    ('2017-01-01 00:00:00', 0)
;

Query 1:

select count(*) as count_drops
from (
    select
        @prev  as previous
      , t1.ts
      , @prev := t1.value as current
    from table1 t1
    cross join (select @prev := null x) var
    ) as d
where current = 0 and previous > 0

Results:

| count_drops |
|-------------|
|           2 |

answered Oct 10 '17 at 07:00

Paul Maxwell

33,002
3
32
51

**Please** note how useful sample data is (not images of data, but usable data). That, coupled with an "expected result" (nb: in my example the expected result is 2) will usually define a sql related question without a lot of wordy explanations and/or many clarification questions. Recommended reading https://stackoverflow.com/help/mcve – Paul Maxwell Oct 10 '17 at 07:33
Also, if like in my case you get 0 value for some time and need to count a number of drops, you can just set a range of value where it gets to 0. For example: `select count(*) as count_drops from ( select @prev as previous , t1.ts , @prev := t1.value as current from table1 t1 cross join (select @prev := null x) var ) as d where current between 0 and 100 and previous > 100` – Corewood Oct 10 '17 at 07:47

MySQL: How to count the number of deviations?

2 Answers2