聚合函数
聚合函数对由 GROUP BY 子句定义的子集进行操作。如果没有 GROUP BY 子句，聚合函数对结果集中的所有元素进行操作。您可以在 GROUP BY、SELECT 和 HAVING 子句中使用聚合函数。
UDB-SX 支持以下聚合函数。
| 函数 | 描述 |
|---|---|
AVG |
返回结果的平均值。 |
COUNT |
返回结果的数量。 |
SUM |
返回结果的总和。 |
MIN |
返回结果的最小值。 |
MAX |
返回结果的最大值。 |
VAR_POP 或 VARIANCE |
返回剔除空值后结果的总体方差。当只有一行结果时返回 0。 |
VAR_SAMP |
返回剔除空值后结果的样本方差。当只有一行结果时返回 null。 |
STD 或 STDDEV |
返回结果的样本标准差。当只有一行结果时返回 0。 |
STDDEV_POP |
返回结果的总体标准差。当只有一行结果时返回 0。 |
STDDEV_SAMP |
返回结果的样本标准差。当只有一行结果时返回 null。 |
以下示例引用了一个 employees 表。您可以使用批量索引操作将以下文档索引到 UDB-SX 中来尝试这些示例：
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;
| department | sum(sales) |
|---|---|
| 1 | 58700 |
| 2 | 37018 |
在 GROUP BY 中使用序号
您可以在 GROUP BY 子句中指定要聚合的列号。列号由列在 SELECT 子句中的位置决定。例如，以下查询与上面的查询等效。它返回每个部门的部门编号和总销售额。它按结果集的第一个列（即 department）对结果进行分组：
SELECT department, sum(sales)
FROM employees
GROUP BY 1;
| department | sum(sales) |
|---|---|
| 1 | 58700 |
| 2 | 37018 |
在 GROUP BY 中使用表达式
您可以在 GROUP BY 子句中使用表达式。例如，以下查询返回每年的平均销售额：
SELECT year(sale_date), avg(sales)
FROM employees
GROUP BY year(sale_date);
| year(start_date) | avg(sales) |
|---|---|
| 2020 | 1356.0 |
| 2021 | 22455.0 |
| 2022 | 16484.0 |
SELECT
您可以在 SELECT 子句中直接使用聚合表达式，或将其作为更大表达式的一部分使用。此外，您可以将表达式用作聚合函数的参数。
直接在 SELECT 中使用聚合表达式
以下查询返回每个部门的平均销售额：
SELECT department, avg(sales)
FROM employees
GROUP BY department;
| department | avg(sales) |
|---|---|
| 1 | 14675.0 |
| 2 | 18509.0 |
在 SELECT 中将聚合表达式作为更大表达式的一部分使用
以下查询计算每个部门员工的平均佣金，为平均销售额的 5%：
SELECT department, avg(sales) * 0.05 as avg_commission
FROM employees
GROUP BY department;
| department | avg_commission |
|---|---|
| 1 | 733.75 |
| 2 | 925.45 |
使用表达式作为聚合函数的参数
以下查询计算每个部门的平均佣金金额。首先，它计算每个 sales 值的佣金金额，即 sales 的 5%。然后确定所有佣金值的平均值：
SELECT department, avg(sales * 0.05) as avg_commission
FROM employees
GROUP BY department;
| department | avg_commission |
|---|---|
| 1 | 733.75 |
| 2 | 925.45 |
COUNT
COUNT 函数接受参数，例如 *，或字面量，例如 1。
下表描述了 COUNT 函数各种形式的操作方式。
| 函数类型 | 描述
|COUNT(*) | 统计表中的总行数。
|COUNT(1)（与 COUNT(*) 相同） | 统计任何非空字面量。
|COUNT(field) | 统计给定字段（或表达式）的值不为空的行数。
例如，以下查询返回每年的销售次数：
SELECT year(sale_date), count(sales)
FROM employees
GROUP BY year(sale_date);
| year(sale_date) | count(sales) |
|---|---|
| 2020 | 1 |
| 2021 | 2 |
| 2022 | 3 |
HAVING
WHERE 和 HAVING 都用于过滤结果。WHERE 过滤器在 GROUP BY 阶段之前应用，因此不能在 WHERE 子句中使用聚合函数。但是，您可以使用 WHERE 子句来限制随后应用聚合的行。
HAVING 过滤器在 GROUP BY 阶段之后应用，因此您可以使用 HAVING 子句来限制结果中包含的组。
HAVING 与 GROUP BY 结合使用
您可以在 HAVING 条件中使用 SELECT 子句中定义的聚合表达式或其别名。
以下查询在 HAVING 子句中使用聚合表达式。它返回每个销售次数超过一次的员工的销售次数：
SELECT employee_id, count(sales)
FROM employees
GROUP BY employee_id
HAVING count(sales) > 1;
| employee_id | count(sales) |
|---|---|
| 1 | 2 |
| 6 | 2 |
HAVING 子句中的聚合不必与 SELECT 列表中的聚合相同。以下查询在 HAVING 子句中使用 count 函数，但在 SELECT 子句中使用 sum 函数。它返回每个销售次数超过一次的员工的总销售额：
SELECT employee_id, sum(sales)
FROM employees
GROUP BY employee_id
HAVING count(sales) > 1;
| employee_id | sum (sales) |
|---|---|
| 1 | 40580 |
| 6 | 18120 |
作为 SQL 标准的扩展，您不仅限于在 GROUP BY 子句中使用标识符。以下查询在 GROUP BY 子句中使用别名，并且与上一个查询等效：
SELECT employee_id as id, sum(sales)
FROM employees
GROUP BY id
HAVING count(sales) > 1;
| id | sum (sales) |
|---|---|
| 1 | 40580 |
| 6 | 18120 |
您还可以在 HAVING 子句中使用聚合表达式的别名。以下查询返回每个销售额超过 40,000 美元的部门的总销售额：
SELECT department, sum(sales) as total
FROM employees
GROUP BY department
HAVING total > 40000;
| department | total |
|---|---|
| 1 | 58700 |
如果一个标识符有歧义（例如，同时作为 SELECT 别名和索引字段存在），则优先使用别名。在以下查询中，该标识符被替换为 SELECT 子句中别名的表达式：
SELECT department, sum(sales) as sales
FROM employees
GROUP BY department
HAVING sales > 40000;
| department | sales |
|---|---|
| 1 | 58700 |
HAVING 不配合 GROUP BY 使用
您可以在没有 GROUP BY 子句的情况下使用 HAVING 子句。在这种情况下，整个数据集被视为一个组。如果 department 列中有多个值，以下查询将返回 True：
SELECT 'True' as more_than_one_department FROM employees HAVING min(department) < max(department);
| more_than_one_department |
|---|
| True |
如果员工表中的所有员工都属于同一个部门，结果将包含零行：
| more_than_one_department |
|---|