Aggregate Functions
editAggregate Functions
editFunctions for computing a single result from a set of input values. Elasticsearch SQL supports aggregate functions only alongside grouping (implicit or explicit).
General Purpose
editAVG
editSynopsis:
Input:
numeric field. If this field contains only |
Output: double
numeric value
Description: Returns the Average (arithmetic mean) of input values.
SELECT AVG(salary) AS avg FROM emp; avg --------------- 48248.55
SELECT AVG(salary / 12.0) AS avg FROM emp; avg --------------- 4020.7125
COUNT
editSynopsis:
Input:
a field name, wildcard ( |
Output: numeric value
Description: Returns the total number (count) of input values.
SELECT COUNT(*) AS count FROM emp; count --------------- 100
COUNT(ALL)
editSynopsis:
Input:
a field name. If this field contains only |
Output: numeric value
Description: Returns the total number (count) of all non-null input values. COUNT(<field_name>)
and COUNT(ALL <field_name>)
are equivalent.
SELECT COUNT(ALL last_name) AS count_all, COUNT(DISTINCT last_name) count_distinct FROM emp; count_all | count_distinct ---------------+------------------ 100 |96
SELECT COUNT(ALL CASE WHEN languages IS NULL THEN -1 ELSE languages END) AS count_all, COUNT(DISTINCT CASE WHEN languages IS NULL THEN -1 ELSE languages END) count_distinct FROM emp; count_all | count_distinct ---------------+--------------- 100 |6
COUNT(DISTINCT)
editSynopsis:
Input:
Output: numeric value. If this field contains only null
values, the function
returns null
. Otherwise, the function ignores null
values in this field.
Description: Returns the total number of distinct non-null values in input values.
SELECT COUNT(DISTINCT hire_date) unique_hires, COUNT(hire_date) AS hires FROM emp; unique_hires | hires ----------------+--------------- 99 |100
SELECT COUNT(DISTINCT DATE_TRUNC('YEAR', hire_date)) unique_hires, COUNT(DATE_TRUNC('YEAR', hire_date)) AS hires FROM emp; unique_hires | hires ---------------+--------------- 14 |100
FIRST/FIRST_VALUE
editSynopsis:
Input:
Output: same type as the input
Description: Returns the first non-null
value (if such exists) of the field_name
input column sorted by
the ordering_field_name
column. If ordering_field_name
is not provided, only the field_name
column is used for the sorting. E.g.:
a | b |
---|---|
100 |
1 |
200 |
1 |
1 |
2 |
2 |
2 |
10 |
null |
20 |
null |
null |
null |
SELECT FIRST(a) FROM t
will result in:
FIRST(a) |
1 |
and
SELECT FIRST(a, b) FROM t
will result in:
FIRST(a, b) |
100 |
SELECT FIRST(first_name) FROM emp; FIRST(first_name) -------------------- Alejandro
SELECT gender, FIRST(first_name) FROM emp GROUP BY gender ORDER BY gender; gender | FIRST(first_name) ------------+-------------------- null | Berni F | Alejandro M | Amabile
SELECT FIRST(first_name, birth_date) FROM emp; FIRST(first_name, birth_date) -------------------------------- Remzi
SELECT gender, FIRST(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender; gender | FIRST(first_name, birth_date) --------------+-------------------------------- null | Lillian F | Sumant M | Remzi
FIRST_VALUE
is a name alias and can be used instead of FIRST
, e.g.:
SELECT gender, FIRST_VALUE(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender; gender | FIRST_VALUE(first_name, birth_date) --------------+-------------------------------------- null | Lillian F | Sumant M | Remzi
SELECT gender, FIRST_VALUE(SUBSTRING(first_name, 2, 6), birth_date) AS "first" FROM emp GROUP BY gender ORDER BY gender; gender | first ---------------+--------------- null |illian F |umant M |emzi
FIRST
cannot be used in a HAVING clause.
FIRST
cannot be used with columns of type text
unless
the field is also saved as a keyword.
LAST/LAST_VALUE
editSynopsis:
Input:
Output: same type as the input
Description: It’s the inverse of FIRST/FIRST_VALUE
. Returns the last non-null
value (if such exists) of the
field_name
input column sorted descending by the ordering_field_name
column. If ordering_field_name
is not
provided, only the field_name
column is used for the sorting. E.g.:
a | b |
---|---|
10 |
1 |
20 |
1 |
1 |
2 |
2 |
2 |
100 |
null |
200 |
null |
null |
null |
SELECT LAST(a) FROM t
will result in:
LAST(a) |
200 |
and
SELECT LAST(a, b) FROM t
will result in:
LAST(a, b) |
2 |
SELECT LAST(first_name) FROM emp; LAST(first_name) ------------------- Zvonko
SELECT gender, LAST(first_name) FROM emp GROUP BY gender ORDER BY gender; gender | LAST(first_name) ------------+------------------- null | Patricio F | Xinglin M | Zvonko
SELECT LAST(first_name, birth_date) FROM emp; LAST(first_name, birth_date) ------------------------------- Hilari
SELECT gender, LAST(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender; gender | LAST(first_name, birth_date) -----------+------------------------------- null | Eberhardt F | Valdiodio M | Hilari
LAST_VALUE
is a name alias and can be used instead of LAST
, e.g.:
SELECT gender, LAST_VALUE(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender; gender | LAST_VALUE(first_name, birth_date) -----------+------------------------------------- null | Eberhardt F | Valdiodio M | Hilari
SELECT gender, LAST_VALUE(SUBSTRING(first_name, 3, 8), birth_date) AS "last" FROM emp GROUP BY gender ORDER BY gender; gender | last ---------------+--------------- null |erhardt F |ldiodio M |lari
LAST
cannot be used in HAVING
clause.
LAST
cannot be used with columns of type text
unless
the field is also saved as a keyword
.
MAX
editSynopsis:
Input:
a numeric field. If this field contains only |
Output: same type as the input
Description: Returns the maximum value across input values in the field field_name
.
SELECT MAX(salary) AS max FROM emp; max --------------- 74999
SELECT MAX(ABS(salary / -12.0)) AS max FROM emp; max ----------------- 6249.916666666667
MAX
on a field of type text
or keyword
is translated into
LAST/LAST_VALUE
and therefore, it cannot be used in HAVING
clause.
MIN
editSynopsis:
Input:
a numeric field. If this field contains only |
Output: same type as the input
Description: Returns the minimum value across input values in the field field_name
.
SELECT MIN(salary) AS min FROM emp; min --------------- 25324
MIN
on a field of type text
or keyword
is translated into
FIRST/FIRST_VALUE
and therefore, it cannot be used in HAVING
clause.
SUM
editSynopsis:
Input:
a numeric field. If this field contains only |
Output: bigint
for integer input, double
for floating points
Description: Returns the sum of input values in the field field_name
.
SELECT SUM(salary) AS sum FROM emp; sum --------------- 4824855
SELECT ROUND(SUM(salary / 12.0), 1) AS sum FROM emp; sum --------------- 402071.3
Statistics
editKURTOSIS
editSynopsis:
Input:
a numeric field. If this field contains only |
Output: double
numeric value
Description:
Quantify the shape of the distribution of input values in the field field_name
.
SELECT MIN(salary) AS min, MAX(salary) AS max, KURTOSIS(salary) AS k FROM emp; min | max | k ---------------+---------------+------------------ 25324 |74999 |2.0444718929142986
KURTOSIS
cannot be used on top of scalar functions or operators but only directly on a field. So, for example,
the following is not allowed and an error is returned:
SELECT KURTOSIS(salary / 12.0), gender FROM emp GROUP BY gender
MAD
editSynopsis:
Input:
a numeric field. If this field contains only |
Output: double
numeric value
Description:
Measure the variability of the input values in the field field_name
.
SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg, MAD(salary) AS mad FROM emp; min | max | avg | mad ---------------+---------------+---------------+--------------- 25324 |74999 |48248.55 |10096.5
SELECT MIN(salary / 12.0) AS min, MAX(salary / 12.0) AS max, AVG(salary/ 12.0) AS avg, MAD(salary / 12.0) AS mad FROM emp; min | max | avg | mad ------------------+-----------------+---------------+----------------- 2110.3333333333335|6249.916666666667|4020.7125 |841.3750000000002
PERCENTILE
editSynopsis:
Input:
a numeric field. If this field contains only |
|
a numeric expression (must be a constant and not based on a field). If
|
|
optional string literal for the percentile algorithm. Possible values: |
|
optional numeric literal that configures the percentile algorithm. Configures |
Output: double
numeric value
Description:
Returns the nth percentile (represented by numeric_exp
parameter)
of input values in the field field_name
.
SELECT languages, PERCENTILE(salary, 95) AS "95th" FROM emp GROUP BY languages; languages | 95th ---------------+----------------- null |74482.4 1 |71122.8 2 |70271.4 3 |71926.0 4 |69352.15 5 |56371.0
SELECT languages, PERCENTILE(salary / 12.0, 95) AS "95th" FROM emp GROUP BY languages; languages | 95th ---------------+------------------ null |6206.866666666667 1 |5926.9 2 |5855.949999999999 3 |5993.833333333333 4 |5779.345833333333 5 |4697.583333333333
SELECT languages, PERCENTILE(salary, 97.3, 'tdigest', 100.0) AS "97.3_TDigest", PERCENTILE(salary, 97.3, 'hdr', 3) AS "97.3_HDR" FROM emp GROUP BY languages; languages | 97.3_TDigest | 97.3_HDR ---------------+-----------------+--------------- null |74720.036 |74992.0 1 |72316.132 |73712.0 2 |71792.436 |69936.0 3 |73326.23999999999|74992.0 4 |71753.281 |74608.0 5 |61176.16000000001|56368.0
PERCENTILE_RANK
editSynopsis:
Input:
a numeric field. If this field contains only |
|
a numeric expression (must be a constant and not based on a field). If
|
|
optional string literal for the percentile algorithm. Possible values: |
|
optional numeric literal that configures the percentile algorithm. Configures |
Output: double
numeric value
Description:
Returns the nth percentile rank (represented by numeric_exp
parameter)
of input values in the field field_name
.
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 |96.79075152940749
SELECT languages, PERCENTILE_RANK(salary/12, 5000) AS rank FROM emp GROUP BY languages; languages | rank ---------------+------------------ null |66.91240875912409 1 |66.70766707667076 2 |84.13266895048271 3 |61.052992625621684 4 |76.55646443990001 5 |94.00696864111498
SELECT languages, ROUND(PERCENTILE_RANK(salary, 65000, 'tdigest', 100.0), 2) AS "rank_TDigest", ROUND(PERCENTILE_RANK(salary, 65000, 'hdr', 3), 2) AS "rank_HDR" FROM emp GROUP BY languages; languages | rank_TDigest | rank_HDR ---------------+---------------+--------------- null |73.66 |80.0 1 |73.73 |73.33 2 |88.88 |89.47 3 |79.44 |76.47 4 |85.7 |83.33 5 |96.79 |95.24
SKEWNESS
editSynopsis:
Input:
a numeric field. If this field contains only |
Output: double
numeric value
Description:
Quantify the asymmetric distribution of input values in the field field_name
.
SELECT MIN(salary) AS min, MAX(salary) AS max, SKEWNESS(salary) AS s FROM emp; min | max | s ---------------+---------------+------------------ 25324 |74999 |0.2707722118423227
SKEWNESS
cannot be used on top of scalar functions but only directly on a field. So, for example, the following is
not allowed and an error is returned:
SELECT SKEWNESS(ROUND(salary / 12.0, 2), gender FROM emp GROUP BY gender
STDDEV_POP
editSynopsis:
Input:
a numeric field. If this field contains only |
Output: double
numeric value
Description:
Returns the population standard deviation of input values in the field field_name
.
SELECT MIN(salary) AS min, MAX(salary) AS max, STDDEV_POP(salary) AS stddev FROM emp; min | max | stddev ---------------+---------------+------------------ 25324 |74999 |13765.125502787832
SELECT MIN(salary / 12.0) AS min, MAX(salary / 12.0) AS max, STDDEV_POP(salary / 12.0) AS stddev FROM emp; min | max | stddev ------------------+-----------------+----------------- 2110.3333333333335|6249.916666666667|1147.093791898986
STDDEV_SAMP
editSynopsis:
Input:
a numeric field. If this field contains only |
Output: double
numeric value
Description:
Returns the sample standard deviation of input values in the field field_name
.
SELECT MIN(salary) AS min, MAX(salary) AS max, STDDEV_SAMP(salary) AS stddev FROM emp; min | max | stddev ---------------+---------------+------------------ 25324 |74999 |13834.471662090747
SELECT MIN(salary / 12.0) AS min, MAX(salary / 12.0) AS max, STDDEV_SAMP(salary / 12.0) AS stddev FROM emp; min | max | stddev ------------------+-----------------+----------------- 2110.3333333333335|6249.916666666667|1152.872638507562
SUM_OF_SQUARES
editSynopsis:
Input:
a numeric field. If this field contains only |
Output: double
numeric value
Description:
Returns the sum of squares of input values in the field field_name
.
SELECT MIN(salary) AS min, MAX(salary) AS max, SUM_OF_SQUARES(salary) AS sumsq FROM emp; min | max | sumsq ---------------+---------------+---------------- 25324 |74999 |2.51740125721E11
SELECT MIN(salary / 24.0) AS min, MAX(salary / 24.0) AS max, SUM_OF_SQUARES(salary / 24.0) AS sumsq FROM emp; min | max | sumsq ------------------+------------------+------------------- 1055.1666666666667|3124.9583333333335|4.370488293767361E8
VAR_POP
editSynopsis:
Input:
a numeric field. If this field contains only |
Output: double
numeric value
Description:
Returns the population variance of input values in the field field_name
.
SELECT MIN(salary) AS min, MAX(salary) AS max, VAR_POP(salary) AS varpop FROM emp; min | max | varpop ---------------+---------------+---------------- 25324 |74999 |1.894786801075E8
SELECT MIN(salary / 24.0) AS min, MAX(salary / 24.0) AS max, VAR_POP(salary / 24.0) AS varpop FROM emp; min | max | varpop ------------------+------------------+------------------ 1055.1666666666667|3124.9583333333335|328956.04185329855
VAR_SAMP
editSynopsis:
Input:
a numeric field. If this field contains only |
Output: double
numeric value
Description:
Returns the sample variance of input values in the field field_name
.
SELECT MIN(salary) AS min, MAX(salary) AS max, VAR_SAMP(salary) AS varsamp FROM emp; min | max | varsamp ---------------+---------------+---------------- 25324 |74999 |1.913926061691E8
SELECT MIN(salary / 24.0) AS min, MAX(salary / 24.0) AS max, VAR_SAMP(salary / 24.0) AS varsamp FROM emp; min | max | varsamp ------------------+------------------+---------------- 1055.1666666666667|3124.9583333333335|332278.830154847