Aggregate Functions
Aggregate functions compute a single result from a group of rows.
You can use the DISTINCT keyword with any aggregation function, such as COUNT(DISTINCT col).
Aggregate Functions
Function | Syntax | Purpose |
|---|---|---|
ANY_VALUE | ANY_VALUE(col) | Returns an arbitrary, non-NULL, value from the input column. The function gives preference to any non-NULL values in the column and returns NULL only if all rows in the input column are NULL. The ANY_VALUE function is explicitly undefined if you use it for window aggregation and the function generates an error. |
APPROX_COUNT_DISTINCT | APPROX_COUNT_DISTINCT(col) | Approximate distinct count by using hyper-log-log (95% confidence interval that the value is within 4.5%) |
APPROX_SUM | APPROX_SUM(col) | Allows the aggregation engine to use a faster, non-deterministic, ordering to summate floating-point columns. This summation might lead to minute differences in the result on the order of the machine epsilon. For other column types, the aggregation engine uses the standard SUM algorithm. |
AVG | AVG(col) | Average, or arithmetic mean, over the set |
CORRELATION | CORRELATION(col1,col2) | Sample correlation |
CORR | CORR(col1,col2) | Alias for CORRELATION |
CORRELATIONP | CORRELATIONP(col1,col2) | Population correlation |
COUNT | COUNT(col) | Number of rows in the set where values in col are not null |
COUNT | COUNT(*) | Total number of rows in the table |
COVARIANCE | COVARIANCE(col1,col2) | Sample covariance |
COVAR_SAMP | COVAR_SAMP(col1, col2) | Alias for COVARIANCE |
COVARIANCEP | COVARIANCEP(col1,col2) | Population covariance |
COVAR_POP | COVAR_POP(col1, col2) | Alias for COVARIANCEP |
MAX | MAX(col) | Maximum value in the given column |
MIN | MIN(col) | Minimum value in the given column |
STDEVP | STDEVP(col) | Population standard deviation |
STDDEV_POP | STDDEV_POP(col) | Alias for STDEVP |
STDEV | STDEV(col) | Sample standard deviation |
STDDEV | STDDEV(col) | Alias for STDEV |
STDDEV_SAMP | STDDEV_SAMP(col) | Alias for STDEV |
SUM | SUM(col) | Sum over the set |
VARIANCE | VARIANCE(col) | Sample variance |
VAR_SAMP | VAR_SAMP(col) | Alias for VARIANCE |
VARIANCEP | VARIANCEP(col) | Population variance |
VAR_POP | VAR_POP(col) | Alias for VARIANCEP |
PRODUCT | PRODUCT(col) | Product over the set |
SKEW | SKEW(col) | The sample over the set |
SWEP | SWEWP(col) | The population over the set |
KURTOSIS | KURTOSIS(col) | The sample over the set |
KURTOSISP | KURTOSISP(col) | The population over the set |
Query Ocient