« 聚合函数 日期/时间和区间函数与运算符 »
Elastic 文档 ›Elasticsearch 指南 [8.16] ›SQL ›函数和运算符

分组函数

编辑

分组函数

编辑

用于创建特殊分组（也称为分桶）的函数；因此，这些函数需要用作分组的一部分。

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，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。如果指定的区间小于一天，例如：HISTOGRAM(CAST(birth_date AS DATE), INTERVAL '20' HOUR)，则使用的区间将为INTERVAL '1' DAY。

为日期/时间 HISTOGRAM 指定的所有区间在其date_histogram聚合定义中将使用固定区间，但INTERVAL '1' YEAR、INTERVAL '1' MONTH和INTERVAL '1' DAY例外，这些情况下使用日历区间。选择日历区间是为了使 YEAR、MONTH 和 DAY 分组的结果更直观。例如，在 YEAR 的情况下，日历区间将一年区间视为从当年 1 月 1 日开始的区间，而固定区间一年区间则将一年视为毫秒数（例如，31536000000ms对应于 365 天，每天 24 小时，每小时 60 分钟等）。对于固定区间，例如 2019 年 2 月 5 日属于从 2018 年 12 月 20 日开始的区间，Elasticsearch（以及隐含的 Elasticsearch SQL）将返回实际上在 2019 年的年份 2018。使用日历区间，此行为更直观，2019 年 2 月 5 日实际上属于 2019 年区间。

SQL 中的直方图不能应用于TIME类型。例如：目前不支持HISTOGRAM(CAST(birth_date AS TIME), INTERVAL '10' MINUTES)。

« 聚合函数 日期/时间和区间函数与运算符 »