ApsaraDB for ClickHouse supports aggregate functions for statistical analysis, approximate computation, bitmap operations, and more. All NULL arguments are skipped during aggregation. Functions that accept multiple arguments ignore any row where at least one argument is NULL—except for first_value, last_value, any, and anyLast when used with the RESPECT NULLS modifier.
Click a function name to view its syntax, parameters, and examples in the ClickHouse documentation.
Basic aggregation
| Function | Description |
|---|
| count | Counts rows or non-NULL values |
| sum | Calculates the sum of values |
| avg | Calculates the arithmetic mean |
| min | Returns the minimum value |
| max | Returns the maximum value |
| any | Returns an arbitrary value from the group. Alias: first_value |
| first_value | Returns the first encountered value. Alias of any |
| last_value | Returns the last encountered value. Alias of anyLast |
| anyHeavy | Selects a frequently occurring value using the heavy hitters algorithm |
| anyLast | Returns the last value encountered. Alias: last_value |
| avgWeighted | Calculates the weighted arithmetic mean |
| sumWithOverflow | Computes the sum using the same data type as the input, allowing overflow |
| sumKahan | Computes the sum using the Kahan compensated summation algorithm for higher floating-point accuracy |
| sumCount | Computes the sum and count of non-NULL values simultaneously |
Argument selection
| Function | Description |
|---|
| argMin | Returns the arg value corresponding to the minimum val |
| argMax | Returns the arg value corresponding to the maximum val |
| boundingRatio | Calculates the slope between the minimum and maximum argument-value pairs |
Statistical functions
| Function | Description |
|---|
| stddevPop | Calculates the population standard deviation |
| stddevSamp | Calculates the sample standard deviation |
| varSamp | Calculates the sample variance |
| covarPop | Calculates the population covariance |
| covarSamp | Calculates the sample covariance |
| corr | Calculates the Pearson correlation coefficient |
| rankCorr | Calculates the rank correlation coefficient (Spearman's rho) |
| entropy | Calculates the Shannon entropy of a column |
| skewPop | Calculates the population skewness |
| skewSamp | Calculates the sample skewness |
| kurtPop | Calculates the population kurtosis |
| kurtSamp | Calculates the sample kurtosis |
| simpleLinearRegression | Performs simple (one-dimensional) linear regression |
| stochasticLinearRegression | Implements stochastic gradient descent for linear regression |
| stochasticLogisticRegression | Implements stochastic gradient descent for logistic regression (binary classification) |
| categoricalInformationValue | Calculates the information value for a discrete (categorical) feature relative to a binary target |
| exponentialmovingaverage | Calculates the exponential moving average of values at the given time |
| intervalLengthSum | Calculates the total length of the union of intervals |
| deltaSum | Sums the positive differences between consecutive rows |
| deltaSumTimestamp | Sums the positive differences between consecutive values, ordered by a timestamp column |
| sparkbar | Plots a frequency histogram for values within a specified range |
Statistical hypothesis testing
| Function | Description |
|---|
| kolmogorovSmirnovTest | Applies the Kolmogorov-Smirnov test to samples from two populations |
| studentTTest | Applies Student's t-test to samples from two populations |
| welchTTest | Applies Welch's t-test to samples from two populations |
| meanZTest | Applies the mean z-test to samples from two populations |
| mannWhitneyUTest | Applies the Mann-Whitney U test to samples from two populations |
Association measures
| Function | Description |
|---|
| contingency | Calculates the contingency coefficient, a measure of association between two columns |
| cramersV | Calculates Cramer's V, a measure of association between two columns |
| cramersVBiasCorrected | Calculates a bias-corrected version of Cramer's V |
| theilsU | Calculates Theil's U uncertainty coefficient, an asymmetric association measure |
Approximate and top-K functions
| Function | Description |
|---|
| topK | Returns an array of the approximately most frequent values using the Space-Saving algorithm |
| topKWeighted | Returns an array of the approximately most frequent values, weighted by a second column |
Cardinality estimation
| Function | Description |
|---|
| uniq | Estimates the number of distinct values (fast, low memory) |
| uniqExact | Counts the exact number of distinct values |
| uniqCombined | Estimates distinct values using a combined algorithm (array, hash table, HyperLogLog) |
| uniqCombined64 | Same as uniqCombined but uses a 64-bit hash, reducing collision probability for large datasets |
| uniqHLL12 | Estimates distinct values using the HyperLogLog algorithm with 2^12 cells |
| uniqTheta | Estimates distinct values using the Theta Sketch Framework |
Quantile and percentile functions
All quantile functions share a common family of variants. For example, quantile has corresponding quantiles, quantileExact, quantileExactWeighted, and other variants that trade accuracy for performance or accept weight columns.
Bitwise aggregation
| Function | Description |
|---|
| groupBitAnd | Applies bitwise AND across all values in a group |
| groupBitOr | Applies bitwise OR across all values in a group |
| groupBitXor | Applies bitwise XOR across all values in a group |
Map aggregation
| Function | Description |
|---|
| sumMap | Sums map values by key across rows |
| minMap | Returns the minimum map value per key across rows |
| maxMap | Returns the maximum map value per key across rows |
Advanced features
The following entries link to advanced aggregate function capabilities rather than individual functions.
| Feature | Description |
|---|
| Aggregate Function Combinators | Suffixes that modify aggregate function behavior (for example, -If, -Array, -State, -Merge) |
| Parametric Aggregate Functions | Aggregate functions that accept additional parameters alongside the column arguments |
| GROUPING | Returns 1 if a column is used in GROUP BY, and 0 if it represents an aggregated row (used with ROLLUP or CUBE) |