dev.frostlake.functions.aggregate.AggregateNumerics

public final class AggregateNumerics extends Object

Numeric 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). SUM preserves its argument's integer-ness the way Snowflake does: summing an INTEGER / NUMBER(p,0) column yields a whole number (SUM(i)::VARCHAR is "6", not "6.0"), while a DECIMAL or FLOAT argument keeps the prior double result. The integer sum is exact (BigInteger), returned as a Long when it fits and a BigInteger otherwise (Snowflake's NUMBER(38,0) range). A VARIANT input makes either aggregate DOUBLE (live-verified: SUM over PARSE_JSON('1'), PARSE_JSON('2') is 3.0 with SYSTEM$TYPEOF FLOAT). AVG of fixed-point inputs is a BigDecimal with scale = (max input scale) + 6, rounded HALF_UP (live-verified: AVG of the integers 90 and 95 is exactly 92.500000).

Method Summary

Modifier and Type

Method

Description

static Object

avg(Iterable<Object> values)

AVG over values (nulls are ignored).

static Object

avg(Iterable<Object> values, boolean variantArgument)

See sum(Iterable, boolean) — the same declared-VARIANT rule applied to AVG.

static boolean

isApproximateInput(Object value)

Whether an accumulated input is APPROXIMATE, which keeps the whole result a double.

static Object

median(Iterable<Object> values)

MEDIAN — the interpolating percentile at one half, and typed as one.

static Object

median(Iterable<Object> values, boolean approximateArgument)

MEDIAN with the argument's declared tier — see percentileCont(Iterable, double, boolean).

static Object

medianOverCoerced(Iterable<Object> values)

MEDIAN over a VARCHAR or VARIANT argument — see percentileContOverCoerced(Iterable, double).

static Object

nonCumulativeWindowAvg(Iterable<Object> values, boolean variantArgument)

AVG over a NON-CUMULATIVE window — a bare OVER (), a PARTITION BY, or an ORDER BY carrying a ROWS frame.

static Object

percentileCont(Iterable<Object> values, double fraction)

PERCENTILE_CONT — a value INTERPOLATED between two of the inputs, so it is a computed number and carries a computed scale: the widest input scale plus three, live-verified across seven NUMBER widths (a NUMBER(10,2) column gives NUMBER(13,5), and the value 2.00000 rather than 2.0).

static Object

percentileCont(Iterable<Object> values, double fraction, boolean approximateArgument)

PERCENTILE_CONT with the argument's DECLARED tier, which the values alone cannot always give: a FLOAT-declared value can still arrive in an exact carrier — computed exactly, or restored from an older snapshot — indistinguishable from an exact one, and only the declaration says there is no scale to build on.

static Object

percentileContOverCoerced(Iterable<Object> values, double fraction)

PERCENTILE_CONT over a VARCHAR or VARIANT key, which live neither reads at its decimals nor orders as numbers: every value is converted to a WHOLE number, NUMBER(9,0), and the inputs are ordered by their OWN type — text as text, so '10' sorts before '8' and '-2.5' before '0.5'.

static Object

percentileDisc(Iterable<Object> values, double fraction)

PERCENTILE_DISC — one of the inputs, handed back as it arrived.

static Object

percentileDiscOverCoerced(Iterable<Object> values, double fraction)

PERCENTILE_DISC over a VARCHAR or VARIANT key: the inputs ordered by their own type, as for percentileContOverCoerced(Iterable, double), and the picked one handed back as the whole number it converts to — live declares it NUMBER(9,0) and answers 2 for the lower of '1.5' and '2.25'.

static Double

rootOfDeclaredVariance(double variance, int maxInputScale, boolean approximate)

The STDDEV family's value: the square root of the variance ROUNDED TO ITS OWN DECLARED SCALE.

static Object

runningSum(Iterable<Object> values, boolean variantArgument)

SUM read as a RUNNING sum: every value its own partial, so the double's last compensation is left unapplied (see PartialFloatSum).

static int

scaleOfInput(Object value)

The scale an accumulated input contributes: a decimal's own, zero for an integral value.

static Object

sum(Iterable<Object> values)

SUM over values (nulls are ignored).

static Object

sum(Iterable<Object> values, boolean variantArgument)

The variantArgument flag is for a SUM whose ARGUMENT is declared VARIANT even though the runtime values arrive as plain numbers — live, SUM(v:b) over whole-number JSON values is DOUBLE (7.0, SYSTEM$TYPEOF FLOAT): the declared type decides the tier exactly as a runtime VariantValue does.

static Object

varianceAtDeclaredScale(double variance, int maxInputScale, boolean approximate)

A VARIANCE-family result presented at the scale its column DECLARES.

