› › ›

Функции группировки

Функции для создания специальных группировок (также известных как бинерование); поэтому их необходимо использовать в рамках группировки GROUP BY.

`HISTOGRAM`

Синопсис:

HISTOGRAM(
    numeric_exp,        
    numeric_interval)   

HISTOGRAM(
    date_exp,           
    date_time_interval)

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

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

Выходные данные: непустые бины или группы данного выражения, разделенные в соответствии с заданным интервалом

Описание: функция гистограммы берет все соответствующие значения и делит их на бины с фиксированным размером, соответствующим заданному интервалу, используя (приблизительно) следующую формулу:

bucket_key = Math.floor(value / interval) * interval

Гистограмма в SQL НЕ возвращает пустые бины для отсутствующих интервалов, как традиционная гистограмма и гистограмма дат. Такое поведение не соответствует концепции SQL, которая рассматривает все пропущенные значения как null; поэтому гистограмма помещает все пропущенные значения в группу null.

Histogram может применяться к числовым полям:

SELECT HISTOGRAM(salary, 5000) AS h FROM emp GROUP BY h;

       h
---------------
25000
30000
35000
40000
45000
50000
55000
60000
65000
70000

или к полям даты/времени:

SELECT HISTOGRAM(birth_date, INTERVAL 1 YEAR) AS h, COUNT(*) AS c FROM emp GROUP BY h;


           h            |       c
------------------------+---------------
null                    |10
1952-01-01T00:00:00.000Z|8
1953-01-01T00:00:00.000Z|11
1954-01-01T00:00:00.000Z|8
1955-01-01T00:00:00.000Z|4
1956-01-01T00:00:00.000Z|5
1957-01-01T00:00:00.000Z|4
1958-01-01T00:00:00.000Z|7
1959-01-01T00:00:00.000Z|9
1960-01-01T00:00:00.000Z|8
1961-01-01T00:00:00.000Z|8
1962-01-01T00:00:00.000Z|6
1963-01-01T00:00:00.000Z|7
1964-01-01T00:00:00.000Z|4
1965-01-01T00:00:00.000Z|1

Также поддерживаются выражения внутри гистограммы, при условии, что тип возвращаемого значения является числовым:

SELECT HISTOGRAM(salary % 100, 10) AS h, COUNT(*) AS c FROM emp GROUP BY h;

       h       |       c
---------------+---------------
0              |10
10             |15
20             |10
30             |14
40             |9
50             |9
60             |8
70             |13
80             |3
90             |9

Обратите внимание, что гистограммы (и функции группировки в целом) допускают пользовательские выражения, но не могут иметь какие-либо функции, примененные к ним в GROUP BY. Другими словами, следующее утверждение НЕ разрешено:

SELECT MONTH(HISTOGRAM(birth_date), 2)) AS h, COUNT(*) as c FROM emp GROUP BY h ORDER BY h DESC;

так как оно требует двух группировок (одной для гистограммы и второй для применения функции поверх групп гистограммы).

Вместо этого можно переписать запрос, чтобы переместить выражение в гистограмму внутри её:

SELECT HISTOGRAM(MONTH(birth_date), 2) AS h, COUNT(*) as c FROM emp GROUP BY h ORDER BY h DESC;

       h       |       c
---------------+---------------
12             |7
10             |17
8              |16
6              |16
4              |18
2              |10
0              |6
null           |10

Когда гистограмма в SQL применяется к типу DATE вместо DATETIME, указанный интервал усекается до кратного дня. Например: для HISTOGRAM(CAST(birth_date AS DATE), INTERVAL '2 3:04' DAY TO MINUTE) фактически используемый интервал будет INTERVAL '2' DAY. Если указанный интервал меньше 1 дня, например: HISTOGRAM(CAST(birth_date AS DATE), INTERVAL '20' HOUR), то используемый интервал будет INTERVAL '1' DAY.

Все интервалы, указанные для гистограммы даты/времени, будут использовать фиксированный интервал в их date_histogram определении агрегации, за исключением INTERVAL '1' YEAR, INTERVAL '1' MONTH и INTERVAL '1' DAY, где используется календарный интервал. Выбор календарного интервала был сделан для получения более интуитивного результата для группировок по ГОДУ, МЕСЯЦУ и ДНЮ. Например, в случае ГОДА календарные интервалы рассматривают один-годичный бин как тот, который начинается с 1 января того года, тогда как фиксированный интервал одного года рассматривает один год как количество миллисекунд (например, 31536000000ms, соответствующий 365 дням, 24 часам в день, 60 минутам в час и т. д.). При использовании фиксированных интервалов день 5 февраля 2019 года, например, попадает в бин, который начинается 20 декабря 2018 года, и Elasticsearch (и неявно Elasticsearch SQL) вернул бы год 2018 для даты, которая фактически находится в 2019 году. С календарным интервалом это поведение более интуитивно, так как день 5 февраля 2019 года фактически принадлежит бину года 2019.

Гистограмма в SQL не может применяться к типу TIME. Например: HISTOGRAM(CAST(birth_date AS TIME), INTERVAL '10' MINUTES) в настоящее время не поддерживается.

© 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-grouping.html