Geek Answers Handbook

States change over time, list all objects that had the state x = y between time A and time B

I am creating a database to store the states of objects. This indicates, for example, the color changes over time.

I want to query all objects that have a certain state for a given time, for example. everything that was green at least once between 13:00 and 2:00 on a particular day.

My idea was a table like this:

CREATE TABLE states (
  type text,
  value text,
  name text,
  timestamp timeuuid,
  primary key ((type, value), timestamp, name)
) WITH CLUSTERING ORDER BY (timestamp DESC);

Given some test data:

// A, becomes green, turns red and back to green
insert into states(type, value, name, timestamp) values ('color', 'red', 'A',  minTimeuuid('2016-07-07T12:00:00+0000'));
insert into states(type, value, name, timestamp) values ('color', 'green', 'A',  minTimeuuid('2016-07-07T13:35:00+0000'));
insert into states(type, value, name, timestamp) values ('color', 'red', 'A',  minTimeuuid('2016-07-07T13:42:00+0000'));
insert into states(type, value, name, timestamp) values ('color', 'green', 'A',  minTimeuuid('2016-07-07T13:45:00+0000'));


// B stays red
insert into states(type, value, name, timestamp) values ('color', 'red', 'B',  minTimeuuid('2016-07-07T01:00:00+0000'));


// C stays green
insert into states(type, value, name, timestamp) values ('color', 'green', 'C',  minTimeuuid('2016-07-07T11:27:00+0000'));

// D becomes red
insert into states(type, value, name, timestamp) values ('color', 'green', 'D',  minTimeuuid('2016-07-07T13:00:00+0000'));
insert into states(type, value, name, timestamp) values ('color', 'red', 'D',  minTimeuuid('2016-07-07T13:27:00+0000'));

 type  | value | system.dateof(timestamp) | name
-------+-------+--------------------------+------
 color | green | 2016-07-07 13:45:00+0000 |    A
 color | green | 2016-07-07 13:35:00+0000 |    A
 color | green | 2016-07-07 13:00:00+0000 |    D
 color | green | 2016-07-07 11:27:00+0000 |    C
 color |   red | 2016-07-07 13:42:00+0000 |    A
 color |   red | 2016-07-07 13:27:00+0000 |    D
 color |   red | 2016-07-07 12:00:00+0000 |    A
 color |   red | 2016-07-07 01:00:00+0000 |    B

What I would like to get is A, C, D, not B, as it was not green within the timer.

And simple between request:

select name from states where type = 'color' and value = 'green' and timestamp >= minTimeuuid('2016-07-07T13:00:00+0000') and timestamp < minTimeuuid('2016-07-07T14:00:00+0000');

I get A,A,Dthe result. I cannot use different values ​​here because "SELECT DISTINCT queries must only request partition key columns and/or static columns (not name)", but I could live with duplicates because they are easy to handle on the application side.

, C, .

, , I cannot specify when the connected devices send updates. They just send data as their state changes and the middleware has to be stateless , ( ) .

?

+4
1

, , , User Defined Function User Defined Aggregates, ( JIRA CASSANDRA-10783). , :

  • is_in_interval() (. )
  • matching_objects_in_interval() (. )

( CASSANDRA-10783)

CREATE FUNCTION is_in_interval(state set<text>, name text, timestamp timeuuid, 
min_date timeuuid, max_date timeuuid)
RETURNS NULL ON NULL INPUT
RETURNS set<text>
LANGUAGE java
AS $$
    // The object has it timestamp inside the provided date range
    if(timestamp.compareTo(min_date) >= 0 &&
       timestamp.compareTo(max_date) <= 0) {
       // We don't care adding multiple time because Set eliminates duplicates anyway 
       state.add(name);
    }
    return state;
$$;

CREATE AGGREGATE IF NOT EXISTS matching_objects_in_interval(text, timeuuid, timeuuid, timeuuid)
SFUNC is_in_interval
STYPE set<text>
// {} is the Cassandra LITERAL SYNTAX for empty set
INITCOND {};

:

SELECT matching_objects_in_interval(name, timestamp, minTimeuuid('2016-07-07T13:00:00+0000'), minTimeuuid('2016-07-07T14:00:00+0000'))
FROM states 
WHERE type = 'color' 
AND value = 'green';
+4

More articles:


All Articles