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;

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com