Cycling user-defined aggregate team_average
Creates a Java user-defined aggregate function that calculates the average of a target column.
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_state;
DROP FUNCTION IF EXISTS cycling.average_state;
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);
$$
;
DESCRIBE FUNCTION cycling.average_final;
DROP FUNCTION IF EXISTS cycling.average_final;
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 TABLE IF NOT EXISTS cycling.test_average_state (
id int PRIMARY KEY,
state frozen<tuple<int, bigint>>,
val int
);
DROP TABLE IF EXISTS cycling.test_average_state;
// tag::table-create-test-average-state[]
CREATE TABLE IF NOT EXISTS cycling.test_average_state (
id int PRIMARY KEY,
state frozen<tuple<int, bigint>>,
val int
);
// end::table-create-test-average-state[]
// tag::data-insert-test-average-state[]
INSERT INTO cycling.test_average_state (
id, state, val
) VALUES (
1, (6, 9949), 51
);
INSERT INTO cycling.test_average_state (
id, state, val
) VALUES (
2, (79, 10000), 9999
);
// end::data-insert-test-average-state[]
// tag::query-test-average-state[]
SELECT state, average_state(state, val), val
FROM cycling.test_average_state;
// end::query-test-average-state[]
// tag::table-drop-test-average-state[]
DROP TABLE IF EXISTS cycling.test_average_state;
// end::table-drop-test-average-state[]
SELECT state, average_state(state, val), val
FROM cycling.test_average_state;