聚合函数

聚合函数对 GROUP BY 子句定义的子集进行操作。如果没有 GROUP BY 子句，聚合函数将对结果集的所有元素进行操作。您可以在 GROUP BY、SELECT 和 HAVING 子句中使用聚合函数。

OpenSearch 支持以下聚合函数。

以下示例引用了一个 employees 表。您可以通过使用批量索引操作将以下文档索引到 OpenSearch 中来尝试这些示例

PUT employees/_bulk?refresh
{"index":{"_id":"1"}}
{"employee_id": 1, "department":1, "firstname":"Amber", "lastname":"Duke", "sales":1356, "sale_date":"2020-01-23"}
{"index":{"_id":"2"}}
{"employee_id": 1, "department":1, "firstname":"Amber", "lastname":"Duke", "sales":39224, "sale_date":"2021-01-06"}
{"index":{"_id":"6"}}
{"employee_id":6, "department":1, "firstname":"Hattie", "lastname":"Bond", "sales":5686, "sale_date":"2021-06-07"}
{"index":{"_id":"7"}}
{"employee_id":6, "department":1, "firstname":"Hattie", "lastname":"Bond", "sales":12432, "sale_date":"2022-05-18"}
{"index":{"_id":"13"}}
{"employee_id":13,"department":2, "firstname":"Nanette", "lastname":"Bates", "sales":32838, "sale_date":"2022-04-11"}
{"index":{"_id":"18"}}
{"employee_id":18,"department":2, "firstname":"Dale", "lastname":"Adams", "sales":4180, "sale_date":"2022-11-05"}

GROUP BY

GROUP BY 子句定义了结果集的子集。聚合函数对这些子集进行操作，并为每个子集返回一行结果。

您可以在 GROUP BY 子句中使用标识符、序号或表达式。

在 GROUP BY 中使用标识符

您可以在 GROUP BY 子句中指定要聚合的字段名（列名）。例如，以下查询返回每个部门的部门编号和总销售额

SELECT department, sum(sales) 
FROM employees 
GROUP BY department;

在 GROUP BY 中使用序号

您可以在 GROUP BY 子句中指定要聚合的列号。列号由 SELECT 子句中的列位置决定。例如，以下查询与上面的查询等效。它返回每个部门的部门编号和总销售额。它按结果集的第一列（即 department）对结果进行分组

SELECT department, sum(sales) 
FROM employees 
GROUP BY 1;

在 GROUP BY 中使用表达式

您可以在 GROUP BY 子句中使用表达式。例如，以下查询返回每年的平均销售额

SELECT year(sale_date), avg(sales) 
FROM employees 
GROUP BY year(sale_date);

SELECT

您可以在 SELECT 子句中直接使用聚合表达式，或作为更大表达式的一部分使用。此外，您可以使用表达式作为聚合函数的参数。

直接在 SELECT 中使用聚合表达式

以下查询返回每个部门的平均销售额

SELECT department, avg(sales) 
FROM employees 
GROUP BY department;

在 SELECT 中将聚合表达式用作更大表达式的一部分

以下查询计算每个部门员工的平均佣金，为平均销售额的 5%

SELECT department, avg(sales) * 0.05 as avg_commission 
FROM employees 
GROUP BY department;

使用表达式作为聚合函数的参数

以下查询计算每个部门的平均佣金金额。它首先计算每个 sales 值的佣金金额，即 sales 的 5%。然后确定所有佣金值的平均值

SELECT department, avg(sales * 0.05) as avg_commission 
FROM employees 
GROUP BY department;

COUNT

COUNT 函数接受参数，例如 *，或字面量，例如 1。下表描述了 COUNT 函数的各种形式如何操作。

例如，以下查询返回每年的销售额计数

SELECT year(sale_date), count(sales) 
FROM employees 
GROUP BY year(sale_date);

HAVING

WHERE 和 HAVING 都用于过滤结果。WHERE 过滤器在 GROUP BY 阶段之前应用，因此您不能在 WHERE 子句中使用聚合函数。但是，您可以使用 WHERE 子句来限制应用聚合的行。

HAVING 过滤器在 GROUP BY 阶段之后应用，因此您可以使用 HAVING 子句来限制包含在结果中的组。

带 GROUP BY 的 HAVING

您可以在 HAVING 条件中使用聚合表达式或其在 SELECT 子句中定义的别名。

以下查询在 HAVING 子句中使用聚合表达式。它返回每个销售额超过一次的员工的销售额数量

SELECT employee_id, count(sales)
FROM employees
GROUP BY employee_id
HAVING count(sales) > 1;

HAVING 子句中的聚合不必与 SELECT 列表中的聚合相同。以下查询在 HAVING 子句中使用 count 函数，但在 SELECT 子句中使用 sum 函数。它返回每个销售额超过一次的员工的总销售额

SELECT employee_id, sum(sales)
FROM employees
GROUP BY employee_id
HAVING count(sales) > 1;

作为 SQL 标准的扩展，您不限于在 GROUP BY 子句中仅使用标识符。以下查询在 GROUP BY 子句中使用别名，并且与上一个查询等效

SELECT employee_id as id, sum(sales)
FROM employees
GROUP BY id
HAVING count(sales) > 1;

您也可以在 HAVING 子句中使用聚合表达式的别名。以下查询返回销售额超过 40,000 美元的每个部门的总销售额

SELECT department, sum(sales) as total
FROM employees
GROUP BY department
HAVING total > 40000;

如果标识符不明确（例如，同时作为 SELECT 别名和索引字段存在），则优先选择别名。在以下查询中，标识符被替换为在 SELECT 子句中指定别名的表达式

SELECT department, sum(sales) as sales
FROM employees
GROUP BY department
HAVING sales > 40000;

不带 GROUP BY 的 HAVING

您可以在没有 GROUP BY 子句的情况下使用 HAVING 子句。在这种情况下，整个数据集被视为一个组。以下查询将在 department 列中存在多个值时返回 True

SELECT 'True' as more_than_one_department FROM employees HAVING min(department) < max(department);

如果员工表中的所有员工都属于同一部门，则结果将包含零行