grafana 使用 clickhouse数据源 clickhouse aggregatefunction

clickhouse之SQL语法–Functions

介绍

Clickhouse 中至少有2中类型的函数

-正常函数(被称为 functions)和聚合函数.

这两个函数完全不同。正常函数被应用到每行(对于每行,函数的结果不依赖与其他行).聚合函数从不同的行中累计相应的值(例如. 他们依赖整个行的集合). 在本章中,我们将讨论正常函数. 对于聚合函数, 查看章节"Aggregate functions". *

- 有第三种函数的类型, 'arrayJoin’函数属于; 表函数也能够被单独提及.

强类型

与标准的 SQL 不同, ClickHouse 有强类型. 换句话说, 它不能在类型之间进行隐式转换. 每个函数可以运行在不同数据类型之上. 这意味着有时你需要使用类型转换函数.

通用的子表达消除

在一个查询中所有的表达式都有相同的 AST (相同的记录或者相同的语法解析) 能够被考虑有完全相同的值. 这些表达式可以拼接的,一次执行. 相同的子查询也能够按照相同的方式消除.

结果类型

所有的函数返回一个单值作为结果 (不是多个值，也不是0值). 通常情况下，结果的类型仅通过参数的类型来定义, 不通过值来定义. 异常是 tupleElement 函数 (a.N 操作符), 同时是 toFixedString 函数.

常量

为了简化, 某些函数的参数仅能带有常数工作. 例如, LIKE 操作符的右侧参数必须是一个常数. 对于常数参数, 几乎所有的函数返回一个常数. 异常是一个函数，可生成随机数. 对于查询来说, ’now’ 函数返回不同的值，如果运行在不同的时间点上,但是结果是一个常数, 常数仅在一次查询内是重要的. 一个常数表达式也考虑到一个常数 (例如, LIKE 操作符右侧从多个常量中构建). 函数能够以不同的方式实现,可带有常数和非常数参数. 但是对于一个常数的结果，对于一个仅包含相同值的列应该互相匹配.

不可变性

对于这些参数, 函数不能改变值

-任何的改变作为结果返回. 因此, 计算不同的函数的结果不依赖哪个函数写到了查询中.

错误处理

一些函数可能抛出一些异常 如果数据是无效的. 在这种情况下, 查询被计算，错误文本将返回给客户端. 对于分布式处理,当一个异常在一个服务器中发生时, 其他服务器也放弃这个查询.

参数表达式赋值

在大多数编程语言中, 对于某些操作符一个参数可能并不被赋值. 通常情况下，对于操作符 &&, ||, ?:. 但是在 ClickHouse 中, 函数的参数 (操作符) 通常是被赋值的. 这是因为列的整个部分被一次性赋值, 而不是单独计算每个行.

为分布式查询处理执行函数

对于分布式查询处理, 查询处理的多个阶段尽量执行在远程服务器上, 剩下的阶段被处理在请求服务器上 (合并中间结果和后续处理) . 这意味着函数能够被执行在不同的服务器上. 例如, 在查询中SELECTf(sum(g(x)))FROM distributed_table GROUP BYh(y),

-如果%%distributed_table%%有至少2个分片,此函数%%g%%和%%h%%在远程服务器上被执行,同时函数%%f%% -在请求服务器上执行.

- 如果 %%distributed_table%% 仅有一个分片, 所有的函数 %%f%%, %%g%%, 和 %%h%% 在这个分片服务器上执行. 函数的结果通常不依赖于哪个服务器执行. 然而, 优势这个非常重要的. 例如, 函数使用词典, 词典存在于服务器内. 另外一个例子是 %%hostName%% 函数, 返回服务器的名称为了通过服务器进行GROUP BY汇总查询. 如果在查询中一个函数在请求服务器中被执行, 但是你需要在远程服务器上处理, 你能够封装它到任意的聚合函数中或者添加到GROUP BY的一个 Key 中.

算术函数

对于所有的算术函数, 结果类型被计算作为适合结果的最小数字类型, 如果有这样一个类型. 最小值基于bits位数, 是否它被分配, 是否它是浮点数. 如果没有足够的 bits 位数, 最高的 bit 类型被采纳. 例如:

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,or Float64。Overflow以C++的方式被生成。

plus(a,b),a + b 操作符

计算数字的总和。你也可以添加带有Date或DateTime的整个数字。对于 Date 类型，添加一个整个数字意味着添加对应的天数。意味着添加对应的秒数。

minus(a,b),a - b 操作符

计算不同.此结果通常是signed.你也能够从date或datetime类型上计算整个数字.想法是相同的-查看以上的’plus’.

