i need to detect rising and falling edges from a loading state in my logs and need to list all loading cycles.
Lets say i have a table LOG
UTS | VALUE | STATE
1438392102 | 1000 | 0
1438392104 | 1001 | 1
1438392106 | 1002 | 1
1438392107 | 1003 | 0
1438392201 | 1007 | 1
1438392220 | 1045 | 1
1438392289 | 1073 | 0
1438392305 | 1085 | 1
1438392310 | 1090 | 1
1438392315 | 1095 | 1
And need all cycles where STATE = 1 I need to know when they started how long they lasted and how much VALUE changed in each cycle.
I also might have a situation where the last cycle isn't finished yet.
Do you have an idea how i can do this in SQL in a good performing way ? Cuz i might run into situations where my logs return several hundred of thausends of rows.
Thanks for help
