› › ›

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

Функции для вычисления единственного результата из набора входных значений. 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 значения в этом поле.

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

Описание: Возвращает общее количество не-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 значения в этом поле.

Описание: Возвращает общее количество уникальных не-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])

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

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

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

Описание: Возвращает первое не-null значение (если оно существует) входного столбца 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. Возвращает последнее не-null значение (если оно существует) столбца 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)

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

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

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

Описание:

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

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

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

Описание:

Возвращает nth ранг перцентиля (представленный параметром 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              |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`

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

SKEWNESS(field_name)

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

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

Выходные данные: числовое значение 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/8.17/sql-functions-aggs.html