Average of the last X periods

[#1]

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]

HiHLOlo

[#2]

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

[#3]

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)

[swuehl · Accepted solution]

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

[#5]

BINGO . Thanks!