ClickHouse函数 5.聚合函数

clickhouse

发布日期: 2021-09-16

文章字数: 2.1k

阅读时长: 10 分

阅读次数:

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

返回最小值

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 计算值(最小值)。

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 计算值(最大值)。

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

求和，仅对数值有效

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

9. sumWithOverflow

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

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

10. sumMap

sumMap(key,value)
sumMap(Tuple(key, value))
对于相同的 key，对其 value 求和

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. 返回第一个遇见的值

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)
返回最后遇到的值

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 类型数值
计算聚合中所有参数的哈希值，然后在计算中使用它
使用自适应采样算法。对于计算状态，该函数使用最多65536个元素哈希值的样本

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

16. uniqCombined

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

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

17. uniqExact

uniqExact(x[, …])
该函数采用可变数量的参数。参数可以是 Tuple, Array, Date, DateTime, String，或数字类型。
计算不同参数值的准确数目
uniqExact 函数比 uniq 使用更多的内存，因为状态的大小随着不同值的数量的增加而无界增长。

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)值。

-- 获取最小的部门号的第一条记录的工资
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)值。

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 指定集合最大长度

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

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 hnbian !

clickhouse olap

ClickHouse函数 6.聚合函数组合

1. 聚合函数组合器聚合函数的名称可以附加一个后缀。这改变了聚合函数的工作方式。 If Array ForEach OrDefault OrNull Resample State Merge MergeState 2

2021-09-21 clickhouse

clickhouse olap

ClickHouse函数 4.数组函数

1. empty 判断数组是否为空为空：1 不为空：0 SELECT empty([]), empty([1,2,3]); ┌─empty(array())─┬─empty([1, 2, 3])─┐ │ 1

2021-09-10 clickhouse

clickhouse olap

ClickHouse函数 5.聚合函数

1. 创建测试表

2. count

3. min

minMap

5. max

6. maxMap

7. avg

8. sum

9. sumWithOverflow

10. sumMap

11. sumCount

any

13. anyHeavy

14. anyLast

15. uniq

16. uniqCombined

17. uniqExact

18. argMin(arg,val)

argMax(arg,val)

20. groupArray

21. groupUniqArray

22. groupArrayMovingSum

23. groupArrayMovingAvg

24. topK

你的赏识是我前进的动力