Class Aggregates
@SQLAction(requires={"avgx","avgy","slope","intercept"}, install="WITH data (y, x) AS (VALUES (1.761 ::float8, 5.552::float8), (1.775, 5.963), (1.792, 6.135), (1.884, 6.313), (1.946, 6.713) ), expected (avgx, avgy, slope, intercept) AS ( SELECT 6.1352, 1.8316, 0.1718, 0.7773 ), got AS ( SELECT round( avgx(y,x)::numeric, 4) AS avgx, round( avgy(y,x)::numeric, 4) AS avgy, round( slope(y,x)::numeric, 4) AS slope, round(intercept(y,x)::numeric, 4) AS intercept FROM data )SELECT CASE WHEN expected IS NOT DISTINCT FROM got THEN javatest.logmessage(\'INFO\', \'aggregate examples ok\') ELSE javatest.logmessage(\'WARNING\', \'aggregate examples ng\') END FROM expected, got") @Aggregate(provides="avgx",name={"javatest","avgx"},arguments={"y double precision","x double precision"},plan=@Plan(stateType="double precision[]",stateSize=72,initialState="{0,0,0,0,0,0}",accumulate={"javatest","accumulateXY"},finish={"javatest","finishAvgX"})) @Aggregate(provides="avgy",name={"javatest","avgy"},arguments={"y double precision","x double precision"},plan=@Plan(stateType="double precision[]",stateSize=72,initialState="{0,0,0,0,0,0}",accumulate={"javatest","accumulateXY"},finish={"javatest","finishAvgY"})) @Aggregate(provides="slope",name={"javatest","slope"},arguments={"y double precision","x double precision"},plan=@Plan(stateType="double precision[]",stateSize=72,initialState="{0,0,0,0,0,0}",accumulate={"javatest","accumulateXY"},finish={"javatest","finishSlope"})) @Aggregate(provides="intercept",name={"javatest","intercept"},arguments={"y double precision","x double precision"},plan=@Plan(stateType="double precision[]",stateSize=72,initialState="{0,0,0,0,0,0}",accumulate={"javatest","accumulateXY"},finish={"javatest","finishIntercept"})) @Aggregate(name="javatest.regression",arguments={"y double precision","x double precision"},plan=@Plan(stateType="double precision[]",stateSize=72,initialState="{0,0,0,0,0,0}",accumulate={"javatest","accumulateXY"},finish={"javatest","finishRegr"}),movingPlan=@Plan(stateType="double precision[]",stateSize=72,initialState="{0,0,0,0,0,0}",accumulate={"javatest","accumulateXY"},remove={"javatest","removeXY"},finish={"javatest","finishRegr"})) public class Aggregates extends Object
They are (some of) the same twovariable statistical aggregates already offered in core PostgreSQL, just because they make clear examples. For numerical reasons, they might not produce results identical to PG's builtin ones. These closely follow the "schoolbook" formulas in the HP11C calculator owner's handbook, while the ones built into PostgreSQL use a more clever algorithm instead to reduce rounding error in the finishers.
All these aggregates can be computed by different finishers that share a
state that accumulates the count of rows, sum of x, sum of xx, sum of y, sum
of yy, and sum of xy. That is easy with finishers that don't need to modify
the state, so the default FinishEffect=READ_ONLY
is appropriate.
Everything here takes the y parameter first, then x, like the SQL ones.

Method Summary
Modifier and Type Method Description static double[]
accumulateXY(double[] state, double y, double x)
A common accumulator for twovariable statistical aggregates that depend on n, Sx, Sxx, Sy, Syy, and Sxy.static long
count(double[] state)
Finisher that returns the count of nonnull rows accumulated.static Double
finishAvgX(double[] state)
Finisher that returns the mean of the accumulated x values.static Double
finishAvgY(double[] state)
Finisher that returns the mean of the accumulated y values.static Double
finishIntercept(double[] state)
Finisher that returns the intercept of a regression line.static boolean
finishRegr(double[] state, ResultSet out)
A finisher that returns the slope and intercept together.static Double
finishSlope(double[] state)
Finisher that returns the slope of a regression line.static double[]
removeXY(double[] state, double y, double x)
'Removes' from the state a row previously accumulated, for possible use in a window with a moving frame start.static double
sum(double state, double x)
An example aggregate that sums its input.

Method Details

accumulateXY
@Function(schema="javatest", effects=IMMUTABLE, onNullInput=RETURNS_NULL) public static double[] accumulateXY(double[] state, double y, double x)A common accumulator for twovariable statistical aggregates that depend on n, Sx, Sxx, Sy, Syy, and Sxy. 
removeXY
@Function(schema="javatest", effects=IMMUTABLE, onNullInput=RETURNS_NULL) public static double[] removeXY(double[] state, double y, double x)'Removes' from the state a row previously accumulated, for possible use in a window with a moving frame start.This can be a numerically poor idea for exactly the reasons covered in the PostgreSQL docs involving loss of significance in long sums, but it does demonstrate the idea.

count
@Aggregate(arguments={"y double precision","x double precision"}, plan=@Plan(stateSize=72,initialState="{0,0,0,0,0,0}",accumulate={"javatest","accumulateXY"})) @Function(schema="javatest", effects=IMMUTABLE, onNullInput=RETURNS_NULL) public static long count(double[] state)Finisher that returns the count of nonnull rows accumulated.As an alternative to collecting all
@Aggregate
annotations up at the top of the class and specifying everything explicitly, an@Aggregate
annotation can be placed on a method, either the accumulator or the finisher, in which case less needs to be specified. The state type can always be determined from the annotated method (whether it is the accumulator or the finisher), and its SQL name will be the default name for the aggregate also. When the method is the accumulator, the aggregate's arguments are also determined.This being a finisher method, the
@Aggregate
annotation placed here does need to specify the arguments, initial state, and accumulator. 
finishAvgX
@Function(schema="javatest", effects=IMMUTABLE, onNullInput=RETURNS_NULL) public static Double finishAvgX(double[] state)Finisher that returns the mean of the accumulated x values. 
finishAvgY
@Function(schema="javatest", effects=IMMUTABLE, onNullInput=RETURNS_NULL) public static Double finishAvgY(double[] state)Finisher that returns the mean of the accumulated y values. 
finishSlope
@Function(schema="javatest", effects=IMMUTABLE, onNullInput=RETURNS_NULL) public static Double finishSlope(double[] state)Finisher that returns the slope of a regression line. 
finishIntercept
@Function(schema="javatest", effects=IMMUTABLE, onNullInput=RETURNS_NULL) public static Double finishIntercept(double[] state)Finisher that returns the intercept of a regression line. 
finishRegr
@Function(schema="javatest", effects=IMMUTABLE, onNullInput=RETURNS_NULL, out={"slope double precision","intercept double precision"}) public static boolean finishRegr(double[] state, ResultSet out) throws SQLExceptionA finisher that returns the slope and intercept together.An aggregate can be built over this finisher and will return a record result, but at present (PG 13) access to that record by field doesn't work, as its tuple descriptor gets lost along the way. Unclear so far whether it might be feasible to fix that.
 Throws:
SQLException

sum
@Aggregate @Function(schema="javatest", effects=IMMUTABLE, onNullInput=RETURNS_NULL) public static double sum(double state, double x)An example aggregate that sums its input.The simplest kind of aggregate, having only an accumulate function, default initial state, and no finisher (the state value is the return) can be declared very concisely by annotating the accumulate method.
