Riak TS Aggregate Functions

You can turn a set of rows in your Riak TS table into a value with the aggregate feature. This document will walk you through the functions that make up aggregation in Riak TS.

Note: At the moment, you cannot combine arithmetic functions with aggregation functions.

Aggregate Functions

• COUNT() - Returns the number of entries that match a specified criteria.
• SUM() - Returns the sum of entries that match a specified criteria.
• MEAN() & AVG() - Returns the average of entries that match a specified criteria.
• MIN() - Returns the smallest value of entries that match a specified criteria.
• MAX() - Returns the largest value of entries that match a specified criteria.
• STDDEV() - Returns the statistical standard deviation of all entries that match a specified criteria using Population Standard Deviation.

AVG & MEAN

Mean average over the specified sint64 or double column.

SELECT AVG(temperature) FROM GeoCheckin
WHERE time > 1452252523182 AND time < 1452252543182 AND myfamily = 'family1' AND myseries = 'series1'

Returns NULL if no values were returned or all values were NULL.

COUNT

Count the number of returned values.

SELECT COUNT(*) FROM GeoCheckin
WHERE time > 1452252523182 AND time < 1452252543182 AND myfamily = 'family1' AND myseries = 'series1'

If a single column is used as an input then NULL values are ignored. If all values were NULL or no rows were returned by the query then NULL is returned.

MAX

The largest value from the set of values returned by the query.

SELECT MAX(temperature) FROM GeoCheckin
WHERE time > 1452252523182 AND time < 1452252543182 AND myfamily = 'family1' AND myseries = 'series1'

Returns NULL if no values were returned or all values were NULL.

MIN

The smallest value from the set of values returned by the query.

SELECT MIN(temperature) FROM GeoCheckin
WHERE time > 1452252523182 AND time < 1452252543182 AND myfamily = 'family1' AND myseries = 'series1'

Returns NULL if no values were returned or all values were NULL.

STDDEV

Calculate the standard deviation for a set of values returned by the query.

Note: We use population standard deviation for our calculations.

SELECT STDDEV(temperature) FROM GeoCheckin
WHERE time > 1452252523182 AND time < 1452252543182 AND myfamily = 'family1' AND myseries = 'series1'

Returns NULL if less than two non-null values were returned.

SUM

The sum of all the values of one sint64 or double column returned by the query.

SELECT SUM(temperature) FROM GeoCheckin
WHERE time > 1452252523182 AND time < 1452252543182 AND myfamily = 'family1' AND myseries = 'series1'

Returns NULL if no values were returned or all values were NULL.