16.20 - C Function Definition - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)
#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;
}