ClickHouse函数 5.聚合函数

clickhouse

发布日期: 2021-09-16

1. 创建测试表

-- 创建雇员表
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');

2. count

计算记录条数

SELECT count(1) FROM emp;
┌─count()─┐
│      14 │
└─────────┘

select count(distinct(deptno)) from emp;
┌─uniqExact(deptno)─┐
│                 3 │
└───────────────────┘
````



## 3. min

- 返回最小值

```sql
SELECT deptno,min(empno) FROM emp group by deptno;

┌─deptno─┬─min(empno)─┐
│     10 │       7782 │
│     20 │       7369 │
│     30 │       7499 │
└────────┴────────────┘
````



## minMap

- minMap(key, value)
- minMap(Tuple(key, value))
- 根据 `key` 数组中指定的键对 `value` 数组计算最小值。
- 传递 `key` 和 `value` 数组的元组与传递 `key` 和 `value` 的两个数组是同义的。
- 要总计的每一行的 `key` 和 `value` (数组)元素的数量必须相同。
- 返回两个数组组成的元组: 排好序的 `key` 和对应 `key` 的 `value` 计算值(最小值)。

```sql
SELECT
    deptno,
    minMap(dtpesal.1, dtpesal.2)
FROM
(
    SELECT
        deptno,
        ([deptno], [sal]) AS dtpesal
    FROM emp
) AS t
GROUP BY deptno;

┌─deptno─┬─minMap(tupleElement(dtpesal, 1), tupleElement(dtpesal, 2))─┐
│     10 │ ([10],[1300])                                              │
│     20 │ ([20],[800])                                               │
│     30 │ ([30],[950])                                               │
└────────┴────────────────────────────────────────────────────────────┘

5. max

返回最大值

SELECT deptno,max(empno) FROM emp group by deptno;
┌─deptno─┬─max(empno)─┐
│     10 │       7934 │
│     20 │       7902 │
│     30 │       7900 │
└────────┴────────────┘
````



## 6. maxMap

- maxMap(key, value)
- maxMap(Tuple(key, value))
- 根据 `key` 数组中指定的键对 `value` 数组计算最大值。
- 传递 `key` 和 `value` 数组的元组与传递 `key` 和 `value` 的两个数组是同义的。
- 要总计的每一行的 `key` 和 `value` (数组)元素的数量必须相同。
- 返回两个数组组成的元组: 排好序的`key` 和对应 `key` 的 `value` 计算值(最大值)。



```sql
SELECT
    deptno,
    maxMap(dtpesal.1, dtpesal.2)
FROM
(
    SELECT
        deptno,
        ([deptno], [sal]) AS dtpesal
    FROM emp
) AS t
GROUP BY deptno;

┌─deptno─┬─maxMap(tupleElement(dtpesal, 1), tupleElement(dtpesal, 2))─┐
│     10 │ ([10],[5000])                                              │
│     20 │ ([20],[3000])                                              │
│     30 │ ([30],[2850])                                              │
└────────┴────────────────────────────────────────────────────────────┘

7. avg

求平均值

SELECT deptno,max(sal) FROM emp group by deptno;
┌─deptno─┬─max(sal)─┐
│     10 │     5000 │
│     20 │     3000 │
│     30 │     2850 │
└────────┴──────────┘
````



## 8. sum

- 求和，仅对数值有效

```sql
SELECT deptno,sum(sal) FROM emp group by deptno;
┌─deptno─┬─sum(sal)─┐
│     10 │     8750 │
│     20 │    10875 │
│     30 │     9400 │
└────────┴──────────┘
````



## 9. sumWithOverflow

- sumWithOverflow(x)

- 使用与输入参数相同的数据类型计算数字的总和。 如果总和超过此数据类型的最大值，则函数返回错误。
- 只适用于数字。

```sql
select sumWithOverflow(toUInt8(1)) from emp;
┌─sumWithOverflow(toUInt8(1))─┐
│                          14 │
└─────────────────────────────┘
````



## 10. sumMap

- sumMap(key,value)
- sumMap(Tuple(key, value))

- 对于相同的 key，对其 value 求和


```sql
CREATE TABLE sum_map
(
    `date` Date,
    `timeslot` DateTime,
    `statusMap` Nested(status UInt16, requests UInt64)
)
ENGINE = Log;

INSERT INTO sum_map VALUES
('2000-01-01', '2000-01-01 00:00:00', [1, 2, 3], [10, 20, 30]),
('2000-01-01', '2000-01-01 00:00:00', [2, 3, 4], [40, 50, 60]),
('2000-01-01', '2000-01-01 00:01:00', [4, 5, 6], [10, 20, 30]),
('2000-01-01', '2000-01-01 00:01:00', [5, 6, 7], [40, 50, 60]);

-- 结果
SELECT
    timeslot,
    sumMap(statusMap.status, statusMap.requests)
