user-defined-aggregate-average

CREATE OR REPLACE AGGREGATE cycling.average (int)
  SFUNC average_state
  STYPE tuple<int,bigint>
  FINALFUNC average_final
  INITCOND (0, 0);

DESCRIBE AGGREGATE cycling.average;

DROP AGGREGATE IF EXISTS cycling.average;

CREATE OR REPLACE FUNCTION cycling.average_final (state tuple<int,bigint>)
  CALLED ON NULL INPUT
  RETURNS double
  LANGUAGE java AS
    $$
      double r = 0;
      if (state.getInt(0) == 0) return null;
      r = state.getLong(1);
      r /= state.getInt(0);
      return Double.valueOf(r);
    $$ ;

CREATE OR REPLACE FUNCTION cycling.average_state
  (state tuple<int, bigint>, val int)
  CALLED ON NULL INPUT
  RETURNS tuple<int, bigint>
  LANGUAGE java AS
    $$
      if (val != null) {
        state.setInt(0, state.getInt(0) + 1);
        state.setLong(1, state.getLong(1) + val.intValue());
      }
      return state;
    $$
;

DESCRIBE FUNCTION cycling.average_final;

DESCRIBE FUNCTION cycling.average_state;

DROP FUNCTION IF EXISTS cycling.average_final;

DROP FUNCTION IF EXISTS cycling.average_state;