clickhouse 中的数组（array）和元组（Tuple）—— clickhouse 基础篇（二）

数组

数组中的数据类型

• 数组中的每一个元素都是同一种数据，在一个数组中创建使用不同数据类型，会报错。
• 当数组中的元素都不为空时，数组类型就是不可空的。
• 如果数组中元素有一个NULL（空值），类型会相应地变成可空元素类型。
  

判断是否为空

SELECT
  empty([]) AS isEmpty,
  empty([1]) AS notEmpty
FORMAT Vertical

# 输出
isEmpty: 1
notEmpty: 0

计算数组长度

SELECT
  length([1,2,3]) AS a1,
  length([]) AS a2,
  length(NULL) AS a3
FORMAT Vertical

# 输出
a1: 3
a2: 0
a3: NULL

获取数组元素

clickhouse 数组元素通过索引访问，索引从1开始。

判断某个元素是否存在

SELECT has([1,2,3], 1) AS hasIt

###############
┌─hasIt─┐
│     1 │
└───────┘

数组切片

参数说明：

• 数组
• 偏移量，正数从左边开始，负数从右边开始，需要注意clickhouse 索引下标从1开始
• 数组长度

SELECT
  arraySlice([10, 20, 30, 40, 50], 1, 2) AS res1,
  arraySlice([10, 20, 30, 40, 50], 1) AS res2
FORMAT Vertical

################
res1: [10,20]
res2: [10,20,30,40,50]

数组元素展开

将数组中的元素展开至多行

SELECT arrayJoin([1,2,3])

###############
┌─arrayJoin([1, 2, 3])─┐
│                    1 │
│                    2 │
│                    3 │
└──────────────────────┘

数组元素去重

SELECT arrayDistinct([1,1,nan,nan,2,3,3,3,NULL,NULL,3,4,5,6,6,6]) as c
FORMAT Vertical

#######################
c: [1,nan,2,3,4,5,6]

删除连续重复元素

SELECT arrayCompact([1,1,nan,nan,2,3,3,3,NULL,NULL,3,4,5,6,6,6]) AS c
FORMAT Vertical

####################
c: [1,nan,2,3,NULL,3,4,5,6]

连接多个数组

SELECT arrayConcat([1,2], [3,4], [5,6,3,4]) as res

#######################
┌─res───────────────┐
│ [1,2,3,4,5,6,3,4] │
└───────────────────┘

数组倒序

SELECT arrayReverse([1,2,3]) as res

################
┌─res─────┐
│ [3,2,1] │
└─────────┘

数组拍平

SELECT 
  arrayFlatten([[[1]], [[2], [3,4,5]]] AS src) AS flatArr, src
FORMAT Vertical

######################
flatArr: [1,2,3,4,5]
src:     [[[1]],[[2],[3,4,5]]]

数组元素映射

SELECT arrayMap(x -> (x*x), [1,2,3]) AS res
FORMAT Vertical

#####################
res: [1,4,9]

数组元素过滤

SELECT arrayFilter(x -> ((x%2) = 0), [1,2,3,4,5,6]) AS res
FORMAT Vertical

#############
res: [2,4,6]

数组聚合分析

将数组中的元素放入聚合函数执行，并返回结果，需要注意函数参数使用单引号

SELECT arrayReduce('max', [1,2,3,3,4,4]) as res

##################
┌─res─┐
│   4 │
└─────┘

SELECT arrayReduce('sum', [1,2,3,3,4,4]) as res

#############
┌─res─┐
│  17 │
└─────┘

计算数组交集

SELECT arrayIntersect([1,2,3,3], [4,5,6]) AS noIntersect,
       arrayIntersect([1,2,3,3], [3,4,5,6]) AS hasIntersect
FORMAT Vertical

##################
noIntersect:  []
hasIntersect: [3]

计算数组并集

SELECT 
  [1,2] AS a,
  [2,3] AS b,
  arrayDistinct(arrayConcat(a,b)) AS res
FORMAT Vertical

##################
a:   [1,2]
b:   [2,3]
res: [1,2,3]

计算数组差集

需要结合 arrayIntersect 和 arrayMap 和 arrayFilter 组合实现。

SELECT 
  [1,2] AS a,
  [2,3] AS b,
  arrayFilter(x -> (x IS NOT NULL), arrayMap(x -> multiIf(x NOT IN arrayIntersect(a,b), x, NULL), a)) AS res
FORMAT Vertical

##################
a:   [1,2]
b:   [2,3]
res: [1]

SQL 子查询进行集合操作

交集SQL

SELECT a.i
FROM
(
  SELECT arrayJoin([1,2]) AS i
) AS a
INTERSECT
SELECT b.i
FROM
(
  SELECT arrayJoin([2,3]) AS i
) AS b

####################
┌─i─┐
│ 2 │
└───┘

并集 SQL

SET union_default_mode = 'ALL';

SELECT DISTINCT t.i
FROM
(
  SELECT a.i
  FROM
  (
    SELECT arrayJoin([1,2]) AS i
  ) AS a
  UNION
  SELECT b.i
  FROM 
  (
    SELECT arrayJoin([2,3]) AS i
  )AS b
) AS t

########################
┌─i─┐
│ 2 │
│ 3 │
└───┘
┌─i─┐
│ 1 │
└───┘

差集 SQL

SELECT a.i
FROM
(
  SELECT arrayJoin([1,2]) AS i
) AS a
EXCEPT
SELECT b.i
FROM
(
  SELECT arrayJoin([2,3]) AS i
) AS b

########################
┌─i─┐
│ 1 │
└───┘

元组

元组中的数据类型说明

• 不同于数组中的每个元素类型要一样，元组中的元素类型可以不一样
• 元组一般表示列的聚合，SQL 中的 in 查询也是使用的元组
• 在 clickhouse 中元组也可以作为查询的结果
  

创建元组

SELECT tuple(1, 'a', NULL) AS x, toTypeName(x) as tp FORMAT Vertical;

SELECT (1, 'a', NULL) AS x, toTypeName(x) as tp FORMAT Vertical;

获取元组中的元素

元组和数组一样，元素索引都是从1开始。

select (1, 'a', 3, 4) as tp1, tupleElement(tp1, 2) as t2 Format Vertical;
# 等价于
select (1, 'a', 3, 4) as tp1, tp1.2 as t2 Format Vertical;

################
tp1: (1,'a',3,4)
t2:  a

获取元组中的每个元素：

select (1, 'a', 3, 4) as tp1, untuple(tp1) as t2 Format Vertical;

#####################
tp1:  (1,'a',3,4)
t2.1: 1
t2.2: a
t2.3: 3
t2.4: 4

插入元组元素

CREATE TABLE t1
(
  `a` Date,
  `b` UInt8,
  `c` Tuple(UInt8, String)
)
Engine = MergeTree(a, b, 8192);

insert into t1(a, b, c) values(now(), 1, (1, 'a'));