Package org.postgresql.pljava.example.annotation

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
    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.

    • Method Detail

      • accumulateXY

        @Function(schema="javatest",
          effects=IMMUTABLE,
          onNullInput=RETURNS_NULL)
public 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.

      • 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 non-null 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 SQLException
        A 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.