Moving avg and windowing functions

[Anonymous]

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

[Anonymous]

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