multiply(a,b),a * b 操作符

计算数字相乘.

divide(a,b),a / b 操作符

计算数字相除. 结果类型经常是一个浮点类型. 不是整数相除. 对于整数相除, 使用 ‘intDiv’ 函数. 当除以0时,结果为 ‘inf’, ‘-inf’, 或者’nan’.

intDiv(a,b)

计算商数. 除以整数, 四舍五入(通过绝对值). 当除以0或最小负整数时，抛出异常.

计算数字的商。

intDivOrZero(a,b)

不同于 ‘intDiv’ ，它将返回0，当除以0或当除以一个最小负整数时.

modulo(a,b),a % b操作符

在相除之后计算剩余数. 如果参数是浮点数, 它们先转换为整数，通过去除浮点精度部分. 剩余数与C++处理方式相同. 截断相除用于负数. 一个异常将要抛出,当除以0或者当除以最小负数.

negate(a),-a 操作符

计算一个带有反符号的数字. 结果经常带有符号.

abs(a)操作符

计算一个数的绝对值’a’. 如果 a< 0, 它将返回 -a. 对于非符号类型, 什么都不做. 对于符号整型类型, 将返回非符号数字.

gcd(a, b)操作符

返回数字最通用的除数. 当除以0或当除以最小负数后，异常将抛出.

lcm(a, b)

返回最通用的乘数. 当除以0或当除以最小负数后，异常将抛出.

Bit 函数

Bit函数服务于UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64,Float32,或者Float64.结果类型是一个带有位数的整型,与参数的最大位数相等.如果至少有一个参数被指定,结果将是一个符号化数字.如果一个参数是一个浮点数,它将转换到Int64.

比较函数

比较函数总是返回0或1 (Uint8)。

可以比较以下几种类型:

• numbers
• strings and fixed strings
• dates
• dates with times

在每个组内，而不是在不同组之间。

例如，您不能将日期与字符串进行比较。必须使用函数将字符串转换为日期，反之亦然。

字符串按字节进行比较。一个较短的字符串比所有以它开头并且包含至少一个字符的字符串都要小。

请注意。在1.1.54134版本之前，用与c++相同的方法比较带符号和无符号的数字。换句话说，在选择9223372036854775807 > -1这样的情况下，可能会得到错误的结果。这种行为在1.1.54134版本中发生了变化，现在在数学上是正确的。

equals, a = b and a == b operator
 notEquals, a ! operator= b and a <> b
 less, < operator
 greater, > operator
 lessOrEquals, <= operator
 greaterOrEquals, >= operator

逻辑函数

逻辑函数接受任何数字类型，但返回一个等于0或1的UInt8数字。

作为参数的Zero被认为是“false”，而任何非零值都被认为是“true”。

and, AND operator
 or, OR operator
 not, NOT operator 
 xor

类型转换函数

toUInt8, toUInt16, toUInt32, toUInt64
 toInt8, toInt16, toInt32, toInt64
 toFloat32, toFloat64
 toDate, toDateTime
 toUInt8OrZero, toUInt16OrZero, toUInt32OrZero, toUInt64OrZero, toInt8OrZero, toInt16OrZero, toInt32OrZero, toInt64OrZero, toFloat32OrZero, toFloat64OrZero, toDateOrZero, toDateTimeOrZero
 toUInt8OrNull, toUInt16OrNull, toUInt32OrNull, toUInt64OrNull, toInt8OrNull, toInt16OrNull, toInt32OrNull, toInt64OrNull, toFloat32OrNull, toFloat64OrNull, toDateOrNull, toDateTimeOrNull
 toDecimal32(value, S), toDecimal64(value, S), toDecimal128(value, S)

toString

用于在数字、字符串(但不是固定字符串)、日期和日期与时间之间进行转换的函数。所有这些函数都接受一个参数。

在转换为字符串或从字符串转换时，将使用与选项卡分隔格式(以及几乎所有其他文本格式)相同的规则对值进行格式化或解析。如果无法解析字符串，则抛出异常并取消请求。

当将日期转换为数字或反之亦然时，日期对应自Unix纪元开始以来的天数。当将日期与时间转换为数字或反之亦然时，日期与时间对应自Unix纪元开始以来的秒数。

toDate/toDateTime函数的date和date-with-time格式定义如下:

YYYY-MM-DD
YYYY-MM-DD hh:mm:ss