FROM sum_map
GROUP BY timeslot;
┌────────────timeslot─┬─sumMap(statusMap.status, statusMap.requests)─┐
│ 2000-01-01 00:00:00 │ ([1,2,3,4],[10,60,80,60])                    │
│ 2000-01-01 00:01:00 │ ([4,5,6,7],[10,60,80,60])                    │
└─────────────────────┴──────────────────────────────────────────────┘

-- 将员工表按照部门进行工资汇总
SELECT
    deptno,
    sumMap(dtpesal.1, dtpesal.2)
FROM
(
    SELECT
        deptno,
        ([deptno], [sal]) AS dtpesal
    FROM emp
) AS t
GROUP BY deptno;

┌─deptno─┬─sumMap(tupleElement(dtpesal, 1), tupleElement(dtpesal, 2))─┐
│     10 │ ([10],[8750])                                              │
│     20 │ ([20],[10875])                                             │
│     30 │ ([30],[9400])                                              │
└────────┴────────────────────────────────────────────────────────────┘

-- 同 
SELECT deptno,sum(sal) FROM emp group by deptno;

11. sumCount

SELECT deptno,sumCount(sal) FROM emp group by deptno;
┌─deptno─┬─sumCount(sal)─┐
│     10 │ (8750,3)      │
│     20 │ (10875,5)     │
│     30 │ (9400,6)      │
└────────┴───────────────┘

any

any(column)
1. 返回第一个遇见的值

1
2
3

SELECT any(sal) AS res FROM emp;

SELECT any(sal) AS res FROM (select sal from emp order by sal desc);

13. anyHeavy

anyHeavy(column)
通过 heavy hitters 算法，得到一个经常出现的值。

SELECT anyHeavy(sal) AS res FROM emp;
┌──res─┐
│ 3000 │
└──────┘
````



## 14. anyLast

- anyLast(column)

- 返回最后遇到的值

```sql
SELECT anyLast(sal) AS res FROM emp;
┌──res─┐
│ 1300 │
└──────┘

SELECT anyLast(sal) AS res FROM (select sal from emp order by sal desc);
┌─res─┐
│ 800 │
└─────┘
````



## 15. uniq

- uniq(x[, ...])

- 该函数采用可变数量的参数。 参数可以是 `Tuple`, `Array`, `Date`, `DateTime`, `String`, 或数字类型
- 返回值 [UInt64](https://clickhouse.com/docs/zh/sql-reference/data-types/int-uint/) 类型数值
- 计算聚合中所有参数的哈希值，然后在计算中使用它
- 使用自适应采样算法。 对于计算状态，该函数使用最多65536个元素哈希值的样本

```sql
select uniq(sal) from emp;
┌─uniq(sal)─┐
│        12 │
└───────────┘
````



## 16. uniqCombined

- uniqCombined(HLL_precision)(x[, ...])
- 计算不同参数值的近似数量
- 该函数采用可变数量的参数。 参数可以是 `Tuple`, `Array`, `Date`, `DateTime`, `String`，或数字类型。
- 使用了组合的三个算法：数组，hash 表和 HyperLogLog 表。
- 内存消耗比 uniq 要小几倍，同时精度高几倍，但是性能一般比 uniq 慢，虽然有时也会快。最大支持到96KiB 个状态。

```sql
select uniqCombined(sal) from emp;
┌─uniqCombined(sal)─┐
│                12 │
└───────────────────┘
````



## 17. uniqExact

- uniqExact(x[, ...])
- 该函数采用可变数量的参数。 参数可以是 `Tuple`, `Array`, `Date`, `DateTime`, `String`，或数字类型。

- 计算不同参数值的准确数目
- `uniqExact` 函数比 `uniq` 使用更多的内存，因为状态的大小随着不同值的数量的增加而无界增长。

```sql
select uniqExact(sal) from emp;
┌─uniqExact(sal)─┐
│             12 │
└────────────────┘

-- 同
select count(distinct(sal)) from emp;
┌─uniqExact(sal)─┐
│             12 │
└────────────────┘
````



## 18. argMin(arg,val)

- argMin(arg, val)
- argMin(tuple(arg, val))

- 计算 val 最小值对应的 arg 值。 如果 val 最小值存在几个不同的 arg 值，输出遇到的第一个(arg)值。

```sql
-- 获取最小的部门号的第一条记录的工资
select argMin(sal,deptno) from emp;

┌─argMin(sal, deptno)─┐
│                2450 │
└─────────────────────┘

-- 同
SELECT any(sal) FROM (
    SELECT
        deptno,
        sal
    FROM emp
    WHERE deptno = (
        SELECT min(deptno) AS deptno FROM emp
    )
) AS t


┌─any(sal)─┐
│     2450 │
└──────────┘
````



## argMax(arg,val)

- argMax(arg, val)
- argMax(tuple(arg, val))

- 计算 val 最大值对应的 arg 值。 如果 val 最小值存在几个不同的 arg 值，输出遇到的第一个(arg)值。

```sql
select argMax(sal,deptno) from emp;
┌─argMax(sal, deptno)─┐
│                1600 │
└─────────────────────┘

