#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>
#include <math.h>
typedef struct agr_storage {
FLOAT count;
FLOAT x_sq;
FLOAT x_sum;
} AGR_Storage;
void STD_DEV( FNC_Phase phase,
FNC_Context_t *fctx,
FLOAT *x,
FLOAT *result,
int *x_i,
int *result_i,
char sqlstate[6],
SQL_TEXT fncname[129],
SQL_TEXT sfncname[129],
SQL_TEXT error_message[257] )
{
/* pointers to intermediate storage areas */
AGR_Storage *s1 = fctx->interim1;
AGR_Storage *s2 = fctx->interim2;
/* The standard deviation function described here is: */
/* */
/* s = sqrt(sum(x^2)/N - (sum(x)/N)^2) */
/* */
/* sum(x^2) :> x_sq, N :> count, sum(x) :> x_sum */
/* switch to determine the aggregation phase */
switch (phase)
{
/* This case selection is called once per group and */
/* allocates and initializes all intermediate */
/* aggregate storage
*/
case AGR_INIT:
/* Get some storage for intermediate aggregate values. */
/* FNC_DefMem returns zero if it cannot get the requested */
/* memory. The amount of storage required is the size of */
/* the structure used for intermediate aggregate values */
s1 = FNC_DefMem(sizeof(AGR_Storage));
if (s1 == NULL)
{
/* could not get storage */
strcpy(sqlstate, "U0001"); /* see SQLSTATE table */
return;
}
/* Initialize the intermediate aggregate values */
s1->count = 0;
s1->x_sq = 0;
s1->x_sum = 0;
/***************************************************/
/* Fall through to detail phase, because the */
/* AGR_INIT call passes in the first set of */
/* values for the group */
/***************************************************/
/* This case selection is called once for each */
/* selected row to aggregate. x is the column the */
/* std_dev is being calculated for. */
/* One copy will be running on each AMP */
case AGR_DETAIL:
if (*x_i != -1)
{
s1->count++;
s1->x_sq += *x * *x;
s1->x_sum += *x;
}
break;
/* This case selection combines the results of ALL */
/* individual AMPs for each group */
case AGR_COMBINE:
s1->count += s2->count;
s1->x_sq += s2->x_sq;
s1->x_sum += s2->x_sum;
break;
/* This case selection returns the final standard */
/* deviation. It is called once for each group. */
case AGR_FINAL:
{
FLOAT term2 = s1->x_sum/s1->count;
FLOAT variance = s1->x_sq/s1->count - term2*term2;
/* Adjust for deviations close to zero */
if (fabs(variance) < 1.0e-14)
variance = 0.0;
*result = sqrt(variance);
break;
}
case AGR_NODATA:
/* return null if no data */
*result_i = -1;
break;
default:
/* If it gets here there must be an error because this */
/* function does not accept any other phase options */
strcpy(sqlstate, "U0005");
}
return;
}