2. 主页
  4. 大数据技术
  6. 十五、ClickHouse
  8. 5. ClickHouse 常用函数

5. ClickHouse 常用函数

一、数组函数

--1、数组定义：
SELECT array('a','b','b','c');

--2、返回数组下标
SELECT arrayEnumerate(['a','b','c']);

--3、数组求和
SELECT arraySum([1,2,3,4]);     --10
SELECT arrayCumSum([1,2,3,4]);  --[1,3,6,10]

--4、数组去重：
SELECT arrayDistinct(['a','b','b','c']);  --['a','b','c']

--5、数组去重统计个数
SELECT uniqArray(['a','b','b','c']);      --3

--6、合并数组：
SELECT arrayConcat(['a','b'],['hello','jim'],['e','f']);

--7、数组添加、移除元素：
SELECT arrayPushBack(['a','b'],'c');
SELECT arrayPopBack(['a','b','c']);

--8、数组排序：
SELECT arraySort(range(2, 10, 2));
SELECT arrayReverseSort(range(2, 10, 2));

--9、数组反转
SELECT arrayReverse(['a','b','b','c']);  --['c','b','b','a']

--10、数组拉链操作：
SELECT arrayZip(['a','b','c'],['1','2','3']);

二、字符串函数

1. 字符串切割

SELECT splitByString(',','1,2,3,abcde');

2. JSON 解析

SELECT visitParamExtractString('{"name":"zss","age":21}' , 'name') ;

随机数：

SELECT number, rand(number), randConstant(number) FROM numbers(3);

三、高阶函数

SELECT arrayMap(a -> a * a ,[1,2,3,4,5]);
SELECT arrayFilter(a -> a % 2 == 0,[1,2,3,4,5]);

--3、符合条件的元素被前面的元素覆盖掉, 如果不满足条件数据不做处理
SELECT arrayFill(a -> not isNull(a) ,[1,2,3,null,4,5]);

--4、符合条件的元素被后面的元素覆盖掉
SELECT arrayReverseFill(a -> not isNull(a) ,[1,2,3,null,4,5]);

--5、数组炸裂 行转列 
SELECT arrayJoin(splitByChar(',','a,b,c')) ;

--6、列转行，将所有的列聚合成一个数组
SELECT groupArray(arrayJoin(splitByChar(',','a,b,c')));
SELECT groupUniqArray(arrayJoin(splitByChar(',','a,b,b,c')));

行列转换参考: http://t.zoukankan.com/cbugs-p-13394444.html

四、字符串搜索函数

--position：显示 hello 在字符串中第一个出现的位置。
SELECT position('ckhelloworldhellospark','hello') AS positionSearch;

--match：匹配到了则返回 1，否则返回 0
SELECT match('ckhelloworldhellospark','hello') AS matchSearch;

五、链路函数

  • 1) sequenceMatch(pattern)(time, cond1, cond2, ...) 判断是否存在满足根据时间排序且依次条件1,条件2,...条件n都成立的链路.若满足 返回 1,否则为 0 .

  • 2) sequenceCount(pattern)(time, cond1, cond2, ...) 判断是否存在满足根据时间排序且依次条件1,条件2,...条件n都成立的链路 若满足 返回出现的次数

  • 3) windowFunnel(window)(time, cond1, cond2, ...) 判断是否存在满足根据时间排序且依次条件1,条件2,...条件n 都成立的链路. 并且设置链路的窗口时间. 一般用于计算漏斗模型

SELECT uid,
       windowFunnel(4)(toDateTime(eventTime), eventid = 'login', eventid = 'view', eventid = 'buy') AS funnel
FROM test_funnel
GROUP BY uid;
  • 4) retention(cond1, cond2, ..., cond32)
SELECT
    uid,
    retention(date = '2020-01-01', date = '2020-01-02', date = '2020-01-03') AS r
FROM retention_test
WHERE date IN ('2020-01-01', '2020-01-02', '2020-01-03')
GROUP BY uid
ORDER BY uid ASC

参数聚合函数：https://clickhouse.com/docs/zh/sql-reference/aggregate-functions/parametric-functions/#retention

参考