作为一个例外，如果从UInt32、Int32、UInt64或Int64数字类型转换到日期，并且该数字大于或等于65536，则该数字将被解释为Unix时间戳(而不是天数)，并四舍五入到日期。这允许支持写入“toDate(unix_timestamp)”的常见情况，否则这将是一个错误，需要编写更麻烦的“toDate(toDateTime(unix_timestamp))”。

日期和带时间的日期之间的转换以一种自然的方式执行:添加一个空时间或删除时间。

在c++中，数值类型之间的转换使用与不同数值类型之间的赋值相同的规则。

此外，DateTime参数的toString函数可以接受包含时区名称的第二个字符串参数。例如:Asia/Yekaterinburg在本例中，时间是按照指定的时区格式化的。

SELECT
    now() AS now_local,
    toString(now(), 'Asia/Yekaterinburg') AS now_yekat

┌───────────now_local─┬─now_yekat───────────┐
│ 2016-06-15 00:11:21 │ 2016-06-15 02:11:21 │
└─────────────────────┴─────────────────────┘

还请参见toUnixTimestamp函数.

toFixedString (s,N)

将字符串类型参数转换为FixedString(N)类型(长度为N的字符串)。N必须是常量。如果字符串的字节数小于N，则向右传递null字节。如果字符串的字节数超过N，则抛出异常。

toStringCutToZero (s)

接受字符串或FixedString参数。返回在找到的第一个零字节处截断内容的字符串。

例子：

SELECT toFixedString('foo', 8) AS s, toStringCutToZero(s) AS s_cut
┌─s─────────────┬─s_cut─┐
│ foo\0\0\0\0\0 │ foo   │
└───────────────┴───────┘
SELECT toFixedString('foo\0bar', 8) AS s, toStringCutToZero(s) AS s_cut
┌─s──────────┬─s_cut─┐
│ foo\0bar\0 │ foo   │
└────────────┴───────┘

reinterpretAsUInt8, reinterpretAsUInt16, reinterpretAsUInt32, reinterpretAsUInt64
 reinterpretAsInt8, reinterpretAsInt16, reinterpretAsInt32, reinterpretAsInt64
 reinterpretAsFloat32, reinterpretAsFloat64
 reinterpretAsDate, reinterpretAsDateTime

这些函数接受字符串并将字符串开头的字节解释为按主机顺序排列的数字(小端)。如果字符串不够长，函数的工作方式就像用必要的空字节数填充字符串一样。如果字符串比需要的长，则忽略额外的字节。日期被解释为自Unix纪元开始以来的天数，带有时间的日期被解释为自Unix纪元开始以来的秒数。

reinterpretAsString

该函数接受一个数字、日期或带时间的日期，并返回一个字符串，其中包含以主机顺序(小端)表示相应值的字节。空字节从末尾删除。例如，UInt32类型值255是一个长度为一个字节的字符串。

reinterpretAsFixedString

该函数接受一个数字、日期或带时间的日期，并返回一个FixedString，其中包含以主机顺序(小端)表示相应值的字节。空字节从末尾删除。例如，UInt32类型值255是一个固定字符串，长度为一个字节。

CAST(x, t)

将“x”转换为“t”数据类型。还支持语法转换(x作为t)。

例子:

SELECT
    '2016-06-15 23:00:00' AS timestamp,
    CAST(timestamp AS DateTime) AS datetime,
    CAST(timestamp AS Date) AS date,
    CAST(timestamp, 'String') AS string,
    CAST(timestamp, 'FixedString(22)') AS fixed_string

┌─timestamp───────────┬────────────datetime─┬───────date─┬─string──────────────┬─fixed_string──────────────┐
│ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00\0\0\0 │
└─────────────────────┴─────────────────────┴────────────┴─────────────────────┴───────────────────────────┘

转换为FixedString(N)只适用于String或FixedString(N)类型的参数。

支持将类型转换为Nullable和back。例子:

SELECT toTypeName(x) FROM t_null

┌─toTypeName(x)─┐
│ Int8          │
│ Int8          │
└───────────────┘

SELECT toTypeName(CAST(x, 'Nullable(UInt16)')) FROM t_null

┌─toTypeName(CAST(x, 'Nullable(UInt16)'))─┐
│ Nullable(UInt16)                        │
│ Nullable(UInt16)                        │
└─────────────────────────────────────────┘

toIntervalYear, toIntervalQuarter, toIntervalMonth, toIntervalWeek, toIntervalDay, toIntervalHour, toIntervalMinute, toIntervalSecond

将数字类型参数转换为区间类型(持续时间)。interval类型实际上非常有用，您可以使用这种类型的数据直接对Date或DateTime执行算术操作。同时，ClickHouse为声明间隔类型数据提供了更方便的语法。例如:

WITH
    toDate('2019-01-01') AS date,
    INTERVAL 1 WEEK AS interval_week,
    toIntervalWeek(1) AS interval_to_week
SELECT
    date + interval_week,
    date + interval_to_week
┌─plus(date, interval_week)─┬─plus(date, interval_to_week)─┐
│                2019-01-08 │                   2019-01-08 │
└───────────────────────────┴─────────────────────

parseDateTimeBestEffort

将数字类型参数解析为日期或DateTime类型。与toDate和toDateTime不同，parseDateTimeBestEffort可以改进更复杂的日期格式。有关更多信息，请参见链接:复杂日期格式

parseDateTimeBestEffortOrNull

与parseDateTimeBestEffort相同，只是当遇到无法处理的日期格式时返回null。

parseDateTimeBestEffortOrZero

与parseDateTimeBestEffort相同，只是当遇到无法处理的日期格式时，它返回零日期或零日期时间。

为数组服务的函数

empty

为空数组返回1，对于非空数组返回0。结果类型是UInt8。该函数也适用于字符串。

notEmpty

对于空数组返回0，对于非空数组返回1。结果类型是UInt8。该函数也适用于字符串。

length

返回数组中的项目数。结果类型是UInt64。该函数也适用于字符串。

emptyArrayUInt8, emptyArrayUInt16, emptyArrayUInt32, emptyArrayUInt64
emptyArrayInt8, emptyArrayInt16, emptyArrayInt32, emptyArrayInt64
emptyArrayFloat32, emptyArrayFloat64
emptyArrayDate, emptyArrayDateTime
emptyArrayString

接受零参数并返回相应类型的空数组。

emptyArrayToSingle

接受一个空数组并返回一个等于默认值的一个元素的数组。

range(N)

返回从0到N-1的数字数组。以防万一，如果在数据块中创建总长度超过100,000,000个元素的数组，则会引发异常。

array(x1, …), оператор [x1, …]

从函数参数创建一个数组。参数必须是常量，并且具有最小通用类型的类型。必须至少传递一个参数，否则不清楚创建哪种类型的数组。也就是说，你不能使用这个函数来创建一个空数组（为此，使用上面描述的’emptyArray *'函数）。返回一个“Array（T）”类型结果，其中“T”是传递参数中最小的通用类型。

arrayConcat

绑定作为参数传递的数组.

arrayConcat(arrays)

Arguments

arrays – 逗号分隔的数组[值].

Example

SELEC TarrayConcat([1,2],[3,4],[5,6]) AS res

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

arrayElement(arr, n), 操作符 arr[n]

从数组’arr’中获取索引为’n’的元素。'n’必须是任何整数类型。数组中的索引从1开始。支持负数索引。在这种情况下，它从末尾选择相应的元素。例如，'arr [-1]'是数组中的最后一项。

如果索引落在数组边界之外，它将返回一些默认值（数字为0，字符串为空字符串等）。

has(arr, elem)

检查’arr’数组是否有’elem’元素。如果元素不在数组中，则返回0;如果在数组中，则返回1。

indexOf(arr, x)

如果它在数组中，则返回“x”元素的索引（从1开始）;如果不在数组中，则返回0。

countEqual(arr, x)

返回数组中的等于 x 的元素数量. 等于 arrayCount (elem->elem = x, arr).

arrayEnumerate(arr)

返回数组 [1, 2, 3, …, length (arr) ]

函数通常被用于ARRAY JOIN。它允许为每个数组计算 count，在使用ARRAY JOIN后。例如：

SELECT count() AS Reaches,countIf(num=1) AS Hits FROM test.hits ARRAY JOIN GoalsReached,array Enumerate(GoalsReached) AS num WHERE CounterID=160656 LIMIT 10
┌─Reaches─┬──Hits─┐
│ 95606 │ 31406 │
└─────────┴───────┘

在这个例子中，Reaches是转换次数（应用ARRAY JOIN后收到的字符串），Hits是页面浏览量（ARRAY JOIN之前的字符串）。在这种情况下，您可以通过更简单的方式获得相同的结果：

SELECT sum(length(GoalsReached)) AS Reaches, count() AS Hits FROM test.hits WHERE(CounterID=160656) AND notEmpty(GoalsReached)
┌─Reaches─┬──Hits─┐
│ 95606 │ 31406 │
└─────────┴───────┘

该功能也可用于高阶功能。例如，您可以使用它来获取匹配条件的元素的数组索引。

arrayEnumerateUniq(arr, …)

