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

## Teradata Vantage™ - SQL External Routine Programming

Product
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,
SQL_TEXT        fncname,
SQL_TEXT        sfncname,
SQL_TEXT        error_message )
{
/* 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;
}```
{"ft_lang_label":"__label__en","ft_lang_prob":0.5945697,"math_prob":0.8670819,"size":2591,"snap":"2022-27-2022-33","text_gpt3_token_len":716,"char_repetition_ratio":0.15925783,"word_repetition_ratio":0.024509804,"special_character_ratio":0.37514472,"punctuation_ratio":0.15513127,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.98595697,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2022-06-29T03:46:58Z\",\"WARC-Record-ID\":\"<urn:uuid:f2dc9f58-5c59-40f0-b72a-014c4276f480>\",\"Content-Length\":\"466753\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:9ef21728-a9a5-4d66-a3d8-8159d2af4f9b>\",\"WARC-Concurrent-To\":\"<urn:uuid:b328d9e4-efec-4030-b260-f0125a9a9ae4>\",\"WARC-IP-Address\":\"3.19.78.58\",\"WARC-Target-URI\":\"https://docs.teradata.com/r/Teradata-VantageTM-SQL-External-Routine-Programming/April-2020/UDF-Code-Examples/C-Aggregate-Function/C-Function-Definition\",\"WARC-Payload-Digest\":\"sha1:GZZCMNPYZZBMJSRHZNY2B6ZGN7HKT63M\",\"WARC-Block-Digest\":\"sha1:PKITUXBDNBYFNVFVZINAIAQVJHQCPE4D\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2022/CC-MAIN-2022-27/CC-MAIN-2022-27_segments_1656103620968.33_warc_CC-MAIN-20220629024217-20220629054217-00162.warc.gz\"}"}