› › ›

分组函数

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

`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。如果指定的间隔小于 1 天，例如：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)。

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