import com.teradata.fnc.*;
import java.io.*;
import java.sql.*;
class agr_storage implements Serializable{
double count;
double x_sq;
double x_sum;
public agr_storage(double a, double b, double c){
count =a;
x_sq =b;
x_sum = c;
}
}
public class UDFExample {
static boolean debug = true;
public static Double STD_DEV(Phase phase,
Context[] context,
double x) throws SQLException
{
try {
agr_storage s1 =null;
agr_storage s2 =null;
/* The aggregate storage only hold valid data in the
* AGR_DETAIL, AGR_COMBINE and AGR_FINAL phases. */
if(phase.getPhase()>Phase.AGR_INIT&&
phase.getPhase()<Phase.AGR_NODATA){
s1 = (agr_storage)context[0].getObject(1);
/* switch to determine the aggregation phase */
switch (phase.getPhase())
{
/* This phase is executed once per group and */
/* allocates and initializes intermediate */
/* aggregate storage */
case Phase.AGR_INIT:
if (debug) System.err.println("Phase: AGR_INIT");
/* Get storage for intermediate aggregate values.*/
s1 = new agr_storage(0,0,0);
context[0].initCtx(s1);
/*********************************************/
/* Fall through to detail phase, because the */
/* AGR_INIT phase passes in the first set of */
/* values for the group. */
/*********************************************/
/* This phase is executed once for each selected */
/* row to aggregate. One copy will run on each AMP. */
case Phase.AGR_DETAIL:
if (debug) System.err.println("Phase: AGR_DETAIL");
s1.count++;
s1.x_sq +=x*x;
s1.x_sum += x;
break;
/* This phase combines the results of ALL */
/* individual AMPs for each group. */
case Phase.AGR_COMBINE:
if (debug) System.err.println("Phase: AGR_COMBINE");
/* This is the only phase where Context.getObject(2) */
/* has a valid value. */
s2=(agr_storage)context[0].getObject(2);
s1.count += s2.count;
s1.x_sq += s2.x_sq;
s1.x_sum += s2.x_sum;
break;
/* This phase returns the final result. */
/* It is called once for each group. */
case Phase.AGR_FINAL:
if (debug) System.err.println("Phase: AGR_FINAL");
double term2 = s1.x_sum/s1.count;
double variance = s1.x_sq/s1.count - term2*term2;
/* Adjust for deviations close to zero */
if (Math.abs(variance) < 1.0e-14)
variance = 0.0;
return new Double(Math.sqrt(variance));
case Phase.AGR_NODATA:
if (debug) System.err.println("Phase: AGR_NODATA");
/* return null if no data */
return null;
default:
/* If it gets here there must be an error */
/* because this function does not accept any */
/* other phase options */
throw new SQLException("Invalid Phase", "38U05");
}
context[0].setObject( 1, s1 );
} catch(IOException ex) {
ex.printStackTrace();
} catch(ClassNotFoundException e){
e.printStackTrace();
}
return null;
}
public static Double fastSTD_DEV(Phase phase,
Context[] context,
double x) throws SQLException
{
/*The intermediate storage is a byte array of 3 doubles*/
double count=0;
double x_sq=0;
double x_sum=0;
try {
ByteBuffer s1 =null;
ByteBuffer s2 =null;
/* The aggregate storage only hold valid data in the */
/* AGR_DETAIL, AGR_COMBINE and AGR_FINAL phases. */
if (phase.getPhase() > Phase.AGR_INIT &&
phase.getPhase() < Phase.AGR_NODATA) {
s1 = ByteBuffer.wrap(context[0].getBytes(1));
count = s1.getDouble();
x_sq = s1.getDouble();
x_sum = s1.getDouble();
}
/* switch to determine the aggregation phase */
switch (phase.getPhase())
{
/* This phase is executed once per group and */
/* allocates and initializes intermediate */
/* aggregate storage */
case Phase.AGR_INIT:
if (debug) System.err.println("Phase: AGR_INIT");
/* Get storage for intermediate aggregate values.*/
context[0].initCtx(24);
/*********************************************/
/* Fall through to detail phase, because the */
/* AGR_INIT phase passes in the first set of */
/* values for the group. */
/*********************************************/
/* This phase is executed once for each selected */
/* row to aggregate. One copy will run on each AMP. */
case Phase.AGR_DETAIL:
if (debug) System.err.println("Phase: AGR_DETAIL");
s1 = ByteBuffer.allocate(24);
count++;
x_sq +=x*x;
x_sum += x;
s1.putDouble(count);
s1.putDouble(x_sq);
s1.putDouble(x_sum);
break;
/* This phase combines the results of ALL */
/* individual AMPs for each group. */
case Phase.AGR_COMBINE:
if (debug) System.err.println("Phase: AGR_COMBINE");
/*This is the only phase where Context.getBytes(2) */
/* has a valid value. */
s2 =ByteBuffer.wrap(context[0].getBytes(2));
count += s2.getDouble();
x_sq += s2.getDouble();
x_sum += s2.getDouble();
break;
/* This phase returns the final result. */
/* It is called once for eeach group. */
case Phase.AGR_FINAL:
{
if (debug) System.err.println("Phase: AGR_FINAL");
double term2 = x_sum/count;
double variance = x_sq/count - term2*term2;
/* Adjust for deviations close to zero */
if (Math.abs(variance) < 1.0e-14)
variance = 0.0;
return new Double(Math.sqrt(variance));
}
case Phase.AGR_NODATA:
if (debug) System.err.println("Phase: AGR_NODATA");
/* return null if no data */
return null;
default:
/* If it gets here there must be an error */
/* because this function does not accept any */
/* other phase options */
throw new SQLException("Invalid Phase", "38U05");
}
context[0].setBytes( 1, s1.array() );
} catch(IOException ex){
ex.printStackTrace();
} catch(ClassNotFoundException e){
e.printStackTrace();
}
return null;/*will be ignored*/
}
}