ClickHouse函數(shù)介紹

ClickHouse中至少存在兩種類型的函數(shù) ：常規(guī)函數(shù)和聚合函數(shù)。 常規(guī)函數(shù)的工作就像分別為每一行執(zhí)行一次函數(shù)計算一樣（對于每一行，函數(shù)的結(jié)果不依賴于其他行）。 聚合函數(shù)則從各行累積一組值（即函數(shù)的結(jié)果依賴整個結(jié)果集）。
1. 常規(guī)函數(shù)
1.1 算數(shù)函數(shù)
對于所有算術(shù)函數(shù)，如果存在這樣的類型，則結(jié)果類型將計算為結(jié)果適合的最小數(shù)字類型。 根據(jù)位數(shù)、是否有符號、是否浮點(diǎn)數(shù)，同時取最小值。 如果沒有足夠的位，則采用最高位類型，如：

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 │ └───────────────┴────────────────────────┴─────────────────────────────────┴──────────────────────────────────────────┘

算術(shù)函數(shù)適用于UInt8，UInt16，UInt32，UInt64，Int8，Int16，Int32，Int64，Float32或Float64中的任何類型。

相加：

select plus(5, 10); ┌─plus(5, 10)─┐ │ 15 │ └─────────────┘

還可以將Date或DateTime與整數(shù)進(jìn)行相加。在Date的情況下，和整數(shù)相加整數(shù)意味著添加相應(yīng)的天數(shù)。對于DateTime，這意味著添加相應(yīng)的秒數(shù)。

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與整數(shù)進(jìn)行相減，參考上面的相加計算。

相乘：

select multiply(3, 5); ┌─multiply(3, 5)─┐ │ 15 │ └────────────────┘

相除：

select divide(12, 5); ┌─divide(12, 5)─┐ │ 2.4 │ └───────────────┘

divide不是整數(shù)除法，結(jié)果類型始終是浮點(diǎn)類型。對于整數(shù)除法，請使用’intDiv’函數(shù)，‘intDiv’向下舍入取整（按絕對值），除以零或?qū)⒆钚∝?fù)數(shù)除以-1時拋出異常。可以使用’intDivOrZero’，它在除以零或?qū)⒆钚∝?fù)數(shù)除以-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 │ └───────────────┘

如果參數(shù)是浮點(diǎn)數(shù)，則通過刪除小數(shù)部分將它們預(yù)轉(zhuǎn)換為整數(shù)。除以零或?qū)⒆钚∝?fù)數(shù)除以-1時拋出異常，可以使用moduloOrZero，它在除以0時結(jié)果返回0。

select moduloOrZero(12, 0); ┌─moduloOrZero(12, 0)─┐ │ 0 │ └─────────────────────┘

絕對值：

select abs(-12); ┌─abs(-12)─┐ │ 12 │ └──────────┘

1.2 比較函數(shù)
比較函數(shù)始終返回0或1（UInt8）。
可以比較以下類型：

• 數(shù)字
• String 和 FixedString
• 日期
• 日期時間

以上每個組內(nèi)的類型均可互相比較，但是對于不同組的類型間不能夠進(jìn)行比較。例如，您無法將日期與字符串進(jìn)行比較。您必須使用函數(shù)將字符串轉(zhuǎn)換為日期，反之亦然。
字符串按字節(jié)進(jìn)行比較。較短的字符串小于以其開頭并且至少包含一個字符的所有字符串。

等于，a=b、a==b 不等于，a!=b、a<>b 小于，< 大于, > 小于等于, <= 大于等于, >=

1.3 類型轉(zhuǎn)換函數(shù)
該部分內(nèi)容較多，詳見另一篇：ClickHouse類型轉(zhuǎn)換函數(shù)

1.4 GEO函數(shù)
使用great-circle distance公式計算地球表面兩點(diǎn)之間的距離

greatCircleDistance(lon1Deg, lat1Deg, lon2Deg, lat2Deg)

輸入?yún)?shù)

• lon1Deg — 第一個點(diǎn)的經(jīng)度，單位：度，范圍： [-180°, 180°]。
• lat1Deg — 第一個點(diǎn)的緯度，單位：度，范圍： [-90°, 90°]。
• lon2Deg — 第二個點(diǎn)的經(jīng)度，單位：度，范圍： [-180°, 180°]。
• lat2Deg — 第二個點(diǎn)的緯度，單位：度，范圍： [-90°, 90°]。

正值對應(yīng)北緯和東經(jīng)，負(fù)值對應(yīng)南緯和西經(jīng)。

返回值：地球表面的兩點(diǎn)之間的距離，以米為單位。當(dāng)輸入?yún)?shù)值超出規(guī)定的范圍時將拋出異常。

示例：

SELECT greatCircleDistance(55.755831, 37.617673, -55.755831, -37.617673); ┌─greatCircleDistance(55.755831, 37.617673, -55.755831, -37.617673)─┐ │ 14128353 │ └───────────────────────────────────────────────────────────────────┘

1.5 IP函數(shù)

• IPv4NumToString(num)：接受一個UInt32（大端）表示的IPv4的地址，返回相應(yīng)IPv4的字符串表現(xiàn)形式，格式為A.B.C.D（以點(diǎn)分割的十進(jìn)制數(shù)字）。
• IPv4StringToNum(s)：與IPv4NumToString函數(shù)相反。如果IPv4地址格式無效，則返回0。
• IPv4NumToStringClassC(num)：與IPv4NumToString類似，但使用xxx替換最后一個字節(jié)。
• IPv6NumToString(x)：接受FixedString(16)類型的二進(jìn)制格式的IPv6地址。以文本格式返回此地址的字符串。
• IPv6StringToNum(s):與IPv6NumToString的相反。如果IPv6地址格式無效，則返回空字節(jié)字符串。

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 │ └──────────────┘

更多常規(guī)函數(shù)詳見：ClickHouse函數(shù)

2. 聚合函數(shù)

count：計數(shù)行數(shù)或非空值，ClickHouse支持以下 count 語法:

• count(expr) 或 COUNT(DISTINCT expr)。
• count() 或 COUNT(*). 該 count() 語法是ClickHouse特定的。

min：計算最小值。

max：計算最大值。

sum：計算總和，只適用于數(shù)字。

avg ：計算算術(shù)平均值。

any：選擇第一個遇到的值。查詢可以以任何順序執(zhí)行，甚至每次都以不同的順序執(zhí)行，因此此函數(shù)的結(jié)果是不確定的。

deltaSum：計算連續(xù)行之間的差值和。如果差值為負(fù)，則忽略。
語法：deltaSum(value)，value — 必須是 整型 或者 浮點(diǎn)型 。
返回值：Integer or Float 型的算術(shù)差值和。

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))。
根據(jù) key 數(shù)組中指定的鍵對 value 數(shù)組進(jìn)行求和。傳遞 key 和 value 數(shù)組的元組與傳遞 key 和 value 的兩個數(shù)組是同義的。要統(tǒng)計的每一行的 key 和 value (數(shù)組)元素的數(shù)量必須相同。返回兩個數(shù)組組成的一個元組: 排好序的 key 和對應(yīng) 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]) │ └─────────────────────┴──────────────────────────────────────────────┴────────────────────────────────┘

更多聚合函數(shù)詳見：ClickHouse聚合函數(shù)

總結(jié)

以上是生活随笔為你收集整理的ClickHouse函数介绍的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。