static Object

windowAvg(List<? extends Iterable<Object>> partials, boolean variantArgument, boolean nonCumulative, boolean rowWise)

AVG over a window frame given as the PARTIALS its double sum is read in, as windowSum(List, boolean, boolean) reads SUM's — the whole partition one partial, a cumulative RANGE frame one per peer group, and rowWise for a running or sliding frame — at a cumulative window's scale, or three decimals narrower and truncated for every other shape (nonCumulativeWindowAvg(java.lang.Iterable<java.lang.Object>, boolean)).

static Object

windowSum(List<? extends Iterable<Object>> partials, boolean variantArgument, boolean rowWise)

SUM over a window frame given as the PARTIALS its double is read in (see PartialFloatSum): the whole partition is one partial, a cumulative RANGE frame one per peer group, and rowWise makes every value a partial of its own, as a running or sliding frame reads it.

Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Method Details
- sum
  
  public static Object sum(Iterable<Object> values)
  
  SUM over values (nulls are ignored). Returns null when no non-null value is present (Snowflake SUM of an empty set is NULL). All-integer input yields a Long/BigInteger; any decimal/float/VARIANT input keeps the double sum (live-verified: SUM over a VARIANT column is DOUBLE even for whole-number JSON values).
  
  Parameters:
  
  values - the aggregated argument values, in group order
  
  Returns:
  
  the sum — Long/BigInteger for all-integral input, BigDecimal for fixed-point, Double otherwise — or null for an empty set
- sum
  
  public static Object sum(Iterable<Object> values, boolean variantArgument)
  
  The variantArgument flag is for a SUM whose ARGUMENT is declared VARIANT even though the runtime values arrive as plain numbers — live, SUM(v:b) over whole-number JSON values is DOUBLE (7.0, SYSTEM$TYPEOF FLOAT): the declared type decides the tier exactly as a runtime VariantValue does.
  A DOUBLE sum is the aggregate's: added with Kahan compensation and read back with its last compensation applied — live, SUM(x * x) over 0.3 then 0.7 is 0.57999999999999984901, where the running sum is 0.57999999999999996003 (see PartialFloatSum).
  
  Parameters:
  
  values - the aggregated argument values, in group order
  
  variantArgument - whether the argument expression is statically VARIANT-typed, which forces the DOUBLE tier
  
  Returns:
  
  the sum on the tier the inputs select, or null for an empty set
- runningSum
  
  public static Object runningSum(Iterable<Object> values, boolean variantArgument)
  
  SUM read as a RUNNING sum: every value its own partial, so the double's last compensation is left unapplied (see PartialFloatSum). SUM(DISTINCT) answers this way — live, over 0.3, 0.7 and 0.3 squared it is 0.57999999999999996003 where SUM is 0.57999999999999984901. The exact tiers are the same sum either way.
  
  Parameters:
  
  values - the aggregated argument values, in group order
  
  variantArgument - whether the argument expression is statically VARIANT-typed
  
  Returns:
  
  the sum on the tier the inputs select, or null for an empty set
- windowSum
  
  public static Object windowSum(List<? extends Iterable<Object>> partials, boolean variantArgument, boolean rowWise)
  
  SUM over a window frame given as the PARTIALS its double is read in (see PartialFloatSum): the whole partition is one partial, a cumulative RANGE frame one per peer group, and rowWise makes every value a partial of its own, as a running or sliding frame reads it.
  
  Parameters:
  
  partials - the frame's values, partial by partial, in partition order
  
  variantArgument - whether the argument expression is statically VARIANT-typed
  
  rowWise - whether every value is a partial of its own
  
  Returns:
  
  the sum on the tier the inputs select, or null for an empty frame
- avg
  
  public static Object avg(Iterable<Object> values)
  
  AVG over values (nulls are ignored). Returns null when no non-null value is present (Snowflake AVG of an empty set is NULL). All fixed-point input (Long/Integer/BigDecimal — not Double/Float) yields a BigDecimal with scale = (max input scale) + 6, rounded HALF_UP and with trailing zeros KEPT — AVG of the integers 90 and 95 is exactly 92.500000 and AVG(2, 2) renders 2.000000, matching live Snowflake. Any Double/Float or VARIANT input keeps the double average.
  
  Parameters:
  
  values - the aggregated argument values, in group order
  
  Returns:
  
  the average — a scaled BigDecimal for all fixed-point input, a Double otherwise — or null for an empty set
- avg
  
  public static Object avg(Iterable<Object> values, boolean variantArgument)
  
  See sum(Iterable, boolean) — the same declared-VARIANT rule applied to AVG.
  
  Parameters:
  
  values - the aggregated argument values, in group order
  
  variantArgument - whether the argument expression is statically VARIANT-typed, which keeps the average on the double path
  
  Returns:
  
  the average on the tier the inputs select, or null for an empty set
