---
url: /v6.2/calculation-function-aggregate.md
---
# 聚合函数

聚合函数是指对一组数值进行统计计算，返回一个单一的数值结果。本文先准备一张表，接下来的函数介绍例子就使用这张表给计算结果。

| f1 | f2 | f3 |
|----------|----------|----------|
| 10       | 20       | 30       |
| 20       | 30       | 40       |
| 30       | null     | 50       |
| 40       | 50       | 60       |
| 50       | 60       | 70       |
| 60       | 70       | null     |

## avg

| 语法         | avg(N)                    |
|--------------|---------------------------|
| 参数说明     | 数字类型参数，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 获取指定数值列的平均值 |
| 例子         | avg({f1})， 返回值 35 |

## corr

| 语法         | corr(ARG1, ARG2)          |
|--------------|---------------------------|
| 参数说明     | 1. ARG1，可选类型：数字；2. ARG2，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 返回两个数字列的相关系数 |
| 例子         | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)], corr({arg1}, {arg2})， 返回 1 |

## count

| 语法         | count(ARG)                |
|--------------|---------------------------|
| 参数说明     | 参与计算的参数，可选类型：任意类型 |
| 返回值类型   | 数字                   |
| 描述         | 获取指定列的值的数目 |
| 例子         | count({f2})，返回：5（不统计null值） |

## covar_pop

| 语法         | covar_pop(ARG1, ARG2)     |
|--------------|---------------------------|
| 参数说明     | 1. ARG1，可选类型：数字；2. ARG2，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 返回两个数字列的总体协方差 |
| 例子         | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)], covar_pop({arg1}, {arg2})，返回 138058.47222222222  |

## covar_samp

| 语法         | covar_samp(ARG1, ARG2)    |
|--------------|---------------------------|
| 参数说明     | 1. ARG1，可选类型：数字；2. ARG2，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 返回两个数字列的样本协方差 |
| 例子         | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)], covar_samp(arg1, arg2)， 返回 165670.16666666666 |

## distinct_count

| 语法         | distinct_count(ARG)       |
|--------------|---------------------------|
| 参数说明     | 参与计算的参数，可选类型：任意类型 |
| 返回值类型   | 数字                    |
| 描述         | 获取指定列中非重复结果的数目。Impala 数据源中该函数使用近似去重统计，适合大数据量下优先考虑性能的场景；如果需要精确去重统计，请使用 `exact_distinct_count`。 |
| 例子         | distinct_count({f2})，返回：5 |
| 不支持的数据源 | 无                        |

## exact_distinct_count

| 语法         | exact_distinct_count(ARG) |
|--------------|---------------------------|
| 参数说明     | 参与计算的参数，可选类型：任意类型 |
| 返回值类型   | 数字                    |
| 描述         | 精确统计指定列中非重复结果的数目。当前用于 Impala 数据源中需要精确去重计数的场景。 |
| 例子         | exact_distinct_count({f2})，返回：5 |
| 适用数据源   | Impala                    |
| 注意         | 该函数是聚合函数，使用位置与 `distinct_count` 一致，不能作为普通字段公式或子查询字段使用。 |

## first

| 语法         | first(ARG)                |
|--------------|---------------------------|
| 参数说明     | 参与计算的参数，可选类型：ANY |
| 返回值类型   | ANY                       |
| 描述         | 获取指定列中第一个记录的值，它的值不是固定的 |
| 例子         | first({f1})，返回：10 |

## last

| 语法         | last(ARG)                 |
|--------------|---------------------------|
| 参数说明     | 参与计算的参数，可选类型：任意类型 |
| 返回值类型   | 任意类型                    |
| 描述         | 获取指定列中最后一个记录的值，它的值不是固定的 |
| 例子         | last({f1})，返回：60 |

## list_collect

