ClickHouse函数 5.聚合函数


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)─┐
│     107782 │
│     207369 │
│     307499 │
└────────┴────────────┘

minMap

  • minMap(key, value)
  • minMap(Tuple(key, value))
  • 根据 key 数组中指定的键对 value 数组计算最小值。
  • 传递 keyvalue 数组的元组与传递 keyvalue 的两个数组是同义的。
  • 要总计的每一行的 keyvalue (数组)元素的数量必须相同。
  • 返回两个数组组成的元组: 排好序的 key 和对应 keyvalue 计算值(最小值)。
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)─┐
│     107934 │
│     207902 │
│     307900 │
└────────┴────────────┘

6. maxMap

  • maxMap(key, value)
  • maxMap(Tuple(key, value))
  • 根据 key 数组中指定的键对 value 数组计算最大值。
  • 传递 keyvalue 数组的元组与传递 keyvalue 的两个数组是同义的。
  • 要总计的每一行的 keyvalue (数组)元素的数量必须相同。
  • 返回两个数组组成的元组: 排好序的key 和对应 keyvalue 计算值(最大值)。
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)─┐
│     105000 │
│     203000 │
│     302850 │
└────────┴──────────┘

8. sum

  • 求和,仅对数值有效
SELECT deptno,sum(sal) FROM emp group by deptno;
┌─deptno─┬─sum(sal)─┐
│     108750 │
│     2010875 │
│     309400 │
└────────┴──────────┘

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
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 hnbian !
评论
 上一篇
ClickHouse函数 6.聚合函数组合 ClickHouse函数 6.聚合函数组合
1. 聚合函数组合器聚合函数的名称可以附加一个后缀。 这改变了聚合函数的工作方式。 If Array ForEach OrDefault OrNull Resample State Merge MergeState 2
2021-09-21
下一篇 
ClickHouse函数 4.数组函数 ClickHouse函数 4.数组函数
1. empty 判断数组是否为空 为空:1 不为空:0 SELECT empty([]), empty([1,2,3]); ┌─empty(array())─┬─empty([1, 2, 3])─┐ │ 1
2021-09-10
  目录