Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to figure out a way to calculate, in the script, the average time for the last X Iterations. In that case, Iteration = periods.
If X is 4 iterations, then the results should be as follow:
th0, t1 = 7:34
th0, t2 = 7:43
See script below
source:
LOAD * INLINE [
ThreadID, TaskId, Timestamp
th0, t1, 17-10-11 7:37
th0, t1, 18-10-11 7:38
th0, t1, 19-10-11 7:42
th0, t1, 20-10-11 7:39
th0, t1, 21-10-11 7:32
th0, t1, 22-10-11 7:37
th0, t1, 23-10-11 7:31
th0, t2, 17-10-2011 7:40
th0, t2, 18-10-2011 7:52
th0, t2, 19-10-2011 7:41
th0, t2, 20-10-2011 7:39
th0, t2, 21-10-2011 7:40
];
AvgTimeTaskId:
noconcatenate
LOAD ThreadID,
TaskId,
Time(Average(frac(Timestamp))) AS Avg(Time)
RESIDENT source
[for the last X iterations]
Hi,
I think you could introduce a rownumber for each partition and then use a where-clause in your final avg-calculation load, like:
source:
LOAD * INLINE [
ThreadID, TaskId, Timestamp
th0, t1, 17-10-11 7:37
th0, t1, 18-10-11 7:38
th0, t1, 19-10-11 7:42
th0, t1, 20-10-11 7:39
th0, t1, 21-10-11 7:32
th0, t1, 22-10-11 7:37
th0, t1, 23-10-11 7:31
th0, t2, 17-10-2011 7:40
th0, t2, 18-10-2011 7:52
th0, t2, 19-10-2011 7:41
th0, t2, 20-10-2011 7:39
th0, t2, 21-10-2011 7:40
];
source2:
LOAD *,
If(Peek(TaskId) = TaskId, Peek(rownumber)+1, 1) as rownumber
Resident source order by ThreadID, TaskId, Timestamp desc;
drop table source;
AvgTimeTaskId:
noconcatenate
LOAD ThreadID as ThreadID2,
TaskId as TaskId2,
Time(Avg(frac(Timestamp))) AS AverageTime
RESIDENT source2 where rownumber <= 4 group by ThreadID,TaskId;
Hope this helps,
Stefan
Hi,
This your solution:
source:
LOAD * INLINE [
ThreadID, TaskId, Timestamp, Time
th0, t1, 17-10-2011 ,7:37
th0, t1, 18-10-2011 ,7:38
th0, t1, 19-10-2011 ,7:42
th0, t1, 20-10-2011 ,7:39
th0, t1, 21-10-2011 ,7:32
th0, t1, 22-10-2011 ,7:37
th0, t1, 23-10-2011 ,7:31
th0, t2, 17-10-2011 ,7:40
th0, t2, 18-10-2011 ,7:52
th0, t2, 19-10-2011 ,7:41
th0, t2, 20-10-2011 ,7:39
th0, t2, 21-10-2011 ,7:40
];
AvgTimeTaskId:
load
*,
floor(AVGTime/60)&':'&round(frac(AVGTime/60)*60) as Time
;
load
ThreadID,
TaskId,
avg(if(len(Time)=5,(left(Time,2)*60) + right(Time,2),(left(Time,1)*60) + right(Time,2))) as AVGTime
resident source
group by ThreadID,TaskId;
drop table source;
Succes!
Halmar
Halmar,
Appericiate your prompt response.
The code calculates the average for the hole subset. In my scenario i require to produce only the last X rows.
In this case:
for th0, t2
th0, t2, 18-10-2011 ,7:52
th0, t2, 19-10-2011 ,7:41
th0, t2, 20-10-2011 ,7:39
th0, t2, 21-10-2011 ,7:40
( 7:43)
for th0,t1
th0, t1, 20-10-2011 ,7:39
th0, t1, 21-10-2011 ,7:32
th0, t1, 22-10-2011 ,7:37
th0, t1, 23-10-2011 ,7:31
( 7:34)
Hi,
I think you could introduce a rownumber for each partition and then use a where-clause in your final avg-calculation load, like:
source:
LOAD * INLINE [
ThreadID, TaskId, Timestamp
th0, t1, 17-10-11 7:37
th0, t1, 18-10-11 7:38
th0, t1, 19-10-11 7:42
th0, t1, 20-10-11 7:39
th0, t1, 21-10-11 7:32
th0, t1, 22-10-11 7:37
th0, t1, 23-10-11 7:31
th0, t2, 17-10-2011 7:40
th0, t2, 18-10-2011 7:52
th0, t2, 19-10-2011 7:41
th0, t2, 20-10-2011 7:39
th0, t2, 21-10-2011 7:40
];
source2:
LOAD *,
If(Peek(TaskId) = TaskId, Peek(rownumber)+1, 1) as rownumber
Resident source order by ThreadID, TaskId, Timestamp desc;
drop table source;
AvgTimeTaskId:
noconcatenate
LOAD ThreadID as ThreadID2,
TaskId as TaskId2,
Time(Avg(frac(Timestamp))) AS AverageTime
RESIDENT source2 where rownumber <= 4 group by ThreadID,TaskId;
Hope this helps,
Stefan
BINGO . Thanks!