返回与源数组大小相同的数组，为每个元素指示其位置在具有相同值的元素中的位置。例如：arrayEnumerateUniq（[10，20，10，30]）= [1,1，2，1]。

使用ARRAY JOIN和数组元素聚合时，此函数很有用。例：

SELECT Goals.IDASGoalID, sum(Sign)ASReaches, sumIf(Sign,num=1) AS Visits FROM test.visits ARRAYJOIN Goals, arrayEnumerateUniq(Goals.ID) AS num WHERE CounterID = 160656 GROUP BY GoalID ORDERBY Reaches DESC LIMIT 10

┌──GoalID─┬─Reaches─┬─Visits─┐

│ 53225 │ 3214 │ 1097 │

│ 2825062 │ 3188 │ 1097 │

│ 56600 │ 2803 │ 488 │

│ 1989037 │ 2401 │ 365 │

│ 2830064 │ 2396 │ 910 │

│ 1113562 │ 2372 │ 373 │

│ 3270895 │ 2262 │ 812 │

│ 1084657 │ 2262 │ 345 │

│ 56599 │ 2260 │ 799 │

│ 3271094 │ 2256 │ 812 │

└─────────┴─────────┴────────┘

在这个例子中，每个目标ID都有一个转换次数的计算（目标嵌套数据结构中的每个元素都是达到的目标，我们称之为转换）以及会话数量。如果没有ARRAY JOIN，我们可以将会话的数量计算为总和（Sign）。但是在这种特殊情况下，行被嵌套的Goals结构乘以，所以为了在此之后计算每个会话一次，我们将一个条件应用到arrayEnumerateUniq（Goals.ID）函数的值。

arrayEnumerateUniq函数可以将多个大小相同的数组作为参数。在这种情况下，唯一性被考虑为所有数组中相同位置元素的元组。

SELECT array EnumerateUniq([1,1,1,2,2,2],[1,1,2,1,1,2]) AS res

┌─res───────────┐

│ [1,2,1,1,2,1] │

└───────────────┘

当使用ARRAY JOIN和嵌套的数据结构，并在这个结构中的多个元素之间进一步聚合时，这是必要的。

arrayPopBack

删除数组中的最后一项。

arrayPopBack(array)

参数

array – 数组.

例如

SELECT arrayPopBack([1,2,3]) AS res

┌─res───┐

│ [1,2] │

└───────┘

arrayPopFront

从数组中删除第一个元素

arrayPopFront(array)

参数

array – 数组.

示例

SELECT arrayPopFront([1,2,3]) AS res

┌─res───┐

│ [2,3] │

└───────┘

arrayPushBack

在数组的结尾添加一个元素

array PushBack(array,single_value)

参数

array – 数组.

single_value - 单个值。只有数字可以添加到数组中，只有将字符串添加到字符串数组中。在添加数字时，ClickHouse会自动为数组的数据类型设置single_value类型。有关ClickHouse中数据类型的更多信息，请参阅“数据类型”。

示例

SELECT arrayPushBack([‘a’],‘b’) AS res

┌─res───────┐

│ [‘a’,‘b’] │

└───────────┘

arrayPushFront

在数组的开头添加一个元素

array PushFront(array,single_value)

array – 数组.

single_value - 单个值。只有数字可以添加到数组中，只有将字符串添加到字符串数组中。在添加数字时，ClickHouse会自动为数组的数据类型设置single_value类型。有关ClickHouse中数据类型的更多信息，请参阅“数据类型”。

示例

SELECT array PushBack([‘b’],‘a’) AS res

┌─res───────┐

│ [‘a’,‘b’] │

└───────────┘

arraySlice

返回数组的切片.

arraySlice(array,offset[,length])

参数

array – 数组数据.

偏移量 - 从数组的边界偏移。正值表示左侧的偏移量，负值表示右侧的缩进量。数组项的编号从1开始。

长度 - 所需切片的长度。如果你指定一个负值，函数返回一个开放的片段[offset，array_length - length）。如果省略该值，则函数返回切片[offset，the_end_of_array]。

示例

SELECT arraySlice([1,2,3,4,5],2,3) AS res

┌─res─────┐

│ [2,3,4] │

└─────────┘

arrayUniq(arr, …)

如果传递一个参数，它会计算数组中不同元素的数量。如果传递多个参数，它将计算多个数组中相应位置的元素的不同元组的数量。

如果你想得到一个数组中唯一元素的列表，你可以使用arrayReduce(‘groupUniqArray’，arr)。

arrayJoin(arr)

一个特殊的函数. 请查看章节 “ArrayJoin function”.