| 语法         | list_collect(ARG)         |
|--------------|---------------------------|
| 参数说明     | 参与计算的参数，可选类型：任意 |
| 返回值类型   | 数组                     |
| 描述         | 聚合函数，把 arg 的参数收集为数组。（返回格式为数组类型，如果您发现显示为null，可以尝试将其转为字符串类型查看。） |
| 例子         | list_collect({f1})，返回：包含 f1 字段所有值的数组 |

## list_collect_flatten

| 语法         | list_collect_flatten(ARR) |
|--------------|---------------------------|
| 参数说明     | 数组类型参数，可选类型：数组 |
| 返回值类型   | 数组                     |
| 描述         | 聚合函数，把 arr 的参数收集打平为数组。（返回格式为数组类型，如果您发现显示为null，可以尝试将其转为字符串类型查看。） |
| 例子         | list_collect_flatten({f1})，返回：把 f1 数组的所有元素拿出来，拼成一个新的数组。 |

## max

| 语法         | max(ARG)                  |
|--------------|---------------------------|
| 参数说明     | 参与计算的参数，可选类型：任意类型 |
| 返回值类型   | 任意类型                    |
| 描述         | 获取指定数值列的最大值 |
| 例子         | max({f1})，返回：60 |
| 不支持的数据源 | 无                        |

## max_by

| 语法         | max_by(ARG, COMPARE)      |
|--------------|---------------------------|
| 参数说明     | 1. 参与计算的参数，可选类型：任意类型；2. 对比数值，可选类型：任意类型 |
| 返回值类型   | 任意类型                    |
| 描述         | 获取 compare 列最大那行对应的 arg 列的值，如有多个最大值取随机一个 |
| 例子         | max_by({f1},if(isnull({f2}),-1,{f2}))，返回：60 |

## median

| 语法         | median(ARG)               |
|--------------|---------------------------|
| 参数说明     | 数值类型参数，可选类型：数字 |
| 返回值类型   | 数字                  |
| 描述         | 获取指定数值列的中位数 |
| 例子         | median({f1})，返回：35 |

## min

| 语法         | min(ARG)                  |
|--------------|---------------------------|
| 参数说明     | 参与计算的参数，可选类型：任意类型 |
| 返回值类型   | 任意类型                    |
| 描述         | 获取指定数值列的最小值 |
| 例子         | min({f1})，返回：10 |

## min_by

| 语法         | min_by(ARG, COMPARE)      |
|--------------|---------------------------|
| 参数说明     | 1. 参与计算的参数，可选类型：任意类型；2. 对比数值，可选类型：任意类型 |
| 返回值类型   | 任意类型                    |
| 描述         | 获取 compare 列最小那行对应的 arg 列的值，如有多个最小值取随机一个 |
| 例子         | min_by({f1},{f2})，返回：10|

## mode

| 语法         | mode() within group(ORDER BY expr2 \[ DESC ] ) |
|--------------|-----------------------------------------------|
| 参数说明     | expr2：用于排序的表达式，可选类型：ANY；DESC：可选参数，指定按降序排序，默认升序 |
| 返回值类型   | ANY                                           |
| 描述         | 返回出现频次最高的一个值，频次相同则返回第一个 |
| 例子         | 对于数据 \[1, 2, 2, 3, 3]，mode() within group(ORDER BY value) 返回：2（2和3频次相同，返回先出现的2） |

## nth

| 语法         | nth(ARG, I)               |
|--------------|---------------------------|
| 参数说明     | 1. 参与计算的参数，可选类型：任意类型；2. 整数类型参数，可选类型：数字 |
| 返回值类型   | 任意类型                      |
| 描述         | 获取第 i 个数值 |
| 例子         | nth({f1}, 2)，返回：30（索引从1开始时） |

## percentile

| 语法         | percentile(ARG, P)        |
|--------------|---------------------------|
| 参数说明     | 1. 数值类型参数，可选类型：数字；2. 小数，0-1之间，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 计算百分位数 |
| 例子         | percentile({f1}, 50)，返回：30） |

