New Analytical and Statistical Aggregate Functions

New analytical and statistical aggregate functions are available in SQL:

CHECKSUM computes the checksum of the input values or expression.

KURTOSIS functions KURTOSIS_POP and KURTOSIS_SAMP measure the tailedness of a data set where a higher value means more of the variance within the data set is the result of infrequent extreme deviations as opposed to frequent modestly sized deviations. Note that a normal distribution has a kurtosis of zero.

SKEWNESS functions SKEWNESS_POP and SKEWNESS_SAMP are measures of asymmetry in data. A positive skewness is means the data skews to the right of the center point. A negative skewness means the data skews to the left.

All of these new aggregate functions support the keywords ALL, DISTINCT, and UNIQUE.

ANY_VALUE, a function to simplify and optimize the performance of GROUP BY statements, returns a random value in a group and is optimized to return the first value in the group. It ensures that there are no comparisons for any incoming row and eliminates the necessity to specify every column as part of the GROUP BY clause.

With these additional SQL aggregation functions, you can write more efficient code and benefit from faster in-database processing.

Practice: Detecting Data Tampering with the CHECKSUM Function

This practice shows how to use the CHECKSUM aggregate function to detect changes in a table. The function can be applied on a column, a constant, a bind variable, or an expression involving them. All datatypes except ADT and JSON are supported. The order of the rows in the table does not affect the result.

Practice: Measuring Asymmetry in Data with the SKEWNESS Functions

This practice shows how to use the SKEWNESS_POP and SKEWNESS_SAMP aggregate functions to measure asymmetry in data. For a given set of values, the result of population skewness (SKEWNESS_POP) and sample skewness (SKEWNESS_SAMP) are always deterministic.

Practice: Measuring Tailedness of Data with the KURTOSIS Functions

This practice shows how to use the KURTOSIS_POP and KURTOSIS_SAMP aggregate functions to measure tailedness of data. Higher kurtosis means more of the variance is the result of infrequent extreme deviations, as opposed to frequent modestly sized deviations. A normal distribution has a kurtosis of zero.

Related Topics