ClickHouse函数介绍
ClickHouse中至少存在两种类型的函数 ：常规函数和聚合函数。 常规函数的工作就像分别为每一行执行一次函数计算一样（对于每一行，函数的结果不依赖于其他行）。 聚合函数则从各行累积一组值（即函数的结果依赖整个结果集）。
1. 常规函数
1.1 算数函数
对于所有算术函数，如果存在这样的类型，则结果类型将计算为结果适合的最小数字类型。 根据位数、是否有符号、是否浮点数，同时取最小值。 如果没有足够的位，则采用最高位类型，如：
SELECT toTypeName(0), toTypeName(0 + 0), toTypeName(0 + 0 + 0), toTypeName(0 + 0 + 0 + 0)
┌─toTypeName(0)─┬─toTypeName(plus(0, 0))─┬─toTypeName(plus(plus(0, 0), 0))─┬─toTypeName(plus(plus(plus(0, 0), 0), 0))─┐
│ UInt8 │ UInt16 │ UInt32 │ UInt64 │
└───────────────┴────────────────────────┴─────────────────────────────────┴──────────────────────────────────────────┘
算术函数适用于UInt8，UInt16，UInt32，UInt64，Int8，Int16，Int32，Int64，Float32或Float64中的任何类型。
相加：
select plus(5, 10);
┌─plus(5, 10)─┐
│ 15 │
└─────────────┘
还可以将Date或DateTime与整数进行相加。在Date的情况下，和整数相加整数意味着添加相应的天数。对于DateTime，这意味着添加相应的秒数。
select plus(cast('2021-07-27' as date),3);
┌─plus(CAST('2021-07-27', 'date'), 3)─┐
│ 2021-07-30 │
└─────────────────────────────────────┘
相减：
select minus(15, 10);
┌─minus(15, 10)─┐
│ 5 │
└───────────────┘
还可以将Date或DateTime与整数进行相减，参考上面的相加计算。
相乘：
select multiply(3, 5);
┌─multiply(3, 5)─┐
│ 15 │
└────────────────┘
相除：
select divide(12, 5);
┌─divide(12, 5)─┐
│ 2.4 │
└───────────────┘
divide不是整数除法，结果类型始终是浮点类型。对于整数除法，请使用’intDiv’函数，‘intDiv’向下舍入取整（按绝对值），除以零或将最小负数除以-1时抛出异常。可以使用’intDivOrZero’，它在除以零或将最小负数除以-1时返回零。
select intDiv(12, 5);
┌─intDiv(12, 5)─┐
│ 2 │
└───────────────┘
select intDivOrZero(12, 0);
┌─intDivOrZero(12, 0)─┐
│ 0 │
└─────────────────────┘
取余：
select modulo(12, 5);
┌─modulo(12, 5)─┐
│ 2 │
└───────────────┘
如果参数是浮点数，则通过删除小数部分将它们预转换为整数。除以零或将最小负数除以-1时抛出异常，可以使用moduloOrZero，它在除以0时结果返回0。
select moduloOrZero(12, 0);
┌─moduloOrZero(12, 0)─┐
│ 0 │
└─────────────────────┘
绝对值：
select abs(-12);
┌─abs(-12)─┐
│ 12 │
└──────────┘
1.2 比较函数
比较函数始终返回0或1（UInt8）。
可以比较以下类型：
- 数字
- String 和 FixedString
- 日期
- 日期时间
以上每个组内的类型均可互相比较，但是对于不同组的类型间不能够进行比较。例如，您无法将日期与字符串进行比较。您必须使用函数将字符串转换为日期，反之亦然。
字符串按字节进行比较。较短的字符串小于以其开头并且至少包含一个字符的所有字符串。
等于，a=b、a==b
不等于，a!=b、a<>b
小于，<
大于, >
小于等于, <=
大于等于, >=
1.3 类型转换函数
该部分内容较多，详见另一篇：ClickHouse类型转换函数
1.4 GEO函数
使用great-circle distance公式计算地球表面两点之间的距离
greatCircleDistance(lon1Deg, lat1Deg, lon2Deg, lat2Deg)
输入参数
- lon1Deg — 第一个点的经度，单位：度，范围： [-180°, 180°]。
- lat1Deg — 第一个点的纬度，单位：度，范围： [-90°, 90°]。
- lon2Deg — 第二个点的经度，单位：度，范围： [-180°, 180°]。
- lat2Deg — 第二个点的纬度，单位：度，范围： [-90°, 90°]。
正值对应北纬和东经，负值对应南纬和西经。
返回值：地球表面的两点之间的距离，以米为单位。当输入参数值超出规定的范围时将抛出异常。
示例：
SELECT greatCircleDistance(55.755831, 37.617673, -55.755831, -37.617673);
┌─greatCircleDistance(55.755831, 37.617673, -55.755831, -37.617673)─┐
│ 14128353 │
└───────────────────────────────────────────────────────────────────┘
1.5 IP函数
- IPv4NumToString(num)：接受一个UInt32（大端）表示的IPv4的地址，返回相应IPv4的字符串表现形式，格式为A.B.C.D（以点分割的十进制数字）。
- IPv4StringToNum(s)：与IPv4NumToString函数相反。如果IPv4地址格式无效，则返回0。
- IPv4NumToStringClassC(num)：与IPv4NumToString类似，但使用xxx替换最后一个字节。
- IPv6NumToString(x)：接受FixedString(16)类型的二进制格式的IPv6地址。以文本格式返回此地址的字符串。
- IPv6StringToNum(s):与IPv6NumToString的相反。如果IPv6地址格式无效，则返回空字节字符串。
select IPv4NumToString(ClientIP) AS k,count() AS c FROM hits_v1 GROUP BY k ORDER BY c DESC LIMIT 5;
┌─k───────────────┬────c─┐
│ 175.215.166.34 │ 8149 │
│ 143.142.175.21 │ 7770 │
│ 148.167.218.200 │ 7696 │
│ 100.167.212.216 │ 7681 │
│ 21.69.235.38 │ 6817 │
└─────────────────┴──────┘
select IPv4NumToStringClassC(ClientIP) AS k,count() AS c FROM hits_v1 GROUP BY k ORDER BY c DESC LIMIT 5;
┌─k───────────────┬────c─┐
│ 175.215.166.xxx │ 8149 │
│ 143.142.175.xxx │ 7770 │
│ 148.167.218.xxx │ 7696 │
│ 100.167.212.xxx │ 7681 │
│ 21.69.235.xxx │ 6817 │
└─────────────────┴──────┘
SELECT IPv6NumToString(toFixedString(unhex('2A0206B8000000000000000000000011'), 16)) AS addr;
┌─addr─────────┐
│ 2a02:6b8::11 │
└──────────────┘
更多常规函数详见：ClickHouse函数
2. 聚合函数
- count：计数行数或非空值，ClickHouse支持以下 count 语法:
- count(expr) 或 COUNT(DISTINCT expr)。
- count() 或 COUNT(*). 该 count() 语法是ClickHouse特定的。
- min：计算最小值。
- max：计算最大值。
- sum：计算总和，只适用于数字。
- avg ：计算算术平均值。
- any：选择第一个遇到的值。查询可以以任何顺序执行，甚至每次都以不同的顺序执行，因此此函数的结果是不确定的。
- deltaSum：计算连续行之间的差值和。如果差值为负，则忽略。
语法：deltaSum(value)，value — 必须是 整型 或者 浮点型 。
返回值：Integer or Float 型的算术差值和。
SELECT deltaSum(arrayJoin([1, 2, 3]));
┌─deltaSum(arrayJoin([1, 2, 3]))─┐
│ 2 │
└────────────────────────────────┘
SELECT deltaSum(arrayJoin([1, 2, 3, 0, 3, 4, 2, 3]));
┌─deltaSum(arrayJoin([1, 2, 3, 0, 3, 4, 2, 3]))─┐
│ 7 │
└───────────────────────────────────────────────┘
SELECT deltaSum(arrayJoin([2.25, 3, 4.5]));
┌─deltaSum(arrayJoin([2.25, 3, 4.5]))─┐
│ 2.25 │
└─────────────────────────────────────┘
- sumMap：语法：sumMap(key, value) 或 sumMap(Tuple(key, value))。
根据 key 数组中指定的键对 value 数组进行求和。传递 key 和 value 数组的元组与传递 key 和 value 的两个数组是同义的。要统计的每一行的 key 和 value (数组)元素的数量必须相同。返回两个数组组成的一个元组: 排好序的 key 和对应 key 的 value 之和。
示例：
CREATE TABLE sum_map(
date Date,
timeslot DateTime,
statusMap Nested(
status UInt16,
requests UInt64
),
statusMapTuple Tuple(Array(Int32), Array(Int32))
) ENGINE = Log;
INSERT INTO sum_map VALUES
('2000-01-01', '2000-01-01 00:00:00', [1, 2, 3], [10, 10, 10], ([1, 2, 3], [10, 10, 10])),
('2000-01-01', '2000-01-01 00:00:00', [3, 4, 5], [10, 10, 10], ([3, 4, 5], [10, 10, 10])),
('2000-01-01', '2000-01-01 00:01:00', [4, 5, 6], [10, 10, 10], ([4, 5, 6], [10, 10, 10])),
('2000-01-01', '2000-01-01 00:01:00', [6, 7, 8], [10, 10, 10], ([6, 7, 8], [10, 10, 10]));
SELECT timeslot,sumMap(statusMap.status, statusMap.requests),sumMap(statusMapTuple) FROM sum_map GROUP BY timeslot;
┌────────────timeslot─┬─sumMap(statusMap.status, statusMap.requests)─┬─sumMap(statusMapTuple)─────────┐
│ 2000-01-01 00:00:00 │ ([1,2,3,4,5],[10,10,20,10,10]) │ ([1,2,3,4,5],[10,10,20,10,10]) │
│ 2000-01-01 00:01:00 │ ([4,5,6,7,8],[10,10,20,10,10]) │ ([4,5,6,7,8],[10,10,20,10,10]) │
└─────────────────────┴──────────────────────────────────────────────┴────────────────────────────────┘
更多聚合函数详见：ClickHouse聚合函数
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
