ClickHouse函数 6.聚合函数组合

1. 聚合函数组合器

聚合函数的名称可以附加一个后缀。 这改变了聚合函数的工作方式。

• If

• Array

• ForEach

• OrDefault

• OrNull

• Resample

• State

• Merge

• MergeState

2. 创建测试表

-- 创建雇员表
CREATE TABLE emp(
    `empno` UInt16 NOT NULL COMMENT '员工编码',
    `ename` String COMMENT '员工姓名',
    `job` String COMMENT '职位',
    `mgr_no` UInt16 COMMENT '领导的员工编号',
    `hiredate` Date COMMENT '入职日期',
    `sal` decimal(7, 2) COMMENT '月薪',
    `comm` decimal(7, 2) COMMENT '奖金',
    `deptno` UInt8 COMMENT '所属部门编号'
)
ENGINE = MergeTree()
order by empno;

-- 向雇员表写入数据
insert into emp (empno, ename, job, mgr_no, hiredate, sal, comm, deptno) values 
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20),
(7499, 'ALLEN', 'SALESMAN', 7698,'1981-02-20', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20),
(7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30),
(7902, 'FORD', 'ANALYST', 7566,'1981-12-02', 3000, null, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

-- 创建部门表
create table dept(
    `deptno` UInt8 not null COMMENT '部门编码',
    `dname` String COMMENT '部门名称',
    `loc` String COMMENT '部门所在地点'
)
ENGINE = MergeTree()
order by deptno;

-- 向部门表写入数据
insert into default.dept (deptno, dname, loc) values 
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');

3. If

• -If 可以加到任何聚合函数之后。

• 加了-If之后聚合函数需要接受一个额外的参数，一个条件（Uint8类型），如果条件满足，那聚合函数处理当前的行数据，

• 如果不满足，那返回默认值（通常是0或者空字符串）。

• 使用条件聚合函数，您可以一次计算多个条件的聚合，而无需使用子查询和 JOIN例如，在Yandex.Metrica，条件聚合函数用于实现段比较功能。

• sumIf(column, cond)

-- 
-- 将编号为 10 的 部分工资求和
select sumIf(sal,deptno=10) from emp;
┌─sumIf(sal, equals(deptno, 10))─┐
│                           8750 │
└────────────────────────────────┘
-- 同 
select sum(sal) from emp where deptno=10;
┌─sum(sal)─┐
│     8750 │
└──────────┘
````



- countIf(cond)

```sql
-- 统计工资大于两千多人数
select countIf(sal,sal>2000) from emp;

┌─countIf(sal, greater(sal, 2000))─┐
│                                6 │
└──────────────────────────────────┘

-- 同
select count(1) from emp where sal > 2000;
┌─count()─┐
│       6 │
└─────────┘

• avgIf(x, cond)

select avgIf(sal,sal>2000) from emp;

┌─avgIf(sal, greater(sal, 2000))─┐
│                         3212.5 │
└────────────────────────────────┘

4. Array

• -Array后缀可以附加到任何聚合函数。
• 在这种情况下，聚合函数采用的参数 Array(T) 类型（数组）而不是 T 类型参数。
• 如果聚合函数接受多个参数，则它必须是长度相等的数组。
• 在处理数组时，聚合函数的工作方式与所有数组元素的原始聚合函数类似。
• 任何聚合函数都可以通过增加后缀 Array, 来使原来的参数类型 T 变为新的参数类型 Array(T)。

如果和 -If 组合，Array 必须先来，然后 If. 例： uniqArrayIf(arr, cond)， quantilesTimingArrayIf(level1, level2)(arr, cond)。由于这个顺序，该 cond 参数不会是数组。

示例1：

示例2：

• sumArray(arr) 对 Arrays 中的所有元素进行求和，即 sum(arraySum(arr))。

select sumArray(array(1,2,3,4,5));
┌─sumArray(array(1, 2, 3, 4, 5))─┐
│                             15 │
└────────────────────────────────┘

select sum(arraySum(array(1,2,3,4,5)));
┌─sum(arraySum(array(1, 2, 3, 4, 5)))─┐
│                                  15 │
└─────────────────────────────────────┘



````



- `uniqArray(arr)` – 计算``arr`中唯一元素的个数。这可以是一个更简单的方法：
-  `uniq(arrayJoin(arr))`，但它并不总是可以添加 `arrayJoin` 到查询。

```sql
select uniqArray(array(1,1,2,2,3,3));

┌─uniqArray(array(1, 1, 2, 2, 3, 3))─┐
│                                  3 │
└────────────────────────────────────┘

• -If 和 -Array 可以组合使用，但是 Array 必须在前，If 在后。

-- sumArrayIf
select sumArrayIf(array(number),number % 2 == 0) from (
  select number from numbers(5)
) t ;

┌─sumArrayIf(array(number), equals(modulo(number, 2), 0))─┐
│                                                       6 │
└─────────────────────────────────────────────────────────┘

5. -ForEach

将对 table 使用的聚合函数，转换为对数组的聚合函数。对数组的每一项进行处理，返回一个结果数组。

with (array(1,2,3) ,array(4,5,6)) as a
select sumForEach(a1) from (
  select a.1 as a1
  union all
  select a.2 as a1
) t ;
┌─sumForEach(a1)─┐
│ [5,7,9]        │
└────────────────┘

with (array(1,2,3) ,array(4,5,6,7)) as a
select countForEach(a1) from (
  select a.1 as a1
  union all
  select a.2 as a1
) t ;
┌─countForEach(a1)─┐
│ [2,2,2,1]        │
└──────────────────┘

6. -OrDefault

• xxxOrDefault(x)

如果聚合函数没有输入值，则使用此组合器它返回其返回数据类型的默认值。 适用于可以采用空输入数据的聚合函数。

-OrDefault 可与其他组合器一起使用。

语法

SELECT avg(number), avgOrDefault(number) FROM numbers(0)
┌─avg(number)─┬─avgOrDefault(number)─┐
│         nan │                    0 │
└─────────────┴──────────────────────┘

SELECT avgOrDefaultIf(x, x > 10) FROM (
    SELECT toDecimal32(1.23, 2) AS x
);
┌─avgOrDefaultIf(x, greater(x, 10))─┐
│                                 0 │
└───────────────────────────────────┘

7. -OrNull

• xxxOrNull(x)

• 更改聚合函数的行为。

• 此组合器将聚合函数的结果转换为 可为空 数据类型。 如果聚合函数没有值来计算它返回 NULL.

• -OrNull 可与其他组合器一起使用。

语法

SELECT sumOrNull(number), toTypeName(sumOrNull(number)) FROM numbers(10) WHERE number > 10;
┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐
│              ᴺᵁᴸᴸ │ Nullable(UInt64)              │
└───────────────────┴───────────────────────────────┘

-- -OrNull 可与其他组合器一起使用。 当聚合函数不接受空输入时，它很有用
SELECT avgOrNullIf(x, x > 10)
FROM
(
    SELECT toDecimal32(1.23, 2) AS x
)
┌─avgOrNullIf(x, greater(x, 10))─┐
│                           ᴺᵁᴸᴸ │
└────────────────────────────────┘

8. -Resample

• xxxResample(start, end, step)(, resampling_key)

• 允许您将数据划分为组，然后单独聚合这些组中的数据。 通过将一列中的值拆分为间隔来创建组。

参数

• start — resampling_key 开始值。
• stop — resampling_key 结束边界。 区间内部不包含 stop 值，即 [start, stop).
• step — 分组的步长。 The aggFunction 在每个子区间上独立执行。
• resampling_key — 取样列，被用来分组.
• aggFunction_params — aggFunction 参数。

返回值

• aggFunction 每个子区间的结果，结果为数组。

示例

考虑一下 people 表具有以下数据的表结构：

-- 将工资从 1000 到 400 的人分组，分组工资间隔为 1000
select 
  groupArrayResample(1000,4000,1000)(sal,toUInt16(sal)) as salGroup
from emp;

┌─salGroup───────────────────────────────────────────────────────┐
│ [[1600,1250,1250,1500,1100,1300],[2975,2850,2450],[3000,3000]] │
└────────────────────────────────────────────────────────────────┘

select 
  countResample(1000,4000,1000)(sal,toUInt16(sal)) as countGroup,
  avgResample(1000,4000,1000)(sal,toUInt16(sal)) as avgGroup
from emp;
┌─countGroup─┬─avgGroup─────────────────────────────────────┐
│ [6,3,2]    │ [1333.3333333333333,2758.3333333333335,3000] │
└────────────┴──────────────────────────────────────────────┘

9. State

返回的不是结果值，返回的是中间状态。 这个是与 AggregatingMergeTree 来配合使用的。

如果应用此combinator，则聚合函数不会返回结果值（例如唯一值的数量 uniq 函数），但是返回聚合的中间状态（对于 uniq，返回的是计算唯一值的数量的哈希表）。 这是一个 AggregateFunction(...) 可用于进一步处理或存储在表中以完成稍后的聚合。

要使用这些状态，请使用:

• AggregatingMergeTree 表引擎。
• finalizeAggregation 功能。
• runningAccumulate 功能。
• -Merge combinator
• -MergeState combinator

10. Merge

聚合函数会把中间状态会为参数，进行 Merge，来完成聚合，返回最终的结果值。

如果应用此组合器，则聚合函数将中间聚合状态作为参数，组合状态以完成聚合，并返回结果值。

11. MergeState

与 -Merge 类似，但是返回的不是结果值，而是类似于 -State 的中间状态。

以与-Merge 相同的方式合并中间聚合状态。 但是，它不会返回结果值，而是返回中间聚合状态，类似于-State。