SQL Aggregate Functions in SAP HANA with Syntax and Examples

SQL Aggregate Functions in SAP HANA with Examples and syntax:-

The SQL Aggregate function in SAP HANA contains something different then the SQL SERVER.Below all the SQL Aggregate functions are explained one by one with examples and with syntax.

1.AUTO_CORR(<column>, <maxTimeLag>    {SERIES(…) | ORDER BY <col1>, …}): 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.

2.CORR (<column1>, <column2>) [OVER([PARTITION BY <col1>, …] [ORDER BY <col1>, … [<window_frame>]])]: 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. Ans: 213947 (ID of the current connection)

[AdSense-A]

3.CORR_SPEARMAN (<column1>, <column2>) [OVER([PARTITION BY <col1>, …][ORDER BY <col1>, … [<window_frame>]])]: 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

4.CROSS_CORR (<expression1>, <expression2>, <maxLag> { <series_orderby> | ORDER BY <expression3> [ ASC | DESC ] [ NULLS FIRST | NULLS ] } ).   { POSITIVE_LAGS | NEGATIVE_LAGS | ZERO_LAG }:  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

5.DFT (<column>, <N>{SERIES( … ) | ORDER BY <col1>, … }).{REAL|IMAGINARY|AMPLITUDE|PHASE}: 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

6.FIRST_VALUE (<expression> ORDER BY <column>): 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.

[AdSense-A]

7. LAST_VALUE (<column1> ORDER BY <column2>):  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.