Aggregate Functionsedit
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
Functions for computing a single result from a set of input values. Elasticsearch SQL supports aggregate functions only alongside grouping (implicit or explicit).
General Purposeedit
AVG
edit
Input: Numeric, Output: double
Average (arithmetic mean) of input values.
SELECT AVG(salary) AS avg FROM emp; avg:d --------------- 48248
COUNT
edit
Input: Any, Output: bigint
Total number (count) of input values.
SELECT COUNT(*) AS count FROM emp; count --------------- 100
COUNT(DISTINCT)
edit
Input: Any, Output: bigint
Total number of distinct values in input values.
SELECT COUNT(DISTINCT hire_date) AS hires FROM emp; hires --------------- 99
MAX
edit
Input: Numeric, Output: Same as input
Maximum value across input values.
SELECT MAX(salary) AS max FROM emp; max --------------- 74999
MIN
edit
Input: Numeric, Output: Same as input
Minimum value across input values.
SELECT MIN(salary) AS min FROM emp; min --------------- 25324
SUM
edit
Input: Numeric, Output: bigint
for integer input, double
for floating points
Sum of input values.
SELECT SUM(salary) AS sum FROM emp; sum --------------- 4824855
Statisticsedit
KURTOSIS
edit
Input: Numeric, Output: double
Quantify the shape of the distribution of input values.
SELECT MIN(salary) AS min, MAX(salary) AS max, KURTOSIS(salary) AS k FROM emp; min | max | k ---------------+---------------+------------------ 25324 |74999 |2.0444718929142986
PERCENTILE
edit
Input: Numeric, Output: double
The nth percentile of input values.
SELECT languages, PERCENTILE(salary, 95) AS "95th" FROM emp GROUP BY languages; languages | 95th ---------------+----------------- null |74999.0 1 |72790.5 2 |71924.70000000001 3 |73638.25 4 |72115.59999999999 5 |61071.7
PERCENTILE_RANK
edit
Input: Numeric, Output: double
The percentile rank of input values of input values.
SELECT languages, PERCENTILE_RANK(salary, 65000) AS rank FROM emp GROUP BY languages; languages | rank ---------------+----------------- null |73.65766569962062 1 |73.7291625157734 2 |88.88005607010643 3 |79.43662623295829 4 |85.70446389643493 5 |100.0
SKEWNESS
edit
Input: Numeric, Output: double
Quantify the asymmetric distribution of input values.
SELECT MIN(salary) AS min, MAX(salary) AS max, SKEWNESS(salary) AS s FROM emp; min | max | s ---------------+---------------+------------------ 25324 |74999 |0.2707722118423227
STDDEV_POP
edit
Input: Numeric, Output: double
Population standard deviation of input values.
SELECT MIN(salary) AS min, MAX(salary) AS max, STDDEV_POP(salary) AS stddev FROM emp; min | max | stddev ---------------+---------------+------------------ 25324 |74999 |13765.125502787832
SUM_OF_SQUARES
edit
Input: Numeric, Output: double
Sum of squares of input values.
SELECT MIN(salary) AS min, MAX(salary) AS max, SUM_OF_SQUARES(salary) AS sumsq FROM emp; min | max | sumsq ---------------+---------------+---------------- 25324 |74999 |2.51740125721E11
VAR_POP
edit
Input: Numeric, Output: double
Population variance of input values.
SELECT MIN(salary) AS min, MAX(salary) AS max, VAR_POP(salary) AS varpop FROM emp; min | max | varpop ---------------+---------------+---------------- 25324 |74999 |1.894786801075E8