Julian Julian - 1 year ago 79
SQL Question

How to get the sum of my value based on the dates from apache ignite cache

What is the best way to get the sum of my value based on the dates from the ignite cache

Im getting all the data I need from my postgres DB based on selected dates.

value is of type String in MyClass.class

date | value | account
01-01-2015 | 363947.5636999999987892806529998779296875 | 110589
23-08-2016 | 56985.5636999999987892806529998779296875 | 110589
30-11-2016 | 875347.5636999999987892806529998779296875 | 110589
23-11-2016 | 756247.5636999999987892806529998779296875 | 225863

Then I want to sum the returned value using my cache query. Whats the best way to do this?

IgniteConfiguration ignitionConfig = new IgniteConfiguration();
Ignite ignite = Ignition.getOrStart(ignitionConfig);

IgniteCache<Integer, MyClass> cache = ignite.getOrCreateCache(cfg);

StringBuilder builder = new StringBuilder();
builder.append(" SELECT SUM(value) FROM MyClass WHERE");
builder.append(" date BETWEEN ? AND ? AND ");
builder.append(" account = ? ");

SqlFieldsQuery qry = new SqlFieldsQuery(builder.toString());

try (QueryCursor<List<?>> cursor = cache.query(qry)) {
for (List<?> row : cursor){
System.out.println("test=" + row.get(0));

Alternatively, I can loop through each results but I wanted to use the SQL way first.

Answer Source

Cast the value to double precision or, if the whole precision is necessary, to numeric:

select sum(value::float)
from myclass
where date between ? and ? and account = ?

float with no precision specified means double precision

Numeric types