Moving avg and windowing functions

Moving avg and windowing functions

Hello,
I'm testing and working with Talend since a few weeks now. Mainly without database but using XML files as input and output.
In my Version 3.0.2/Java on windows I can't find a way to build a moving avg over a number of rows, lets say the last 5 rows. Can anybody help me, getting on the right track.
So, I have kind of the following input-rows:
id value
1 6,74
2 3,82
3 1,65
4 1,71
5 9,3
6 3,26
7 6,96
8 1,89
9 2,9
10 8,27
Ouput-row should look like:
id value moving avg
1 6,74 6,74 -> avg over rows 1 to 1
2 3,82 5,28 -> avg over rows 1 to 2
3 1,65 4,07 -> avg over rows 1 to 3
4 1,71 3,48 -> avg over rows 1 to 4
5 9,3 4,64 -> avg over rows 1 to 5
6 3,26 3,95 -> avg over rows 2 to 6
7 6,96 4,58 -> avg over rows 3 to 7
8 1,89 4,62 -> avg over rows 4 to 8
9 2,9 4,86 -> avg over rows 5 to 9
10 8,27 4,66 -> avg over rows 6 to 10
The question is: Can I do this with Talend? Which components do that with which configuration?
Juergen
One Star

Re: Moving avg and windowing functions

Hi Juergen,
this is possible but only with a custom method. Following a simple routine. This is not an example for good coding practice...
But I think this is a good point to start.
public class forum5020routine {
private static Double[] values= null;
private static int numberOfIterations= 0;
private static int count= 0;
private static int pointer= 0;

/**
* movingAvg: returns the moving average for a predefined number of values
*
* {talendTypes} Double
*
* {Category} User Defined
*
* {param} int("count") count: Number of last values to handle for the arithmetic operation
* {param} Double("value") value: Actual Double value
*
* {example} movingAvg(5, 3.2)
*/
public static Double movingAvg(int count, Double value) {
// initialize count value and array for storing the values
if (forum5020routine.count == 0) {
forum5020routine.count= count;
forum5020routine.values= new Double;
for (int i=0; i < count; i++) {
forum5020routine.values= new Double(0);
}
}

// add the actual value to the array and reset pointer if needed
// the array is used as a circular data container
forum5020routine.values= value;
forum5020routine.pointer++;
if (forum5020routine.pointer >= forum5020routine.count) {
forum5020routine.pointer= 0;
}
// remember the actual iteration. This is only needed to calculate
// the n first values (n < count)
forum5020routine.numberOfIterations++;

// calculate and return value
Double sum= new Double(0.0);
for (Double v: forum5020routine.values) {
sum+=v;
}

if (forum5020routine.numberOfIterations < forum5020routine.count) {
return (sum / forum5020routine.numberOfIterations);
} else {
return (sum / forum5020routine.count);
}
}
}

The result is:
1|6.74|6.74
2|3.82|5.28
3|1.65|4.07
4|1.71|3.4800000000000004
5|9.3|4.644
6|3.26|3.9480000000000004
7|6.96|4.576
8|1.89|4.6240000000000006
9|2.9|4.862
10|8.27|4.656000000000001

Bye
Volker