› › ›

Функции агрегирования

Функции для вычисления единственного результата из набора входных значений. Elasticsearch SQL поддерживает функции агрегирования только вместе с группировкой (явной или неявной).

Общие

`AVG`

Синопсис:

AVG(numeric_field)

Входные данные:

числовое поле. Если это поле содержит только null значения, функция возвращает null. В противном случае функция игнорирует null значения в этом поле.

Выходные данные: double числовое значение

Описание: Возвращает среднее арифметическое входных значений.

SELECT AVG(salary) AS avg FROM emp;

      avg
---------------
48248.55

SELECT AVG(salary / 12.0) AS avg FROM emp;

      avg
---------------
4020.7125

`COUNT`

Синопсис:

COUNT(expression)

Входные данные:

имя поля, подстановка (*) или любое числовое значение. Для COUNT(*) или COUNT(<literal>), учитываются все значения, включая null или отсутствующие. Для COUNT(<field_name>), null значения не учитываются.

Выходные данные: числовое значение

Описание: Возвращает общее количество входных значений.

SELECT COUNT(*) AS count FROM emp;

     count
---------------
100

`COUNT(ALL)`

Синопсис:

COUNT(ALL field_name)

Входные данные:

имя поля. Если это поле содержит только null значения, функция возвращает null. В противном случае функция игнорирует null значения в этом поле.

Выходные данные: числовое значение

Описание: Возвращает общее количество непустых входных значений. COUNT(<field_name>) и COUNT(ALL <field_name>) эквивалентны.

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)`

Синопсис:

COUNT(DISTINCT field_name)

Входные данные:

имя поля

Выходные данные: числовое значение. Если это поле содержит только null значения, функция возвращает null. В противном случае функция игнорирует null значения в этом поле.

Описание: Возвращает общее количество различных непустых значений во входных значениях.

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`

Синопсис:

FIRST(
    field_name               
    [, ordering_field_name])

Входные данные:

	поле для агрегирования
	необязательное поле, используемое для сортировки

Выходные данные: тот же тип, что и вход

Описание: Возвращает первое ненулевое значение (если такое существует) столбца входных данных, отсортированного по столбцу ordering_field_name. Если ordering_field_name не указано, используется только столбец field_name для сортировки. Например:

a	b
100	1
200	1
1	2
2	2
10	null
20	null
null	null

SELECT FIRST(a) FROM t

приведет к:

FIRST(a)

SELECT FIRST(a, b) FROM t

приведет к:

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 - псевдоним имени и может использоваться вместо FIRST, например:

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 не может использоваться в предложении HAVING.

FIRST нельзя использовать со столбцами типа text, если поле также не сохранено как ключевое.

`LAST/LAST_VALUE`

Краткое описание:

LAST(
    field_name               
    [, ordering_field_name])

Входные данные:

	целевое поле для агрегации
	необязательное поле, используемое для сортировки

Выходные данные: тот же тип, что и входные данные

Описание: Это обратное по отношению к FIRST/FIRST_VALUE. Возвращает последнее ненулевое значение (если такое существует) входного столбца field_name, отсортированного по убыванию по столбцу ordering_field_name. Если ordering_field_name не указан, для сортировки используется только столбец field_name. Например:

a	b
10	1
20	1
1	2
2	2
100	null
200	null
null	null

SELECT LAST(a) FROM t

результатом будет:

LAST(a)

200

SELECT LAST(a, b) FROM t

результатом будет:

LAST(a, b)

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 является псевдонимом и может использоваться вместо LAST, например:

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 не может использоваться в предложении HAVING.

LAST не может использоваться со столбцами типа text, если поле не является также saved as a keyword.

`MAX`

Краткое описание:

MAX(field_name)

Входные данные:

числовое поле. Если это поле содержит только значения null, функция возвращает null. В противном случае функция игнорирует значения null в этом поле.

Выходные данные: тот же тип, что и входные данные

Описание: Возвращает максимальное значение среди входных значений в поле 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 в поле типа text или keyword преобразуется в LAST/LAST_VALUE, и поэтому его нельзя использовать в предложении HAVING.

`MIN`

Краткое описание:

MIN(field_name)

Входные данные:

Выходные данные: тот же тип, что и входные данные

Описание: Возвращает минимальное значение среди входных значений в поле field_name.

SELECT MIN(salary) AS min FROM emp;

      min
---------------
25324

MIN в поле типа text или keyword преобразуется в FIRST/FIRST_VALUE, и поэтому его нельзя использовать в предложении HAVING.

`SUM`

Краткое описание:

SUM(field_name)

Входные данные:

Выходные данные: bigint для целочисленных входных данных, double для чисел с плавающей запятой

Описание: Возвращает сумму входных значений в поле 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

Статистика

`KURTOSIS`

Краткое описание:

KURTOSIS(field_name)

