Statistical Functions

Gnok provides statistical analysis through SQL aggregate functions integrated into the query engine.

SQL Aggregate Functions

These functions are fully integrated into the SQL engine and work in SELECT, GROUP BY, and window contexts.

Regression

Function	Description
REGR_SLOPE(y, x)	Slope of the OLS regression line
REGR_INTERCEPT(y, x)	Y-intercept of the regression line
REGR_R2(y, x)	Coefficient of determination (R-squared, 0-1)
REGR_COUNT(y, x)	Count of non-NULL pairs
REGR_AVGX(y, x)	Average of the independent variable
REGR_AVGY(y, x)	Average of the dependent variable
REGR_SXX(y, x)	Sum of squares of the independent variable
REGR_SXY(y, x)	Sum of cross products
REGR_SYY(y, x)	Sum of squares of the dependent variable

-- Fit a linear regression of revenue vs. ad spend
SELECT
    REGR_SLOPE(revenue, ad_spend) AS slope,
    REGR_INTERCEPT(revenue, ad_spend) AS intercept,
    REGR_R2(revenue, ad_spend) AS r_squared
FROM marketing_data;

An R-squared close to 1.0 indicates a strong fitted linear relationship in these observations, not causation or held-out predictive accuracy. Regression/correlation calculations exclude NULL pairs; degenerate inputs such as zero variance need explicit interpretation.

Correlation and Covariance

Function	Description
CORR(y, x)	Pearson correlation coefficient (-1 to +1)
COVAR_POP(y, x)	Population covariance
COVAR_SAMP(y, x)	Sample covariance

SELECT
    CORR(temperature, ice_cream_sales) AS correlation,
    COVAR_SAMP(height, weight) AS covariance
FROM measurements;

Dispersion

Function	Description
STDDEV(x) / STDDEV_SAMP(x)	Sample standard deviation
STDDEV_POP(x)	Population standard deviation
VARIANCE(x) / VAR_SAMP(x)	Sample variance
VAR_POP(x)	Population variance
KURTOSIS(x)	Excess kurtosis (distribution shape)
SKEWNESS(x)	Skewness (distribution asymmetry)

SELECT
    AVG(response_time_ms) AS avg_response,
    STDDEV(response_time_ms) AS stddev_response,
    SKEWNESS(response_time_ms) AS skew
FROM api_logs
WHERE timestamp > NOW() - INTERVAL '1 hour';

Percentiles

Function	Description
PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY expr)	Continuous percentile (interpolated)
PERCENTILE_DISC(p) WITHIN GROUP (ORDER BY expr)	Discrete percentile (selected observed value by cumulative rank)
MEDIAN(x)	Continuous median: PERCENTILE_CONT(0.5); interpolates between middle values for even counts

SELECT
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY response_time_ms) AS p50,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY response_time_ms) AS p90,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time_ms) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY response_time_ms) AS p99
FROM api_logs;

Other Statistical Aggregates

Function	Description
APPROX_COUNT_DISTINCT(x)	Approximate distinct count (HyperLogLog)
ENTROPY(x)	Shannon entropy in bits
KAHAN_SUM(x)	Numerically stable sum (Kahan compensated)
PRODUCT(x)	Multiplicative aggregate
ARG_MIN(val, key) / ARG_MAX(val, key)	Value at min/max key position
MODE(x)	Most frequent value

All aggregate functions support the FILTER (WHERE condition) clause:

SELECT
    STDDEV(amount) FILTER (WHERE region = 'US') AS us_stddev,
    STDDEV(amount) FILTER (WHERE region = 'EU') AS eu_stddev
FROM orders;

Use Cases

The source tables in these fragments are illustrative. For model evaluation, use separate held-out rows and distinguish population from sample statistics.

Use Case	SQL Functions
SLA monitoring	PERCENTILE_CONT (p95, p99 latency)
Trend analysis	REGR_SLOPE, REGR_R2
Data quality	AVG, STDDEV, MIN, MAX
Feature engineering	CORR, COVAR_SAMP
A/B testing	AVG, VARIANCE, STDDEV
Distribution analysis	KURTOSIS, SKEWNESS, ENTROPY