Aggregations
All latencies are measured over AWS PrivateLink within a single availability zone (us-east-1) and include the network round trip. Each request reads the latest N rows from a randomly chosen partition.
Averages
FAVG(x)
| Description | Exact average of x |
|---|---|
| Formula | sum(x) / count(x) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No values: NULL, Infinity: ±Infinity, Opposite infinities: NULL |
| Validation | Matches Python statistics.mean on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.51 |
| 100 | 0.53 |
| 1,000 | 0.95 |
GEOMEAN(x)
| Description | Geometric mean of x |
|---|---|
| Formula | product(x)^(1 / count(x)) |
| Precision | Correctly rounded and the same in any row order: the exact product (INTEGERs exactly) and one rounding of its n-th root |
| Edge cases | No values: NULL, Zero: error, Negative value: error, Infinity: Infinity |
| Validation | Matches DuckDB geomean within 1e-9 on fixed and random inputs; pseudo-oracle against reference.py Geomean (exact), compared exactly |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.62 |
| 100 | 0.66 |
| 1,000 | 1.14 |
GEOMETRIC_STDDEV(x)
| Description | Spread of x as a ×/÷ factor around its geometric mean |
|---|---|
| Formula | exp(sqrt(sum((ln(x) - avg(ln(x)))^2) / (count(x) - 1))) |
| Precision | Approximate answers with error limited to the last digits since the logarithms and the exponential are rounded |
| Edge cases | Fewer than 2 values: NULL, Zero: error, Negative value: error, Infinity: Infinity, Beyond the largest double: Infinity |
| Validation | Matches Python scipy.stats.gstd within 1e-12 on fixed inputs; pseudo-oracle against reference.py GeometricStddev |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.54 |
| 100 | 0.55 |
| 1,000 | 0.97 |
HARMONIC_MEAN(x)
| Description | Harmonic mean of x: the count over the sum of reciprocals |
|---|---|
| Formula | count(x) / sum(1 / x) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No values: NULL, Zero: 0, Negative value: error, Infinity: adds nothing to the sum (only Infinity: Infinity) |
| Validation | Matches Python statistics.harmonic_mean (exact Fractions) on fixed and random inputs; pseudo-oracle against reference.py HarmonicMean |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.65 |
| 100 | 0.77 |
| 1,000 | 2.14 |
WEIGHTED_AVG(x, w)
| Description | Average of x weighted by w |
|---|---|
| Formula | sum(x * w) / sum(w) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No values: NULL, Weights summing to 0: NULL, Infinity: ±Infinity, Opposite infinities: NULL, Infinite weight: NULL, Infinity with zero weight: NULL, Overflow: ±Infinity |
| Validation | Matches Python numpy.average on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.65 |
| 100 | 0.63 |
| 1,000 | 0.88 |
Counting
APPROX_COUNT_DISTINCT(x)
| Description | Estimated number of distinct values of x |
|---|---|
| Precision | Estimated answers with small typical error since the result is computed from a summary of the data |
| Edge cases | No values: 0. INTEGER, REAL, TEXT, and BLOB values are different values (1, 1.0, '1', and x'31' count as four); a REAL never shares a hash with an INTEGER between -2^51 and 2^51 or near the INTEGER limits; any other collision between values of different types happens only by chance (about 1 in 2^64 for a pair), and the two then count once. |
| Validation | Matches DuckDB approx_count_distinct on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.65 |
| 100 | 0.67 |
| 1,000 | 1.06 |
AVG_COUNT_PER_UNIQUE(x)
| Description | Average number of times each distinct value appears |
|---|---|
| Formula | count(x) / count(distinct x) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No values: NULL |
| Validation | Matches Python statistics.mean on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.65 |
| 100 | 0.50 |
| 1,000 | 0.91 |
COUNT(x)
| Description | Number of values of x |
|---|---|
| Precision | Exact answer since counts are never rounded |
| Edge cases | No values: 0 |
| Validation | SQLite built-in |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.65 |
| 100 | 0.67 |
| 1,000 | 1.03 |
COUNT_ABOVE_MEAN(x)
| Description | Number of values above the mean |
|---|---|
| Formula | count(x > sum(x) / count(x)) |
| Precision | Exact answer since counts are never rounded |
| Edge cases | No values: NULL, Only +Infinity: 0, Only -Infinity: values other than -Infinity, Opposite infinities: NULL |
| Validation | Matches an equivalent DuckDB query on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.64 |
| 100 | 0.52 |
| 1,000 | 0.77 |
COUNT_BELOW_MEAN(x)
| Description | Number of values below the mean |
|---|---|
| Formula | count(x < sum(x) / count(x)) |
| Precision | Exact answer since counts are never rounded |
| Edge cases | No values: NULL, Only +Infinity: values other than +Infinity, Only -Infinity: 0, Opposite infinities: NULL |
| Validation | Matches an equivalent DuckDB query on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.50 |
| 100 | 0.61 |
| 1,000 | 0.88 |
COUNT_DISTINCT(x)
| Description | Number of distinct values of x |
|---|---|
| Formula | count(distinct x) |
| Precision | Exact answer since counts are never rounded |
| Edge cases | No values: 0 |
| Validation | Matches DuckDB count(DISTINCT x) on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.65 |
| 100 | 0.68 |
| 1,000 | 0.75 |
COUNT_IF(x)
| Description | Number of true values of x |
|---|---|
| Formula | sum(x) |
| Precision | Exact answer since counts are never rounded |
| Edge cases | No values: NULL, Values other than 0 or 1: error |
| Validation | Matches Python numpy.count_nonzero on fixed inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.62 |
| 100 | 0.63 |
| 1,000 | 0.93 |
HAS_NO_DUPLICATES(x)
| Description | 1 if no value of x repeats, else 0 |
|---|---|
| Formula | count(distinct x) = count(x) |
| Precision | Exact answer since counts are never rounded |
| Edge cases | No values: 1 |
| Validation | Matches an equivalent DuckDB query on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.52 |
| 100 | 0.69 |
| 1,000 | 0.95 |
PERCENT_UNIQUE(x)
| Description | Share of values that are distinct |
|---|---|
| Formula | count(distinct x) / count(x) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No values: NULL |
| Validation | Matches an equivalent DuckDB query on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.66 |
| 100 | 0.59 |
| 1,000 | 0.77 |
Dispersion
L_SCALE(x)
| Description | Half the mean absolute difference between any two values of x |
|---|---|
| Formula | sum((2i - n - 1) * x(i)) / (n * (n - 1)), x(i) sorted ascending |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | Fewer than 2 values: NULL, Infinity: error, Constant values: 0 |
| Validation | Matches Python scipy.stats.lmoment (order 2) within 1e-9 on fixed inputs; pseudo-oracle against reference.py LScale |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.44 |
| 100 | 0.67 |
| 1,000 | 0.91 |
MAD(x)
| Description | Median absolute deviation of x |
|---|---|
| Formula | median(abs(x - median(x))) |
| Precision | Values are used as doubles (an INTEGER beyond 2^53 is first rounded to its nearest double); from those doubles the MAD is computed exactly and rounded once, so answers are consistent regardless of row order |
| Edge cases | No values: NULL, Infinite or undefined median: NULL, Infinite middle deviation: Infinity |
| Validation | Matches Python scipy.stats.median_abs_deviation on fixed inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.69 |
| 100 | 0.69 |
| 1,000 | 0.95 |
SEM(x)
| Description | Standard error of the mean using the population standard deviation |
|---|---|
| Formula | sqrt(sum((x - avg(x))^2) / count(x)) / sqrt(count(x)) |
| Precision | Correctly rounded and the same in any row order: exact sums (INTEGERs exactly) and one rounding of the square root |
| Edge cases | No values: NULL, One value: 0, Infinity or max - min beyond range: error |
| Validation | Matches DuckDB sem within 1e-9 on fixed and random inputs; pseudo-oracle against reference.py Sem (exact), compared exactly |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.69 |
| 100 | 0.67 |
| 1,000 | 0.98 |
STDDEV_POP(x)
| Description | Population standard deviation without bias correction |
|---|---|
| Formula | sqrt((sum(x^2) - sum(x)^2 / count(x)) / count(x)) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No values: NULL, One value: 0, Infinity: error |
| Validation | Matches Python statistics.pstdev on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.60 |
| 100 | 0.69 |
| 1,000 | 1.12 |
STDDEV_SAMP(x)
| Description | Sample standard deviation with bias correction |
|---|---|
| Formula | sqrt((sum(x^2) - sum(x)^2 / count(x)) / (count(x) - 1)) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | Fewer than 2 values: NULL, Infinity or overflow: error |
| Validation | Matches Python statistics.stdev on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.63 |
| 100 | 0.67 |
| 1,000 | 1.12 |
VAR_POP(x)
| Description | Population variance without bias correction |
|---|---|
| Formula | (sum(x^2) - sum(x)^2 / count(x)) / count(x) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No values: NULL, One value: 0, Infinity or overflow: error |
| Validation | Matches Python statistics.pvariance on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.53 |
| 100 | 0.54 |
| 1,000 | 0.96 |
VAR_SAMP(x)
| Description | Sample variance with bias correction |
|---|---|
| Formula | (sum(x^2) - sum(x)^2 / count(x)) / (count(x) - 1) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | Fewer than 2 values: NULL, Infinity or overflow: error |
| Validation | Matches Python statistics.variance on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.54 |
| 100 | 0.66 |
| 1,000 | 1.08 |
Extremes
ARG_MAX(arg, val)
| Description | Value of one column where another column is largest |
|---|---|
| Precision | Exact answers since the answer is one of the input values |
| Edge cases | No values: NULL, Ties: smallest arg |
| Validation | Matches DuckDB arg_max on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.48 |
| 100 | 0.60 |
| 1,000 | 1.07 |
ARG_MIN(arg, val)
| Description | Value of one column where another column is smallest |
|---|---|
| Precision | Exact answers since the answer is one of the input values |
| Edge cases | No values: NULL, Ties: smallest arg |
| Validation | Matches DuckDB arg_min on fixed inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.55 |
| 100 | 0.66 |
| 1,000 | 0.84 |
MAX(x)
| Description | Largest value of x |
|---|---|
| Precision | Exact answers since the answer is one of the input values |
| Edge cases | No values: NULL |
| Validation | SQLite built-in |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.69 |
| 100 | 0.69 |
| 1,000 | 0.96 |
MAX_COUNT(x)
| Description | Number of values equal to the largest |
|---|---|
| Formula | count(x = max(x)) |
| Precision | Exact answer since counts are never rounded |
| Edge cases | No values: NULL |
| Validation | Matches Python collections.Counter on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.62 |
| 100 | 0.67 |
| 1,000 | 1.06 |
MAX_INTERSECTIONS_POSITION(start, end)
| Description | Leftmost start value where the most [start, end) intervals overlap |
|---|---|
| Formula | min p over starts with max count(start <= p < end) |
| Precision | Exact answers since the answer is one of the input values |
| Edge cases | No rows or only empty intervals: NULL, start > end: error, start = end: covers nothing, Touching intervals: do not overlap, Ties: leftmost, INTEGER/REAL compared exactly |
| Validation | Matches ClickHouse maxIntersectionsPosition on typed inputs; pseudo-oracle against reference.py MaxIntersectionsPosition |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.65 |
| 100 | 0.61 |
| 1,000 | 1.13 |
MIN(x)
| Description | Smallest value of x |
|---|---|
| Precision | Exact answers since the answer is one of the input values |
| Edge cases | No values: NULL |
| Validation | SQLite built-in |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.51 |
| 100 | 0.55 |
| 1,000 | 1.07 |
MIN_COUNT(x)
| Description | Number of values equal to the smallest |
|---|---|
| Formula | count(x = min(x)) |
| Precision | Exact answer since counts are never rounded |
| Edge cases | No values: NULL |
| Validation | Matches Python collections.Counter on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.61 |
| 100 | 0.63 |
| 1,000 | 0.99 |
MIN_K(x, k)
| Description | k smallest values, ascending, as a JSON array (duplicates kept) |
|---|---|
| Precision | Exact answers since the answer is one of the input values |
| Edge cases | No values: NULL, k NULL, not an integer, or outside 1 to 999,999: error |
| Validation | Matches DuckDB min on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.51 |
| 100 | 0.67 |
| 1,000 | 1.10 |
RANGE(x)
| Description | Largest minus smallest value of x |
|---|---|
| Formula | max(x) - min(x) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No values: NULL, Infinity: Infinity, All values the same infinity: NULL, Overflow: Infinity, Equal INTEGER and REAL extremes: the INTEGER is kept |
| Validation | Matches Python numpy.ptp on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.64 |
| 100 | 0.65 |
| 1,000 | 1.10 |
Frequency
APPROX_TOP_K(x, k)
| Description | Approximate k most frequent values of x |
|---|---|
| Precision | Estimated answers with small typical error since the result is computed from a summary of the data |
| Edge cases | No values: NULL, k NULL, not an integer, or outside 1 to 999,999: error |
| Validation | Matches DuckDB approx_top_k on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.68 |
| 100 | 0.57 |
| 1,000 | 1.20 |
DISTINCT_VALUES(x)
| Description | Distinct values, ascending, as a JSON array |
|---|---|
| Precision | Exact answers since the answer is one of the input values |
| Edge cases | No values: NULL |
| Validation | Matches an equivalent DuckDB query on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.69 |
| 100 | 0.59 |
| 1,000 | 0.95 |
MODE(x)
| Description | Most frequent value of x |
|---|---|
| Precision | Exact answers since the answer is one of the input values |
| Edge cases | No values: NULL |
| Validation | Matches DuckDB mode on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.61 |
| 100 | 0.54 |
| 1,000 | 0.79 |
SINGLE_VALUE_OR_NULL(x)
| Description | The one distinct non-NULL value of x, or NULL |
|---|---|
| Formula | x when count(DISTINCT x) = 1, else NULL |
| Precision | Exact answers since the answer is one of the input values |
| Edge cases | No values: NULL, Two different values: NULL, INTEGER/REAL compared exactly (1 = 1.0), Returned form: INTEGER if seen, -0.0 only when every zero is -0.0, TEXT/BLOB: error |
| Validation | Matches ClickHouse singleValueOrNull on typed inputs; pseudo-oracle against reference.py SingleValueOrNull |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.63 |
| 100 | 0.68 |
| 1,000 | 1.09 |
TOP_K_FREQUENCIES(x, k)
| Description | Counts of the k most common values, as a JSON array |
|---|---|
| Precision | Exact answer since counts are never rounded |
| Edge cases | No values: NULL, k NULL, not an integer, or outside 1 to 999,999: error |
| Validation | Matches Python collections.Counter.most_common on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.63 |
| 100 | 0.66 |
| 1,000 | 0.91 |
WEIGHTED_TOP_K(x, w, k)
| Description | The k values with the largest total weight, largest first, as a JSON array |
|---|---|
| Precision | Exact answers since the answer is one of the input values |
| Edge cases | No values: NULL, k NULL, not an integer, or outside 1 to 999,999: error, +Infinity total weight: ranked first, -Infinity total weight: ranked last, Opposite infinite weights: ranked last |
| Validation | Matches ClickHouse topKWeighted on fixed inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.68 |
| 100 | 0.59 |
| 1,000 | 0.98 |
Quantiles
APPROX_QUANTILE(x, q)
| Description | Approximate quantile of x at q |
|---|---|
| Precision | Estimated answers with small typical error since the result is computed from a summary of the data |
| Edge cases | No values: NULL, q NULL or outside -1 to 1: error, Interpolating between opposite infinities: NULL, Equal values at q = 0 or 1: +0.0 unless every tied zero is -0.0 |
| Validation | Matches DuckDB approx_quantile on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.54 |
| 100 | 0.77 |
| 1,000 | 1.45 |
EXPECTILE(x, q)
| Description | Expectile of x at q: a tail level that moves with how far the tail reaches |
|---|---|
| Formula | t with a * sum(max(x - t, 0)) = (1 - a) * sum(max(t - x, 0)), a = q (a = 1 - |q| when q < 0) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No values: NULL, q NULL or outside -1 to 1: error, q = 0: minimum, q = 1: maximum, A zero minimum or maximum: +0.0 unless every zero is -0.0, Infinity: ±Infinity, Opposite infinities: NULL |
| Validation | Matches Python scipy.stats.expectile within 1e-9 on fixed inputs; pseudo-oracle against reference.py Expectile |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.67 |
| 100 | 0.64 |
| 1,000 | 1.02 |
QUANTILE_CONT(x, q)
| Description | Interpolated quantile of x at q |
|---|---|
| Formula | x[floor(r)] + (r - floor(r)) * (x[ceil(r)] - x[floor(r)]), r = (count(x) - 1) * |q|, x sorted ascending (descending when q < 0) |
| Precision | Exact on the values as stored (INTEGER and REAL; an INTEGER beyond 2^53 is not rounded first), only the final answer is rounded, so answers are consistent regardless of row order |
| Edge cases | No values: NULL, q NULL or outside -1 to 1: error, Interpolating between opposite infinities: NULL, A zero result: +0.0 unless every zero is -0.0 |
| Validation | Matches Python numpy.quantile on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.67 |
| 100 | 0.48 |
| 1,000 | 0.86 |
QUANTILE_DISC(x, q)
| Description | Value of x at quantile q |
|---|---|
| Precision | Exact answers since the answer is one of the input values |
| Edge cases | No values: NULL, q NULL or outside -1 to 1: error, Equal INTEGER and REAL: the INTEGER, -0.0 and +0.0: +0.0 unless every tied zero is -0.0 |
| Validation | Matches Python numpy.quantile on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.67 |
| 100 | 0.65 |
| 1,000 | 0.93 |
RESERVOIR_QUANTILE(x, q[, sample_size])
| Description | Value of x at quantile q from a random sample |
|---|---|
| Precision | Estimated answers with small typical error since the result is computed from a summary of the data |
| Edge cases | No values: NULL, q NULL or outside -1 to 1: error, sample_size NULL, not an integer, or outside 1 to 2,147,483,647: error, Equal INTEGER and REAL: the INTEGER, -0.0: returned as +0.0 |
| Validation | Matches DuckDB reservoir_quantile on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.55 |
| 100 | 0.70 |
| 1,000 | 1.08 |
WEIGHTED_QUANTILE_CONT(x, w, q)
| Description | Interpolated quantile of x repeated w times, at q |
|---|---|
| Formula | x[k] + (r - k) * (x[k+1] - x[k]), r = (sum(w) - 1) * |q|, k = floor(r), x[i] the i-th of the values each repeated w times, sorted ascending (descending when q < 0) |
| Precision | Exact on the values as stored (INTEGER and REAL; an INTEGER beyond 2^53 is not rounded first), only the final answer is rounded, so answers are consistent regardless of row order |
| Edge cases | No values: NULL, Weights summing to 0: NULL, Negative, infinite, or non-whole weight: error, q NULL or outside -1 to 1: error, Interpolating between opposite infinities: NULL, A zero result: +0.0 unless every zero with a weight is -0.0 |
| Validation | Matches ClickHouse quantileExactWeightedInterpolated on fixed inputs; pseudo-oracle against reference.py WeightedQuantileCont (equal to QUANTILE_CONT on repeated values) |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.49 |
| 100 | 0.55 |
| 1,000 | 1.02 |
WEIGHTED_QUANTILE_DISC(x, w, q)
| Description | Smallest value whose running weight reaches q × total weight |
|---|---|
| Precision | Exact answers since the answer is one of the input values |
| Edge cases | No values: NULL, Weights summing to 0: NULL, Negative or infinite weight: error, q NULL or outside -1 to 1: error, Equal INTEGER and REAL: the INTEGER, -0.0 and +0.0: +0.0 unless every tied zero is -0.0 (values with weight 0 do not count) |
| Validation | Matches Python numpy.quantile on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.53 |
| 100 | 0.72 |
| 1,000 | 1.08 |
Regression
CORR(y, x)
| Description | Correlation coefficient of y and x |
|---|---|
| Formula | (sum(x * y) - sum(x) * sum(y) / count(x)) / sqrt((sum(x^2) - sum(x)^2 / count(x)) * (sum(y^2) - sum(y)^2 / count(y))) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | Fewer than 2 pairs: NULL, Constant x or y: NULL, Infinity: error |
| Validation | Matches DuckDB corr on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.55 |
| 100 | 0.69 |
| 1,000 | 1.17 |
COVAR_POP(y, x)
| Description | Population covariance of y and x |
|---|---|
| Formula | (sum(x * y) - sum(x) * sum(y) / count(x)) / count(x) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No pairs: NULL, One pair: 0, Infinity or result beyond range: NULL |
| Validation | Matches DuckDB covar_pop on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.50 |
| 100 | 0.68 |
| 1,000 | 1.16 |
COVAR_SAMP(y, x)
| Description | Sample covariance of y and x |
|---|---|
| Formula | (sum(x * y) - sum(x) * sum(y) / count(x)) / (count(x) - 1) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | Fewer than 2 pairs: NULL, Infinity or result beyond range: NULL |
| Validation | Matches DuckDB covar_samp on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.63 |
| 100 | 0.67 |
| 1,000 | 1.03 |
RANK_CORR(y, x)
| Description | Rank correlation of y and x (Pearson of the average ranks) |
|---|---|
| Formula | Sxy / sqrt(Sxx · Syy) over the average ranks of x and y |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | Fewer than 2 pairs: NULL, Constant x or y: NULL, Ties: average ranks, INTEGER/REAL compared exactly |
| Validation | Matches ClickHouse rankCorr on inputs without ties; pseudo-oracle against reference.py RankCorr |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.67 |
| 100 | 0.65 |
| 1,000 | 1.09 |
REGR_AVGX(y, x)
| Description | Average of x over complete (y, x) pairs |
|---|---|
| Formula | sum(x) / count(x) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No pairs: NULL, Infinity: ±Infinity, Opposite infinities: NULL |
| Validation | Matches Python statistics.mean on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.50 |
| 100 | 0.66 |
| 1,000 | 1.00 |
REGR_AVGY(y, x)
| Description | Average of y over complete (y, x) pairs |
|---|---|
| Formula | sum(y) / count(y) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No pairs: NULL, Infinity: ±Infinity, Opposite infinities: NULL |
| Validation | Matches Python statistics.mean on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.66 |
| 100 | 0.57 |
| 1,000 | 1.02 |
REGR_COUNT(y, x)
| Description | Number of complete (y, x) pairs |
|---|---|
| Precision | Exact answer since counts are never rounded |
| Edge cases | No pairs: 0 |
| Validation | Matches DuckDB regr_count on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.45 |
| 100 | 0.57 |
| 1,000 | 0.83 |
REGR_INTERCEPT(y, x)
| Description | Intercept of the least-squares line of y on x |
|---|---|
| Formula | (sum(y) - REGR_SLOPE(y, x) * sum(x)) / count(x) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | Fewer than 2 pairs: NULL, Constant x: NULL, Infinite y: NULL, Infinite x: error, Intercept beyond range: NULL |
| Validation | Matches DuckDB regr_intercept on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.64 |
| 100 | 0.67 |
| 1,000 | 1.14 |
REGR_R2(y, x)
| Description | Coefficient of determination of the least-squares line of y on x |
|---|---|
| Formula | CORR(y, x)^2 |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | Fewer than 2 pairs: NULL, Constant x: NULL, Constant y: 1, Infinity: error |
| Validation | Matches DuckDB regr_r2 on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.68 |
| 100 | 0.56 |
| 1,000 | 0.86 |
REGR_SLOPE(y, x)
| Description | Slope of the least-squares line of y on x |
|---|---|
| Formula | (sum(x * y) - sum(x) * sum(y) / count(x)) / (sum(x^2) - sum(x)^2 / count(x)) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | Fewer than 2 pairs: NULL, Constant x: NULL, Infinite y: NULL, Infinite x: error, Slope beyond range: NULL |
| Validation | Matches DuckDB regr_slope on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.51 |
| 100 | 0.58 |
| 1,000 | 1.17 |
REGR_SXX(y, x)
| Description | Sum of squared deviations of x |
|---|---|
| Formula | sum(x^2) - sum(x)^2 / count(x) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No pairs: NULL, One pair: 0, Infinite x or result beyond range: error |
| Validation | Matches Python statistics module (exact sum of squares) on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.53 |
| 100 | 0.55 |
| 1,000 | 0.83 |
REGR_SXY(y, x)
| Description | Sum of products of the deviations of y and x |
|---|---|
| Formula | sum(x * y) - sum(x) * sum(y) / count(x) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No pairs: NULL, One pair: 0, Infinity or result beyond range: NULL |
| Validation | Matches DuckDB regr_sxy on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.52 |
| 100 | 0.69 |
| 1,000 | 0.83 |
REGR_SYY(y, x)
| Description | Sum of squared deviations of y |
|---|---|
| Formula | sum(y^2) - sum(y)^2 / count(y) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No pairs: NULL, One pair: 0, Infinite y or result beyond range: error |
| Validation | Matches Python statistics module (exact sum of squares) on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.68 |
| 100 | 0.72 |
| 1,000 | 0.95 |
THEIL_SEN_SLOPE(y, x)
| Description | Median of the slopes between every two points (y, x) |
|---|---|
| Formula | median((y_j - y_i) / (x_j - x_i)) over i < j with x_i <> x_j |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | Fewer than 2 pairs: NULL, Every x equal: NULL, Infinite x: error, Infinite y: NULL, Beyond the largest double: NULL |
| Validation | Matches Python scipy.stats.theilslopes within 1e-9 on fixed inputs; pseudo-oracle against reference.py TheilSenSlope |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.62 |
| 100 | 0.84 |
| 1,000 | 10.05 |
WASSERSTEIN_DISTANCE(u, v)
| Description | Area between the two columns' empirical CDFs, in the values' units |
|---|---|
| Formula | sum(abs(U(t) - V(t)) * (next(t) - t)) over the sorted union of u and v |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | A column with no values: NULL, NULLs skipped per value, Different shares of an infinity: Infinity, Same shares: finite, Overflow: Infinity |
| Validation | Matches Python scipy.stats.wasserstein_distance within 1e-9 on fixed inputs; pseudo-oracle against reference.py WassersteinDistance |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.52 |
| 100 | 0.69 |
| 1,000 | 0.97 |
Shape
ENTROPY(x)
| Description | Entropy in bits of the distribution of x's values |
|---|---|
| Precision | Approximate answers with error limited to the last digit since intermediate logarithms are rounded |
| Edge cases | No values: 0 |
| Validation | Matches DuckDB entropy on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.63 |
| 100 | 0.67 |
| 1,000 | 0.98 |
KURTOSIS(x)
| Description | Sample excess kurtosis with bias correction |
|---|---|
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | Fewer than 4 values: NULL, All values equal: NULL, Infinity: error |
| Validation | Matches DuckDB kurtosis on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.64 |
| 100 | 0.68 |
| 1,000 | 1.18 |
KURTOSIS_POP(x)
| Description | Population excess kurtosis without bias correction |
|---|---|
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | Fewer than 2 values: NULL, All values equal: NULL, Infinity: error |
| Validation | Matches DuckDB kurtosis_pop on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.64 |
| 100 | 0.68 |
| 1,000 | 1.18 |
SKEWNESS(x)
| Description | Sample skewness with bias correction |
|---|---|
| Precision | Approximate answers since intermediate square roots and divisions are rounded |
| Edge cases | Fewer than 3 values: NULL, All values equal: NULL, Infinity or max - min beyond range: error |
| Validation | Matches DuckDB skewness on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.56 |
| 100 | 0.62 |
| 1,000 | 1.30 |
SKEWNESS_POP(x)
| Description | Population skewness without bias correction |
|---|---|
| Formula | m3 / m2^1.5, with m2 = Σ(x − x̄)²/n and m3 = Σ(x − x̄)³/n |
| Precision | Approximate answers since intermediate square roots and divisions are rounded |
| Edge cases | Fewer than 2 values: NULL, All values equal: NULL, Infinity or max - min beyond range: error |
| Validation | Matches ClickHouse skewPop on fixed inputs; pseudo-oracle against reference.py SkewnessPop |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.76 |
| 100 | 0.73 |
| 1,000 | 1.36 |
Summation
FSUM(x)
| Description | Exact sum of x |
|---|---|
| Formula | sum(x) |
| Precision | Consistent answers regardless of row order since only final answer is rounded |
| Edge cases | No values: NULL, Infinity: ±Infinity, Opposite infinities: NULL, Overflow: ±Infinity |
| Validation | Matches Python math.fsum on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.55 |
| 100 | 0.56 |
| 1,000 | 1.08 |
PRODUCT(x)
| Description | Product of x |
|---|---|
| Precision | Correctly rounded and the same in any row order: the exact product (INTEGERs exactly), rounded once |
| Edge cases | No values: NULL, Infinity: ±Infinity, Zero times infinity: NULL, Overflow: ±Infinity |
| Validation | Matches Python math.prod on fixed and random inputs |
workload vs latency
| last N | p99 (ms) |
|---|---|
| 10 | 0.48 |
| 100 | 0.56 |
| 1,000 | 1.26 |