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
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
| count_drops |
|-------------|
| 2 |