#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; }