aggregate
Performs simple data aggregation operations on numeric fields like count, sum, average, keys, start and end. The aggregate command iterates matching keys or rows in the store so, depending on the size of the specified key or row, it may take a very long time to complete.
aggregate table is an aggregate subcommand.
aggregate table
aggregate table -name <name>
[-count] [-sum <field[,field,..]>]
[-avg <field[,field,..]>]
[-index <name>]
[-field <name> -value <value>]*
[-field <name> [-start <value>] [-end <value>]]
[-json <string>]
Performs simple data aggregation operations on numeric fields of the table.
where:
-
-name
Specifies the table for the operation.
-
-count
Returns the count of matching records.
-
-sum
Returns the sum of the values of matching fields.
-
-avg
Returns the average of the values of matching fields.
-
-index
Specifies the name of the index to use. When an index is used, the fields named must belong to the specified index and the aggregation is performed over rows with matching index entries.
-
-field
and-value
pairs are used to specify the field values of the primary key to use to match for the aggregation, or you can use an empty key to match the entire table. -
The
-field
flat, along with its-start
and-end
flags, can be used for restricting the range used to match rows. -
-json
Specifies the fields and values to use for the aggregation as a JSON input string.
See the example below:
# Create a table 'user_test' with an index on user_test(age):
kv-> execute 'CREATE TABLE user_test (id INTEGER,
firstName STRING, lastName STRING, age INTEGER, PRIMARY KEY (id))'
Statement completed successfully
kv-> execute 'CREATE INDEX idx1 on user_test (age)'
Statement completed successfully
# Insert 3 rows:
kv-> put table -name user_test -json
'{"id":1,"firstName":"joe","lastName":"wang","age":21}'
Operation successful, row inserted.
kv-> put table -name user_test -json
'{"id":2,"firstName":"jack","lastName":"zhao","age":32}'
Operation successful, row inserted.
kv-> put table -name user_test -json
'{"id":3,"firstName":"john","lastName":"gu","age":43}'
Operation successful, row inserted.
# Get count(*), sum(age) and avg(age) of rows in table:
kv-> aggregate table -name user_test -count -sum age -avg age
Row count: 3
Sum:
age(3 values): 96
Average:
age(3 values): 32.00
# Get count(*), sum(age) and avg(age) of rows where
age >= 30, idx1 is utilized to filter the rows:
kv-> aggregate table -name user_test -count -sum age
-avg age -index idx1 -field age -start 30
Row count: 2
Sum:
age(2 values): 75
Average:
age(2 values): 37.50