Aggregate functions
Aggregate functions can be used within the :find
clause in Datalog, or the SELECT
clause in SQL.
In line with the SQL spec (which XTDB also respects in Datalog queries):
-
Except in the case of
(count-star)
/COUNT(*)
, null values in the column are removed before the aggregate is calculated. -
Without grouping columns, aggregate functions will always return exactly one row - if the input column is empty (after nulls have been removed), the result will be a single row containing a null value.
Numeric aggregate functions
Datalog | SQL | |
---|---|---|
|
|
average (mean) |
|
|
average (mean) of distinct values |
|
|
count |
|
|
count of distinct values |
|
|
row count |
|
|
maximum |
|
|
maximum of distinct values |
|
|
minimum |
|
|
minimum of distinct values |
|
|
population standard deviation |
|
|
sample standard deviation |
|
|
sum |
|
|
sum of distinct values |
|
|
population variance |
|
|
sample variance |
-
min
/max
aggregates are not yet supported on string values.
Boolean aggregate functions
Datalog | SQL | |
---|---|---|
|
|
true if all values are true; false otherwise |
|
|
false if all values are false; true otherwise |
Composite-type aggregate functions
Datalog | SQL | |
---|---|---|
|
|
return an array of all of the input values |