文章目錄
- 一、日期函數
- 1、時間或日期截取函數（返回非日期）
- 2、時間或日期截取函數（返回日期）
- 3、日期或時間日期生成函數
- 二、類型轉化類函數
- 1、精度保留（非四舍五入）
- 2、字符串轉化為整數（非整數的字符串返回0）
- 3、日期與時間日期轉化
- 4、轉化為字符型
- 5、查看數據類型
- 三、字符串操作
- 1、基本字符串操作
- 2、字符串查找
- 3、字符串替換
- 4、字符串分割
- 5、字符串拼接
- 四、條件語句
- 五、數學函數
- 六、算術函數
- 七、舍入函數
- 八、URL操作函數
- 九、IP操作函數
- 十、表操作
- 1、表連接操作
- 2、limit操作
- 十一、字典操作
- 十二、聚合函數組合器
- 1、-If
- 2、-Merge
一、日期函數
1、時間或日期截取函數（返回非日期）
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
toYear() | 取日期或時間日期的年份 | toYear(toDateTime(‘2022-04-30 11:12:13’)) toYear(toDate(‘2022-04-30’)) | 2022 2022 |
toMonth() | 取日期或時間日期的月份 | toMonth(toDateTime(‘2022-04-30 11:12:13’)) toMonth(toDate(‘2022-04-30’)) | 4 4 |
toDayOfMonth() | 取日期或時間日期的天（1-31） | toDayOfMonth(toDayOfMonth(‘2022-04-30 11:12:13’)) toDayOfMonth(toDayOfMonth(‘2022-04-30’)) | 30 30 |
toDayOfWeek() | 取日期或時間日期的星期（星期一為1，星期日為7） | toDayOfWeek(toDateTime(‘2022-04-30 11:12:13’)) toDayOfWeek(toDate(‘2022-04-30’)) | 6 6 |
toHour() | 取時間日期的小時 | toHour(toDateTime(‘2022-04-30 11:12:13’)) | 11 |
toMinute() | 取時間日期的分鐘 | toMinute(toDateTime(‘2022-04-30 11:12:13’)) | 12 |
toSecond() | 取時間日期的秒 | toSecond(toDateTime(‘2022-04-30 11:12:13’)) | 13 |
toMonday() | 取時間日期最近的周一（返回日期） | toMonday(toDate(‘2022-04-30’)) toMonday(toDateTime(‘2022-04-30 11:12:13’)) | 2022-04-25 2022-04-25 |
toTime() | 將時間日期的日期固定到某一天，保留原始時間 | toTime(toDateTime(‘2022-04-30 11:12:13’)) | 1970-01-02 11:12:13 |
2、時間或日期截取函數（返回日期）
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
toStartOfMonth() | 取日期或時間日期的月份的第一天，返回日期 | toStartOfMonth(toDateTime(‘2022-04-30 11:12:13’)) toStartOfMonth(toDate(‘2022-04-30’)) | 2022-04-01 2022-04-01 |
toStartOfQuarter() | 取日期或時間日期的季度的第一天，返回日期 | toStartOfQuarter(toDateTime(‘2022-04-30 11:12:13’)) toStartOfQuarter(toDate(‘2022-04-30’)) | 2022-04-01 2022-04-01 |
toStartOfYear() | 取日期或時間日期的年份的第一天，返回日期 | toStartOfYear(toDateTime(‘2022-04-30 11:12:13’)) toStartOfYear(toDate(‘2022-04-30’)) | 2022-01-01 2022-01-01 |
toStartOfMinute() | 截取時間日期到分鐘（之后歸零），返回日期 | toStartOfMinute(toDateTime(‘2022-04-30 11:12:13’)) | 2022-04-30 11:12:00 |
toStartOfFiveMinute() | 截取時間日期到最近的5的倍數分鐘（之后歸零），返回日期 | toStartOfFiveMinute(toDateTime(‘2022-04-30 11:12:13’)) | 2022-04-30 11:10:00 |
toStartOfFifteenMinutes() | 截取時間日期到最近的15的倍數分鐘（之后歸零），返回日期 | toStartOfFifteenMinutes(toDateTime(‘2022-04-30 11:12:13’)) | 2022-04-30 11:00:00 |
toStartOfHour() | 截取時間日期到小時（之后歸零），返回日期 | toStartOfHour(toDateTime(‘2022-04-30 11:12:13’)) | 2022-04-30 11:00:00 |
toStartOfDay() | 截取時間日期到天（之后歸零），返回日期 | toStartOfDay(toDateTime(‘2022-04-30 11:12:13’)) | 2022-04-30 00:00:00 |
timeSlot() | 將時間日期中，分鐘大于等于30的歸于30，分鐘數小于30的歸為00 | timeSlot(toDateTime(‘2022-04-30 11:12:13’)) timeSlot(toDateTime(‘2022-04-30 11:32:13’)) | 2022-04-30 11:00:00 2022-04-30 11:30:00 |
3、日期或時間日期生成函數
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
now() | 生成當前時間日期 | now() | 2022-04-30 11:12:13 |
today() | 生成今天的日期 | today() | 2022-04-30 |
yesterday() | 生成昨天的日期 | yesterday() | 2022-04-29 |
二、類型轉化類函數
1、精度保留（非四舍五入）
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
toDecimal32() | 將數值型或者含有非數字的字符串進行精度保留 | toDecimal32(23.12291, 3) toDecimal32(‘_23.12291’, 3) | 23.122 0.000 |
toDecimal64() | 將數值型或者含有非數字的字符串進行精度保留 | toDecimal64(23.12291, 3) toDecimal64(‘_23.12291’, 3) | 23.122 0.000 |
toDecimal128() | 將數值型或者含有非數字的字符串進行精度保留 | toDecimal128(23.12291, 3) toDecimal64(‘_23.12291’, 3) | 23.122 0.000 |
2、字符串轉化為整數（非整數的字符串返回0）
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
toUInt8OrZero() | 將無符號整數字符型轉化為整數型，否則返回0 | toUInt8OrZero(‘123’) toUInt8OrZero(‘123.12’) | 123 0 |
toInt8OrZero() | 將整數字符型轉化為整數型，否則返回0 | toInt8OrZero(‘123’) toInt8OrZero(‘-123’) | 123 -123 |
toFloat32OrZero() | 將數值字符串型轉化為數值型，注意：從toFloat32OrZero開始，丟32的沒有對應的函數 | toFloat32OrZero(‘-123’) toFloat32OrZero(‘123.123’) | -123 123.123 |
3、日期與時間日期轉化
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
toDate() | 將字符型日期轉化為日期型 | toDate(‘2022-04-30’) | 2022-04-30 |
toDateTime() | 將字符型時間日期轉化為時間日期型 | toDateTime(‘2022-04-30 10:10:00’) | 2022-04-30 10:10:00 |
4、轉化為字符型
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
toString() | 將數值型、字符型、日期等轉化為字符型 | toString(‘2022-04-30’) toString(‘123’) | 2022-04-30 123 |
5、查看數據類型
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
toTypeName() | 返回數據的類型 | toTypeName(toString(‘123’)) toTypeName(toDate(‘2018-12-24’)) | String Date |
三、字符串操作
1、基本字符串操作
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
empty() | 判斷字符串是空為1，否則為0 | empty(‘’) empty(‘123a’) | 1 0 |
notEmpty() | 判斷字符串是非空為1，否則為0 | notEmpty(‘’) notEmpty(‘123a’) | 0 1 |
length() | 返回字符串的長度 | length(‘’) length(‘123a’) | 0 4 |
lower() | 將字符串轉為小寫 | lower(‘aBc’) | abc |
upper() | 將字符串轉為大寫 | upper(‘aBc’) | ABC |
reverse() | 將字符串反轉 | reverse(‘abc’) | cba |
substring(s, offset, length) | 字符串截取 | substring(‘123abcABC’, 2, 3) | 23a |
appendTrailingCharIfAbsent(s, c) | 如果字符串s非空，則將s后追加一個字符c(s最后一個字符與c不同)，否則不處理 | appendTrailingCharIfAbsent(‘123abc’, ‘b’) appendTrailingCharIfAbsent(‘123abc’, ‘c’) | 123abcb 123abc |
2、字符串查找
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
match(haystack,pattern) | 字符串正則匹配，返回0或1 | match(‘avhsca’,‘vh’) | 1 |
extract(haystack,pattern) | 返回匹配到的第一個子串 | extract(‘iioomAj12123124OOBJB’, ‘\d+’) | 12123124 |
extractAll(haystack,pattern) | 返回匹配到的所有子串，輸出列表 | extractAll(‘iioomAj12123124OOBJ123B’, ‘\d+’) | [12123124,123] |
like(haystack,pattern) | 匹配到的數據返回1，否則返回0 | like(‘avhsca’,‘%vh%’) like(‘avhsca’,‘%vabjh%’) | 1 0 |
notLike(haystack, pattern) | 與like()函數相反 | notLike(‘avhsca’,‘%vh%’) notLike(‘avhsca’,‘%vabjh%’) | 0 1 |
3、字符串替換
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
replaceOne(haystack,pattern,replacement) | 替換第一個匹配到的pattern | replaceOne(‘asd123cbbj464sd’, ‘sd’, ‘-’) | a-123cbbj464sd |
replaceAll(haystack,pattern,replacement) | 替換所有匹配到的pattern | replaceAll(‘asd123cbbj464sd’, ‘sd’, ‘-’) | a-123cbbj464- |
replaceRegexpOne(haystack, pattern, replacement) | 正則匹配替換第一個匹配到的pattern | replaceRegexpOne(‘Hello, World!’, ‘o’, ‘-’) | Hell-, World! |
replaceRegexpAll(haystack,pattern,replacement) | 正則匹配替換所有匹配到的pattern | replaceRegexpAll(‘Hello, World!’, ‘o’, ‘-’) | Hell-, W-rld! |
4、字符串分割
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
splitByChar(separator, s) | 以單個字符分割字符串 | splitByChar(‘-’, ‘qw-asaf-asfqw-2312-asd’) | ['qw,‘asaf’,‘asfqw’,‘2312’,‘asd’] |
splitByString(separator, s) | 以單個或多個字符分割字符串 | splitByString(‘-’, ‘qw-asaf-asfqw-2312-asd’) splitByString(‘-a’, ‘qw-asaf-asfqw-2312-asd’) | [‘qw’,‘asaf’,‘asfqw’,‘2312’,‘asd’] [‘qw’,‘saf’,‘sfqw-2312’,‘sd’] |
5、字符串拼接
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
concat(s1,s2,…) | 將字符串拼接 | concat(‘123’, ‘abc’, ‘ABC’) | 123abcABC |
四、條件語句
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
if(cond,then,else) | 條件輸出 | if(1 > 2, ‘正確’, ‘錯誤’) | 錯誤 |
multiIf(cond_1, then_1, cond_2, then_2…else) | 多條件輸出 | multiIf(1 > 2, ‘正確’, 2 < 0, ‘正確’, ‘錯誤’) | 錯誤 |
五、數學函數
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
e() | 返回e的值 | e() | 2.718281828459045 |
pi() | 返回pi的值 | pi() | 3.141592653589793 |
exp(x) | 返回e的x次方 | exp(1) | 2.718281828459045 |
exp2(x) | 返回2的x次方 | exp2(2) | 4 |
exp10(x) | 返回10的x次方 | exp10(1) | 10 |
log(x) | 返回log以e為底的對數值 | log(e()) | 1 |
log2(x) | 返回log以2為底的對數值 | log2(2) | 1 |
log10(x) | 返回log以10為底的對數值 | log10(100) | 2 |
sqrt(x) | 對x開平方 | sqrt(4) | 2 |
cbrt(x) | 對x開立方 | cbrt(8) | 2 |
pow(x, y) | 返回x的y次方 | pow(2, 3) | 8 |
六、算術函數
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
plus(a, b) | 計算數值總和 | plus(50,1) | 51 |
minus(a, b) | 計算數值之差，結果總是有符號的 | minus(10,1) | 9 |
multiply(a, b) | 計算數值的乘積 | multiply(1, 1) | 1 |
divide(a, b) | 計算數值的商 ,結果類型始終是浮點類型。 | divide(1, 1) | 1 |
intDiv(a,b) | 計算數值的商，向下舍入取整（按絕對值），除以零或將最小負數除以-1時拋出異常。 | intDiv(1, 1) | 1 |
intDivOrZero(a,b) | 計算數值的商，向下舍入取整（按絕對值），與’intDiv’的不同之處在于它在除以零或將最小負數除以-1時返回零。 | intDivOrZero(1, 1) | 1 |
modulo(a, b) | 計算除法后的余數。除以零或將最小負數除以-1時拋出異常。 | modulo(1, 1) | 1 |
moduloOrZero(a, b) | 計算除法后的余數。除以零或將最小負數除以-1時拋出異常。和modulo不同之處在于，除以0時結果返回0。 | moduloOrZero(1, 1) | 0 |
negate(a) | 通過改變數值的符號位對數值取反，結果總是有符號的 | negate(1) | -1 |
abs(a) | 計算數值（a）的絕對值 | abs(-1) | 1 |
gcd(a,b) | 返回數值的最大公約數。除以零或將最小負數除以-1時拋出異常。 | gcd(12,3) | 3 |
lcm(a,b) | 返回數值的最小公倍數。除以零或將最小負數除以-1時拋出異常。 | lcm(12,3) | 12 |
七、舍入函數
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
floor(x[, N]) | 向下取數 | floor(123.883, 1) floor(123.883, -1) | 123.8 120 |
ceil(x[, N]) | 向上取數 | ceil(123.883, 1) ceil(123.883, -1) | 123.9 130 |
round(x[, N]) | 四舍五入 | round(123.883, 1) round(123.883, -1) | 123.9 120 |
更詳細四舍五入見：ClickHouse 四舍五入函數
八、URL操作函數
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
protocol() | 返回URL的協議類型 | protocol(‘http://www.baidu.com.cn’) | http |
domain() | 返回URL的域名 | domain(‘http://www.baidu.com.cn’) | www.baidu.com.cn |
domainWithoutWWW() | 返回URL不帶www的域名 | domainWithoutWWW(‘http://www.baidu.com.cn’) | baidu.com.cn |
topLevelDomain() | 返回頂級域名 | topLevelDomain(‘http://www.baidu.com.cn’) | cn |
firstSignificantSubdomain() | Returns the “first significant subdomain”. | firstSignificantSubdomain(‘http://www.baidu.com.cn’) | baidu |
cutToFirstSignificantSubdomain() | cutToFirstSignificantSubdomain(‘http://www.baidu.com.cn’) | baidu.com.cn | |
path() | 返回URL的路徑 | path(‘https://www.baidu.com/s?wd=split’) | /s |
pathFull() | 返回URL的完整路徑 | pathFull(‘https://www.baidu.com/s?wd=split’) | /s?wd=split |
queryString() | 返回URL的參數（查詢字符串） | queryString(‘https://www.baidu.com/s?wd=split’) | wd=split |
extractURLParameters() | 以列表的形式返回URL的參數 | extractURLParameters(‘https://www.baidu.com/s?wd=split&name=tom’) | [‘wd=split’,‘name=tom’] |
extractURLParameterNames() | 以列表的形式返回URL的參數名 | extractURLParameterNames(‘https://www.baidu.com/s?wd=split&name=tom’) | [‘wd’,‘name’] |
cutQueryString() | 返回URL？（參數）前面的內容 | cutQueryString(‘https://www.baidu.com/s?wd=split&name=tom’) | https://www.baidu.com/s |
九、IP操作函數
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
IPv4StringToNum(s) | 將IPV4轉為數值，非IPV4的轉化為0 | IPv4StringToNum(‘23.217.198.69’) IPv4StringToNum(‘adwh.124.qwfqw’) | 400148037 0 |
IPv4NumToString(num) | 將數值轉為IPV4 | IPv4NumToString(400148037) | 23.217.198.69 |
IPv4NumToStringClassC(num) | 將數值轉為IPV4，且最后的段位用xxx代替 | IPv4NumToStringClassC(400148037) | 23.217.198.xxx |
十、表操作
1、表連接操作
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
INNER JOIN | 內連接 | select table_A.id from table_A inner join table_B on table_A.id = table_B.id | 返回 A表 與 B表 的公共部分 |
LEFT OUTER JOIN | 左外連接 | select table_A.id from table_A left outer join on table_A.id = table_B.id | 返回 A表 不在 B表 中的部分 |
RIGHT OUTER JOIN | 右外連接 | select table_A.id from table_A right outer join on table_A.id = table_B.id | 返回 B表 不在 A表 中的部分 |
FULL OUTER JOIN | 全外連接 | select table_A.id from table_A full outer join on table_A.id = table_B.id | 返回 A 與 B表 全部，沒有為NULL |
2、limit操作
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
LIMIT N | 查詢N條數據，一般跟ORDER BY 連用 | ORDER BY hit DESC LIMIT 10 | 按照hit列降序取前10條 |
LIMIT N BY Clause | 按照Clause列查詢N條數據，一般跟ORDER BY 連用 | SELECT date， domain, count(1) AS hit from db.tb where…GROUP BY date， domain,ORDER BY hit DESCLIMIT 10 BY date | 取每天TOP10的域名 |
十一、字典操作
| 函數 | 說明 | 舉例 | 結果 |
|---|---|---|---|
dictGetString() | 字典映射 | dictGetString(‘ck_abc_dic’, ‘ck_value’, tuple(_abc)) |
十二、聚合函數組合器
1、-If
-If可以加到任何函數之后。加了 -If 之后聚合函數需要接受一個額外的參數，一個條件，如果條件滿足，那聚合函數處理當前的數據，如果不滿足，那返回默認值。