- nonCumulativeWindowAvg
  
  public static Object nonCumulativeWindowAvg(Iterable<Object> values, boolean variantArgument)
  
  AVG over a NON-CUMULATIVE window — a bare OVER (), a PARTITION BY, or an ORDER BY carrying a ROWS frame. Live declares three decimals wider than the input there rather than six, and TRUNCATES to that scale instead of rounding.
  Both halves are measured. Over NUMBER(10,2) the declared type is NUMBER(25,5) against the cumulative NUMBER(28,8); and seven rows summing to 1.00 average to 0.14285, where a half-up round of 0.142857142857… would give 0.14286. Rounding here would produce the wrong last digit.
  A RANGE frame is CUMULATIVE and does not come here — see WindowFunctionExpression.isRowsFramed.
  
  Parameters:
  
  values - the aggregated argument values, in partition order
  
  variantArgument - whether the argument is statically VARIANT, which keeps the double path
  
  Returns:
  
  the average at the window's own declared scale, or null for an empty set
- windowAvg
  
  public static Object windowAvg(List<? extends Iterable<Object>> partials, boolean variantArgument, boolean nonCumulative, boolean rowWise)
  
  AVG over a window frame given as the PARTIALS its double sum is read in, as windowSum(List, boolean, boolean) reads SUM's — the whole partition one partial, a cumulative RANGE frame one per peer group, and rowWise for a running or sliding frame — at a cumulative window's scale, or three decimals narrower and truncated for every other shape (nonCumulativeWindowAvg(java.lang.Iterable<java.lang.Object>, boolean)). Live, AVG(x * x) over 0.3 then 0.7 is 0.28999999999999992450 over the whole partition and 0.28999999999999998002 on a running frame's last row.
  
  Parameters:
  
  partials - the frame's values, partial by partial, in partition order
  
  variantArgument - whether the argument keeps the double path whatever its values
  
  nonCumulative - whether the window is not cumulative, which narrows and truncates the scale
  
  rowWise - whether every value is a partial of its own
  
  Returns:
  
  the average, or null for an empty frame
- median
  
  public static Object median(Iterable<Object> values)
  
  MEDIAN — the interpolating percentile at one half, and typed as one.
  
  Parameters:
  
  values - the aggregated argument values, in group order
  
  Returns:
  
  the median on the tier the inputs select, or null for an empty set
- median
  
  public static Object median(Iterable<Object> values, boolean approximateArgument)
  
  MEDIAN with the argument's declared tier — see percentileCont(Iterable, double, boolean).
  
  Parameters:
  
  values - the aggregated argument values, in group order
  
  approximateArgument - whether the argument is declared FLOAT / DOUBLE / REAL
  
  Returns:
  
  the median on the tier the declaration and the inputs select, or null for an empty set
- percentileCont
  
  public static Object percentileCont(Iterable<Object> values, double fraction)
  
  PERCENTILE_CONT — a value INTERPOLATED between two of the inputs, so it is a computed number and carries a computed scale: the widest input scale plus three, live-verified across seven NUMBER widths (a NUMBER(10,2) column gives NUMBER(13,5), and the value 2.00000 rather than 2.0).
  The exact tier is what makes that possible. Collapsing every input to a double — as this did — loses the scale before there is anything to present at, and prints a small value in SCIENTIFIC notation besides: a median over NUMBER(5,4) came back as 2.0E-4 where live gives 0.0002000. A FLOAT or VARIANT input still selects the double tier, as it does for AVG and SUM.
  
  Parameters:
  
  values - the aggregated argument values, in group order
  
  fraction - the percentile, between 0 and 1
  
  Returns:
  
  the interpolated value, or null for an empty set
- percentileCont
  
  public static Object percentileCont(Iterable<Object> values, double fraction, boolean approximateArgument)
  
  PERCENTILE_CONT with the argument's DECLARED tier, which the values alone cannot always give: a FLOAT-declared value can still arrive in an exact carrier — computed exactly, or restored from an older snapshot — indistinguishable from an exact one, and only the declaration says there is no scale to build on. Live answers a FLOAT percentile as the plain double — 2.5, not 2.5000.
  
  Parameters:
  
  values - the aggregated argument values, in group order
  
  fraction - the percentile, between 0 and 1
  
  approximateArgument - whether the argument is declared FLOAT / DOUBLE / REAL
  
  Returns:
  
  the interpolated value, or null for an empty set
