## 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)

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.

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.

## Recent Comments