My problem is simple: I have a table with a series of statuses and timestamps (for the sake of curiosity, these statuses indicate alarm levels) and I would like to query this table in order to get duration between two statuses.
Seems simple, but here comes the tricky part: I can´t create look-up tables, procedures and it should be as fast as possible as this table is a little monster holding over 1 billion records (no kidding!)...
The schema is drop dead simple:
[pk] Time Value
(actualy, there is a second pk but it is useless for this)
And below a real world example:
Timestamp Status 2013-1-1 00:00:00 1 2013-1-1 00:00:05 2 2013-1-1 00:00:10 2 2013-1-1 00:00:15 2 2013-1-1 00:00:20 0 2013-1-1 00:00:25 1 2013-1-1 00:00:30 2 2013-1-1 00:00:35 2 2013-1-1 00:00:40 0
The output, considering only a level 2 alarm, should be as follow should report the begin of a level 2 alarm an its end (when reach 0):
StartTime EndTime Interval 2013-1-1 00:00:05 2013-1-1 00:00:20 15 2013-1-1 00:00:30 2013-1-1 00:00:40 10
I have been trying all sorts of inner joins, but all of them lead me to an amazing Cartesian explosion. Can you guys help me figure out a way to accomplish this?
Thanks!
Finally figured out a version I was happy with. It took me remembering an answer from another question (can't remember which one though) where it was pointed out that the difference between two (increasing) sequences was always a constant.
(working SQL Fiddle example, with some additional data rows for work checking).
This unfortunately doesn't correctly deal with level-2 statuses that are end rows (behavior unspecified), although it does list them.
Just for the sake of having an alternative. Tried to do some test on performance, but did not finish.
And here is a Fiddle.
This has to be one of the harder questions I've seen today - thanks! I assume you can use CTEs? If so, try something like this:
And the Fiddle. I didn't add the intervals, but I imagine you can handle that part from here.
Good luck.
I do something similar by using id that is an identity to the table.
I would also make the timestamps be seconds since 1980 or 2000 or whatever. But then you might not want to do the reverse conversion all the time and so it depends on how often you use the actual time stamp.