- percentileDisc
  
  public static Object percentileDisc(Iterable<Object> values, double fraction)
  
  PERCENTILE_DISC — one of the inputs, handed back as it arrived. It computes nothing, so it adds nothing to the scale: the value already carries its column's, which is why this family was right all along wherever the value reached the caller unconverted.
  
  Parameters:
  
  values - the aggregated argument values, in group order
  
  fraction - the percentile, between 0 and 1
  
  Returns:
  
  the selected input value, or null for an empty set
- medianOverCoerced
  
  public static Object medianOverCoerced(Iterable<Object> values)
  
  MEDIAN over a VARCHAR or VARIANT argument — see percentileContOverCoerced(Iterable, double).
  
  Parameters:
  
  values - the aggregated argument values, in group order
  
  Returns:
  
  the median of the whole numbers the values convert to, or null for an empty set
- percentileContOverCoerced
  
  public static Object percentileContOverCoerced(Iterable<Object> values, double fraction)
  
  PERCENTILE_CONT over a VARCHAR or VARIANT key, which live neither reads at its decimals nor orders as numbers: every value is converted to a WHOLE number, NUMBER(9,0), and the inputs are ordered by their OWN type — text as text, so '10' sorts before '8' and '-2.5' before '0.5'. The interpolation then runs between the converted neighbours, at the three extra decimals the family always adds. Live-verified: over '1.5' and '2.25' the median is 2.000 (each rounds to 2, half away from zero), over '2.5' and '3.5' it is 3.500, over '10', '9', '8' it is 8.000, over '-1.5', '-2.5', '0.5' it is -3.000, and a VARCHAR(4) and a VARCHAR(16777216) agree — the nine digits are the conversion's, not the column's.
  
  Parameters:
  
  values - the aggregated key values, in group order
  
  fraction - the percentile, between 0 and 1
  
  Returns:
  
  the interpolated value at scale three, or null for an empty set
- percentileDiscOverCoerced
  
  public static Object percentileDiscOverCoerced(Iterable<Object> values, double fraction)
  
  PERCENTILE_DISC over a VARCHAR or VARIANT key: the inputs ordered by their own type, as for percentileContOverCoerced(Iterable, double), and the picked one handed back as the whole number it converts to — live declares it NUMBER(9,0) and answers 2 for the lower of '1.5' and '2.25'.
  
  Parameters:
  
  values - the aggregated key values, in group order
  
  fraction - the percentile, between 0 and 1
  
  Returns:
  
  the selected value's whole number, or null for an empty set
- varianceAtDeclaredScale
  
  public static Object varianceAtDeclaredScale(double variance, int maxInputScale, boolean approximate)
  
  A VARIANCE-family result presented at the scale its column DECLARES. The declared type is NUMBER(38, min(12, 2s + 6)) for an exact input of scale s — the SQUARE of a scale, capped at twelve, the rule TypeInferencer already applies — and live pads the value out to it: over a NUMBER(10,2) column, VARIANCE answers 2.9166666667 and VARIANCE_POP answers 2.1875000000, both at ten decimals. Computing the statistic and then presenting it are separate steps, and only this one knows the scale.
  An APPROXIMATE input keeps the double: live types VARIANCE over a FLOAT as FLOAT, where a scaled decimal would render the other way.
- rootOfDeclaredVariance
  
  public static Double rootOfDeclaredVariance(double variance, int maxInputScale, boolean approximate)
  
  The STDDEV family's value: the square root of the variance ROUNDED TO ITS OWN DECLARED SCALE.
  Live-verified, and the reason the same three numbers give two different answers: STDDEV over NUMBER(38,0) is sqrt(2.333333) — the scale-6 variance — while over NUMBER(10,2) it is sqrt(2.3333333333), the scale-10 one. Live is not computing at a hidden precision; it takes the root of a variance that has already been rounded.
  The result is always a DOUBLE, because live declares FLOAT for every input the family takes, NUMBER ones included — so it renders at the FLOAT text width rather than at a scale.
- scaleOfInput
  
  public static int scaleOfInput(Object value)
  
  The scale an accumulated input contributes: a decimal's own, zero for an integral value.
- isApproximateInput
  
  public static boolean isApproximateInput(Object value)
  
  Whether an accumulated input is APPROXIMATE, which keeps the whole result a double.

Class AggregateNumerics

Method Summary

Methods inherited from class java.lang.Object

Method Details

sum

sum

runningSum

windowSum

avg

avg

nonCumulativeWindowAvg

windowAvg

median

median

percentileCont

percentileCont

percentileDisc

medianOverCoerced

percentileContOverCoerced

percentileDiscOverCoerced

varianceAtDeclaredScale

rootOfDeclaredVariance

scaleOfInput

isApproximateInput