Statistical functions

106 functions. Names resolve case-insensitively; an alias binds the same function as its canonical name. Each entry gives the signatures, the behaviour and an example.

analysis_of_variance

Returns the F statistic and significance probability for differences among group means.

analysis_of_variance(value:int*|uint*|float32|float64|decimal(P,S),group_id:uint64) -> tuple(float64,float64)

Performs one-way ANOVA on numeric values assigned to independent samples by group_id and returns an (F statistic, p value) tuple. Rows with NULL inputs are excluded; insufficient sample degrees of freedom returns NULL. Streaming state holds per-sample counts and moments under a declared group cap, raising RESOURCE_LIMIT when too many groups are encountered.

Example

(
  SELECT analysis_of_variance(x, group_id)
  FROM (VALUES (1, cast(0 AS UINT64))) AS readings(x, group_id)
)
-- → NULL

corr

Measures Pearson linear association between paired numeric observations.

corr(x:int*|uint*|float32|float64|decimal(P,S),y:int*|uint*|float32|float64|decimal(P,S)) -> float64

Returns the centered cross-product divided by the product of centered square-root sums, producing Pearson correlation. Pairs containing NULL are excluded; fewer than two usable pairs or zero variance in either coordinate produces NULL. Fixed central-moment state supports streaming updates, and merge order determines floating replay; checked arithmetic overflow raises NUMERIC_OVERFLOW.

Example

(SELECT corr(x, y) FROM (VALUES (1, 2), (2, 4), (3, 6)) AS readings(x, y))
-- → 1.0

corr_matrix

Builds a symmetric Pearson association grid from fixed-length observation vectors.

corr_matrix(values:array(int*|uint*|float32|float64|decimal(P,S))) -> array(array(float64))

Treats each input array as one observation and computes every pairwise Pearson correlation using complete vectors only. A NULL array or any NULL component excludes that row, and insufficient observations or a constant coordinate produces NULL cells. Unequal vector lengths raise INVALID_ARGUMENT; the bind-time dimension and squared-moment storage limits raise RESOURCE_LIMIT when exceeded.

Example

(
  SELECT corr_matrix(v)
  FROM (VALUES ([1, 2]), ([2, 4]), ([3, 6])) AS readings(v)
)
-- → [[1.0, 1.0], [1.0, 1.0]]

corr_stable

Estimates linear dependence with compensated paired central moments.

corr_stable(x:int*|uint*|float32|float64|decimal(P,S),y:int*|uint*|float32|float64|decimal(P,S)) -> float64

Computes Pearson correlation while compensating central-moment accumulation to reduce cancellation for numerically difficult values. Pairs with either coordinate NULL are omitted, and zero variance in either coordinate or too few complete pairs returns NULL. The compensated streaming state remains bounded; NUMERIC_OVERFLOW signals checked arithmetic failure and a fixed merge order is still needed for identical replay.

Example

(
  SELECT corr_stable(x, y)
  FROM (VALUES (1, 2), (2, 4), (3, 6)) AS readings(x, y)
)
-- → 1.0

covar_pop

Measures joint numeric dispersion using the entire observed population.

covar_pop(x:int*|uint*|float32|float64|decimal(P,S),y:int*|uint*|float32|float64|decimal(P,S)) -> float64

Returns the sum of paired centered products divided by the number of accepted pairs. Any pair with a NULL coordinate is skipped, an empty group returns NULL, and one complete pair yields 0.0. Streaming state maintains count, means, and co-moment with fixed merge ordering; NUMERIC_OVERFLOW reports an unrepresentable checked result.

Example

(SELECT covar_pop(x, y) FROM (VALUES (1, 2), (3, 6)) AS readings(x, y))
-- → 2.0

covar_pop_matrix

Returns all population co-dispersions for the columns of numeric vectors.

covar_pop_matrix(values:array(int*|uint*|float32|float64|decimal(P,S))) -> array(array(float64))

Calculates a symmetric covariance matrix by dividing each centered cross-product by the count of complete vectors. It skips vectors containing NULL, produces NULL cells when no vector is accepted, and gives zero covariance from a single complete observation. Vector dimension must remain constant or INVALID_ARGUMENT is raised, and the admitted quadratic state ceiling is enforced with RESOURCE_LIMIT.

Example

(SELECT covar_pop_matrix(v) FROM (VALUES ([1, 2]), ([3, 6])) AS readings(v))
-- → [[1.0, 2.0], [2.0, 4.0]]

covar_pop_stable

Computes compensated population covariance to reduce subtractive cancellation.

covar_pop_stable(x:int*|uint*|float32|float64|decimal(P,S),y:int*|uint*|float32|float64|decimal(P,S)) -> float64

Returns the mean centered product of complete numeric pairs using compensated central moments rather than uncompensated accumulation. NULL in either coordinate excludes the pair; no complete pair returns NULL and one pair yields zero. Its fixed streaming accumulator retains correction terms, and checked arithmetic that exceeds the numeric range raises NUMERIC_OVERFLOW.

Example

(SELECT covar_pop_stable(x, y) FROM (VALUES (1, 2), (3, 6)) AS readings(x, y))
-- → 2.0

covar_samp

Estimates unbiased co-variation from a numeric sample using one lost degree of freedom.

covar_samp(x:int*|uint*|float32|float64|decimal(P,S),y:int*|uint*|float32|float64|decimal(P,S)) -> float64

Divides the centered cross-product total by n-1 for the n complete numeric pairs. NULL in either coordinate removes the whole pair, and fewer than two accepted pairs returns NULL. Incremental central moments avoid retaining observations, while consistent merge order controls rounding; arithmetic overflow raises NUMERIC_OVERFLOW.

Example

(SELECT covar_samp(x, y) FROM (VALUES (1, 2), (3, 6)) AS readings(x, y))
-- → 4.0

covar_samp_matrix

Produces an unbiased covariance grid for multivariate samples.

covar_samp_matrix(values:array(int*|uint*|float32|float64|decimal(P,S))) -> array(array(float64))

Uses complete fixed-dimensional arrays as observations and divides every centered cross-product by n-1. Rows containing NULL are removed as a whole, and fewer than two complete vectors yields NULL matrix cells. Changing dimension raises INVALID_ARGUMENT, while the configured dimension and byte ceilings bound streaming moment storage and raise RESOURCE_LIMIT on excess.

Example

(SELECT covar_samp_matrix(v) FROM (VALUES ([1, 2]), ([3, 6])) AS readings(v))
-- → [[2.0, 4.0], [4.0, 8.0]]

covar_samp_stable

Returns a bias-corrected paired dispersion estimate with compensation.

covar_samp_stable(x:int*|uint*|float32|float64|decimal(P,S),y:int*|uint*|float32|float64|decimal(P,S)) -> float64

Accumulates a compensated co-moment from complete pairs and divides it by one less than their count. NULL pairs are skipped and fewer than two usable observations returns NULL, preserving the sample estimator’s degrees of freedom. Correction terms belong to bounded stream state; NUMERIC_OVERFLOW is reported for checked arithmetic failure.

