CODEWITHSUNDEEP
mysql
Alex van Es
Alex van Es

Reputation: 1181

Calculate peaks in dataset using mysql

I have a dataset showing a datetime and a battery charge level;

| 2013-05-14 09:40:30 |      29 |
| 2013-05-14 09:41:31 |      24 |
| 2013-05-14 09:42:31 |      10 |
| 2013-05-14 09:43:33 |      5 |
| 2013-05-14 09:44:34 |      50 |   -> battery charge started
| 2013-05-14 09:50:35 |      70 |
| 2013-05-14 09:51:36 |      100 |  -> battery full 
| 2013-05-14 09:52:37 |      90 |
| 2013-05-14 09:53:37 |      75 |
| 2013-05-14 09:54:39 |      32 |
| 2013-05-14 09:55:39 |      19 |
| 2013-05-14 09:56:40 |      2 |
| 2013-05-14 09:54:39 |      42 |   -> battery charge started
| 2013-05-14 09:55:39 |      89 |
| 2013-05-14 09:56:40 |      100 | -> battery full 
| 2013-05-14 09:57:39 |      100 |
| 2013-05-14 09:58:39 |      99 |

I want to count how many times the battery was fully charged.. I can't simply count the 100%'s since sometimes (as you see at the bottom) I have multiple rows sometimes showing 100% (for up to several days depending on usage of the device). Anyone knows a way to count the peaks using mysql? So in the above example the result should be 2.. in the example the datetime is a bit condensed.. in reality is spans a week.

Upvotes: 0

Views: 77

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65284

This should do the trick:

SELECT * FROM (
   SELECT
    pit,
    IF(@oldcharge<>100 OR chargeperc<>100,@oldcharge:=chargeperc,101) AS charge
  FROM
    (SELECT @oldcharge:=0) AS init,
    charging
  ORDER BY pit
) AS baseview
WHERE charge=100;

SQLfiddle

Explanation:

  • In the inner query we raise all charging values, that are 100 and come immediately after another 100 to 101
  • In the outer query, we select those with 100, but not those with 101

Upvotes: 1

Related Questions