## regr_avgx

| 语法         | regr_avgx(ARG1, ARG2)     |
|--------------|---------------------------|
| 参数说明     | 1. ARG1，可选类型：数字；2. ARG2，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 返回自变量的平均值（sum(ARG2)/N） |
| 例子         | regr_avgx({f1},{f2})，返回： 46 |

## regr_avgy

| 语法         | regr_avgy(ARG1, ARG2)     |
|--------------|---------------------------|
| 参数说明     | 1. ARG1，可选类型：数字；2. ARG2，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 返回因变量的平均值（sum(ARG1)/N） |
| 例子         | regr_avgy({f1},{f2})，返回： 36 |

## regr_count

| 语法         | regr_count(ARG1, ARG2)    |
|--------------|---------------------------|
| 参数说明     | 1. ARG1，可选类型：数字；2. ARG2，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 两个表达式都不为空的输入行的数目 |
| 例子         | regr_count({f1},{f2})，返回： 5 |

## regr_intercept

| 语法         | regr_intercept(ARG1, ARG2) |
|--------------|---------------------------|
| 参数说明     | 1. ARG1，可选类型：数字；2. ARG2，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 由(arg2, arg1)对决定的最小二乘拟合的线性方程的 arg1 截距 |
| 例子         | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1)], regr_intercept(arg1, arg2)，返回 665.666666666666 |

## regr_r2

| 语法         | regr_r2(ARG1, ARG2)       |
|--------------|---------------------------|
| 参数说明     | 1. ARG1，可选类型：数字；2. ARG2，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 相关系数的平方 |
| 例子         | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_r2({arg1}, {arg2}) = 0.06323185011709602 |

## regr_slope

| 语法         | regr_slope(ARG1, ARG2)    |
|--------------|---------------------------|
| 参数说明     | 1. ARG1，可选类型：数字；2. ARG2，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 由(arg2, arg1)对决定的最小二乘拟合的线性方程的斜率 |
| 例子         | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_slope(arg1, arg2) = -0.42857142857142855 |

## regr_sxx

| 语法         | regr_sxx(ARG1, ARG2)      |
|--------------|---------------------------|
| 参数说明     | 1. ARG1，可选类型：数字；2. ARG2，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | sum(arg2^2) - sum(arg2)^2/N（自变量的“平方和”） |
| 例子         | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_sxx(arg1, arg2) = 17.5 |

## regr_sxy

| 语法         | regr_sxy(ARG1, ARG2)      |
|--------------|---------------------------|
| 参数说明     | 1. ARG1，可选类型：数字；2. ARG2，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | sum(arg2 \* arg1) - sum(arg2) \* sum(arg1)/N（自变量乘以因变量的“积之合”） |
| 例子         | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_sxy(arg1, arg2) = -7.5 |

## regr_syy

| 语法         | regr_syy(ARG1, ARG2)      |
|--------------|---------------------------|
| 参数说明     | 1. ARG1，可选类型：数字；2. ARG2，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | sum(arg1^2) - sum(arg1)^2/N（因变量的“平方和”） |
| 例子         | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_syy(arg1, arg2) = 50.833333333333336 |

## set_collect

| 语法         | set_collect(ARG)          |
|--------------|---------------------------|
| 参数说明     | 参与计算的参数，可选类型：任意类型|
| 返回值类型   | 数组                     |
| 描述         | 聚合函数，把 arg 的参数收集为去重数组。（返回格式为数组类型，如果您发现显示为null，可以尝试将其转为字符串类型查看。） |
| 例子         | set_collect(\[1, 2, 2, 3])，返回：\[1, 2, 3] |

## set_collect_flatten