Входные данные:

Выходные данные: числовое значение double

Описание:

Количественно определяет форму распределения входных значений в поле 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 не может использоваться поверх скалярных функций или операторов, а только напрямую в поле. Таким образом, например, следующее недопустимо и возвращается ошибка:

 SELECT KURTOSIS(salary / 12.0), gender FROM emp GROUP BY gender

`MAD`

Краткое описание:

MAD(field_name)

Входные данные:

Выходные данные: числовое значение double

Описание:

Измеряет изменчивость входных значений в поле 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`

Краткое описание:

PERCENTILE(
    field_name,         
    percentile[,        
    method[,            
    method_parameter]])

Входные данные:

	числовое поле. Если это поле содержит только значения `null`, функция возвращает `null`. В противном случае функция игнорирует значения `null` в этом поле.
	числовое выражение (должно быть константой и не основываться на поле). Если `null`, функция возвращает `null`.
	необязательный строковый литерал для алгоритма перцентилей. Возможные значения: `tdigest` или `hdr`. По умолчанию используется `tdigest`.
	необязательный числовой литерал, который настраивает алгоритм перцентилей. Настраивает `compression` для `tdigest` или `number_of_significant_value_digits` для `hdr`. Значение по умолчанию такое же, как у базового алгоритма.

Выходные данные: числовое значение double

Описание:

Возвращает n-й перцентиль (представленный параметром numeric_exp) входных значений в поле field_name.

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

SELECT languages, PERCENTILE(salary / 12.0, 95) AS "95th" FROM emp
       GROUP BY languages;

   languages   |       95th
---------------+------------------
null           |6249.916666666667
1              |6065.875
2              |5993.725
3              |6136.520833333332
4              |6009.633333333332
5              |5089.3083333333325

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           |74999.0        |74992.0
1              |73717.0        |73712.0
2              |73530.238      |69936.0
3              |74970.0        |74992.0
4              |74572.0        |74608.0
5              |66117.118      |56368.0

`PERCENTILE_RANK`

Краткое описание:

PERCENTILE_RANK(
    field_name,         
    value[,             
    method[,            
    method_parameter]])

Входные данные:

	числовое поле. Если это поле содержит только значения `null`, функция возвращает `null`. В противном случае функция игнорирует значения `null` в этом поле.
	числовое выражение (должно быть константой и не основываться на поле). Если `null`, функция возвращает `null`.
	необязательный строковый литерал для алгоритма перцентилей. Возможные значения: `tdigest` или `hdr`. По умолчанию используется `tdigest`.
	необязательный числовой литерал, который настраивает алгоритм перцентилей. Настраивает `compression` для `tdigest` или `number_of_significant_value_digits` для `hdr`. Значение по умолчанию такое же, как у базового алгоритма.

Выходные данные: числовое значение double

Описание:

Возвращает n-й ранг перцентиля (представленный параметром numeric_exp) входных значений в поле 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              |100.0

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              |100.0          |95.24

`SKEWNESS`

Краткое описание:

SKEWNESS(field_name)

Входные данные:

Выходные данные: числовое значение double

Описание:

Количественно оценивает асимметричное распределение входных значений в поле 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 не может использоваться поверх скалярных функций, а только непосредственно на поле. Например, следующее недопустимо и возвращает ошибку:

 SELECT SKEWNESS(ROUND(salary / 12.0, 2), gender FROM emp GROUP BY gender

`STDDEV_POP`

Краткое описание:

STDDEV_POP(field_name)

Входные данные:

Выходные данные: числовое значение double

Описание:

Возвращает среднее квадратическое отклонение генеральной совокупности входных значений в поле 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`

Краткое описание:

STDDEV_SAMP(field_name)

Входные данные:

Выходные данные: числовое значение double

Описание:

Возвращает выборочное среднее квадратическое отклонение входных значений в поле 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`

Краткое описание:

SUM_OF_SQUARES(field_name)

Входные данные:

Выходные данные: числовое значение double

Описание:

Возвращает сумму квадратов входных значений в поле 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`

Краткое описание:

VAR_POP(field_name)

Входные данные:

Выходные данные: числовое значение double

Описание:

Возвращает дисперсию генеральной совокупности входных значений в поле 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`

Синопсис:

VAR_SAMP(field_name)

Входные данные:

Выходные данные: double числовое значение

Описание:

Возвращает выборочную дисперсию входных значений в поле 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

© 2023-2025 Elasticsearch
As of September 2024, Elasticsearch is available under a choice of three licenses: the Server Side Public License (SSPL), the Elastic License, or the AGPLv3 (OSI approved).
Elasticsearch and the Elasticsearch logo are trademarks of Elasticsearch B.V., registered in the U.S. and in other countries.
https://www.elastic.co/guide/en/elasticsearch/reference/7.17/sql-functions-aggs.html