Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table similar to this.
ID | Date | Time |
---|---|---|
1 | 1/10/17 | 09:00 |
1 | 1/10/17 | 10:00 |
1 | 1/10/17 | 11:00 |
1 | 1/10/17 | 12:00 |
2 | 1/10/17 | 05:00 |
2 | 1/10/17 | 05:15 |
2 | 1/10/17 | 05:30 |
2 | 1/10/17 | 05:45 |
2 | 1/10/17 | 06:00 |
I want to calculate the average time difference per per ID, Date e.g. 60mins or 15 mins.
Can anyone help me do this within the script?
This should do the trick:
Data:
LOAD * INLINE [
ID, Date, Time
1, 1/10/17, 09:00
1, 1/10/17, 10:00
1, 1/10/17, 11:00
1, 1/10/17, 12:00
2, 1/10/17, 05:00
2, 1/10/17, 05:15
2, 1/10/17, 05:30
2, 1/10/17, 05:45
2, 1/10/17, 06:00
];
Difference:
LOAD ID,
Date,
Time,
IF(ID = Previous(ID) AND Date = Previous(Date), Interval(Time - Previous(Time), 'mm')) as Diff
RESIDENT Data
ORDER BY ID, Date, Time;
Average:
LOAD ID,
Date,
Interval(Avg(Diff), 'mm') As Average
RESIDENT Difference
GROUP BY ID, Date;
DROP TABLES Data, Difference;
combine firstsortedvalue with -Date for max and Date for min and then just subtract the 2
something like:
load
Timestamp(firstsortedvalue, -Date) - Timestamp(firstsortedvalue, Date) as diff
Resident table
Group by
ID, Date