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
AVGedit
Input: Numeric, Output: double
Average (arithmetic mean) of input values.
SELECT AVG(salary) AS avg FROM emp;
avg:d
---------------
48248
COUNTedit
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
MAXedit
Input: Numeric, Output: Same as input
Maximum value across input values.
SELECT MAX(salary) AS max FROM emp;
max
---------------
74999
MINedit
Input: Numeric, Output: Same as input
Minimum value across input values.
SELECT MIN(salary) AS min FROM emp;
min
---------------
25324
SUMedit
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
KURTOSISedit
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
PERCENTILEedit
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_RANKedit
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
SKEWNESSedit
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_POPedit
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_SQUARESedit
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_POPedit
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