Package dev.frostlake.functions.aggregate
package dev.frostlake.functions.aggregate
-
ClassDescriptionNumeric helpers shared by every SUM / AVG code path (the fast-path plan, the general grouped evaluator, the generic dispatch, the accumulators and the window frame).ANY_VALUE(expr) — an arbitrary non-NULL value from the group (this implementation keeps the first one it sees), or NULL when the group holds none.Accumulator for
AnyValue.APPROX_COUNT_DISTINCT(expr) — an approximate count of the distinct non-NULL values in the group, estimated with theHllAccumulatorHyperLogLog sketch (~0.8% standard error, near-exact at low cardinality).An accumulator whose result depends on whether its argument is APPROXIMATE, which the values alone cannot always say.APPROX_PERCENTILE(expr, percentile) — the approximate value at the given percentile (a constant in [0, 1]) ofexpr.APPROX_PERCENTILE_ACCUMULATE(expr): thePercentileDigestof a numeric input as an OBJECT state, for APPROX_PERCENTILE_COMBINE to merge and APPROX_PERCENTILE_ESTIMATE to read.Accumulator for APPROX_PERCENTILE(expr, percentile).APPROX_PERCENTILE_COMBINE(state): the APPROX_PERCENTILE_ACCUMULATE states of its input merged into one digest — every centroid kept with its weight, sorted by mean.APPROX_PERCENTILE_ESTIMATE(state, percentile): the value at a fraction of an APPROX_PERCENTILE_ACCUMULATE or APPROX_PERCENTILE_COMBINE state, as a FLOAT — the same number APPROX_PERCENTILE gives over the values the state was built from (seePercentileDigestfor the weighted reading of a hand-written state).APPROX_TOP_K(expr [, k [, counters]]): the k most frequent values of the input as an ARRAY of [value, count] pairs, count descending, computed by the Space-Saving summary inTopKSummaryovercounterscounters (default 100000; k defaults to 1).APPROX_TOP_K_ACCUMULATE(expr, counters): the wholeTopKSummaryof the input as an OBJECT state —{"counters":C,"datatype":T,"precision":P,"scale":S,"state":[[value, count], …], "type":"approx_top_k"}— for APPROX_TOP_K_COMBINE to merge and APPROX_TOP_K_ESTIMATE to rank.The accumulator ofApproxTopK: aTopKSummarysized by the call's third argument, answering the first k of its ranking.APPROX_TOP_K_COMBINE(state [, counters]): the APPROX_TOP_K_ACCUMULATE states of its input merged into one — equal values add their counts, nothing is inherited — and trimmed tocounters(a constant between 1 and 100000; by default the inputs' own limit, which every input must share: differing limits refuse withInvalid parameter value: ApproxTopK state. Reason: combining states with different numbers of counters).The accumulator ofApproxTopKCombine: every input state is read into one unboundedTopKSummary, which is trimmed to the result's counter limit only once all of them are in.APPROX_TOP_K_ESTIMATE(state [, k]): the k most frequent [value, count] pairs of an APPROX_TOP_K_ACCUMULATE or APPROX_TOP_K_COMBINE state, as APPROX_TOP_K would list them (k defaults to 1 and is a constant between 1 and 100000, judged at compile time).The accumulator ofApproxTopKAccumulate: aTopKSummarysized by the call's second argument, serialised whole.Accumulator forARRAY_AGG(expr).ARRAY_UNION_AGG(array) — an aggregate whose every input row is itself an ARRAY.Accumulator forArrayUnionAgg— Snowflake's MULTISET union (live-verified): every element appears with the MAXIMUM multiplicity it has in any single input array, so[2,2,2] UNION [2,2]is[2,2,2]and[5,5]alone keeps both.ARRAY_UNIQUE_AGG(expr) — an aggregate that collects the DISTINCT non-NULL scalar input values (each input row contributes a single element, not an array) into a VARIANT ARRAY.Accumulator forArrayUniqueAgg.AVG(expr) — seeAvgAccumulator/AggregateNumerics.avg(Iterable)for the live-verified Snowflake result typing (fixed-point inputs → BigDecimal scale = max input scale + 6, HALF_UP; any double/VARIANT input → double; empty → NULL).Accumulator forAvg, delegating the numeric result toAggregateNumerics.avg(Iterable)so every AVG code path shares the same live-verified Snowflake typing: fixed-point inputs (Long/Integer/ BigDecimal) yield a BigDecimal with scale = (max input scale) + 6 rounded HALF_UP and trailing zeros kept (AVG of 90 and 95 is exactly 92.500000), while any Double/Float/VARIANT input keeps the double average.Accumulator forBitAndAgg.Accumulator forBitOrAgg.Accumulator forBitXorAgg.Accumulator forBoolAndAgg.The three boolean aggregates' shared argument rule.Accumulator forBoolOrAgg.Accumulator forBoolXorAgg.An accumulator whose numeric answer can be asked of a VARCHAR or VARIANT argument.A running sum of doubles with Kahan compensation, which is the closest single rule to how the account adds them.An accumulator that reads its constant arguments from the call's TEXT rather than from the rows — the APPROX_TOP_K family's item and counter limits, which must be known even when no row arrives: an empty group still reports the capacity it was configured with.Reads the constant integer an aggregate call wrote as one of its arguments — the k and the counter limit of the APPROX_TOP_K family — from the argument's text.Accumulator forCorr.Accumulator forCount.Accumulator forCountIf.An accumulator that needs the aggregated expression's DECLARED type, which its values cannot give — a NUMBER(10,2) column and a literal 1.5 both arrive as a BigDecimal, and only the declaration says which width the APPROX_TOP_K_ACCUMULATE state must report.HASH_AGG(expr) — a single, order-INDEPENDENT signed 64-bit hash over the whole (multi)set of input rows.Accumulator forHashAgg.HyperLogLog sketch for APPROX_COUNT_DISTINCT (Flajolet et al.), with linear counting in the small-cardinality range.Accumulator forKurtosis.LISTAGG(<expr> [, <delimiter>]) — concatenate values into a single string.Accumulator for LISTAGG: concatenates the non-null accumulated values withListAggAccumulator.delimiter.Accumulator forMax.MAX_BY(value, sort_key) — a two-argument aggregate returning thevaluefrom the row whosesort_keyis the maximum.Accumulator forMedian, delegating toAggregateNumerics.median(Iterable)so the interpolating percentiles share one typing rule.Accumulator forMin.MIN_BY(value, sort_key) — a two-argument aggregate returning thevaluefrom the row whosesort_keyis the minimum.MODE's accumulator: the most frequent non-NULL value, a tie going to the value seen FIRST.One value on its way INTO a numeric aggregate, converted the way live converts it — which for text that is not a number means a refusal, not a zero.OBJECT_AGG(key, value) — aggregates key/value pairs into a single OBJECT.Accumulator forOBJECT_AGG(key, value)— collects key/value pairs into one OBJECT.The double a FLOAT SUM or AVG answers, built from PARTIALS: each partial is a run of terms added with Kahan compensation and read back with its last compensation applied (CompensatedSum.correctedValue()), and the partials are added in turn to a running compensated sum that is read without it (CompensatedSum.value()).Accumulator forPercentileCont, delegating toAggregateNumerics.percentileCont(Iterable, double)— the same rule MEDIAN uses, MEDIAN being this function at one half.The percentile digest behind APPROX_PERCENTILE_ACCUMULATE, _COMBINE and _ESTIMATE: a list of centroids (mean, weight) serialised as{"state":[mean, weight, mean, weight, …], "type":"tdigest","version":1}, means ascending, every number in the fifteen-decimal exponent form a DOUBLE takes inside a VARIANT.The accumulator ofApproxPercentileAccumulate: one centroid of weight 1 per non-NULL value.The accumulator ofApproxPercentileCombine: every input state's centroids, gathered.Accumulator forPercentileDisc, delegating toAggregateNumerics.percentileDisc(Iterable, double).Shared accumulator for the REGR_* linear-regression family.REGR_AVGX(y, x) — average of the independent variable x over non-null (y, x) pairs (y dependent, x independent).REGR_AVGY(y, x) — average of the dependent variable y over non-null (y, x) pairs (y dependent, x independent).REGR_COUNT(y, x) — count over non-null (y, x) pairs (y dependent, x independent).REGR_INTERCEPT(y, x) — y-intercept of the least-squares regression line over non-null (y, x) pairs (y dependent, x independent).Which member of the REGR_* linear-regression family aRegrAccumulatorcomputes from its sums.REGR_R2(y, x) — coefficient of determination (R squared) over non-null (y, x) pairs (y dependent, x independent).REGR_SLOPE(y, x) — slope of the least-squares regression line over non-null (y, x) pairs (y dependent, x independent).REGR_SXX(y, x) — sum of squares of x deviations over non-null (y, x) pairs (y dependent, x independent).REGR_SXY(y, x) — sum of products of x and y deviations over non-null (y, x) pairs (y dependent, x independent).REGR_SYY(y, x) — sum of squares of y deviations over non-null (y, x) pairs (y dependent, x independent).Accumulator forSkew.The count, the sum and the sum of squares of a FLOAT series — the moments the account's variance family reads over FLOATs.Accumulator forStdDev.Accumulator forStdDevPop.Accumulator forSum.One counter of aTopKSummary: a value, how many times the summary has counted it, and the tick of the summary's clock at which it was last touched — the tie-breaker every ordering rule of the APPROX_TOP_K family reads.The Space-Saving summary behind the APPROX_TOP_K family: a bounded set of (value, count) counters that names the most frequent values of a stream without holding every distinct one.Accumulator forVariance.Accumulator forVarPop.Which element-wise reduction aVectorAggregateAccumulatorapplies across the group's rows.VECTOR_AVG(v)— the element-wise MEAN of a group's vectors, always aVECTOR(FLOAT, n): over the rows[1,2,3]and[4,5,6]it is[2.5,3.5,4.5](live-verified).VECTOR_MAX(v)— the element-wise MAXIMUM of a group's vectors, keeping the element type: over the rows[1,9,3],[4,5,-6]and[7,2,0]it is[7.0,9.0,3.0](live-verified), i.e. each dimension is maximised on its own.VECTOR_MIN(v)— the element-wise MINIMUM of a group's vectors, keeping the element type: over the rows[1,9,3],[4,5,-6]and[7,2,0]it is[1.0,2.0,-6.0](live-verified), i.e. each dimension is minimised on its own.VECTOR_SUM(v)— the element-wise SUM of a group's vectors, keeping the element type: over the rows[1,2,3]and[4,5,6]it is[5.0,7.0,9.0](live-verified).The running count, mean and sum of squared deviations of a double series — Welford's update, merged with Chan's formula — which the variance family uses over EXACT inputs.