常用函数
原创大约 16 分钟
条件函数
1. if函数
其实Clickhouse本质上就是一门可以编程的数据库，所以条件判断也是标配。
最简单的条件判断就是这样的。
:) SELECT if(cond, then, else);如果cond返回非零值（即true），那么就执行then，否则执行else。
:) SELECT if(1, plus(1, 2), plus(3, 4));
┌─plus(1, 2)─┐
│ 3 │
└────────────┘有如下数据表。
:) CREATE TABLE t_left_right
(
left UInt32,
right UInt32
) ENGINE = Memory();
:) INSERT INTO t_left_right VALUES
(0, 4),
(1, 3),
(2, 2),
(3, 1),
(4, 0);
┌─left─┬─right─┐
│ 0 │ 4 │
│ 1 │ 3 │
│ 2 │ 2 │
│ 3 │ 1 │
│ 4 │ 0 │
└──────┴───────┘可以这样来比较它里面的值。
:) SELECT left, right,
if(left < right, '左边比右边小', '左边大于等于右边') AS smaller
FROM t_left_right
WHERE left != 0 AND right != 0;
┌─left─┬─right─┬─smaller──────────┐
│ 1 │ 3 │ 左边比右边小 │
│ 2 │ 2 │ 左边大于等于右边 │
│ 3 │ 1 │ 左边大于等于右边 │
└──────┴───────┴──────────────────┘2. 三元运算符
和大多数编程语言一样，Clickhouse也有三元运算符。
-- 三元运算符语法
cond ? then : else
:) SELECT 1 > 0 ? plus(1, 2) : plus(3, 4) AS res;
┌─res─┐
│ 3 │
└─────┘把之前的if()函数改为三元运算符。
:) SELECT left, right,
left < right ? '左边比右边小': '左边大于等于右边' AS smaller
FROM t_left_right
WHERE left != 0 AND right != 0;
┌─left─┬─right─┬─smaller──────────┐
│ 1 │ 3 │ 左边比右边小 │
│ 2 │ 2 │ 左边大于等于右边 │
│ 3 │ 1 │ 左边大于等于右边 │
└──────┴───────┴──────────────────┘3. CASE运算符
-- CASE运算符语法
multiIf(cond_1, then_1, cond_2, then_2, ..., else)再次使用之前的t_left_right表。
:) SELECT left, right,
multiIf(left < right, '左边更小', left > right, '左边更大', left = right, '两边相等', '0值') AS res
FROM t_left_right;
┌─left─┬─right─┬─res──────┐
│ 0 │ 4 │ 左边更小 │
│ 1 │ 3 │ 左边更小 │
│ 2 │ 2 │ 两边相等 │
│ 3 │ 1 │ 左边更大 │
│ 4 │ 0 │ 左边更大 │
└──────┴───────┴──────────需要注意，当条件中包含NULL值时，结果也将为NULL。
:) SELECT NULL < 1 AS r1, 2 < NULL AS r2, NULL < NULL AS r3, NULL = NULL AS r4;
┌─r1───┬─r2───┬─r3───┬─r4───┐
│ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │
└──────┴──────┴──────┴──────┘字符串函数
:) SELECT empty('hello Clickhouse') AS a1,
notEmpty('hello Clickhouse') AS b1,
isValidUTF8('hello Clickhouse') AS c1,
length('hello Clickhouse') AS d1,
lengthUTF8('hello Clickhouse') AS e1,
char_length('hello Clickhouse') AS f1,
lower('hello Clickhouse') AS g1,
upper('hello Clickhouse') AS h1,
lowerUTF8('hello Clickhouse') AS i1,
upperUTF8('hello Clickhouse') AS j1,
isValidUTF8('hello Clickhouse') AS k1,
toValidUTF8('hello Clickhouse') AS l1,
reverse('hello Clickhouse') AS m1,
format('{1} {0} {1}', 'Clickhouse', 'hello') AS n1,
concat('hello', 'Clickhouse') AS o1,
concatAssumeInjective('hello', ' Clickhouse') AS p1,
substring('hello Clickhouse', 2, 10) AS q1,
appendTrailingCharIfAbsent('hello Clickhouse', '!') AS r1,
base64Encode('hello Clickhouse') AS s1,
base64Decode('aGVsbG8gQ2xpY2tob3VzZQ==') AS t1,
endsWith('hello Clickhouse', 'house') AS u1,
trimLeft(' hello Clickhouse') AS v1,
trimRight('hello Clickhouse ') AS w1,
trimBoth(' hello Clickhouse ') AS x1,
soundex('hello Clickhouse') AS y1,
-- 替换类字符串函数
replaceOne('hello Clickhouse', 'Click', 'Event') AS aa1,
replaceAll('hello Clickhouse', 'Click', 'Event') AS bb1,
replaceRegexpOne(toString(now()), '(\\d{4})-(\\d{2})-(\\d{2})', '\\2/\\3/\\1') AS cc1,
replaceRegexpAll('hello Clickhouse', '.', '\\0\\0') AS dd1,
regexpQuoteMeta('(hello Clickhouse)') AS ee1,
-- 拆分合并
splitByChar(' ', 'hello Clickhouse') AS ff1,
splitByString('o C', 'hello Clickhouse') AS gg1,
splitByWhitespace('hello Clickhouse') AS hh1,
splitByNonAlpha('hello, Clickhouse') AS ii1,
alphaTokens('hello Clickhouse') AS jj1Arrays函数
:) SELECT empty([]) AS a1, -- 返回1
notEmpty([1]) AS b1, -- 返回1
length([-1, 0, 1]) AS c1, -- 返回3
emptyArrayUInt8() AS d1, -- 返回[]
emptyArrayDateTime() AS e1, -- 返回[]
-- range([start, ] end [, step]) 返回一个以`step`作为增量步长的从`start`到`end - 1`的整形数字数组
range(1, 12, 2) AS f1, -- 返回[1, 3, 5, 7, 9, 11]
-- 合并参数中传递的所有数组
arrayConcat([1, 2, 3], [-3], [5, 6]) AS g1, -- 返回[1, 2, 3, -3, 5, 6]
-- 数组的负号表示从尾部开始往前，例如3为world，而-3为hello，超出索引边界则返回默认值
arrayElement(['hello', 'new', 'world'], -4) AS h1, -- 返回''空字符串
has(['hello', 'new', 'world'], 'new2') AS i1, -- 返回0
has([1, 2, NULL], NULL) AS i2, -- 返回1
-- 检查第二个参数是否是第一个参数的子数组
hasAll(['hello', 'new', 'world'], ['new1', 'world']) AS j1, -- 返回0
hasAll([], []) AS j2, -- 返回1
hasAll([1, Null], [Null]) AS j3, -- 返回1
hasAll([1.0, 2, 3, 4], [1, 3]) AS j4, -- 返回1
hasAll([[1, 2], [3, 4]], [[1, 2], [3, 5]]) AS j5, -- 返回0
-- 检查两个数组是否存在交集
hasAny([1], []) AS k1, -- 返回0
hasAny([Null], [Null, 1]) AS k2, -- 返回1
hasAny([-128, 1., 512], [1]) AS k3, -- 返回1
--hasAny([[1, 2], [3, 4]], ['a', 'c']) AS k4, -- 将抛出异常
hasAny([[1, 2], [3, 4]], [[1, 2], [1, 2]]) AS k5, -- 返回1
-- hasSubstr(array1, array2) 检查`array2`的所有元素是否以相同的顺序出现在`array1`中。当且仅当`array1 = prefix + array2 + suffix`时，该函数将返回1
hasSubstr([1,2,3,4], [2,3]) AS l1, -- 返回1
hasSubstr([1,2,3,4], [3,2]) AS l2, -- 返回0
hasSubstr([1,2,3,4], [1,2,4]) AS l3, -- 返回0
hasSubstr([], []) AS l4, -- 返回1
hasSubstr([1, Null], [Null]) AS l5, -- 返回1
hasSubstr([[1, 2], [3, 4], [5, 6]], [[1, 2], [3, 4]]) AS l6, -- 返回1
-- indexOf(arr,x) 返回数组中第一个’x’元素的索引（从1开始），如果’x’元素不存在在数组中，则返回0
indexOf([1, 3, NULL, NULL], NULL) AS m1, -- 返回3
-- countEqual(arr,x) 返回数组中等于x的元素的个数，相当于高阶函数`arrayCount（elem - > elem = x，arr)`
countEqual([1, 2, NULL, NULL], NULL) AS n1, -- 返回2
-- arrayEnumerate(arr) 获取数组索引，其作用等同于`ROW_NUMBER`
arrayEnumerate(['2024-03-01',
'2024-03-02',
'2024-03-03',
'2024-03-01',
'2024-03-01',
'2024-03-02']
) AS o1, -- 返回[1,2,3,4,5,6]
arrayEnumerate([1, 2, 4, NULL, nan]) AS o2, -- 返回[1,2,3,4,5]
-- arrayEnumerateUniq(arr, ...) 返回与源数组大小相同的数组，其中每个元素表示与其下标对应的源数组元素在源数组中出现的次数。例如：`arrayEnumerateUniq[10,20,10,30]）= [1,1,2,1]`
arrayEnumerateUniq([1, 2, 4, NULL, nan]) AS p1, -- 返回[1,1,1,1,1]
arrayEnumerateUniq(['2024-03-01',
'2024-03-02',
'2024-03-03',
'2024-03-01',
'2024-03-01',
'2024-03-02']
) AS p2, -- 返回[1,1,1,2,3,2]
-- 可以使用参数大小相同的多个数组。此时，对于所有阵列中相同位置的元素元组，考虑唯一性
arrayEnumerateUniq([1, 1, 1, 2, 2, 2], [1, 1, 2, 1, 1, 2]) AS p3, -- 返回[1,2,1,1,2,1]
-- arrayEnumerateDense(arr) 返回与源数组大小相同的数组，指示每个元素首次出现在源数组中的位置
arrayEnumerateDense(['2024-03-01',
'2024-03-02',
'2024-03-03',
'2024-03-01',
'2024-03-01',
'2024-03-02']
) AS p4, -- 返回[1,1,1,2,3,2]
-- arrayPopBack 删除数组中的最后一项
arrayPopBack([1, 1, 1, 2, 2, 2]) AS q1, -- 返回[1,1,1,2,2]
-- arrayPopFront 删除数组中的第一项
arrayPopFront([1, 1, 1, 2, 2, 2]) AS r1, -- 返回[1,1,2,2,2]
-- arrayPushBack 添加一个元素到数组的末尾
arrayPushBack([1, 1, 1], 2) AS s1, -- 返回[1,1,1,2]
-- arrayPushFront 添加一个元素到数组的开头
arrayPushFront([1, 1, 1], 2) AS t1, -- 返回[2,1,1,1]
-- arrayResize(array, size[, extender]) 更改数组的长度。`size`为数组所需的长度。如果`size`小于数组的原始大小，则数组将从右侧截断。如果`size`大于数组的初始大小，则使用`extender`值或数组项的数据类型的默认值将数组扩展到右侧。`extender`为扩展数组的值，可以是`NULL`
arrayResize([1], 3) AS u1, -- 返回[1,0,0]
arrayResize([1], 3, NULL) AS u2, -- 返回[1,NULL,NULL]
arrayResize([1, 2, 3, 4, 5], 3, NULL) AS u3, -- 返回[1,2,3]
-- arraySlice(array, offset[, length]) 对数组进行切片，返回一个子数组，包含从指定位置offset的指定长度length的元素
arraySlice([1, 2, NULL, 4, 5], 3, 2) AS v1, -- 返回[NULL,4]
arraySlice([1, 2, NULL, 4, 5], 3, -2) AS v2, -- 返回[NULL]
arraySlice([1, 2, NULL, 4, 5], -3, -2) AS v3, -- 返回[NULL]
arraySlice([1, 2, NULL, 4, 5], -4, 2) AS v4, -- 返回[2,NULL]
arraySlice([1, 2, NULL, 4, 5], -4, -2) AS v5, -- 返回[2,NULL]
arraySlice([1, 2, NULL, 4, 5], 0, -2) AS v6, -- 返回[]
-- arrayUniq(arr, ...) 如果传递一个参数，则计算数组中元素的数量。如果传递了多个参数，则计算多个数组中相应位置的不同元素元组的数量
arrayUniq([1, 1, NULL], ['hello', 'new', 'world']) AS w1, -- 返回3
-- arrayDifference(arr) 计算数组相邻元素之间的差异。返回一个数组，其中第一个元素为0，第二个是`a[1] - a[0]`之差等。结果数组中元素的类型由减法的类型推断规则确定，例如：`UInt8 - UInt8 = Int16`
arrayDifference([1, 2, -3, -4]) AS x1, -- 返回[0,1,-5,-1]
arrayDifference([0, 10000000000000000000]) AS x2, -- 结果溢出，返回[0,-8446744073709551616]
-- arrayDistinct(arr) 返回一个包含所有数组中不同元素的数组
arrayDistinct([1, 2, 2, 3, 1]) AS y1, -- 返回[1,2,3]
-- arrayIntersect(arr1, ...) 返回所有数组元素的交集
arrayIntersect([1, 2], [1, 3], [2, 3]) AS z1, -- 返回[]
arrayIntersect([1, 2], [1, 3], [2, 1]) AS z2, -- 返回[1]
-- arrayReduce(agg_func, arr1, arr2, ...) 将聚合函数应用于数组元素并返回其结果。聚合函数的名称以单引号'max'、'sum'中的字符串形式传递。使用参数聚合函数时，参数在括号中的函数名称后`uniqUpTo(6)`
arrayReduce('max', [1, 2, 3]) AS aa1, -- 返回3
arrayReduce('maxIf', [3, 5], [1, 0]) AS aa2, -- 返回3
arrayReduce('uniqUpTo(3)', [1, 2, 3, 4]) AS aa3, -- 返回4
-- arrayReduceInRanges(agg_func, ranges, arr1, arr2, ...) 将聚合函数应用于给定范围内的数组元素，并返回一个包含与每个范围对应的结果的数组。该函数将返回与多个`arrayReduce(agg_func, arraySlice(arr1, index, length), ...)`相同的结果
arrayReduceInRanges('sum',
[(1, 5), (2, 3), (3, 4), (4, 4)],
[1000000, 200000, 30000, 4000, 500, 60, 7]
) AS bb1, -- 返回[1234500,234000,34560,4567]
-- arrayReverse(arr) 返回一个与原始数组大小相同的数组，其中包含相反顺序的元素，也可以写成`reverse(arr)`
arrayReverse([1, 2, 3]) AS cc1, -- 返回[3,2,1]
-- arrayFlatten(array_of_arrays) 将嵌套的数组展平，也可以写成`flatten(array_of_arrays)`
arrayFlatten([[[1]], [[2], [4, 5]]]) AS dd1, -- 返回[1,2,4,5]
-- arrayCompact(arr) 从数组中删除连续的重复元素。结果值的顺序由源数组中的顺序决定
arrayCompact([1, 1, nan, nan, 2, 3, 2, 3, 3]) AS ee1, -- 返回[1,nan,2,3,2,3]
-- arrayZip(arr1, arr2, ...) 将多个数组组合成一个数组。结果数组包含按列出的参数顺序分组为元组的源数组的相应元素
arrayZip(['a', 'b', 'c'], [5, 2, 1]) AS ff1, -- 返回[('a',5),('b',2),('c',1)]
-- arrayAUC(arr_scores, arr_labels) 计算AUC（ROC曲线下的面积，这是机器学习中的一个概念）
arrayAUC([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1]) AS gg1, -- 返回0.75
-- arrayProduct(arr) 将一个数组中的元素相乘
arrayProduct([1.1, 2.2, 3.3, 4.4, 5.5, 6.6]) AS hh1; -- 返回1275.52392Maps函数
:) SELECT map('key1', 1, 'key2', 2.1) AS a1, -- 返回{'key1':1,'key2':2.1}
-- mapFromArrays(keys, values) 从数组中创建map
mapFromArrays(['a', 'b', 'c', 'd'], [1, 2, 3]) AS b1, -- 返回{'a':1,'b':2,'c':3}
mapFromArrays([1, 2, 3], map('a', 1, 'b', 2, 'c', 3)) AS b2, -- 返回{1:('a',1),2:('b',2),3:('c',3)}
-- extractKeyValuePairs(data[, key_value_delimiter[, pair_delimiter[, quoting_character]]]) 将字符串键值对转换为map
extractKeyValuePairs('name:neymar, age:31 team:psg, nationality:brazil') AS c1, -- 返回{'name':'neymar','age':'31','team':'psg','nationality':'brazil'}
extractKeyValuePairs('name:\'neymar\';\'age\':31;team:psg;nationality:brazil,last_key:last_value', ':', ';,', '\'') AS c2, -- 返回{'name':'neymar','age':'31','team':'psg','nationality':'brazil','last_key':'last_value'}
extractKeyValuePairs('age:a\\x0A\\n\\0') AS c3, -- 返回{'age':'a\\x0A\\n\\0'}
map('John', '33', 'Paula', '31') AS c4, toString(c4) AS c5, extractKeyValuePairs(c5, ':', ',', '\'') AS c6, -- 返回{'John':'33','Paula':'31'} │ {'John':'33','Paula':'31'} │ {'John':'33','Paula':'31'}
-- extractKeyValuePairsWithEscaping 和extractKeyValuePairs()功能相同，但支持转义字符：`\x`, `\N`, `\a`, `\b`, `\e`, `\f`, `\n`, `\r`, `\t`, `\v`和`\0`
extractKeyValuePairsWithEscaping('age:a\\x0A\\n\\0') AS d1, -- 返回{'age':'a\n\n\0'}
-- mapAdd(arg1, arg2 [, ...]) 计算所有键对应的值之和
mapAdd(map(1, 1.1), map(2, 2.2)) AS e1, -- 返回{1:1.1,2:2.2}
mapAdd(map(1, 1.1), map(1, 2.2)) AS e2, -- 返回{1:3.3000000000000003}
mapAdd(([toUInt8(1), 2], [1, 1]), ([toUInt8(1), 2], [1, 1])) AS e3, toTypeName(e3) AS e4, -- 返回([1,2],[2,2]) │ Tuple(Array(UInt8), Array(UInt64))
-- mapSubtract(Tuple(Array, Array), Tuple(Array, Array) [, ...]) 计算所有键，并减去相应的值
mapSubtract(map(1,1), map(1,1)) AS f1, -- 返回{1:0}
mapSubtract(([toUInt8(1), 2], [1, 1]), ([toUInt8(1), 2], [1, 1])) AS f2, toTypeName(f2) AS f3, -- 返回([1,2],[0,0]) │ Tuple(Array(UInt8), Array(UInt64))
-- mapPopulateSeries(map[, max]) 或 mapPopulateSeries(keys, values[, max])，用整数键填充映射中缺少的键值对。更具体地说，该函数返回一个map
-- 在该map中，键形成从最小到最大的键（或指定的最大参数）的序列，步长为1，以及相应的值。如果没有为键指定值，则使用默认值作为值。如果键重复出现，则只有第一个值（按出现顺序）与该键相关联
mapPopulateSeries(map(1, 10, 5, 20), 6) AS g1, -- 返回{1:10,2:0,3:0,4:0,5:20,6:0}
mapPopulateSeries(map(1, 10, 5, 20), 4) AS g2, -- 返回{1:10,2:0,3:0,4:0}
mapPopulateSeries([1,2,4], [11,22,44], 5) AS g3, toTypeName(g3) AS g4, -- 返回([1,2,3,4,5],[11,22,0,44,0]) │ Tuple(Array(UInt8), Array(UInt8))
-- mapContains(map, key) 返回给定的键是否包含在给定的map中
mapContains(map('name', 110, 'age', 2), 'name') AS h1, -- 返回1
-- mapKeys(map) 返回map中所有的键
mapKeys(map('name', 110, 'age', 2, 'gender', 1, 'code', 157849)) AS i1, -- 返回['name','age','gender','code']
-- mapValues(map) 返回map中所有的值
mapValues(map('name', 110, 'age', 2, 'gender', 1, 'code', 157849)) AS j1, -- 返回[110,2,1,157849]
-- mapContainsKeyLike(map, pattern) 如果map包含类指定模式的键，则返回1，否则返回0
mapContainsKeyLike(map('name', 110, 'age', 2, 'gender', 1, 'code', 157849), 'a%') AS k1, -- 返回1
-- mapExtractKeyLike(map, pattern) 给定一个带有字符串键和LIKE模式的map，此函数将返回一个带有元素的map，其中键与模式匹配
mapExtractKeyLike(map('name', 110, 'age', 2, 'gender', 1, 'code', 157849), 'a%') AS l1, -- 返回{'age':2}
-- mapUpdate(map1, map2) 用map2更新map1后返回map1
mapUpdate(map('key1', 0, 'key3', 0), map('key1', 10, 'key2', 20)) AS m1, -- 返回{'key3':0,'key1':10,'key2':20}
-- mapConcat(maps) 根据key相等性来连接多个map。如果具有相同键的元素存在于多个map中，则所有元素都会添加到结果中，但只有第一个元素可以通过运算符[]访问
mapConcat(map('key1', 1, 'key3', 3), map('key2', 2)) AS n1, -- 返回{'key1':1,'key3':3,'key2':2}
mapConcat(map('key1', 1, 'key2', 2), map('key1', 3)) AS n2, n2['key1'] AS n3, -- 返回{'key1':1,'key2':2,'key1':3} │ 1Tuples函数
:) SELECT tuple(1, '2', 3.1415926, NULL, nan, inf) AS a1 -- 创建并返回一个元组(1,'2',3.1415926,NULL,nan,inf)
-- tupleElement(tuple, index, [, default_value]) 或 tupleElement(tuple, name, [, default_value]) 允许从元组中获取列
tupleElement(tuple(1, '2', 3.1415926, NULL, nan, inf), '2') AS b1, -- 返回2
tupleElement(tuple(1, '2', 3.1415926, NULL, nan, inf), 3) AS b2, -- 返回3.1415926
-- untuple(x) 将元组拆解成列
untuple(tuple(1, '2', 3.1415926, NULL, nan, inf)) AS c1, -- 返回1 │ 2 │ 3.1415926 │ ᴺᵁᴸᴸ │ nan │ inf
-- tupleHammingDistance(tuple1, tuple2) 返回两个大小相同的元组之间的汉明距离（是一种衡量两个字符串差异的度量，主要用于计算机科学和信息论中。它定义为两个字符串中每个位置不同的比特数）
tupleHammingDistance((1, 2, 3), (3, 2, 1)) AS d1, -- 返回2
-- tupleToNameValuePairs(tuple) 将元组转换为名值对
-- CREATE TABLE tupletest (col Tuple(userid UInt64, sessionid UInt64)) ENGINE = Memory;
-- INSERT INTO tupletest VALUES (tuple(100, 2502)), (tuple(1, 200));
-- SELECT tupleToNameValuePairs(col) FROM tupletest;
tupleToNameValuePairs(tuple('1', '2')) AS e1, -- 返回[('1','1'),('2','2')]
tupleToNameValuePairs(tuple(1, 2, 3, 4)) AS e2, -- 返回[('1',1),('2',2),('3',3),('4',4)]
-- tuplePlus(tuple1, tuple2) 别名`vectorSum`，计算两个大小相同的元组的相应值之和
tuplePlus((1, 7), (2, 3)) AS f1, -- 返回(3,10)
-- tupleMinus(tuple1, tuple2) 别名`vectorDifference`，计算两个大小相同的元组的相应值之差
tupleMinus((1, 7), (2, 3)) AS g1, -- 返回(-1,4)
-- tupleMultiply(tuple1, tuple2) 元组相乘
tupleMultiply((3, 5), (7, 8)) AS h1, -- 返回(21,40)
-- tupleDivide(tuple1, tuple2) 元组相除
tupleDivide((2, 4), (7, 8)) AS i1, -- 返回(0.2857142857142857,0.5)
tupleDivide((2, 4), (0, 8)) AS i2, -- 返回(inf,0.5)
tupleDivide((0, 4), (0, 8)) AS i3, -- 返回(nan,0.5)
-- tupleNegate(tuple) 计算元组值的负数值
tupleNegate((1, 2)) AS j1, -- 返回(-1,-2)
-- tupleMultiplyByNumber(tuple, number) 返回一个元组，其中所有值都乘以一个数字
tupleMultiplyByNumber((1, 2), -2.1) AS k1, -- 返回(-2.1,-4.2)
-- tupleDivideByNumber(tuple, number) 返回一个元组，其中所有值都除以一个数字
tupleDivideByNumber((3, 2), 2) AS l1, -- 返回(1.5,1)
-- tupleConcat(tuples) 合并元组
tupleConcat((1, 2), ('3', 4.11), (true, NULL)) AS m1, -- 返回(1,2,'3',4.11,true,NULL)
-- tupleIntDiv(tuple_num, tuple_div) 对分子元组和分母元组进行整数除法，并返回商元组
tupleIntDiv((15, 10, 5), (5, 5, 5)) AS n1, -- 返回(3,2,1)
-- tupleIntDivOrZero(tuple_num, tuple_div) 和`tupleIntDiv()`类似，它对分子元组和分母元组进行整数除法，并返回商元组。但它不会抛出0除错误，而是将商返回为0
tupleIntDivOrZero((5, 10, 15), (0, 0, 0)) AS o1, -- 返回(0,0,0)
-- tupleIntDivByNumber(tuple_num, div) 将一个分子元组除以给定的分母，并返回一个商元组
tupleIntDivByNumber((15, 10, 5), 5) AS p1, -- 返回(3,2,1)
-- tupleIntDivOrZeroByNumber(tuple_num, div) 和`tupleIntDivByNumber()`类似，它将分子元组除以给定的分母，并返回商元组。但它不会抛出0除错误，而是将商返回为0
tupleIntDivOrZeroByNumber((15, 10, 5), 0) AS q1, -- 返回(0,0,0)
-- tupleModulo(tuple_num, tuple_mod) 返回两个元组的除法运算的模（余数）的元组
tupleModulo((15, 10, 5), (5, 3, 2)) AS r1, -- 返回(0,1,1)
-- tupleModuloByNumber(tuple_num, div) 返回元组和给定除数的除法运算的模（余数）的元组
tupleModuloByNumber((15, 10, 5), 2) AS s1, -- 返回(1,0,1)
-- flattenTuple(input) 展开嵌套的元组
-- CREATE TABLE t_flatten_tuple(t Tuple(t1 Nested(a UInt32, s String), b UInt32, t2 Tuple(k String, v UInt32))) ENGINE = Memory;
-- INSERT INTO t_flatten_tuple VALUES (([(1, 'a'), (2, 'b')], 3, ('c', 4)));
-- SELECT flattenTuple(t) FROM t_flatten_tuple;
:)时间日期函数
:) SELECT toDateTime(now()) AS time_normal,
-- 仅保留日期
toDate(time_normal) AS date_local,
-- 叶卡捷琳堡日期，仅保留年月日
toDate(time_normal, 'Asia/Yekaterinburg') AS date_yekat,
-- 日期时间转换为字符串
toString(time_normal, 'US/Samoa') AS time_samoa,
toTypeName(time_samoa) AS time_string,
-- 将DateTime中的日期转换为一个固定的日期，同时保留时间部分
toTime(time_normal) AS onlytime,
toDateTime(time_normal, 'Asia/Shanghai') AS datetime_shanghai,
-- 将DateTime转换成Unix时间戳
toUnixTimestamp(time_normal) AS unixtimestamp,
-- 获取年份，月份，季度，小时，分钟，秒钟
toYear(date_local) AS year,
toMonth(date_local) AS month,
-- 一年分为四个季度。1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）
toQuarter(date_local) AS quarter,
toHour(time_normal) AS hour,
toMinute(time_normal) AS minute,
toSecond(time_normal) AS SECOND,
-- 获取 DateTime中的当前日期是当前年份的第几天，当前月份的第几日，当前星期的周几
toDayOfYear(date_local) AS "当前年份中的第几天",
toDayOfMonth(date_local) AS "当前月份的第几天",
toDayOfWeek(time_normal) AS week,
toDate(time_normal, 'Asia/Shanghai') AS date_shanghai,
-- 得到当前年份的第一天,当前月份的第一天，当前季度的第一天，当前日期的开始时刻
toStartOfYear(date_local) AS cur_start_year,
toStartOfMonth(date_local) AS cur_start_month,
toStartOfQuarter(date_local) AS cur_start_quarter,
toStartOfDay(time_normal) AS cur_start_day,
toStartOfHour(time_normal) AS cur_start_hour,
toStartOfMinute(time_normal) AS cur_start_minute,
-- 从过去的某个固定的时间开始，以此得到当前指定的日期的编号
toRelativeYearNum(time_normal),
toRelativeQuarterNum(time_normal),
-- 加一年
addYears(date_local, 1) AS add_years_with_date,
addYears(time_normal, 1) AS add_years_with_date_time,
-- 加一个季度
addQuarters(date_local, 1) AS add_quarters_with_date,
addQuarters(time_normal, 1) AS add_quarters_with_date_time,
-- 加一月
addMonths(date_local, 1) AS add_months_with_date,
addMonths(time_normal, 1) AS add_months_with_date_time,
-- 加一周
addWeeks(date_local, 1) AS add_weeks_with_date,
addWeeks(time_normal, 1) AS add_weeks_with_date_time,
-- 加一天
addDays(date_local, 1) AS add_days_with_date,
addDays(time_normal, 1) AS add_days_with_date_time,
-- 加一小时
addHours(time_normal, 1) AS add_hours_with_date_time,
-- 加一分钟
addMinutes(time_normal, 1) AS add_minutes_with_date_time,
-- 加10秒钟
addSeconds(time_normal, 10) AS add_seconds_with_date_time,
-- 减一年
subtractYears(date_local, 1) AS subtract_years_with_date,
subtractYears(time_normal, 1) AS subtract_years_with_date_time,
-- 减一季度
subtractQuarters(date_local, 1) AS subtract_Quarters_with_date,
subtractQuarters(time_normal, 1) AS subtract_Quarters_with_date_time,
-- 减一月
subtractMonths(date_local, 1) AS subtract_Months_with_date,
subtractMonths(time_normal, 1) AS subtract_Months_with_date_time,
-- 减一周
subtractWeeks(date_local, 1) AS subtract_Weeks_with_date,
subtractWeeks(time_normal, 1) AS subtract_Weeks_with_date_time,
-- 减一天
subtractDays(date_local, 1) AS subtract_Days_with_date,
subtractDays(time_normal, 1) AS subtract_Days_with_date_time,
-- 减一小时
subtractHours(time_normal, 1) AS subtract_Hours_with_date_time,
-- 减一分钟
subtractMinutes(time_normal, 1) AS subtract_Minutes_with_date_time,
-- 减10秒
subtractSeconds(time_normal, 10) AS subtract_Seconds_with_date_time,
-- 时刻
toDateTime('2023-05-07 22:36:00', 'Asia/Shanghai') AS date_time_one,
toDateTime('2024-03-25 04:00:00', 'Asia/Shanghai') AS date_time_two,
-- 不同时间的差值
dateDiff('year', date_time_one, date_time_two) AS diff_years,
dateDiff('month', date_time_one, date_time_two) AS diff_months,
dateDiff('week', date_time_one, date_time_two) AS diff_week,
dateDiff('day', date_time_one, date_time_two) AS diff_days,
dateDiff('hour', date_time_one, date_time_two) AS diff_hours,
dateDiff('minute', date_time_one, date_time_two) AS diff_minutes,
dateDiff('second', date_time_one, date_time_two) AS diff_seconds,
-- 本地当前时间
now() AS now,
dateDiff('year', now, addYears(now, 1)) AS local_diff_years,
dateDiff('month', now, addMonths(now, 2)) AS local_diff_months,
dateDiff('week', now, addWeeks(now, 3)) AS local_diff_week,
dateDiff('day', now, addDays(now, 3)) AS local_diff_days,
dateDiff('hour', now, addHours(now, 3)) AS local_diff_hours,
dateDiff('minute', now, addMinutes(now, 30)) AS local_diff_minutes,
dateDiff('second', now, addSeconds(now, 35)) AS local_diff_seconds;感谢支持
更多内容，请移步《超级个体》。