Class Aggregates

java.lang.Object
org.postgresql.pljava.example.annotation.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
A class demonstrating several aggregate functions.

They are (some of) the same two-variable 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 built-in ones. These closely follow the "schoolbook" formulas in the HP-11C 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 two-variable statistical aggregates that depend on n, Sx, Sxx, Sy, Syy, and Sxy.
    static long count​(double[] state)
    Finisher that returns the count of non-null 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.

    Methods inherited from class java.lang.Object

    clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
  • Method Details