Example

(SELECT covar_samp_stable(x, y) FROM (VALUES (1, 2), (3, 6)) AS readings(x, y))
-- → 4.0

cramers_v

Measures categorical association strength from a contingency table.

cramers_v(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json],y:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json]) -> float64

Computes Cramér’s V from the chi-squared statistic of paired category frequencies, normalized for table dimensions and count. NULL pairs are excluded, and an empty table or degenerate marginal counts returns NULL. Exact streaming category cells require admitted category and byte bounds, with RESOURCE_LIMIT when those are exceeded.

Example

(
  SELECT cramers_v(x, y)
  FROM (VALUES ('a', 'c'), ('a', 'c'), ('b', 'd'), ('b', 'd')) AS readings(x, y)
)
-- → 1.0

entropy

Measures Shannon uncertainty in bits from empirical value frequencies.

entropy(x:T[bool|int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6|enum|array(E)|map(K,V)|tuple(T1,...,Tn)|json]) -> float64

Returns -sum(p×log2(p)) over the observed frequencies of distinct non-NULL values. Empty effective input yields NULL and a group containing only one distinct value has entropy zero. Exact frequency counts allow streaming insertions and deletions under a declared distinct/group cap, raising RESOURCE_LIMIT rather than silently approximating when full.

Example

(SELECT entropy(x) FROM (VALUES ('a'), ('b')) AS readings(x))
-- → 1.0

histogram

Approximates a numeric distribution as adaptive interval bounds and bucket heights.

histogram(bins:uint32)(x:number) -> array(tuple(float64,float64,float64))

Returns an array of (lower bound, upper bound, height) tuples describing an adaptive histogram with the requested maximum number of bins. NULL observations are skipped and no accepted numbers produce an empty array; bins must be a positive bind-time constant or INVALID_ARGUMENT is raised. Continuous retractions require explicitly capped candidate history and raise RESOURCE_LIMIT if its capacity is exceeded.

Example

(SELECT histogram(3)(x) FROM (VALUES (cast(NULL AS FLOAT64))) AS readings(x))
-- → []

kurt_pop

Calculates excess tail heaviness from the fourth population central moment.

kurt_pop(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Returns m4/m2²-3 using population central moments of non-NULL numbers, making the Gaussian reference value zero. Empty input, insufficient observations, or zero variance yields NULL. Count and the first four central moments form bounded streaming state; fixed floating merge order supports replay and checked arithmetic failure raises NUMERIC_OVERFLOW.

Example

(SELECT kurt_pop(x) FROM (VALUES (1), (3)) AS readings(x))
-- → -2.0

kurt_samp

Estimates bias-adjusted excess kurtosis for a numeric sample.

kurt_samp(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Applies the sample-size correction to excess kurtosis computed from the second and fourth central moments. NULL numbers do not count, and fewer than four accepted observations or zero variance returns NULL. Streaming state retains four central moments rather than raw samples, and NUMERIC_OVERFLOW reports checked arithmetic failure.

Example

(SELECT kurt_samp(x) FROM (VALUES (1), (2), (3), (4)) AS readings(x))
-- → -1.2

mann_whitney_u_test

Returns a rank-based U statistic and probability for comparing two samples.

mann_whitney_u_test(value:int*|uint*|float32|float64|decimal(P,S),sample_index:uint8) -> tuple(float64,float64)
mann_whitney_u_test(alternative:string,continuity:bool)(value:int*|uint*|float32|float64|decimal(P,S),sample_index:uint8) -> tuple(float64,float64)

Ranks observations from sample_index 0 and 1 together, averages tied ranks, and returns the Mann–Whitney U statistic with its p value. NULL inputs are skipped and an empty sample yields NULL; incompatible sample indices or alternative parameters raise INVALID_ARGUMENT. Optional constant alternative and continuity settings select the test contract, while capped ranked sample storage raises RESOURCE_LIMIT on exhaustion.

Example

(
  SELECT mann_whitney_u_test(x, sample_index)
  FROM (VALUES (1, cast(0 AS UINT8))) AS readings(x, sample_index)
)
-- → NULL

median

Estimates the middle reading from a reservoir holding at most 8,192 numeric observations.

median(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Computes the 0.5 quantile of a reservoir sample containing no more than 8192 accepted values per group. NULL readings are skipped and an empty group yields NULL; sample and merge order can change the estimate. Streaming replay must preserve the sampling seed and merge order, and retractions require bounded replay state or admission fails. The result is exact over the whole input.

Example

(SELECT median(12))
-- → 12

median_bfloat16

Approximates the central numeric value using a compact histogram of reduced precision floating-point bins.

median_bfloat16(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Reduces observations to BFloat16 histogram bins and reads the 0.5 quantile as float64; BFloat16 is internal storage rather than a logical input type. Missing values do not contribute, and a group without non-NULL observations produces NULL. Each streaming group has 65536 counters; append and merge are supported, while deletions need bounded replay or are rejected at admission.

Example

(SELECT median_bfloat16(8))
-- → 8

median_bfloat16_weighted

Finds a frequency-adjusted midpoint from numeric measurements rounded into 16-bit exponent-and-mantissa buckets.

median_bfloat16_weighted(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64

Uses unsigned multiplicities to populate the reduced-precision BFloat16 histogram and estimates its halfway point as float64. A NULL measurement or weight removes that pair from consideration, and no accepted positive mass gives NULL; total-weight overflow raises NUMERIC_OVERFLOW. This retained extension has fixed counter storage, but streaming retractions require bounded replay. Its weighted upstream spelling remains unverified in the offline sources.

Example

(SELECT median_bfloat16_weighted(16, 3))
-- → 16

median_dd

Returns a relative-error midpoint estimate from logarithmically binned sensor magnitudes.

median_dd(relative_accuracy:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Applies a DDSketch-style relative_accuracy setting to estimate the 0.5 quantile of numeric input. NULL is ignored and absence of accepted readings returns NULL; an invalid accuracy raises INVALID_ARGUMENT. The declared bin ceiling is strict: exceeding it raises RESOURCE_LIMIT instead of collapsing bins and weakening accuracy. Appending and merging use bounded sketch state, whereas retractions require admitted replay storage.

Example

(SELECT median_dd(0.01)(cast(NULL AS FLOAT64)))
-- → NULL

median_deterministic

Estimates a reproducible halfway value from observations selected by persisted hashes of row determinators.

median_deterministic(x:int*|uint*|float32|float64|decimal(P,S),determinator:uint64) -> float64

Chooses up to 8192 observations using stable hashes of the uint64 determinator and returns the sample median as float64. The determinator controls sampling rather than contributing to the measured value; NULL pairs are skipped and an empty effective sample returns NULL. Persisting the hash policy makes replay selection stable, but retracting stream rows still requires bounded replay state or fails admission.

Example

(SELECT median_deterministic(9, 42))
-- → 9

median_exact

Selects the true central order statistic from retained numeric or temporal values without sampling.

median_exact(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)]) -> T

Retains accepted values and evaluates the exact 0.5 order statistic, preserving the input numeric or temporal type. NULL entries are excluded; no retained values means NULL. Continuous aggregation requires a positive maximum_group_elements and a byte ceiling, with RESOURCE_LIMIT raised before an insertion would exceed either limit.

Example

(SELECT median_exact(7))
-- → 7

median_exact_high

Returns the upper middle observation when sorted data have two candidates for their central rank.

median_exact_high(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)]) -> T

Evaluates an exact median and resolves a split central rank by choosing the upper observation, keeping its original type. NULL rows are omitted and a group containing only NULLs has result NULL. The streaming implementation retains ordered values within declared element and byte limits; excess state fails with RESOURCE_LIMIT before insertion.

Example

(SELECT median_exact_high(x) FROM (VALUES (2), (8)) AS readings(x))
-- → 8

median_exact_low

Chooses the lower center item of an even-sized ordered collection, retaining its numeric or date type.

median_exact_low(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)]) -> T