| 语法         | set_collect_flatten(ARR)  |
|--------------|---------------------------|
| 参数说明     | 数组类型参数，可选类型：数组 |
| 返回值类型   | 数组                     |
| 描述         | 聚合函数，把 arr 的参数收集打平为去重数组。（返回格式为数组类型，如果您发现显示为null，可以尝试将其转为字符串类型查看。） |
| 例子         | set_collect_flatten(\[\[1, 2], \[2, 3]])，返回：\[1, 2, 3] |

## stddev_pop

| 语法         | stddev_pop(N)             |
|--------------|---------------------------|
| 参数说明     | 数字类型参数，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 返回数字列的总体标准偏差 |
| 例子         | n = \[(1000),(1001),(1003),(1004),(1005),(1000)], stddev_pop(n) = 1.9507833184532709 |

## stddev_samp

| 语法         | stddev_samp(N)            |
|--------------|---------------------------|
| 参数说明     | 数字类型参数，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 返回数字列的样本标准偏差 |
| 例子         | n = \[(1000),(1001),(1003),(1004),(1005),(1000)], stddev_samp(n) = 2.1369760566432809 |

## string_agg

| 语法         | string_agg(S, DELIMITER)  |
|--------------|---------------------------|
| 参数说明     | 1. S：需要拼接的字段，可选类型：任意类型；2. DELIMITER：分隔符，可选类型：字符串 |
| 返回值类型   | 字符串                    |
| 描述         | 把字段 S 拼接成字符串，中间用 DELIMITER 进行分割，其中 oracle 必须带有 within group 子句 |
| 例子         | string_agg(\['a', 'b', 'c'], ',')，返回：'a,b,c' |

## sum

| 语法         | sum(N)                    |
|--------------|---------------------------|
| 参数说明     | 数字类型参数，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 获取指定数值列的和 |
| 例子         | sum({f1})，返回：210 |

## var_pop

| 语法         | var_pop(N)                |
|--------------|---------------------------|
| 参数说明     | 数字类型参数，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 返回数字列的总体方差 |
| 例子         | n = \[(1000),(1001),(1003),(1004),(1005),(1000)], var_pop(n) = 3.8055555555555554（注：原示例描述有误，实际为总体方差） |

## var_samp

| 语法         | var_samp(N)               |
|--------------|---------------------------|
| 参数说明     | 数字类型参数，可选类型：数字 |
| 返回值类型   | 数字                    |
| 描述         | 返回数字列的样本方差（样本标准偏差的平方） |
| 例子         | n = \[(1000),(1001),(1003),(1004),(1005),(1000)], var_samp(n) = 4.5666666666666667 |

## 添加过滤条件

支持在聚合计算表达式后面使用 filter 子句，实现过滤的效果。语法如下，其中 filter_clause 是过滤条件

```
aggregate_name(expression ...) FILTER (WHERE filter_clause )
```

* 示例1:

```
sum({f1}) FILTER (WHERE {f1} > 10)
```

表示对 f1 字段大于 10 的数据进行求和。

* 示例2:

```
avg({f1}) FILTER (WHERE {f1} > 10 AND {f2} = 'abc')
```

表示对 f1 字段大于 10 且 f2 字段等于 'abc' 的数据进行求和。

## 添加排序语句

添加排序语句可以对聚合结果进行排序。支持排序语句的函数有：first, last, mode, percentile, string_agg。 语法如下，其中 order_clause 是排序条件。

```
aggregate_name(expression ...) within group(ORDER BY order_clause)
```

* 示例1:

```
first({f1}) within group(ORDER BY {f2})
```

表示对 f1 字段按照 f2 字段进行排序，取每组第一个 f1 字段的值。

* 示例2:

```
last({f1}) within group(ORDER BY {f2})
```

表示对 f1 字段按照 f2 字段进行排序，取每组最后一个 f1 字段的值。

* 示例3:

```
percentile({f1}, 0.5) within group(ORDER BY {f2})
```

表示对 f1 字段按照 f2 字段进行排序，取每组排在 50% 位置的 f1 字段的值。