CQL (cassandra) - Select only the rows with maximum value in one of the columns

user_14490988 0
Viewed 818

I need to find the rows with given stationid, that have time1 greater than specified time and maximal time2.

The table is created like this:

CREATE TABLE forec (
    stationid int,
    time1 timestamp,
    time2 timestamp,
    value double,
    PRIMARY KEY ((stationid), time1, time2)
) WITH CLUSTERING ORDER BY (time1 DESC)

Lets suppose the data in the table is like this:

    +------------+-----------------------+----------------------+--------+
    | stationid  | time1                 |  time2               |  value |
    +------------+-----------------------+----------------------+--------+
    | 1          | 2020-10-21 06:00:00   | 2020-10-21 05:00:00  | 1      |                                  
    | 1          | 2020-10-21 06:00:00   | 2020-10-21 04:00:00  | 2      |                                   
    | 1          | 2020-10-21 06:00:00   | 2020-10-21 03:00:00  | 3      |                                   
    | 1          | 2020-10-21 05:00:00   | 2020-10-21 04:00:00  | 4      |
    | 1          | 2020-10-21 05:00:00   | 2020-10-21 03:00:00  | 5      |
    | 1          | 2020-10-21 04:00:00   | 2020-10-21 02:00:00  | 6      |
    +------------+-----------------------+----------------------+--------+

I would like to query: Give me all the rows where stationid = 1 and time1 >= 2020-10-21 05:00:00 and time2 has maximum value. The query should return the following rows:

    +------------+-----------------------+----------------------+--------+
    | stationid  | time1                 |  time2               |  value |
    +------------+-----------------------+----------------------+--------+
    | 1          | 2020-10-21 06:00:00   | 2020-10-21 05:00:00  | 1      |        
    | 1          | 2020-10-21 05:00:00   | 2020-10-21 04:00:00  | 4      | 
    +------------+-----------------------+----------------------+--------+

I know I could query like:

SELECT * FROM forec WHERE stationid = 1 AND time1 >= '2020-10-21 05:00:00';

and then filter the results on client (and keep only the rows that have maximum time2), however I would like to know if this can be done more efficiently (filtering results on the Cassandra side).

Or maybe I should change the table model?

cql cassandra
2 Answers

The solution using UDA/UDFs:

State function:

CREATE OR REPLACE FUNCTION curValState ( state tuple<timestamp,double>, time timestamp, value double ) CALLED ON NULL INPUT RETURNS tuple<timestamp, double> LANGUAGE java AS 'if (time != null && value != null) { if(state == null) {com.datastax.driver.core.TupleType tupleType = com.datastax.driver.core.TupleType.of(com.datastax.driver.core.ProtocolVersion.NEWEST_SUPPORTED, com.datastax.driver.core.CodecRegistry.DEFAULT_INSTANCE, com.datastax.driver.core.DataType.timestamp(), com.datastax.driver.core.DataType.cdouble()); state = tupleType.newValue(time, value);} else {if(state.getTimestamp(0).compareTo(time)<0){ state.setTimestamp(0, time); state.setDouble(1, value);}}} return state;';

Final function:

CREATE OR REPLACE FUNCTION finalVal ( state tuple<timestamp, double> ) CALLED ON NULL INPUT RETURNS double LANGUAGE java AS 'return state.getDouble(1);';

Aggregate function:

CREATE OR REPLACE AGGREGATE valueatlatesttime (timestamp, double) SFUNC curValState STYPE tuple<timestamp, double> FINALFUNC finalVal INITCOND null;

The query:

SELECT
  stationid,
  time1,
  max(time2) AS max_time2,
  valueatlatesttime(time2, value) AS value_at_max_time2
FROM
  forec
WHERE
  stationid = 1
AND
  time1 >= '2020-10-21 05:00:00'
GROUP BY time1;
user_144909880

Edit: According to Cassandra document, "If a column is selected without an aggregate function, in a statement with a GROUP BY, the first value encounter in each group will be returned." So below example only works when time2 is stored in DESC order.

If you are using recent version of Cassandra (like 3.11.x) then you can use GROUP BY to do things like

SELECT
  stationid,
  time1,
  max(time2) AS max_time2,
  value
FROM
  forec
WHERE
  stationid = 1
AND
  time1 >= '2020-10-21 05:00:00'
GROUP BY time1;

and you get

cqlsh:test> SELECT stationid, time1, max(time2) as max_time2, value FROM forec WHERE stationid = 1 AND time1 >= '2020-10-21 05:00:00' GROUP BY  time1;

 stationid | time1                           | max_time2                       | value
-----------+---------------------------------+---------------------------------+-------
         1 | 2020-10-21 06:00:00.000000+0000 | 2020-10-21 05:00:00.000000+0000 |     1
         1 | 2020-10-21 05:00:00.000000+0000 | 2020-10-21 04:00:00.000000+0000 |     4

(2 rows)

Note that this scans your partition so be aware of partition size, especially when you use timestamp in clustering column.

user_11461960
Related