› › ›

分组函数

分组函数编辑

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

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

为日期/时间直方图指定的所有间隔将在其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)当前不受支持。

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

分组函数

分组函数编辑

HISTOGRAM编辑

`HISTOGRAM`编辑