文章目录
判断空数组
SELECT empty([]) AS a, empty([1]) AS b, empty('') AS c;
###################
┌─a─┬─b─┬─c─┐
│ 1 │ 0 │ 1 │
└───┴───┴───┘
判断非空数组
SELECT notEmpty([]) AS a, notEmpty([1]) AS b, notEmpty('') AS c;
###################
┌─a─┬─b─┬─c─┐
│ 0 │ 1 │ 0 │
└───┴───┴───┘
计算长度
SELECT
'' AS a,
[] AS b,
[1] AS c,
length(a) AS length_a,
length(b) AS length_b,
length(c) AS length_c
Query id: 6a6ee36e-a60f-482c-a6a9-1566d91947ae
┌─a─┬─b──┬─c───┬─length_a─┬─length_b─┬─length_c─┐
│ │ [] │ [1] │ 0 │ 0 │ 1 │
└───┴────┴─────┴──────────┴──────────┴──────────┘
根据范围构造数组
range([start,] end [, step])
SELECT
range(10) AS a,
range(1, 10) AS b,
range(1, 10, 2) AS c
Query id: 97016df3-93c5-4d5c-8057-b0d3607f31b0
┌─a─────────────────────┬─b───────────────────┬─c───────────┐
│ [0,1,2,3,4,5,6,7,8,9] │ [1,2,3,4,5,6,7,8,9] │ [1,3,5,7,9] │
└───────────────────────┴─────────────────────┴─────────────┘
拼接数组
SELECT arrayConcat([1, 2], [3, 4], [5, 6]) AS res
Query id: ca8de8f4-6eae-429d-bbf6-304535061da8
┌─res───────────┐
│ [1,2,3,4,5,6] │
└───────────────┘
根据下标查询数组元素
SELECT
[1, 2, 3, 4, 5] AS arr,
arr[1] AS a1,
arr[2] AS a2
Query id: 3d867603-003f-43cf-8471-d69d077fa844
┌─arr─────────┬─a1─┬─a2─┐
│ [1,2,3,4,5] │ 1 │ 2 │
└─────────────┴────┴────┘
判断是否包含元素
SELECT
[1, 2, 3, 4, 5] AS arr,
3 AS x,
has(arr, x) AS res
Query id: 0f7467ec-d004-4ce8-ae15-3c8422445308
┌─arr─────────┬─x─┬─res─┐
│ [1,2,3,4,5] │ 3 │ 1 │
└─────────────┴───┴─────┘
判断是不是子数组
SELECT
[1, 2, 3, 4, 5] AS set,
[1, 3] AS subset,
hasAll(set, subset) AS res
Query id: 93f55924-4bba-4abd-a6c7-8665d9768cf4
┌─set─────────┬─subset─┬─res─┐
│ [1,2,3,4,5] │ [1,3] │ 1 │
└─────────────┴────────┴─────┘
判断两个数组是否有交集
SELECT
[1, 2, 3, 4, 5] AS set,
[1, 3] AS subset,
[8, 9] AS y,
hasAny(set, subset) AS res1,
hasAny(set, y) AS res2
Query id: c9bbe03a-bf3f-461c-818c-e1ee470b5555
┌─set─────────┬─subset─┬─y─────┬─res1─┬─res2─┐
│ [1,2,3,4,5] │ [1,3] │ [8,9] │ 1 │ 0 │
└─────────────┴────────┴───────┴──────┴──────┘
返回元素下标
SELECT
[1, 2, 3, 4, 5] AS arr,
3 AS x,
indexOf(arr, x) AS res
Query id: 96eceb10-b58b-41ff-acf2-0cfef5e90ccb
┌─arr─────────┬─x─┬─res─┐
│ [1,2,3,4,5] │ 3 │ 3 │
└─────────────┴───┴─────┘
数组切片
SELECT arraySlice([1, 2, 3, 4, 5], 2, 3) AS res
Query id: b4764de1-e2b3-4624-aa68-34eca492037a
┌─res─────┐
│ [2,3,4] │
└─────────┘
数组升序排序
SELECT arraySort([1, 3, 2, 5, 7, 6, 0]) AS res
Query id: 8fd0be21-4d18-448e-8aea-70389e66147b
┌─res─────────────┐
│ [0,1,2,3,5,6,7] │
└─────────────────┘
数组降序排序
SELECT arrayReverseSort([1, 3, 2, 5, 7, 6, 0]) AS res
Query id: d920f5f6-75d4-430b-af86-877f847a1525
┌─res─────────────┐
│ [7,6,5,3,2,1,0] │
└─────────────────┘
数组自定义排序
- 使用 lambda 表达式
SELECT arraySort(x -> (-x), [1, 2, 3]) AS res
Query id: eca589b4-83aa-4264-963c-bbb088a1ae43
┌─res─────┐
│ [3,2,1] │
└─────────┘
计算数组不重复元素个数
SELECT arrayUniq([1, 2, 2, 3, 4, 5, 5]) AS res
Query id: d313e61d-8fc1-4931-93c0-f76e447c5273
┌─res─┐
│ 5 │
└─────┘
数组元素去重
SELECT arrayDistinct([1, 2, 2, 3, 4, 5, 5, 6, 6, 6]) AS res
Query id: 51b5abac-379a-47d1-ae0f-f7b826be8834
┌─res───────────┐
│ [1,2,3,4,5,6] │
└───────────────┘
数组交集
SELECT
arrayIntersect([1, 2], [3, 4], [2, 3]) AS no_interset,
arrayIntersect([1, 2, 5], [1, 2, 3], [1, 2, 4]) AS interset
Query id: 4886d4de-8d56-4a1f-8694-f6ad9896f323
┌─no_interset─┬─interset─┐
│ [] │ [2,1] │
└─────────────┴──────────┘
数组归并
- 将聚合函数应用于数组元素并返回其结果,函数以单引号括起来
SELECT arrayReduce('uniq', [1, 2, 3, 4, 5, 5, 5, 6, 6]) AS res
Query id: df912733-1914-4ffb-b1e8-e112b1157b29
┌─res─┐
│ 6 │
└─────┘
数组逆序
SELECT arrayReverse([1, 2, 3]) AS res
Query id: d0f8bfaf-441e-4cc9-abec-af3b3473a9b1
┌─res─────┐
│ [3,2,1] │
└─────────┘
数组拍平
SELECT flatten([[1, 2, 3], [2, 3, 4], [3, 4, 5]]) AS res
Query id: f42113b9-7500-4242-a7b8-937f8d53924a
┌─res─────────────────┐
│ [1,2,3,2,3,4,3,4,5] │
数组压缩成一个元组数组
SELECT arrayZip(['a', 'b', 'c'], [3, 2, 1]) AS res
Query id: 8ad7b1d6-fb23-4a5c-bb61-0a78c1b76d7f
┌─res───────────────────────┐
│ [('a',3),('b',2),('c',1)] │
└───────────────────────────┘
数组元素映射
SELECT arrayMap(x -> (x * x), [1, 2, 3]) AS res
Query id: aa12c467-7fb9-4314-b052-a647d6fb8efd
┌─res─────┐
│ [1,4,9] │
└─────────┘
数组过滤
SELECT
[1, 2, 3, 4, 5, 6, 7, 8, 9] AS arr,
arrayFilter(x -> ((x % 2) = 0), arr) AS res
Query id: 358b9dec-cc62-4a45-a4e5-1e982a3cb5a4
┌─arr─────────────────┬─res───────┐
│ [1,2,3,4,5,6,7,8,9] │ [2,4,6,8] │
└─────────────────────┴───────────┘
求数组最小元素
SELECT arrayMin([1, 2, 3]) AS res
Query id: f21443b5-b195-4f46-ab1b-dd16bfe4e99a
┌─res─┐
│ 1 │
└─────┘
求数组最大元素
SELECT arrayMax([1, 2, 3]) AS res
Query id: f21443b5-b195-4f46-ab1b-dd16bfe4e99a
┌─res─┐
│ 3 │
└─────┘
数组元素求和
SELECT arraySum([1, 2, 3]) AS res
Query id: b5bbd5f6-0c68-4327-a7f3-524eff944537
┌─res─┐
│ 6 │
└─────┘
数组元素平均值
SELECT arrayAvg([1, 2, 3, 4]) AS res
Query id: 8c77fc4f-cbf7-4f8b-a1c8-89a895a8578f
┌─res─┐
│ 2.5 │
└─────┘
数组元素相乘
SELECT arrayProduct([1, 2, 3, 4, 5]) AS res
Query id: 69507cdf-1d99-4e4d-b314-feafd705430b
┌─res─┐
│ 120 │
└─────┘
数组元素展开
SELECT
arrayJoin([1, 2, 3]) AS e,
'abc' AS a
Query id: e5358a00-b344-4cfd-b3fd-b25bd4e0f39b
┌─e─┬─a───┐
│ 1 │ abc │
│ 2 │ abc │
│ 3 │ abc │
└───┴─────┘