IMPORTANT: No additional bug fixes or documentation updates will be released for this version. For the latest information, see the current release documentation.

« Math Operators Date and Time Functions »

› › ›

Aggregate Functions

edit

Aggregate Functions

edit

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 Purpose

edit

`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

Statistics

edit

`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

« Math Operators Date and Time Functions »