When the middle rank falls between two observations, this exact aggregate returns the lower one rather than averaging them. It accepts numeric and temporal values, ignores NULLs, and produces NULL for an empty effective collection. Stream admission requires finite retained-value storage; exceeding its element count or byte budget raises RESOURCE_LIMIT.

Example

(SELECT median_exact_low(x) FROM (VALUES (2), (8)) AS readings(x))
-- → 2

median_exact_weighted

Locates the halfway observation after applying integer occurrence counts to numeric values or timestamps.

median_exact_weighted(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)],weight:uint64) -> T

Treats each uint64 weight as an observation multiplicity and finds the exact central order statistic without converting its type. Pairs containing NULL are skipped; zero weights contribute no mass, and a group with no positive mass yields NULL. Checked weight accumulation raises NUMERIC_OVERFLOW on overflow. Retained values and counts need bounded streaming storage, with RESOURCE_LIMIT on exhaustion.

Example

(SELECT median_exact_weighted(5, 4))
-- → 5

median_exact_weighted_interpolated

Calculates a floating-point center between adjacent numeric observations using their exact occurrence totals.

median_exact_weighted_interpolated(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64

Combines exact weighted ranks with interpolation to obtain the 0.5 result as float64, so the answer can fall between observed values. NULL pairs and zero multiplicities do not affect the distribution; empty positive mass returns NULL and overflowing total multiplicity raises NUMERIC_OVERFLOW. Continuous use stores observations and weights under explicit element and byte caps, reporting RESOURCE_LIMIT when either is exhausted.

Example

(SELECT median_exact_weighted_interpolated(11, 2))
-- → 11

median_gk

Estimates the halfway rank from a Greenwald–Khanna summary controlled by an accuracy parameter.

median_gk(accuracy:uint64)(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Builds a Greenwald–Khanna summary and queries its middle rank, returning float64. NULL observations are absent from the summary and no accepted observations gives NULL; invalid accuracy is INVALID_ARGUMENT. Storage grows with input count for a fixed error bound, so streaming requires both a finite summary-entry limit and a row cap, failing with RESOURCE_LIMIT on exhaustion. Retractions are admitted only with bounded replay.

Example

(SELECT median_gk(100)(6))
-- → 6

median_interpolated_weighted

Returns the linearly interpolated balance point of cumulative weighted ranks, ignoring zero-frequency entries.

median_interpolated_weighted(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64

Orders numeric observations by value and linearly interpolates the exact cumulative-weight rank at 0.5. Zero weights and pairs containing NULL are ignored; no remaining mass produces NULL, while a total exceeding uint64 raises NUMERIC_OVERFLOW. Values and their multiplicities are retained in bounded stream state, and reaching the configured element or byte ceiling raises RESOURCE_LIMIT.

Example

(SELECT median_interpolated_weighted(14, 5))
-- → 14

median_or_null

Produces an explicit nullable center estimate for groups, including those with no accepted measurements.

median_or_null(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Wraps the reservoir median with an explicit NULL result when the base accepts no rows. Nonempty groups keep the same sampled 0.5 estimate, including its order dependence and 8192-value reservoir; NULL inputs are skipped. The wrapper shares base state and replay constraints, so streamed deletions need bounded replay rather than a separate inverse sketch.

Example

(SELECT median_or_null(cast(NULL AS FLOAT64)))
-- → NULL

median_or_zero

Supplies numeric zero for empty groups while preserving the sampled midpoint of populated readings.

median_or_zero(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Finalizes an empty effective reservoir as zero, after excluding NULL inputs; any nonempty group returns its normal approximate median. This default applies only to emptiness and never converts invalid input or arithmetic failure to zero. The 8192-sample state remains sensitive to seed and merge order, and streaming retractions require bounded replay or fail admission.

Example

(SELECT median_or_zero(cast(NULL AS FLOAT64)))
-- → 0

median_tdigest

Approximates a distribution center from compressed clusters of nearby numeric observations.

median_tdigest(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Queries a t-digest at probability 0.5 and returns a float64 estimate of the central value. NULL readings are skipped, with NULL returned if the digest receives no observations; compression and merge order can alter the answer. A persisted centroid cap and byte ceiling bound streaming memory, producing RESOURCE_LIMIT at exhaustion, and retractions need bounded replay.

Example

(SELECT median_tdigest(18))
-- → 18

median_tdigest_weighted

Estimates the central magnitude of repeated observations through frequency-bearing t-digest centroids.

median_tdigest_weighted(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64

Adds each numeric value to a t-digest with its uint64 multiplicity and reads the median from the resulting centroids. NULL pairs are excluded and an empty positive-weight distribution gives NULL; weight accumulation is checked for NUMERIC_OVERFLOW. Compression order affects approximation, and both centroid count and bytes are capped for streaming. Deleting observations requires bounded replay, while exceeding allocated sketch capacity raises RESOURCE_LIMIT.

Example

(SELECT median_tdigest_weighted(21, 7))
-- → 21

median_timing

Returns a central duration estimate after saturating nonnegative timing readings at 30,000.

median_timing(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Uses the fixed timing histogram to approximate the 0.5 quantile, clamping every reading above 30000 to that ceiling. Negative durations raise INVALID_ARGUMENT; NULLs are skipped and an empty histogram returns NULL. Histogram storage is bounded per stream group, but retractions require a bounded replay plan rather than an assumed inverse operation.

Example

(SELECT median_timing(40000))
-- → 30000

median_timing_weighted

Finds the halfway latency bucket from counted duration samples with a fixed saturation ceiling.

median_timing_weighted(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64

Weights nonnegative timing observations by their uint64 occurrence counts and estimates the central duration after clamping values above 30000. A NULL duration or count is skipped, zero counts add no observations, and absence of positive mass yields NULL. Negative timings raise INVALID_ARGUMENT and count overflow raises NUMERIC_OVERFLOW. Streaming uses a fixed histogram; accepted retractions additionally need bounded replay storage.

Example

(SELECT median_timing_weighted(40000, 2))
-- → 30000

percentile_cont

Interpolates an ordered numeric distribution at a requested cumulative fraction.

percentile_cont(p:float64) WITHIN GROUP (ORDER BY x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Sorts non-NULL numeric values and linearly interpolates between adjacent observations at rank 1+(n-1)×p, returning float64. The fraction must lie in [0,1] or INVALID_ARGUMENT is raised, and an empty effective group yields NULL. Exact ordering requires retained values, so continuous plans enforce an element cap and raise RESOURCE_LIMIT before exceeding it.

Example

(
  SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY x)
  FROM (VALUES (2), (8)) AS readings(x)
)
-- → 5.0

percentile_disc

Selects an actual ordered observation whose cumulative share reaches the target.

percentile_disc(p:float64) WITHIN GROUP (ORDER BY x:T[int*|uint*|float32|float64|decimal(P,S)|string|bytes|fixed_string(n)|date|datetime64(p,tz)|uuid|ipv4|ipv6]) -> T

Returns the first non-NULL ordered value whose cumulative fraction reaches p, preserving the input descriptor; p=0 selects the first value. An empty group yields NULL and a probability outside [0,1] raises INVALID_ARGUMENT. A streaming plan retains bounded exact ordering state, with RESOURCE_LIMIT when the configured group capacity is exhausted.

Example

(
  SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY x)
  FROM (VALUES (2), (8)) AS readings(x)
)
-- → 2

quantile

Estimates a requested percentile from a bounded random sample of numeric input, defaulting to the midpoint.

quantile(p:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> float64
quantile(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Returns a reservoir-sampled quantile for constant p in [0,1], with the unary overload using 0.5; invalid probability is INVALID_ARGUMENT. NULL values are skipped and empty input yields NULL, while at most 8192 observations are retained. Sampling seed and merge order affect the result and must be persisted for stream replay; retractions need bounded replay state. The current metadata registration has no executable implementation.

Example

(SELECT quantile(0.9)(25))
-- → 25

quantile_bfloat16

Reads one requested percentile from a reduced-precision numeric histogram and returns a float64 estimate.

quantile_bfloat16(p:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> float64
quantile_bfloat16(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Converts measurements into BFloat16 histogram bins and estimates the selected rank, leaving the exposed result type float64. Probability is a bind-time constant between zero and one, defaulting to 0.5; an invalid level raises INVALID_ARGUMENT. NULL input is omitted and no accepted measurements yields NULL. The 65536-counter streaming sketch supports append and merge, with retractions requiring bounded replay.

Example

(SELECT quantile_bfloat16(0.75)(8))
-- → 8

quantile_bfloat16_weighted

Retrieves a frequency-sensitive percentile by accumulating integer weights in compact floating-point buckets.

quantile_bfloat16_weighted(p:float64)(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64
quantile_bfloat16_weighted(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64

Populates a reduced-precision histogram with value/uint64-weight pairs and reports the chosen quantile as float64. NULL pairs and zero weights supply no mass; an empty distribution returns NULL, and checked total-weight overflow raises NUMERIC_OVERFLOW. The constant probability lies in [0,1], with INVALID_ARGUMENT otherwise and 0.5 as the default. This retained weighted extension uses fixed counter storage for streams, while retractions require bounded replay.

Example

(SELECT quantile_bfloat16_weighted(0.8)(16, 4))
-- → 16

quantile_dd

Computes a chosen rank estimate with relative magnitude error bounded by a DDSketch accuracy setting.

quantile_dd(relative_accuracy:float64,p:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Uses logarithmic bins to estimate a constant probability under the supplied relative_accuracy contract. NULL observations are ignored and an empty sketch yields NULL; invalid accuracy or probability outside [0,1] raises INVALID_ARGUMENT. A finite bin ceiling bounds stream memory, and outgrowing the representable range raises RESOURCE_LIMIT without silently collapsing bins. Deletions require bounded replay of accepted observations.

Example

(SELECT quantile_dd(0.01, 0.9)(cast(NULL AS FLOAT64)))
-- → NULL

quantile_deterministic

Selects a stable hash-based reservoir to estimate an arbitrary percentile of numeric records.

quantile_deterministic(p:float64)(x:int*|uint*|float32|float64|decimal(P,S),determinator:uint64) -> float64
quantile_deterministic(x:int*|uint*|float32|float64|decimal(P,S),determinator:uint64) -> float64

Uses a persisted hash of each uint64 determinator to select at most 8192 values for a reproducible sample quantile. The bind-time level is in [0,1], defaults to 0.5, and raises INVALID_ARGUMENT if invalid. NULL value/determinator pairs are skipped and an empty sample returns NULL. Streaming replay must retain the hash-selection policy; retraction support additionally requires bounded replay state.

Example

(SELECT quantile_deterministic(0.9)(4, 101))
-- → 4

quantile_exact

Returns an unsampled order statistic at a chosen probability while preserving the observed value type.

quantile_exact(p:float64)(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)]) -> T
quantile_exact(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)]) -> T

Evaluates a constant quantile level against all accepted numeric or temporal values, returning the same type as its input. The default level is 0.5; levels outside [0,1] raise INVALID_ARGUMENT, and NULLs are removed before rank selection, with NULL for an empty group. Exact streaming state needs a positive element cap and byte limit, and RESOURCE_LIMIT is raised before either bound would be crossed.

Example

(SELECT quantile_exact(0.9)(17))
-- → 17

quantile_exact_exclusive

Interpolates a numeric percentile at rank (n + 1) × p, rejecting levels beyond attainable sample endpoints.

quantile_exact_exclusive(p:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> float64
quantile_exact_exclusive(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

After dropping NULLs, computes the exclusive rank (n+1)*p and linearly interpolates adjacent observations as float64. Empty input returns NULL; invalid probabilities or ranks beyond the attainable endpoints raise INVALID_ARGUMENT, with 0.5 used by the unary overload. All values are retained for exact streaming evaluation under declared element and byte caps, and exhaustion raises RESOURCE_LIMIT.

Example

(SELECT quantile_exact_exclusive(0.5)(10))
-- → 10

quantile_exact_high

Chooses the upper observation at a split requested rank in numeric or temporal data.

quantile_exact_high(p:float64)(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)]) -> T
quantile_exact_high(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)]) -> T

Selects the exact quantile while resolving a split rank upward to an observed value, preserving its type. Probability must be constant in [0,1] or INVALID_ARGUMENT is raised; omitted probability means 0.5. NULLs do not participate, and an empty effective group returns NULL. Streaming requires finite retained-value capacity, with RESOURCE_LIMIT before an insertion exceeds the row or byte budget.

Example

(SELECT quantile_exact_high(0.5)(x) FROM (VALUES (3), (9)) AS readings(x))
-- → 9

quantile_exact_inclusive

Returns an interpolated percentile on the closed sample range using rank 1 + (n − 1) × p.

quantile_exact_inclusive(p:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> float64
quantile_exact_inclusive(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Uses inclusive rank 1+(n-1)*p over non-NULL numeric observations, so probabilities zero and one reach the sample endpoints. Results are float64 and the default p is 0.5; an empty sample returns NULL, while a nonconstant or out-of-range probability raises INVALID_ARGUMENT. Exact continuous evaluation retains values under element and byte ceilings and raises RESOURCE_LIMIT if either is exhausted.

Example

(SELECT quantile_exact_inclusive(0.25)(x) FROM (VALUES (0), (8)) AS readings(x))
-- → 2

quantile_exact_low

Selects the preceding observed item whenever a requested probability lies on a split order-statistic rank.

quantile_exact_low(p:float64)(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)]) -> T
quantile_exact_low(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)]) -> T

Computes an exact quantile and chooses the lower observation at a split rank rather than interpolating or rounding upward. Numeric and temporal types are preserved; NULL inputs are skipped and empty input gives NULL. The bind-time p defaults to 0.5 and must lie in [0,1], otherwise INVALID_ARGUMENT is raised. Continuous groups retain values within a declared row count and byte budget, failing with RESOURCE_LIMIT before overflow.

Example

(SELECT quantile_exact_low(0.5)(x) FROM (VALUES (3), (9)) AS readings(x))
-- → 3

quantile_exact_weighted

Selects a value at a requested rank in the distribution implied by exact unsigned occurrence weights.

quantile_exact_weighted(p:float64)(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)],weight:uint64) -> T
quantile_exact_weighted(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)],weight:uint64) -> T

Interprets uint64 weights as repeated observations and chooses the requested exact order statistic, preserving the measured type. Probability is constant in [0,1] with default 0.5; invalid levels raise INVALID_ARGUMENT. NULL pairs and zero weights are excluded, no remaining mass yields NULL, and overflowing weight sums raise NUMERIC_OVERFLOW. Bounded streaming retains value/count state and raises RESOURCE_LIMIT on element or byte exhaustion.

Example

(SELECT quantile_exact_weighted(0.8)(13, 6))
-- → 13

quantile_exact_weighted_interpolated

Interpolates a floating result at a selected probability across exact multiplicity-expanded numeric ranks.

quantile_exact_weighted_interpolated(p:float64)(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64
quantile_exact_weighted_interpolated(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64

Determines the requested position from exact integer multiplicities and interpolates to a float64 answer rather than requiring an observed value. NULL pairs and zero mass are discarded; an empty effective distribution gives NULL and overflowing totals raise NUMERIC_OVERFLOW. The constant level defaults to 0.5, with INVALID_ARGUMENT outside [0,1]. Streaming state is bounded by retained-element and byte limits, reporting RESOURCE_LIMIT on exhaustion.

Example

(SELECT quantile_exact_weighted_interpolated(0.75)(19, 3))
-- → 19

quantile_gk

Returns a rank-error-controlled estimate for one probability from a growing Greenwald–Khanna data summary.

quantile_gk(accuracy:uint64,p:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Queries a Greenwald–Khanna summary at the supplied constant probability using the requested accuracy parameter. Invalid accuracy or a level outside [0,1] raises INVALID_ARGUMENT; NULL measurements are ignored and no accepted data produces NULL. A fixed error tolerance does not imply constant memory, so streaming needs finite row and summary-entry caps, raising RESOURCE_LIMIT when reached. Retracting input requires bounded replay rather than direct sketch subtraction.

Example

(SELECT quantile_gk(100, 0.9)(22))
-- → 22

quantile_interpolated_weighted

Calculates a chosen percentile by linear interpolation over exact cumulative observation weights.

quantile_interpolated_weighted(p:float64)(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64
quantile_interpolated_weighted(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64

Sorts numeric values, forms cumulative uint64 weight ranks, and linearly interpolates the chosen percentile as float64. Zero weights and NULL pairs are ignored; without positive mass the result is NULL, while total-weight overflow raises NUMERIC_OVERFLOW. A constant p in [0,1] is required, defaulting to 0.5 and raising INVALID_ARGUMENT otherwise. Stream execution retains exact weighted state within declared limits and raises RESOURCE_LIMIT when capacity is exhausted.

Example

(SELECT quantile_interpolated_weighted(0.4)(23, 8))
-- → 23

quantile_or_null

Keeps an estimated percentile nullable when the aggregate accepts no nonmissing observations.

quantile_or_null(p:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> float64
quantile_or_null(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Applies the reservoir quantile and explicitly returns NULL when its base aggregate accepts no values. NULL inputs are skipped; nonempty groups retain the approximate result, with a default level of 0.5 and INVALID_ARGUMENT for a nonconstant p or one outside [0,1]. It shares the 8192-value state, sampling seed and merge-order sensitivity of the base, and stream retractions require bounded replay.

Example

(SELECT quantile_or_null(0.9)(cast(NULL AS FLOAT64)))
-- → NULL

quantile_or_zero

Fills an empty percentile calculation with zero without suppressing failures in populated numeric groups.

quantile_or_zero(p:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> float64
quantile_or_zero(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Returns zero after the reservoir aggregate accepts no non-NULL observations; otherwise it preserves the selected sample quantile. Its default never hides invalid input or arithmetic errors, and a probability outside [0,1] raises INVALID_ARGUMENT; omitted p is 0.5. Streaming shares the base reservoir of at most 8192 values, including seed and merge-order requirements, and supports retractions only through bounded replay.

Example

(SELECT quantile_or_zero(0.9)(cast(NULL AS FLOAT64)))
-- → 0

quantile_prometheus_histogram

Estimates a percentile from ordered histogram bucket boundaries paired with cumulative counts.

quantile_prometheus_histogram(p:float64)(bucket_upper_bound:float64,cumulative_bucket_count:float64) -> float64
quantile_prometheus_histogram(bucket_upper_bound:float64,cumulative_bucket_count:float64) -> float64

Interprets each group as one Prometheus-style cumulative histogram and linearly interpolates within the selected bucket, treating the final +Inf bucket explicitly. Bounds must increase, counts must be nondecreasing, and constant p must lie in [0,1], otherwise INVALID_ARGUMENT is raised; p defaults to 0.5. NULL pairs are skipped and an empty accepted histogram gives NULL. Streaming retains bounded bucket state, and retractions require admitted replay capacity.

Example

(
  SELECT
    quantile_prometheus_histogram(0.5)(
      cast(NULL AS FLOAT64),
      cast(NULL AS FLOAT64)
    )
)
-- → NULL

quantile_tdigest

Estimates an arbitrary distribution percentile from a t-digest whose centroid budget is fixed in the plan.

quantile_tdigest(p:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> float64
quantile_tdigest(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Compresses numeric observations into t-digest centroids and estimates the selected constant quantile as float64. The default level is 0.5; invalid p outside [0,1] raises INVALID_ARGUMENT, NULL readings are ignored, and an empty digest yields NULL. Compression and merge order affect the answer, while persisted centroid and byte caps bound stream state and raise RESOURCE_LIMIT on exhaustion. Stream retractions need bounded replay.

Example

(SELECT quantile_tdigest(0.95)(24))
-- → 24

quantile_tdigest_weighted

Reads a chosen percentile from compressed numeric clusters carrying unsigned observation multiplicities.

quantile_tdigest_weighted(p:float64)(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64
quantile_tdigest_weighted(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64

Uses weighted t-digest centroids to approximate a constant probability, defaulting to 0.5. NULL pairs and zero weights add no mass, so an empty distribution returns NULL; invalid p raises INVALID_ARGUMENT and overflowing multiplicity raises NUMERIC_OVERFLOW. Approximation depends on compression and merge order, with the centroid cap persisted in the streaming plan. RESOURCE_LIMIT reports exhausted sketch storage, and deletes require bounded replay state.

Example

(SELECT quantile_tdigest_weighted(0.95)(26, 9))
-- → 26

quantile_timing

Estimates a chosen latency percentile from nonnegative durations clipped to a maximum of 30,000.

quantile_timing(p:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> float64
quantile_timing(x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Places timing readings in a fixed histogram after replacing values above 30000 with 30000, then estimates the requested quantile. Negative input or an invalid constant probability raises INVALID_ARGUMENT; p defaults to 0.5. NULL durations are skipped and no remaining measurements yields NULL. Streaming uses bounded histogram state and requires replay capacity to admit retractions.

Example

(SELECT quantile_timing(0.99)(40000))
-- → 30000

quantile_timing_weighted

Returns a frequency-adjusted latency percentile from saturated timing buckets with checked uint64 counters.

quantile_timing_weighted(p:float64)(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64
quantile_timing_weighted(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> float64

Counts timing observations using unsigned weights and queries the fixed histogram at a constant p in [0,1], defaulting to 0.5. Durations above 30000 saturate; negative values or invalid levels raise INVALID_ARGUMENT, and counter overflow raises NUMERIC_OVERFLOW. NULL pairs and zero counts are excluded, with NULL returned for no positive mass. Bounded stream histogram state supports append and merge; retractions need replay or fail admission.

Example

(SELECT quantile_timing_weighted(0.99)(40000, 5))
-- → 30000

quantiles

Returns several sampled percentile estimates together, reusing one reservoir for the requested probability list.

quantiles(p1:float64,...,pn:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> array(float64)

Evaluates all requested constant levels against a shared reservoir of at most 8192 numeric observations and returns an array in request order. Each level must lie in [0,1] or INVALID_ARGUMENT is raised; NULL observations are skipped and an empty group returns one NULL per level. Estimates depend on seed and merge order, both of which must be persisted for streaming replay. Bounded replay state is required to accept retractions.

Example

(SELECT quantiles(0.25, 0.75)(10))
-- → [10, 10]

quantiles_bfloat16

Produces an array of percentile approximations from one shared histogram using BFloat16 internal bins.

quantiles_bfloat16(p1:float64,...,pn:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> array(float64)

Stores reduced-precision histogram counts once and reads a float64 estimate for every requested constant probability. Levels outside [0,1] raise INVALID_ARGUMENT; input NULLs contribute no counts and an empty histogram returns an array of NULLs matching the level list. Its 65536-counter state is bounded per streaming group, and BFloat16 remains an internal representation. Retractions need bounded replay rather than direct inverse updates.

Example

(SELECT quantiles_bfloat16(0.1, 0.9)(8))
-- → [8, 8]

quantiles_bfloat16_weighted

Summarizes repeated measurements as multiple float estimates obtained from frequency-populated low-precision bins.

quantiles_bfloat16_weighted(p1:float64,...,pn:float64)(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> array(float64)

Uses one weighted BFloat16 histogram to answer a list of constant probabilities as an array of float64 estimates. NULL pairs and zero multiplicities are skipped, and absence of positive weight yields one NULL for each requested level. INVALID_ARGUMENT reports a level outside [0,1], while NUMERIC_OVERFLOW reports an overflowing uint64 weight total. This retained extension has fixed streaming counter storage; retractions require bounded replay.

Example

(SELECT quantiles_bfloat16_weighted(0.2, 0.8)(16, 3))
-- → [16, 16]

quantiles_dd

Returns several relative-accuracy percentile estimates from a shared logarithmic sketch of numeric magnitudes.

quantiles_dd(relative_accuracy:float64,p1:float64,...,pn:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> array(float64)

Builds one DDSketch-style state using relative_accuracy and reads every requested constant probability from its logarithmic bins. NULL measurements are ignored; if no values remain, the result contains NULL at each level. Invalid accuracy or levels outside [0,1] raise INVALID_ARGUMENT, and exceeding the fixed bin ceiling raises RESOURCE_LIMIT without silently changing the error contract. Streaming deletions require bounded replay of observations.

Example

(SELECT quantiles_dd(0.01, 0.1, 0.9)(cast(NULL AS FLOAT64)))
-- → [NULL, NULL]

quantiles_deterministic

Generates a reproducible percentile vector from one sample chosen by stable per-record determinator hashes.

quantiles_deterministic(p1:float64,...,pn:float64)(x:int*|uint*|float32|float64|decimal(P,S),determinator:uint64) -> array(float64)

Shares a hash-selected reservoir across all requested probabilities, keeping at most 8192 measured values and returning estimates in level order. Persisted determinator hashes govern selection; NULL pairs are skipped, with an array of NULLs when no sample survives. Every level must be a constant in [0,1], otherwise INVALID_ARGUMENT is raised. Streaming replay preserves the selection policy, and retractions are admitted only with bounded replay storage.

Example

(SELECT quantiles_deterministic(0.2, 0.8)(29, 55))
-- → [29, 29]

quantiles_exact

Returns a typed array of exact order statistics, deriving every requested rank from the same retained observations.

quantiles_exact(p1:float64,...,pn:float64)(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)]) -> array(T)

Answers multiple constant quantile levels using one exact collection of numeric or temporal values, preserving the input type in each array element. NULLs are removed before ranking, and no accepted values produces a NULL entry per level; INVALID_ARGUMENT rejects probabilities outside [0,1]. Continuous groups require positive element caps and byte ceilings, with RESOURCE_LIMIT raised before excess state is inserted.

Example

(SELECT quantiles_exact(0.2, 0.8)(31))
-- → [31, 31]

quantiles_exact_exclusive

Calculates a vector of exclusive interpolated percentiles using sample-size-plus-one rank positions.

quantiles_exact_exclusive(p1:float64,...,pn:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> array(float64)

For each requested level, evaluates rank (n+1)*p over the same non-NULL numeric sample and interpolates a float64 result. Levels outside [0,1] or beyond attainable sample endpoints raise INVALID_ARGUMENT; an empty effective sample yields an array of NULLs. Streaming retains all observations under finite row and byte limits, reporting RESOURCE_LIMIT before either bound is exceeded.

Example

(SELECT quantiles_exact_exclusive(0.5)(10))
-- → [10]

quantiles_exact_high

Builds an array of upper-rank selections whenever several requested percentile positions split adjacent values.

quantiles_exact_high(p1:float64,...,pn:float64)(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)]) -> array(T)

Evaluates each constant probability against exact retained observations and chooses the upper item at any split rank. Returned array elements keep the measured numeric or temporal type, while NULL input is ignored and an empty collection yields NULL for each requested position. A level outside [0,1] raises INVALID_ARGUMENT. Finite element and byte budgets are mandatory for streams, with RESOURCE_LIMIT on attempted excess growth.

Example

(SELECT quantiles_exact_high(0.5)(x) FROM (VALUES (4), (12)) AS readings(x))
-- → [12]

quantiles_exact_inclusive

Produces a percentile array spanning attainable endpoints through inclusive interpolation of one numeric sample.

quantiles_exact_inclusive(p1:float64,...,pn:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> array(float64)

Applies rank 1+(n-1)*p separately to every requested constant level and returns interpolated float64 values in request order. NULL observations are discarded, and an empty sample gives an equally sized array of NULLs; invalid levels outside [0,1] raise INVALID_ARGUMENT. Since exact streaming evaluation stores accepted values, declared element and byte limits are enforced with RESOURCE_LIMIT before insertion.

Example

(
  SELECT quantiles_exact_inclusive(0, 0.25, 1)(x)
  FROM (VALUES (0), (8)) AS readings(x)
)
-- → [0, 2, 8]

quantiles_exact_low

Returns preceding sample members at split ranks for a list of requested numeric or temporal percentiles.

quantiles_exact_low(p1:float64,...,pn:float64)(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)]) -> array(T)

Uses one exact ordered collection to select the lower observed value wherever a requested rank is split. Each result keeps its input type; NULL readings are excluded and an empty effective group returns one NULL per requested level. Probabilities must be bind-time constants in [0,1], otherwise INVALID_ARGUMENT is raised. Bounded streaming retention enforces element and byte caps, failing with RESOURCE_LIMIT before they are crossed.

Example

(SELECT quantiles_exact_low(0.5)(x) FROM (VALUES (4), (12)) AS readings(x))
-- → [4]

quantiles_exact_weighted

Finds several exact rank values within the multiplicity distribution represented by one set of value-count pairs.

quantiles_exact_weighted(p1:float64,...,pn:float64)(x:T[int*|uint*|float32|float64|decimal(P,S)|date|datetime64(p,tz)],weight:uint64) -> array(T)

Treats unsigned weights as occurrence counts and answers all requested quantile levels from a shared exact weighted collection. NULL pairs and zero counts have no effect; with no positive mass, every returned array element is NULL. Invalid constant probabilities raise INVALID_ARGUMENT and a uint64 total-weight overflow raises NUMERIC_OVERFLOW. Stream groups retain values and counts under explicit capacity limits, raising RESOURCE_LIMIT on exhaustion.

Example

(SELECT quantiles_exact_weighted(0.1, 0.9)(33, 7))
-- → [33, 33]

quantiles_exact_weighted_interpolated

Computes multiple floating percentile positions across a retained numeric distribution with exact occurrence counts.

quantiles_exact_weighted_interpolated(p1:float64,...,pn:float64)(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> array(float64)

Interpolates each requested probability using exact weighted ranks and returns a float64 array in the supplied level order. Pairs containing NULL and zero weights are excluded, while a group with no positive mass returns a NULL array entry for every level. Levels must be constant in [0,1] or INVALID_ARGUMENT is raised; accumulated count overflow is NUMERIC_OVERFLOW. The shared streaming state obeys declared element and byte ceilings, with RESOURCE_LIMIT when exhausted.

Example

(SELECT quantiles_exact_weighted_interpolated(0.3, 0.7)(35, 2))
-- → [35, 35]

quantiles_gk

Estimates a list of ranks with one Greenwald–Khanna synopsis whose size depends on observation count.

quantiles_gk(accuracy:uint64,p1:float64,...,pn:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> array(float64)

Maintains a single Greenwald–Khanna summary for the requested accuracy and evaluates all constant probability levels against it. NULL observations are ignored; an empty synopsis yields an array of NULLs, and invalid accuracy or levels outside [0,1] raise INVALID_ARGUMENT. Memory grows with input count, so streaming admission requires finite row and summary-entry caps, enforced by RESOURCE_LIMIT. Retraction support requires bounded replay capacity.

Example

(SELECT quantiles_gk(100, 0.2, 0.8)(37))
-- → [37, 37]

quantiles_interpolated_weighted

Derives a percentile vector from linear segments connecting exact cumulative-weight positions of numeric observations.

quantiles_interpolated_weighted(p1:float64,...,pn:float64)(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> array(float64)

Forms exact cumulative ranks from nonzero unsigned weights and linearly interpolates each requested probability over ordered numeric values. NULL pairs are omitted and no positive mass produces one NULL per level; overflowing total weight raises NUMERIC_OVERFLOW. A nonconstant level or one outside [0,1] raises INVALID_ARGUMENT. Shared retained state is bounded for streaming by element and byte limits, with RESOURCE_LIMIT on capacity exhaustion.

Example

(SELECT quantiles_interpolated_weighted(0.2, 0.8)(39, 4))
-- → [39, 39]

quantiles_tdigest

Returns a set of approximate percentiles by querying several levels of the same compressed centroid distribution.

quantiles_tdigest(p1:float64,...,pn:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> array(float64)

Builds one t-digest from numeric observations and emits estimates for all requested constant probabilities in their original order. NULLs are skipped and an empty digest returns a NULL entry per level; levels outside [0,1] raise INVALID_ARGUMENT. Compression and merge order affect results, so stream plans persist the centroid budget and enforce byte limits with RESOURCE_LIMIT. Retractions require bounded replay state.

Example

(SELECT quantiles_tdigest(0.5, 0.95)(41))
-- → [41, 41]

quantiles_tdigest_weighted

Produces several frequency-aware percentile estimates from a shared t-digest of repeated numeric measurements.

quantiles_tdigest_weighted(p1:float64,...,pn:float64)(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> array(float64)

Adds each value with its unsigned multiplicity to a t-digest and reads the requested constant levels into a float64 array. NULL pairs and zero weights are ignored; an empty positive-mass digest returns NULL at every level, invalid probabilities raise INVALID_ARGUMENT, and weight overflow raises NUMERIC_OVERFLOW. Compression and merge ordering influence approximation, while streaming centroid and byte caps are enforced by RESOURCE_LIMIT. Accepted retractions need bounded replay storage.

Example

(SELECT quantiles_tdigest_weighted(0.5, 0.99)(43, 6))
-- → [43, 43]

quantiles_timing

Reports multiple latency percentiles from one fixed histogram after capping excessively large durations.

quantiles_timing(p1:float64,...,pn:float64)(x:int*|uint*|float32|float64|decimal(P,S)) -> array(float64)

Clamps timings above 30000 and uses one bounded histogram to answer every requested probability. Negative durations or constant levels outside [0,1] raise INVALID_ARGUMENT; NULL durations are ignored and no accepted readings returns a NULL entry per level. The histogram supports streaming append and merge, while retractions are admitted only with bounded replay state.

Example

(SELECT quantiles_timing(0.5, 0.99)(40000))
-- → [30000, 30000]

quantiles_timing_weighted

Returns a latency percentile vector from counted observations, saturating durations and checking cumulative frequency overflow.

quantiles_timing_weighted(p1:float64,...,pn:float64)(x:int*|uint*|float32|float64|decimal(P,S),weight:uint64) -> array(float64)

Populates the timing histogram with uint64 multiplicities, clamps durations above 30000, and evaluates the supplied constant probability list. NULL pairs and zero counts add no mass; an empty effective histogram yields NULL at each requested level. Negative timings or levels outside [0,1] raise INVALID_ARGUMENT, and overflowing counters raise NUMERIC_OVERFLOW. Its fixed stream state supports append and merge, with bounded replay required for retractions.

Example

(SELECT quantiles_timing_weighted(0.5, 0.99)(40000, 3))
-- → [30000, 30000]

regr_avgx

Averages the predictor coordinate among complete regression pairs.

regr_avgx(y:int*|uint*|float32|float64|decimal(P,S),x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Returns the arithmetic mean of the second argument x after accepting only pairs whose y and x are both non-NULL. Missing y therefore removes its x from the denominator, unlike averaging x independently, and no accepted pair gives NULL. Streaming count and predictor sum are bounded sufficient statistics; checked overflow raises NUMERIC_OVERFLOW.

Example

(
  SELECT regr_avgx(y, x)
  FROM (VALUES (3, 1), (7, 3), (NULL, 9)) AS readings(y, x)
)
-- → 2.0

regr_avgy

Calculates the response-coordinate mean over jointly observed data.

regr_avgy(y:int*|uint*|float32|float64|decimal(P,S),x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Averages the first argument y across rows with both regression coordinates present. A NULL predictor excludes its paired response, and an empty set of complete pairs returns NULL. The incremental state consists of an accepted-pair count and response accumulator, with NUMERIC_OVERFLOW for a checked arithmetic failure.

Example

(
  SELECT regr_avgy(y, x)
  FROM (VALUES (3, 1), (7, 3), (9, NULL)) AS readings(y, x)
)
-- → 5.0

regr_count

Counts complete numeric pairs usable by regression aggregates.

regr_count(y:int*|uint*|float32|float64|decimal(P,S),x:int*|uint*|float32|float64|decimal(P,S)) -> uint64

Returns a uint64 count of rows where both y and x are non-NULL, regardless of whether either coordinate is constant. Empty input or entirely incomplete pairs yield zero, unlike regression estimates that can be undefined. A streaming counter supports exact insertions and retractions and raises NUMERIC_OVERFLOW if its representable range is exceeded.

Example

(
  SELECT regr_count(y, x)
  FROM (VALUES (3, 1), (7, 3), (NULL, 9)) AS readings(y, x)
)
-- → 2

regr_intercept

Returns the fitted response at predictor zero for an ordinary least-squares line.

regr_intercept(y:int*|uint*|float32|float64|decimal(P,S),x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Computes mean(y) minus slope×mean(x) using complete non-NULL pairs and an intercept-bearing least-squares fit. Empty input or zero predictor variance makes the result NULL. Streaming paired moments produce the estimate without storing all observations, and nonrepresentable checked arithmetic raises NUMERIC_OVERFLOW.

Example

(
  SELECT regr_intercept(y, x)
  FROM (VALUES (3, 1), (5, 2), (7, 3)) AS readings(y, x)
)
-- → 1.0

regr_r2

Reports the fraction of response variation explained by a fitted straight line.

regr_r2(y:int*|uint*|float32|float64|decimal(P,S),x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Returns squared Pearson correlation for complete regression pairs when both coordinates vary. Zero x variance yields NULL; constant y with varying x returns 1.0, and pairs containing NULL never enter the fit. Fixed paired-moment streaming state controls memory, with NUMERIC_OVERFLOW on arithmetic failure and merge order governing floating reproducibility.

Example

(SELECT regr_r2(y, x) FROM (VALUES (3, 1), (5, 2), (7, 3)) AS readings(y, x))
-- → 1.0

regr_slope

Calculates the least-squares change in response per unit of predictor.

regr_slope(y:int*|uint*|float32|float64|decimal(P,S),x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Divides the centered y/x cross-product total by the centered squared-deviation total of x. Only complete pairs contribute, and zero predictor variance or no complete observations returns NULL. Incremental sufficient statistics support streaming aggregation; a checked intermediate or result that cannot be represented raises NUMERIC_OVERFLOW.

Example

(SELECT regr_slope(y, x) FROM (VALUES (3, 1), (5, 2), (7, 3)) AS readings(y, x))
-- → 2.0

regr_sxx

Totals squared predictor deviations around their complete-pair mean.

regr_sxx(y:int*|uint*|float32|float64|decimal(P,S),x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Returns the sum of (x-mean(x))² over rows where both y and x are non-NULL. A single accepted pair yields 0.0 and empty effective input yields NULL; missing responses remove their predictors from both the mean and deviation sum. Bounded central-moment state handles streaming updates, with NUMERIC_OVERFLOW for checked arithmetic exhaustion.

Example

(SELECT regr_sxx(y, x) FROM (VALUES (3, 1), (5, 2), (7, 3)) AS readings(y, x))
-- → 2.0

regr_sxy

Returns the centered cross-product total used in linear fitting.

regr_sxy(y:int*|uint*|float32|float64|decimal(P,S),x:int*|uint*|float32|float64|decimal(P,S)) -> float64

Sums (x-mean(x))×(y-mean(y)) for complete non-NULL pairs, without dividing by count or degrees of freedom. No complete pair produces NULL and one complete pair produces 0.0. Streaming means and co-moment provide fixed-size state; arithmetic failure raises NUMERIC_OVERFLOW and fixed merge ordering preserves reproducibility.

Example

(SELECT regr_sxy(y, x) FROM (VALUES (3, 1), (5, 2), (7, 3)) AS readings(y, x))
-- → 4.0

regr_syy

Accumulates response spread as a centered sum of squares.

regr_syy(y:int*|uint*|float32|float64|decimal(P,S),x:int*|uint*|float32|float64|decimal(P,S)) -> float64