AGGREGATE FUNCTIONS

AUTO_CORR: This function is used to compute all autocorrelation coefficients for a given input column and returns an array of values.
The time frame size is limited by the maxTimeLag parameter. This parameter must be a positive integer. The result size is the minimum of maxTimeLag and column size – 2 for dense series data.

Syntax: AUTO_CORR(<column>, <maxTimeLag>    {SERIES(…) | ORDER BY <col1>, …})

CORR: This function is used to compute the Pearson product momentum correlation coefficient between two columns.
The result ranges from -1 to 1, depending on the correlation, or null if a correlation could not be computed.
Syntax: CORR (<column1>, <column2>) [OVER([PARTITION BY <col1>, …] [ORDER BY <col1>, … [<window_frame>]])]

CORR_SPEARMAN: This function is used to return the Spearman’s rank correlation coefficient of the values found in the corresponding rows of <column1> and <column2>.
Column1 and column2 may contain number or even character types.

Syntax: CORR_SPEARMAN (<column1>, <column2>) [OVER([PARTITION BY <col1>, …][ORDER BY <col1>, … [<window_frame>]])]

CROSS_CORR: This function is used to compute all cross-correlation coefficients between two given columns.
The result is an array of cross-correlation coefficients of length <maxLag>.Syntax:CROSS_CORR (<expression1>, <expression2>, <maxLag> { <series_orderby> | ORDER BY <expression3> [ ASC | DESC ] [ NULLS FIRST | NULLS ] } ).   { POSITIVE_LAGS | NEGATIVE_LAGS | ZERO_LAG }

DFT: This function is used to compute the Discrete Fourier Transform of a column for the first N values and returns an array with exactly N elements.
The returned values depend on the output parameter, which must be one of REAL, IMAGINARY, AMPLITUDE, or PHASE.

Syntax: DFT (<column>, <N>{SERIES( … ) | ORDER BY <col1>,}).{REAL|IMAGINARY|AMPLITUDE|PHASE}

FIRST_VALUE:This function is used to return the value of the first element in <expression> as ordered by <column>.
Null is returned if the value is null or if <expression> is empty.

Syntax: FIRST_VALUE (<expression> ORDER BY <column>)

LAST_VALUE:This function is used to return the value of the last element in <column1> as ordered by <column2>.
Null is returned if the value is null or if <column1> is empty.

Syntax: LAST_VALUE (<column1> ORDER BY <column2>)

MEDIAN:This function is used to find the statistical median of an input column with a numeric data type.
Null values are eliminated. If there is an even number of elements, the average of the two middle elements is returned. Otherwise, the middle element is returned.

Syntax: MEDIAN (<column>)[OVER([PARTITION BY <col1>, …] [ORDER BY <col1>, … [<window_frame>]])]

NTH_VALUE:This function is used to return the value of the element at position <n> in <column1> as ordered by <column2>. Null is returned if the value is null or if <n> is larger than the number of elements in <column1>. An error is raised if <n> is less than or equal to 0.

Syntax: NTH_VALUE (<column1>, <n> ORDER BY <column2>)

STDDEV_POP:This function is used to return the standard deviation of the given expression as the square root of VAR_POP function.

Syntax: STDDEV_POP(<expression>)

STDDEV_SAMP:This function is used to return the standard deviation of the given expression as the square root of VAR_SAMP function.

Syntax: STDDEV_SAMP(<expression>)

VAR_POP:This function is used to return the population variance of the expression as the sum of squares of the difference of <expression> from the mean of <expression>, divided by the number of rows remaining.

Syntax: VAR_POP(<expression>)

VAR_SAMP:This function is used to return the sample variance of the expression as the sum of squares of the difference of <expression> from the mean of <expression>, divided by the number of rows remaining minus 1 (one). This functions is similar to VAR, the only difference is that it returns NULL when the number of rows is 1.

Syntax: VAR_SAMP(<expression>)