Hello,
I have a table with about 60000 records which look something like this:
Table1:
LOAD *
INLINE [
Group, Start_datetime, Stop_datetime
"A", "2012-05-01 12:00:01", "2012-05-01 12:15:11",
"B", "2012-05-01 13:00:01", "2012-05-01 13:15:11",
"A", "2012-05-01 16:00:01", "2012-05-01 17:13:54",
"A", "2012-05-02 05:00:01", "2012-05-02 12:15:11",
"B", "2012-05-02 12:00:01", "2012-05-02 13:15:11",
"C", "2012-05-02 13:00:01", "2012-05-02 14:15:11"
];
Now I would like to calculate the difference in time between Start_datetime and previous Start_datetime, grouped by the column Group. The result would be something like this:
| Group | Start_datetime | Stop_datetime | Time since last one |
|---|
| A | 2012-05-01 12:00:01 | 2012-05-01 12:15:11 | n/a |
| A | 2012-05-01 16:00:01 | 2012-05-01 17:13:54 | 04:00:00 |
| A | 2012-05-02 05:00:01 | 2012-05-02 12:15:11 | 13:00:00 |
| B | 2012-05-01 13:00:01 | 2012-05-01 13:15:11 | n/a |
| B | 2012-05-02 12:00:01 | 2012-05-02 13:15:11 | 23:00:00 |
| C | 2012-05-02 13:00:01 | 2012-05-02 14:15:11 | n/a |
The next step would be to calculate the sum of all "time since last one" over a time dimension (year) and group.
The problem is not to calculate the time difference. I found the functions previous() and peak() for addressing the previous line, and interval() and timestamp() to calculate the difference and display it in any format. However - I can not find out a way to group the lines by the column Group and calculate only the matching lines!
If you could present me a solution or a hint thereof, I would be grateful.
Peter
