0. 序言

• ClickHouse的SQL函数库非常丰富。相比其它 OLAP 引擎而言，ClickHouse包含了不少吊炸天的数组操作函数。实际开发中，该怎么来灵活运用这些SQL函数呢?

  
  

1. 聚合函数篇

1.1 groupArray(x)

• 这是个特殊的聚合函数，对指定列[x]进行 group by 操作，将每条记录的[x]列值存入array数组（顺序任意），作为结果返回，可以最大程度保存[x]列聚合前的明细。

• 示例：

-- 用户-标签关系表
CREATE TABLE user_tag
(
    `user_id` UInt32 comment '用户id',
    `tag` UInt16 comment '标签id'
)
ENGINE = TinyLog;

复制

select * from user_tag :
┌─user_id─┬─tag─┐
│       1 │  10 │
│       2 │  11 │
│       3 │  21 │
│       1 │  21 │
│       3 │  15 │
│       3 │  23 │
│       1 │  25 │
│       2 │  17 │
│       1 │  20 │
└─────────┴─────┘

复制

-- 按用户查询标签:
select user_id,groupArray(tag) as tags 
from user_tag 
group by user_id 
order by user_id
limit 10;

复制

┌─user_id─┬─tags──────────┐
│       1 │ [10,21,25,20] │
│       2 │ [11,17]       │
│       3 │ [21,15,23]    │
└─────────┴───────────────┘


复制

-- 进一步按用户展示标签宽表
select
user_id
, has(tags,10) as tag_10
, has(tags,11) as tag_11
, has(tags,15) as tag_15
, has(tags,17) as tag_17
, has(tags,20) as tag_20
from (
    select user_id,groupArray(tag) as tags 
    from user_tag 
    group by user_id
) tmp;

复制

┌─user_id─┬─tag_10─┬─tag_11─┬─tag_15─┬─tag_17─┬─tag_20─┐
│       1 │      1 │      0 │      0 │      0 │      1 │
│       2 │      0 │      1 │      0 │      1 │      0 │
│       3 │      0 │      0 │      1 │      0 │      0 │
└─────────┴────────┴────────┴────────┴────────┴────────┘


复制

1.2 衍生函数：

1.2.1 groupArray(max_size)(x)

• 限制返回的array大小为 [max_size]

1.2.2 groupArrayInsertAt[(default_val,arr_size)](x,position)

• 用途：

• 默认填充值 default_val : 除 result[position] 外，result其它位置使用该值填充

• 结果集数组长度 arr_size : 使用该参数时，default_val 必须提供。用于对最终的result数组进行重新设置

• 通过指定列[x]和插入位置 [position]，构造一个结果集数组result，将x列值赋给 result[position]

• 聚合中key对应的[x]值有多个时，会任意选中一个值存入 result[position] (单线程情况下会选择第一个x值)

• 如果聚合后[x]值为空，会使用对应类型默认值替换

• 可选参数：

• 参数说明：

• x : 列名或表达式

• position : x值在结果数组中的位置下标，从0开始

• defailt_val : 可选。结果集除position位置的元素外，其它元素的值；不提供时，为x的值类型默认值

• arr_size : 可选。结果集数组的大小；若position下标超出数组范围，返回的结果集使用arr_size进行长度截断

-- 示例1：仅使用必选参数
select user_id
, groupArrayInsertAt(tag+100,3) tag_arr
from user_tag 
group by user_id;

复制

┌─user_id─┬─tag_arr─────┐
│       1 │ [0,0,0,110] │
│       2 │ [0,0,0,111] │
│       3 │ [0,0,0,121] │
└─────────┴─────────────┘


复制

-- 示例2：
select user_id
, groupArrayInsertAt(9,6)(tag+100,3) tag_arr
from user_tag 
group by user_id;

复制

┌─user_id─┬─tag_arr─────────┐
│       1 │ [9,9,9,110,9,9] │
│       2 │ [9,9,9,111,9,9] │
│       3 │ [9,9,9,121,9,9] │
└─────────┴─────────────────┘


复制

1.2.3 groupArrayMovingSum[(window_size)](numbers_for_summing)

• 对 numbers_for_summing 列进行多次求和，并将求和的结果存入结果集返回

• window_size 参数：限制求和计算的操作次数，如果没指定，则使用聚合后 numbers_for_summing的元素个数

• numbers_for_summing : 返回结果为数值类型的表达式

• 示例：

CREATE TABLE t
(
    `int` UInt8,
    `float` Float32,
    `dec` Decimal32(2)
)
ENGINE = TinyLog;

复制

┌─int─┬─float─┬──dec─┐
│   1 │   1.1 │ 1.10 │
│   2 │   2.2 │ 2.20 │
│   4 │   4.4 │ 4.40 │
│   7 │  7.77 │ 7.77 │
└─────┴───────┴──────┘


复制

query sql :

SELECT
    groupArrayMovingSum(int) AS I,
    groupArrayMovingSum(float) AS F,
    groupArrayMovingSum(dec) AS D
FROM t

复制

query result :

┌─I──────────┬─F───────────────────────────────┬─D──────────────────────┐
│ [1,3,6,11] │ [1.1,3.3000002,6.6000004,12.17] │ [1.10,3.30,6.60,12.17] │
└────────────┴─────────────────────────────────┴────────────────────────┘


复制

1.2.4 groupArrayMovingAvg[(window_size)](numbers_for_summing)

• 与上面类似

2. 高阶函数与lambda表达式

2.1 clickhouse中的lambda表达式

• 基本定义：和Java/Scala类似，通过运算符 -> 声明，示例：

x -> 2 * x


复制

2.2 大数据与流处理操作

• 回忆一下Java中的 Stream 流处理，Hadoop中的 Map-Reduce操作，Spark-Streaming中的RDD操作, 其共同特点是，对输入的数据集source， 可以进行如下基本的操作：

• Map：转换，对数据集的元素按转换规则进行转换操作后得到新的集合

• Filter：过滤，对数据集的元素按规则进行过滤，得到新的集合

• Sort：排序，对数据集元素按规则进行排序，得到有序的新集合

• Count: 计数

• Sum/Avg：对source数据集中的数值类型进行求和/求均值

• Exists: 判断数据集是否存在指定元素

• 和上述操作类似，clickhouse中提供了lambda表达式，用于对数组进行类似的流处理

2.3 高阶函数与lambda表达式使用

2.3.1 简单示例

-- 示例：arrayFilter
SELECT arrayFilter(x -> x LIKE '%World%', ['Hello', 'abc World']) AS res;
┌─res───────────┐
│ ['abc World'] │
└───────────────┘

复制

-- 示例：arrayMap
SELECT arrayMap(x -> concat(x,'-test'), ['Hello', 'abc World']) AS res;
┌─res─────────────────────────────┐
│ ['Hello-test','abc World-test'] │
└─────────────────────────────────┘

复制

-- 示例：arraySort
SELECT arraySort(x -> length(x), ['Hello', 'abc']) AS res;
┌─res─────────────┐
│ ['abc','Hello'] │
└─────────────────┘

复制

2.3.2 高阶函数与lambda表达式

• 高阶函数基本用法：arrayFunc([lambda,] arr)

• arrayFunc: 代表数组操作的高阶函数

• lambda: 用户定义的lambda函数

• arr: 源数据集，必须为Array(T) 类型

• 常用高阶函数列表：