Thread: java computation issue

Question background:

One Bank Information System needs to figure out mobile average(MA)( On the chart, the mobile average is a trend line which smoothes the recurrences of the days and provides you with a quick overview of the period trend) amount of monthly loan from Loan Table defined as below,

Time	amount	…
3/14/2011	$43,334.10	…
3/15/2011	$92,304.10	...
3/16/2011	$45,983.80	...
3/17/2011	$36,973.10	...
3/18/2011	$24,987.87	...
...	...	...

It is easy for SQL to calculate monthly loan by grouping by. But I don't know how to make it out with sql as relative location and cross row computation are involved , Soppose we do it purely with java, following the codes:

st=conn.prepareStatement("select sum(amount)amount, to_char(time,'MM')month from loan  where to_char(time,'yyyy')=to_char(sysdate,'yyyy') group by to_char(time,'MM') order by month",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet rs=st.executeQuery();
			int size=rs.getFetchSize();
			for(int currentPos=1;currentPos<=size;currentPos++){
				rs.absolute(currentPos);
				float preAmount=-1,thisAmount=-1,nextAmount=-1;
				float avgAmount=-1;
				String month=rs.getString("month");
				thisAmount=rs.getFloat("amount");
				if(currentPos==1){	
					rs.next();
					nextAmount=rs.getFloat("amount");
					avgAmount=(thisAmount+nextAmount)/2;
				}else if(currentPos==size){
					rs.previous();
					preAmount=rs.getFloat("amount");
					avgAmount=(thisAmount+preAmount)/2;
				}else{
					rs.previous();
					preAmount=rs.getFloat("amount");
					rs.next();
					rs.next();
					nextAmount=rs.getFloat("amount");
					avgAmount=(thisAmount+nextAmount+preAmount)/3;
				}
				System.out.println(month+"   "+avgAmount);	
			}
			rs.close();

....
Except pure java coding, I think this kind of data process job could also be done by sql or esProc and maybe esProc will be easier. But I don't know too much about sql, hope people viewing my thread could give some tips.