SELECT any(sal) FROM (
    SELECT
        deptno,
        sal
    FROM emp
    WHERE deptno = (
        SELECT max(deptno) AS deptno FROM emp
    )
) AS t

┌─any(sal)─┐
│     1600 │
└──────────┘
````



## 20. groupArray

- groupArray(column)
- groupArray(max_size)(column)
- 将指定列返回成一个集合
- max_size 指定集合最大长度

```sql
SELECT groupArray(ename) FROM emp GROUP BY deptno

┌─groupArray(ename)──────────────────────────────────┐
│ ['CLARK','KING','MILLER']                          │
│ ['SMITH','JONES','SCOTT','ADAMS','FORD']           │
│ ['ALLEN','WARD','MARTIN','BLAKE','TURNER','JAMES'] │
└────────────────────────────────────────────────────┘

SELECT groupArray(4)(ename) FROM emp GROUP BY deptno;
┌─groupArray(4)(ename)──────────────┐
│ ['CLARK','KING','MILLER']         │
│ ['SMITH','JONES','SCOTT','ADAMS'] │
│ ['ALLEN','WARD','MARTIN','BLAKE'] │
└───────────────────────────────────┘

21. groupUniqArray

groupUniqArray(column)
groupUniqArray(max_size)(column)
功能同groupArray 但是会将数组内的元素去重，内存消耗与 uniqExact 方法相同

SELECT groupArray(sal) FROM emp GROUP BY deptno;
┌─groupArray(sal)────────────────┐
│ [2450,5000,1300]               │
│ [800,2975,3000,1100,3000]      │
│ [1600,1250,1250,2850,1500,950] │
└────────────────────────────────┘

SELECT groupUniqArray(sal) FROM emp GROUP BY deptno;
┌─groupUniqArray(sal)───────┐
│ [5000,2450,1300]          │
│ [800,1100,2975,3000]      │ -- 只保留一个 3000 
│ [1250,1500,950,2850,1600] │
└───────────────────────────┘

SELECT groupUniqArray(2)(sal) FROM emp GROUP BY deptno
┌─groupUniqArray(2)(sal)─┐
│ [5000,2450]            │
│ [800,2975]             │
│ [1600,1250]            │
└────────────────────────┘

22. groupArrayMovingSum

groupArrayMovingSum(numbers_for_summing)
groupArrayMovingSum(window_size)(numbers_for_summing)
numbers_for_summing — 表达式生成数值数据类型值。
window_size — 窗口大小。
移动计算输入值的和

SELECT number FROM numbers(5);
┌─number─┐
│      0 │
│      1 │
│      2 │
│      3 │
│      4 │
└────────┘

select 
  groupArrayMovingSum(number) 
from (
  select number from numbers(5)
) t;

┌─groupArrayMovingSum(number)─┐
│ [0,1,3,6,10]                │
└─────────────────────────────┘

-- 0 = 0
-- 1 = 1+0
-- 3 = 2+1+0
-- 6 = 3+2+1+0
-- 10 = 4+3+2+1+0


select 
  groupArrayMovingSum(2)(number) 
from (
  select number from numbers(5)
) t;

┌─groupArrayMovingSum(2)(number)─┐
│ [0,1,3,5,7]                    │
└────────────────────────────────┘

-- 0 = 0
-- 1 = 1+0
-- 3 = 2+1
-- 5 = 3+2
-- 7 = 4+3

23. groupArrayMovingAvg

groupArrayMovingAvg(numbers_for_summing)
groupArrayMovingAvg(window_size)(numbers_for_summing)
numbers_for_summing — 表达式生成数值数据类型值。
window_size — 窗口大小。
计算输入值的移动平均值。
该函数可以将窗口大小作为参数。如果未指定，则该函数的窗口大小等于列中的行数。

语法

SELECT number FROM numbers(5);
┌─number─┐
│      0 │
│      1 │
│      2 │
│      3 │
│      4 │
└────────┘

select 
  groupArrayMovingAvg(number) 
from (
  select number from numbers(5)
) t;
┌─groupArrayMovingAvg(number)─┐
│ [0,0.2,0.6,1.2,2]           │
└─────────────────────────────┘


select 
  groupArrayMovingAvg(2)(number) 
from (
  select number from numbers(5)
) t;

┌─groupArrayMovingAvg(2)(number)─┐
│ [0,0.5,1.5,2.5,3.5]            │
└────────────────────────────────┘

24. topK

topK(N)(x)
N — 要返回的元素数，默认值 10
x – (要计算频次的)值
返回指定列中近似最常见值的数组。生成的数组按值的近似频率降序排序（而不是值本身）。
实现了过滤节省空间算法，使用基于reduce-and-combine的算法，借鉴并行节省空间。

select topK(3)(number) from numbers(5)
┌─topK(3)(number)─┐
│ [0,1,2]         │
└─────────────────┘

select topK(2)(sal) from emp;
┌─topK(2)(sal)─┐
│ [1250,3000]  │
└──────────────┘

hnbian

https://www.hnbian.cn/posts/c94ef2d1.html