Statistical Functions

Pinot provides statistical aggregation functions for computing variance, standard deviation, covariance, skewness, and kurtosis. These functions are useful for data analysis, quality monitoring, and understanding the distribution of your data.

Variance Functions

VAR_POP / VARPOP

VAR_POP(col)

Returns the population variance of a numeric column as Double. Population variance measures how far values are spread out from the mean, using all data points.

SELECT VAR_POP(playerScore) AS score_variance
FROM gameStats

VAR_SAMP / VARSAMP

VAR_SAMP(col)

Returns the sample variance of a numeric column as Double. Sample variance uses Bessel's correction (divides by N-1 instead of N), making it an unbiased estimator when working with a sample of the population.

SELECT VAR_SAMP(responseTime) AS response_variance
FROM apiMetrics

Standard Deviation Functions

STDDEV_POP / STDDEVPOP

STDDEV_POP(col)

Returns the population standard deviation of a numeric column as Double. This is the square root of the population variance.

SELECT STDDEV_POP(latency) AS latency_stddev
FROM requestMetrics

STDDEV_SAMP / STDDEVSAMP

STDDEV_SAMP(col)

Returns the sample standard deviation of a numeric column as Double. This is the square root of the sample variance.

SELECT STDDEV_SAMP(revenue) AS revenue_stddev
FROM salesData

Covariance Functions

COVAR_POP / COVARPOP

COVAR_POP(col1, col2)

Returns the population covariance between two numeric columns as Double. Covariance measures how two variables change together.

SELECT COVAR_POP(adSpend, revenue) AS spend_revenue_covariance
FROM campaignMetrics

COVAR_SAMP / COVARSAMP

COVAR_SAMP(col1, col2)

Returns the sample covariance between two numeric columns as Double.

SELECT COVAR_SAMP(temperature, energyUsage) AS temp_energy_covariance
FROM sensorData

Distribution Shape Functions

SKEWNESS

SKEWNESS(col)

Returns the skewness of a numeric column as Double. Skewness measures the asymmetry of the probability distribution. A positive skew indicates a longer right tail, while a negative skew indicates a longer left tail.

SELECT SKEWNESS(orderAmount) AS amount_skewness
FROM orders

KURTOSIS

KURTOSIS(col)

Returns the kurtosis of a numeric column as Double. Kurtosis measures the "tailedness" of the probability distribution. Higher kurtosis indicates more outliers (heavier tails).

SELECT KURTOSIS(responseTime) AS latency_kurtosis
FROM apiMetrics

Notes

• With advanced null handling enabled, covariance functions skip a row when either input is null. They return NULL when no rows contribute; COVAR_SAMP also returns NULL when fewer than two rows contribute. Without advanced null handling, Pinot uses column default null values.

• These functions operate on numeric columns only (INT, LONG, FLOAT, DOUBLE).

• For variance and standard deviation, use the _POP variant when you have the entire population and the _SAMP variant when working with a sample.

• All statistical functions are supported in both the Single-Stage Engine (SSE) and Multi-Stage Engine (MSE).