1.算数函数
--求和SELECT plus(9, 88)as "plus 9+88", plus(10, -10) as "plus 10-10", plus(-10, -10) as " plus-10-10";┌─plus 9+88─┬─plus 10-10─┬─ plus-10-10─┐│ 97 │ 0 │ -20 │└───────────┴────────────┴─────────────┘--差值SELECT minus(10, 5)as "minus 10-5 ", minus(10, -10)as "minus 10-(-10)",minus(-10, -10)as "minus -10-(-10)";┌─minus 10-5 ─┬─minus 10-(-10)─┬─minus -10-(-10)─┐│ 5 │ 20 │ 0 │└─────────────┴────────────────┴────────────-────┘--积SELECT multiply(12, 2), multiply(12, -2), multiply(-12, -2);┌─multiply(12, 2)─┬─multiply(12, -2)─┬─multiply(-12, -2)─┐│ 24 │ -24 │ 24 │└─────────────────┴──────────────────┴───────────────────┘--平均值SELECT divide(12, 4), divide(10, 3), divide(2, 4), divide(-4, -2), divide(-4, 2), divide(-4.5, 3);┌─divide(12, 4)─┬──────divide(10, 3)─┬─divide(2, 4)─┬─divide(-4, -2)─┬─divide(-4, 2)─┬─divide(-4.5, 3)─┐│ 3 │ 3.3333333333333335 │ 0.5 │ 2 │ -2 │ -1.5 │└───────────────┴────────────────────┴──────────────┴────────────────┴───────────────┴─────────────────┘SELECT intDiv(10, 3), divide(10, 3);┌─intDiv(10, 3)─┬──────divide(10, 3)─┐│ 3 │ 3.3333333333333335 │└───────────────┴────────────────────┘SELECT divide(10, 0), divide(-10, 0); -- 出现无穷大字符“ ∞ ”或“ -∞ ”┌─divide(10, 0)─┬─divide(-10, 0)─┐│ inf │ -inf │└───────────────┴────────────────┘SELECT divide(0, 0); -- 特殊字符（类似乱码）┌─divide(0, 0)─┐│ nan │└──────────────┘SELECT intDivOrZero(10, 0); -- 0┌─intDivOrZero(10, 0)─┐│ 0 │└─────────────────────┘--求余数SELECT modulo(10, 3);┌─modulo(10, 3)─┐│ 1 │└───────────────┘SELECT modulo(10.5, 3);┌─modulo(10.5, 3)─┐│ 1.5 │└─────────────────┘--取反SELECT negate(10), negate(-10);┌─negate(10)─┬─negate(-10)─┐│ -10 │ 10 │└────────────┴─────────────┘--绝对值SELECT abs(-10), abs(10);┌─abs(-10)─┬─abs(10)─┐│ 10 │ 10 │└──────────┴─────────┘--最大公约数SELECT gcd(12, 24), gcd(-12, -24), gcd(-12, 24);┌─gcd(12, 24)─┬─gcd(-12, -24)─┬─gcd(-12, 24)─┐│ 12 │ 12 │ 12 │└─────────────┴───────────────┴──────────────┘--最小公倍数SELECT lcm(12, 24), lcm(-12, -24), lcm(-3, 4);┌─lcm(12, 24)─┬─lcm(-12, -24)─┬─lcm(-3, 4)─┐│ 24 │ 24 │ 12 │└─────────────┴───────────────┴────────────┘
2.比较函数
-->>>>>> 比较函数(始终返回0表示false 或 1表示true)SELECT 12 == 12, 12 != 10, 12 == 132, 12 != 12, 12 <> 12;┌─equals(12, 12)─┬─notEquals(12, 10)─┬─equals(12, 132)─┬─notEquals(12, 12)─┬─notEquals(12, 12)─┐│ 1 │ 1 │ 0 │ 0 │ 0 │└────────────────┴───────────────────┴─────────────────┴───────────────────┴───────────────────┘SELECT equals(12, 12), notEquals(12, 10), equals(12, 10), notEquals(12,123);┌─equals(12, 12)─┬─notEquals(12, 10)─┬─equals(12, 10)─┬─notEquals(12, 123)─┐│ 1 │ 1 │ 0 │ 1 │└────────────────┴───────────────────┴────────────────┴────────────────────┘SELECT greater(12, 10), greater(10, 12), greater(12, 12);┌─greater(12, 10)─┬─greater(10, 12)─┬─greater(12, 12)─┐│ 1 │ 0 │ 0 │└─────────────────┴─────────────────┴─────────────────┘SELECT greaterOrEquals(12,10), greaterOrEquals(12,12);┌─greaterOrEquals(12, 10)─┬─greaterOrEquals(12, 12)─┐│ 1 │ 1 │└─────────────────────────┴─────────────────────────┘SELECT less(12, 21), less(12, 10), less(120, 120);┌─less(12, 21)─┬─less(12, 10)─┬─less(120, 120)─┐│ 1 │ 0 │ 0 │└──────────────┴──────────────┴────────────────┘SELECT lessOrEquals(12, 120), lessOrEquals(12, 12);┌─lessOrEquals(12, 120)─┬─lessOrEquals(12, 12)─┐│ 1 │ 1 │└───────────────────────┴──────────────────────┘
3.逻辑函数
-->>>>>> 逻辑操作符（返回0表示false 或 1表示true）SELECT 12==12 or 12!=10;SELECT 12==12 and 12!=10;SELECT not 12, not 0;SELECT or(equals(12, 12), notEquals(12, 10)); --函数表示法：或SELECT and(equals(12, 12), notEquals(12, 10));--函数表示法：且SELECT not(12), not(0);
4.类型转换函数
SELECT toInt8(12.3334343), toFloat32(10.001), toFloat64(1.000040);SELECT toString(now());SELECT now() AS now_local, toString(now(), 'Asia/Yekaterinburg') AS now_yekat;SELECT now() AS now_local, toDate(now()), toDateTime(now()), toUnixTimestamp(now());SELECT '2021-03-31 16: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────────┐│ 2021-03-31 16:00:00 │ 2021-03-31 16:00:00 │ 2021-03-31 │ 2021-03-31 16:00:00 │ 2021-03-31 16:00:00 │└─────────────────────┴─────────────────────┴────────────┴─────────────────────┴─────────────────────┘WITHtoDate('2021-03-31') AS date,toIntervalWeek(1) AS interval_week,toIntervalWeek(1) AS interval_to_week,toIntervalMonth(1) AS interval_to_monthSELECTdate + interval_week,date + interval_to_week,date + interval_to_month;┌─plus(date, interval_week)─┬─plus(date, interval_to_week)─┬─plus(date, interval_to_month)─┐│ 2021-04-07 │ 2021-04-07 │ 2021-04-30 │└───────────────────────────┴──────────────────────────────┴───────────────────────────────┘WITHtoDateTime('2021-03-31 12:10:10') AS datetime,toIntervalHour(1) AS interval_hour,toIntervalHour(1) AS invterval_to_hourSELECTdatetime + interval_hour,datetime + invterval_to_hour;┌─plus(datetime, interval_hour)─┬─plus(datetime, invterval_to_hour)─┐│ 2021-03-31 13:10:10 │ 2021-03-31 13:10:10 │└───────────────────────────────┴───────────────────────────────────┘
5.时间日期函数
SELECTtoDateTime('2019-07-30 10:10:10') AS time,toUnixTimestamp(time) as unixTimestamp,toDate(time) as date_local,toTime(time) as date_time,toMonth(time) as get_month,toQuarter(time) as get_quarter,toHour(time) as get_hour,toMinute(time) as get_minute,toSecond(time) as get_second,toDayOfYear(time) as "当前年份中的第几天",toDayOfMonth(time) as "当前月份的第几天",toDayOfWeek(time) as "星期",toDate(time, 'Asia/Shanghai') AS date_shanghai,toDateTime(time, 'Asia/Shanghai') AS time_shanghai,toStartOfYear(time),toStartOfMonth(time),toStartOfQuarter(time),toStartOfDay(time) AS cur_start_daytime,toStartOfHour(time) as cur_start_hour,toStartOfMinute(time) AS cur_start_minute,toRelativeYearNum(time),toRelativeQuarterNum(time)\GRow 1:──────time: 2021-03-31 10:10:10unixTimestamp: 1617156610date_local: 2021-03-31date_time: 1970-01-02 10:10:10get_month: 3get_quarter: 1get_hour: 10get_minute: 10get_second: 10当前年份中的第几天: 90当前月份的第几天: 31星期: 3date_shanghai: 2021-03-31time_shanghai: 2021-03-31 10:10:10toStartOfYear(toDateTime('2021-03-31 10:10:10')): 2021-01-01toStartOfMonth(toDateTime('2021-03-31 10:10:10')): 2021-03-01toStartOfQuarter(toDateTime('2021-03-31 10:10:10')): 2021-01-01cur_start_daytime: 2021-03-31 00:00:00cur_start_hour: 2021-03-31 10:00:00cur_start_minute: 2021-03-31 10:10:00toRelativeYearNum(toDateTime('2021-03-31 10:10:10')): 2021toRelativeQuarterNum(toDateTime('2021-03-31 10:10:10')): 8084SELECTtoDateTime('2019-07-30 14:27:30') as time,toISOYear(time) AS iso_year,toISOWeek(time) AS iso_week,now() AS cur_dateTime1,today() AS cur_dateTime2,yesterday() AS yesterday,toDate(time) as getY_M_d;┌────────────────time─┬─iso_year─┬─iso_week─┬───────cur_dateTime1─┬─cur_dateTime2─┬──yesterday─┬───getY_M_d─┐│ 2019-07-30 14:27:30 │ 2019 │ 31 │ 2021-03-31 16:48:13 │ 2021-03-31 │ 2021-03-30 │ 2019-07-30 │└─────────────────────┴──────────┴──────────┴─────────────────────┴───────────────┴────────────┴────────────┘SELECTnow() as nowTime,toYYYYMMDDhhmmss(nowTime),toYYYYMMDD(nowTime),toYYYYMM(nowTime);┌─────────────nowTime─┬─toYYYYMMDDhhmmss(now())─┬─toYYYYMMDD(now())─┬─toYYYYMM(now())─┐│ 2021-03-31 16:48:48 │ 20210331164848 │ 20210331 │ 202103 │└─────────────────────┴─────────────────────────┴───────────────────┴─────────────────┘SELECTnow() as now_time,toDateTime('2019-07-31 18:20:30') AS def_datetime,formatDateTime(now_time, '%D') AS now_time_day_month_year,formatDateTime(def_datetime, '%Y') AS def_datetime_year,formatDateTime(def_datetime, '%y') AS def_datetime_year_litter,formatDateTime(def_datetime, '%H') AS hour24,formatDateTime(def_datetime, '%I') AS hour12,formatDateTime(def_datetime, '%p') AS PMorAM,formatDateTime(def_datetime, '%w') AS def_datetime_get_curWeek,formatDateTime(def_datetime, '%F') AS def_datetime_get_date,formatDateTime(def_datetime, '%T') AS def_datetime_get_time,formatDateTime(def_datetime, '%M') AS def_datetime_get_minute,formatDateTime(def_datetime, '%S') AS def_datetime_get_second;Row 1:──────now_time: 2021-03-31 16:51:02def_datetime: 2019-07-31 18:20:30now_time_day_month_year: 03/31/21def_datetime_year: 2019def_datetime_year_litter: 19hour24: 18hour12: 06PMorAM: PMdef_datetime_get_curWeek: 3def_datetime_get_date: 2019-07-31def_datetime_get_time: 18:20:30def_datetime_get_minute: 20def_datetime_get_second: 30-- 1.跳转到之后的日期函数-- 第一种，日期格式（指定日期，需注意时区的问题）WITHtoDate('2021-03-29') AS date,toDateTime('2021-03-29 00:00:00') AS date_timeSELECTaddYears(date, 1) AS add_years_with_date,addYears(date_time, 0) AS add_years_with_date_time;┌─add_years_with_date─┬─add_years_with_date_time─┐│ 2022-03-29 │ 2021-03-29 00:00:00 │└─────────────────────┴──────────────────────────┘-- 第二种，日期格式（当前，本地时间）WITHtoDate(now()) as date,toDateTime(now()) as date_timeSELECTnow() as now_time,addYears(date, 1) AS add_years_with_date,addYears(date_time, 1) AS add_years_with_date_time,addMonths(date, 1) AS add_months_with_date,addMonths(date_time, 1) AS add_months_with_date_time,addWeeks(date, 1) AS add_weeks_with_date,addWeeks(date_time, 1) AS add_weeks_with_date_time,addDays(date, 1) AS add_days_with_date,addDays(date_time, 1) AS add_days_with_date_time,addHours(date_time, 1) AS add_hours_with_date_time,addMinutes(date_time, 1) AS add_minutes_with_date_time,addSeconds(date_time, 10) AS add_seconds_with_date_time,addQuarters(date, 1) AS add_quarters_with_date,addQuarters(date_time, 1) AS add_quarters_with_date_time;Row 1:──────now_time: 2021-03-31 16:53:59add_years_with_date: 2022-03-31add_years_with_date_time: 2022-03-31 16:53:59add_months_with_date: 2021-04-30add_months_with_date_time: 2021-04-30 16:53:59add_weeks_with_date: 2021-04-07add_weeks_with_date_time: 2021-04-07 16:53:59add_days_with_date: 2021-04-01add_days_with_date_time: 2021-04-01 16:53:59add_hours_with_date_time: 2021-03-31 17:53:59add_minutes_with_date_time: 2021-03-31 16:54:59add_seconds_with_date_time: 2021-03-31 16:54:09add_quarters_with_date: 2021-06-30add_quarters_with_date_time: 2021-06-30 16:53:59-- 2.跳转到当前日期之前的函数(函数将Date/DateTime减去一段时间间隔，然后返回Date/DateTime)WITHtoDate(now()) as date,toDateTime(now()) as date_timeSELECTsubtractYears(date, 1) AS subtract_years_with_date,subtractYears(date_time, 1) AS subtract_years_with_date_time,subtractQuarters(date, 1) AS subtract_Quarters_with_date,subtractQuarters(date_time, 1) AS subtract_Quarters_with_date_time,subtractMonths(date, 1) AS subtract_Months_with_date,subtractMonths(date_time, 1) AS subtract_Months_with_date_time,subtractWeeks(date, 1) AS subtract_Weeks_with_date,subtractWeeks(date_time, 1) AS subtract_Weeks_with_date_time,subtractDays(date, 1) AS subtract_Days_with_date,subtractDays(date_time, 1) AS subtract_Days_with_date_time,subtractHours(date_time, 1) AS subtract_Hours_with_date_time,subtractMinutes(date_time, 1) AS subtract_Minutes_with_date_time,subtractSeconds(date_time, 1) AS subtract_Seconds_with_date_time;Row 1:──────subtract_years_with_date: 2020-03-31subtract_years_with_date_time: 2020-03-31 16:59:16subtract_Quarters_with_date: 2020-12-31subtract_Quarters_with_date_time: 2020-12-31 16:59:16subtract_Months_with_date: 2021-02-28subtract_Months_with_date_time: 2021-02-28 16:59:16subtract_Weeks_with_date: 2021-03-24subtract_Weeks_with_date_time: 2021-03-24 16:59:16subtract_Days_with_date: 2021-03-30subtract_Days_with_date_time: 2021-03-30 16:59:16subtract_Hours_with_date_time: 2021-03-31 15:59:16subtract_Minutes_with_date_time: 2021-03-31 16:58:16subtract_Seconds_with_date_time: 2021-03-31 16:59:15-- 计算连个时刻在不同时间单位下的差值-- 第一种：指定时间计算差值示例WITHtoDateTime('2019-07-30 10:10:10', 'Asia/Shanghai') as date_shanghai_one,toDateTime('2020-10-31 11:20:30', 'Asia/Shanghai') as date_shanghai_twoSELECTdateDiff('year', date_shanghai_one, date_shanghai_two) as diff_years,dateDiff('month', date_shanghai_one, date_shanghai_two) as diff_months,dateDiff('week', date_shanghai_one, date_shanghai_two) as diff_week,dateDiff('day', date_shanghai_one, date_shanghai_two) as diff_days,dateDiff('hour', date_shanghai_one, date_shanghai_two) as diff_hours,dateDiff('minute', date_shanghai_one, date_shanghai_two) as diff_minutes,dateDiff('second', date_shanghai_one, date_shanghai_two) as diff_seconds;┌─diff_years─┬─diff_months─┬─diff_week─┬─diff_days─┬─diff_hours─┬─diff_minutes─┬─diff_seconds─┐│ 1 │ 15 │ 65 │ 459 │ 11017 │ 661030 │ 39661820 │└────────────┴─────────────┴───────────┴───────────┴────────────┴──────────────┴──────────────┘-- 第二种：本地当前时间示例WITHtoDateTime('2020-07-30 10:10:10', 'Asia/Shanghai') AS date_shanghai_one,toDateTime('2021-03-31 11:20:30', 'Asia/Shanghai') AS date_shanghai_twoSELECTdateDiff('year', date_shanghai_one, date_shanghai_two) AS diff_years,dateDiff('month', date_shanghai_one, date_shanghai_two) AS diff_months,dateDiff('week', date_shanghai_one, date_shanghai_two) AS diff_week,dateDiff('day', date_shanghai_one, date_shanghai_two) AS diff_days,dateDiff('hour', date_shanghai_one, date_shanghai_two) AS diff_hours,dateDiff('minute', date_shanghai_one, date_shanghai_two) AS diff_minutes,dateDiff('second', date_shanghai_one, date_shanghai_two) AS diff_seconds;┌─diff_years─┬─diff_months─┬─diff_week─┬─diff_days─┬─diff_hours─┬─diff_minutes─┬─diff_seconds─┐│ 1 │ 8 │ 35 │ 244 │ 5857 │ 351430 │ 21085820 │└────────────┴─────────────┴───────────┴───────────┴────────────┴──────────────┴──────────────┘-- timeSlot(StartTime, Duration, [,Size])-- 它返回一个时间数组，其中包括从从“StartTime”开始到“StartTime + Duration 秒”内的所有符合“size”（以秒为单位）步长的时间点-- 作用:搜索在相应会话中综合浏览量是非常有用的。SELECTtimeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600)) AS dateTimeArray,dateTimeArray[0] AS arr_index_0,dateTimeArray[1] AS arr_index_1,dateTimeArray[2] AS arr_index_2,dateTimeArray[3] AS arr_index_3,dateTimeArray[4] AS arr_index_4Row 1:──────dateTimeArray: ['2012-01-01 12:00:00','2012-01-01 12:30:00']arr_index_0: 1970-01-01 08:00:00arr_index_1: 2012-01-01 12:00:00arr_index_2: 2012-01-01 12:30:00arr_index_3: 1970-01-01 08:00:00arr_index_4: 1970-01-01 08:00:00SELECTtimeSlots(now(), toUInt32(600), 20) as dateTimeArray,dateTimeArray[1] as arr_index_1,dateTimeArray[2] as arr_index_2,dateTimeArray[3] as arr_index_3,dateTimeArray[4] as arr_index_4,dateTimeArray[5] as arr_index_5;Row 1:──────dateTimeArray: ['2021-03-31 17:04:20','2021-03-31 17:04:40','2021-03-31 17:05:00','2021-03-31 17:05:20','2021-03-31 17:05:40','2021-03-31 17:06:00','2021-03-31 17:06:20','2021-03-31 17:06:40','2021-03-31 17:07:00','2021-03-31 17:07:20','2021-03-31 17:07:40','2021-03-31 17:08:00','2021-03-31 17:08:20','2021-03-31 17:08:40','2021-03-31 17:09:00','2021-03-31 17:09:20','2021-03-31 17:09:40','2021-03-31 17:10:00','2021-03-31 17:10:20','2021-03-31 17:10:40','2021-03-31 17:11:00','2021-03-31 17:11:20','2021-03-31 17:11:40','2021-03-31 17:12:00','2021-03-31 17:12:20','2021-03-31 17:12:40','2021-03-31 17:13:00','2021-03-31 17:13:20','2021-03-31 17:13:40','2021-03-31 17:14:00','2021-03-31 17:14:20']arr_index_1: 2021-03-31 17:04:20arr_index_2: 2021-03-31 17:04:40arr_index_3: 2021-03-31 17:05:00arr_index_4: 2021-03-31 17:05:20arr_index_5: 2021-03-31 17:05:40-- 指定时间为基准，之后每个元素增加20秒SELECTtimeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600), 20) as cur_dateTimeArray,cur_dateTimeArray[0] as arr_index_0,cur_dateTimeArray[1] as arr_index_1,cur_dateTimeArray[2] as arr_index_2,cur_dateTimeArray[3] as arr_index_3,cur_dateTimeArray[4] as arr_index_4,cur_dateTimeArray[5] as arr_index_5;
6.字符串函数
SELECTlength('hello world') as str_length,empty('hello world'),notEmpty('hello world'),lengthUTF8('hello world'),char_length('hello world'),character_length('hello world'),lower('abcd123--'),upper('abcd123--'),lowerUTF8('abcd123-/*\8asd-\\'),upperUTF8('abcd123--'),isValidUTF8('abcd123--/*\*');SELECT notEmpty(''), notEmpty(NULL), notEmpty('he');str_length: 11empty('hello world'): 0notEmpty('hello world'): 1lengthUTF8('hello world'): 11char_length('hello world'): 11character_length('hello world'): 11lower('abcd123--'): abcd123--upper('abcd123--'): ABCD123--lowerUTF8('abcd123-/*\\8asd-\\'): abcd123-/*\8asd-\upperUTF8('abcd123--'): ABCD123--isValidUTF8('abcd123--/*\\*'): 1-- 2.字符串维度自定义安排SELECT format('{1} {0} {1}', 'World', 'Hello')┌─format('{1} {0} {1}', 'World', 'Hello')─┐│ Hello World Hello │└─────────────────────────────────────────┘SELECT format('{0} {0} {1} {1}', 'one', 'two');┌─format('{0} {0} {1} {1}', 'one', 'two')─┐│ one one two two │└─────────────────────────────────────────┘SELECT format('{} {}', 'Hello', 'World');┌─format('{} {}', 'Hello', 'World')─┐│ Hello World │└───────────────────────────────────┘-- 3.字符串拼接 concat(s1,s2,s3,...)SELECT concat('Hello',' ','World', '!');┌─concat('Hello', ' ', 'World', '!')─┐│ Hello World! │└────────────────────────────────────┘SELECT concatAssumeInjective('Hello',' ','World', '!');┌─concatAssumeInjective('Hello', ' ', 'World', '!')─┐│ Hello World! │└───────────────────────────────────────────────────┘-- 4.字符串截取：substring(s, offset, length), mid(s, offset, length), substr(s, offset, length)-- 以字节为单位截取指定位置字符串，返回以‘offset’位置为开头，长度为‘length’的子串。‘offset’从1开始（与标准SQL相同）。‘offset’和‘length’参数必须是常量。SELECTsubstring('abcdefg', 1, 3),substring('你好，世界', 1, 3),substringUTF8('你好，世界', 1, 3)┌─substring('abcdefg', 1, 3)─┬─substring('你好，世界', 1, 3)─┬─substringUTF8('你好，世界', 1, 3)─┐│ abc │ 你 │ 你好， │└────────────────────────────┴───────────────────────────────┴───────────────────────────────────┘-- 5.字符串拼接：appendTrailingCharIfAbsent(s, c)-- 如果‘s’字符串非空并且末尾不包含‘c’字符，则将‘c’字符附加到末尾。SELECTappendTrailingCharIfAbsent('good', 'c'),appendTrailingCharIfAbsent('goodccc', 'c')┌─appendTrailingCharIfAbsent('good', 'c')─┬─appendTrailingCharIfAbsent('goodccc', 'c')─┐│ goodc │ goodccc │└─────────────────────────────────────────┴────────────────────────────────────────────┘-- 6.字符串编码转换：convertCharset(s, from, to) 返回从‘from’中的编码转换为‘to’中的编码的字符串‘s’。SELECTconvertCharset('hello', 'UTF8', 'Unicode'),convertCharset('hello', 'Unicode', 'UTF8'),convertCharset('hello', 'Unicode', 'ASCII'),convertCharset('hello', 'ascii', 'ascii'),convertCharset('hello', 'UTF8', 'UTF8')Row 1:──────convertCharset('hello', 'UTF8', 'Unicode'): ÿþhelloconvertCharset('hello', 'Unicode', 'UTF8'): 桥汬�convertCharset('hello', 'Unicode', 'ASCII'):convertCharset('hello', 'ascii', 'ascii'): helloconvertCharset('hello', 'UTF8', 'UTF8'): helloSELECTbase64Encode('username+password'),base64Decode('dXNlcm5hbWUrcGFzc3dvcmQ='),tryBase64Decode('dXNlcm5hbWUrcGFzc3dvcmQ=')┌─base64Encode('username+password')─┬─base64Decode('dXNlcm5hbWUrcGFzc3dvcmQ=')─┬─tryBase64Decode('dXNlcm5hbWUrcGFzc3dvcmQ=')─┐│ dXNlcm5hbWUrcGFzc3dvcmQ= │ username+password │ username+password │└───────────────────────────────────┴──────────────────────────────────────────┴─────────────────────────────────────────────┘-- 7.判断字符串是否已什么结尾或结束，返回1：true，0：flase-- endsWith(s, suffix) 返回是否以指定的后缀结尾。如果字符串以指定的后缀结束，则返回1，否则返回0-- startWith(s, prefix) 返回是否以指定的前缀开头。如果字符串以指定的前缀开头，则返回1，否则返回0。SELECTendsWith('string', 'g'),startsWith('string', 'str')┌─endsWith('string', 'g')─┬─startsWith('string', 'str')─┐│ 1 │ 1 │└─────────────────────────┴─────────────────────────────┘-- 8.删除左侧空白字符-- trimLeft(s) 返回一个字符串，用于删除左侧的空白字符-- trimRight(s) 返回一个字符串，用于删除右侧的空白字符-- trimBoth(s) 返回一个字符串，用于删除左侧和右侧的空白字符SELECTtrimLeft(' sdfdgs'),trimRight('abcd '),trimBoth(' abcd ')┌─trimLeft(' sdfdgs')─┬─trimRight('abcd ')─┬─trimBoth(' abcd ')─┐│ sdfdgs │ abcd │ abcd │└─────────────────────┴────────────────────┴────────────────────┘
7.字符串搜索函数
-- pasition(haystack, needle), 显示needle在haystack的第一个出现的位置。SELECTPOSITION('2121stringstrstrstrstr', 'str') AS positionSearch,POSITION('你好，hello,12323-你好，你，好sdfd*dg', '你，好'),positionUTF8('n12你好', '你好') AS positionUTF8,positionCaseInsensitive('ABCDCDEFABCD', 'bc') AS positionCaseInsensitive,locate('hellohellohellohello', 'ello')Row 1:──────positionSearch: 5POSITION('你好，hello,12323-你好，你，好sdfd*dg', '你，好'): 31positionUTF8: 4positionCaseInsensitive: 2locate('hellohellohellohello', 'ello'): 2-- multiSearchAllPositions(haystack, [needle1, needle2, ..., needlen])-- 注意：在所有multiSearch*函数中，由于实现规范，needles的数量应小于2^8。-- 函数返回一个数组，其中包含所有匹配needlei的位置SELECTmultiSearchAllPositions('goodnamegoodnamegoodhellohihihi', ['dn', 'good']) AS multiSearch,multiSearchAllPositionsCaseInsensitive('nameSsdfagpSSDFDFetgfderef', ['SS', 'fa']) AS multiCaseInsensitive,multiSearchAllPositionsUTF8('nameSsdfazz轴功率gpSSDFDFetgfderef', ['Ss', 'fa', 'zz轴']) AS multiSearchUTF8,multiSearchAllPositionsCaseInsensitiveUTF8('nameSsdfazz轴功率gpSSDFDFetgfderef', ['Ss', 'fa', 'zz轴']) AS multiCaseInsensitiveUTF8┌─multiSearch─┬─multiCaseInsensitive─┬─multiSearchUTF8─┬─multiCaseInsensitiveUTF8─┐│ [4,1] │ [5,8] │ [5,8,10] │ [5,8,10] │└─────────────┴──────────────────────┴─────────────────┴──────────────────────────┘-- 检查字符串是否与pattern正则表达式匹配。pattern可以是一个任意的re2正则表达式。re2正则表达式的语法比Perl正则表达式的语法存在更多限制。-- match(haystack, pattern) 匹配到了则返回1，否则返回0SELECTmatch('1232434sadgaDDFSrefds', '[0-9a-zA-Z]'),match('1232321', '[a-z]')┌─match('1232434sadgaDDFSrefds', '[0-9a-zA-Z]')─┬─match('1232321', '[a-z]')─┐│ 1 │ 0 │└───────────────────────────────────────────────┴───────────────────────────┘-- 与match相同，但如果所有正则表达式都不匹配，则返回0；如果任何模式匹配，则返回1。它使用hyperscan库。对于在字符串中搜索子字符串的模式，最好使用“multisearchany”，因为它更高效。-- multiMatchAny(haystack, [pattern1, pattern2, ..., patternn])-- 注意：任何haystack字符串的长度必须小于232字节，否则抛出异常。这种限制是因为hyperscan API而产生的。-- 多个正则表达式对原始字符进行匹配，如若只有一个正则表达式匹配上了则返回1，否则返回0SELECTmultiMatchAny('abcABC', ['[0-9]', '[a-zA-Z]']) AS multiMatchAnyOne,multiMatchAny('123abcABC', ['[0-9]', '[a-zA-Z]']) AS multiMatchAnyTwo,multiMatchAnyIndex('123abcABC', ['[0-9]', '[a-zA-Z]']) AS multiMatchAnyIndex┌─multiMatchAnyOne─┬─multiMatchAnyTwo─┬─multiMatchAnyIndex─┐│ 1 │ 1 │ 1 │└──────────────────┴──────────────────┴────────────────────┘-- 模糊匹配：like()函数，注意大写敏感。-- % 表示任何字节数（包括零字符）-- _ 表示任何一个字节SELECT'hello' LIKE '%h%' AS LIKE_UP,'hello' LIKE 'he' AS like_low,'hello' NOT LIKE 'he' AS not_like,'hello' LIKE '%he%' AS like_litter,'adgadgadfa1232' LIKE '_12_' AS like_func,'sdfasdfasd' LIKE '[a-z]' AS like_func2,'1232423' NOT LIKE '[a-zA-Z]' AS not_like_func┌─LIKE_UP─┬─like_low─┬─not_like─┬─like_litter─┬─like_func─┬─like_func2─┬─not_like_func─┐│ 1 │ 0 │ 1 │ 1 │ 0 │ 0 │ 1 │└─────────┴──────────┴──────────┴─────────────┴───────────┴────────────┴───────────────┘-- 使用字符串截取字符串：extract(haystack, pattern)-- 使用正则表达式截取字符串。如果‘haystack’与‘pattern’不匹配，则返回空字符串。如果正则表达式中不包含子模式，它将获取与整个正则表达式匹配的子串。否则，它将获取与第一个子模式匹配的子串。SELECTextractAll('hellogoodaimantIdeaIDEAfasd123232', '[0-9]'),extractAll('12323dSDFRE', '[A-Z]'),extract('helloclickhouse', '[a-z]')Row 1:──────extractAll('hellogoodaimantIdeaIDEAfasd123232', '[0-9]'): ['1','2','3','2','3','2']extractAll('12323dSDFRE', '[A-Z]'): ['S','D','F','R','E']extract('helloclickhouse', '[a-z]'): h-- ngramSearch(haystack, needle)-- 基于4-gram计算haystack和needle之间的距离：计算两个4-gram集合之间的对称差异，并用它们的基数和对其进行归一化。-- 返回0到1之间的任何浮点数 -- 越接近0则表示越多的字符串彼此相似。-- 如果常量的needle或haystack超过32KB，函数将抛出异常。如果非常量的haystack或needle字符串超过32Kb，则距离始终为1。SELECTngramDistance('hello123456789', '123') AS ngramDistance,ngramDistanceCaseInsensitive('hello123456789', '123') AS ngramDistanceCaseInsensitive,ngramDistanceUTF8('hello123456789', '123') AS ngramDistanceUTF8,ngramDistanceCaseInsensitiveUTF8('hello123456789', '123') AS ngramDistanceCaseInsensitiveUTF8┌─ngramDistance─┬─ngramDistanceCaseInsensitive─┬─ngramDistanceUTF8─┬─ngramDistanceCaseInsensitiveUTF8─┐│ 1 │ 1 │ 0.84615386 │ 0.84615386 │└───────────────┴──────────────────────────────┴───────────────────┴──────────────────────────────────┘-- 注意：对于UTF-8，我们使用3-gram。所有这些都不是完全公平的n-gram距离。-- 我们使用2字节哈希来散列n-gram，然后计算这些哈希表之间的（非）对称差异 - 可能会发生冲突。-- 对于UTF-8不区分大小写的格式，我们不使用公平的tolower函数-- 我们将每个Unicode字符字节的第5位（从零开始）和字节的第一位归零-- 这适用于拉丁语，主要用于所有西里尔字母。
8.数学函数
SELECT1 * e() AS E,1 * pi() AS PI,sqrt(25) AS sqrt_25, --接受一个数值类型的参数并返回它的平方根。cbrt(27) AS cbrt_27, --接受一个数值类型的参数并返回它的立方根。exp(10), --接受一个数值类型的参数并返回它的指数exp10(10), --接受一个数值类型的参数并返回它的10的x次幂。log(10) AS LOG,log2(10) AS LOG2, --接受一个数值类型的参数并返回它的底2对数。ln(e()) AS LOG10; --接受一个数值类型的参数并返回它的自然对数SELECT1 * e() AS E,1 * pi() AS PI,sqrt(25) AS sqrt_25,cbrt(27) AS cbrt_27,exp(10),exp10(10),log(10) AS LOG,log2(10) AS LOG2,ln(e()) AS LOG10Row 1:──────E: 2.718281828459045PI: 3.141592653589793sqrt_25: 5cbrt_27: 3.0000000000000004exp(10): 22026.46579482316exp10(10): 10000000000LOG: 2.3025850938475476LOG2: 3.321928094887362LOG10: 0.9999999987491066-- 示例：三西格玛准则SELECT erf(3 sqrt(2)); -- 0.997SELECTsin(90), -- 返回x的三角正弦值。cos(90), -- 返回x的三角余弦值。tan(90), -- 返回x的三角正切值acos(0), -- 返回x的反三角余弦值。asin(1), -- 返回x的反三角正弦值。atan(45); -- 返回x的反三角正切值。SELECTsin(90),cos(90),tan(90),acos(0),asin(1),atan(45)┌────────────sin(90)─┬─────────────cos(90)─┬────────────tan(90)─┬────────────acos(0)─┬────────────asin(1)─┬───────────atan(45)─┐│ 0.8939966636005579 │ -0.4480736161291701 │ -1.995200412208242 │ 1.5707963267948966 │ 1.5707963267948966 │ 1.5485777614681775 │└────────────────────┴─────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┘-- pow(x, y), power(x, y) 接受x和y两个参数。返回x的y次方。SELECTpow(2, 3), -- 2的三次方pow(3, 2); -- 3的平方SELECTpow(2, 3),pow(3, 2)┌─pow(2, 3)─┬─pow(3, 2)─┐│ 8 │ 9 │└───────────┴───────────┘SELECTintExp2(4), --2^4 接受一个数值类型的参数并返回它的2的x次幂（UInt64）。intExp10(2);--10^2 接受一个数值类型的参数并返回它的10的x次幂（UInt64）。SELECTintExp2(4),intExp10(2)┌─intExp2(4)─┬─intExp10(2)─┐│ 16 │ 100 │└────────────┴─────────────┘
9.取整函数
-- 1.向下取整：floor(x[,N])SELECTfloor(toFloat32(12.08098), 2),floor(toFloat32(12.2323), 2),floor(toFloat32(12.89788), -1),floor(toFloat32(12.0959), 3),floor(toFloat32(12.0987), 3),floor(10, 2)\GRow 1:──────floor(toFloat32(12.08098), 2): 12.08floor(toFloat32(12.2323), 2): 12.23floor(toFloat32(12.89788), -1): 10floor(toFloat32(12.0959), 3): 12.095floor(toFloat32(12.0987), 3): 12.098floor(10, 2): 10-- 2.四舍五入：round(expression [, decimal_places])-- 如果decimal_places=0,则取整数；-- 如果>0,则将值舍入小数点右侧；-- 如果<0,则将小数点左侧的值四舍五入。SELECTround(toFloat32(12.1234), 3),round(toFloat32(12.0025), 3),round(toFloat32(12.0025), 4),round(toFloat32(12.0025002323), 100);┌─round(toFloat32(12.1234), 3)─┬─round(toFloat32(12.0025), 3)─┬─round(toFloat32(12.0025), 4)─┬─round(toFloat32(12.0025002323), 100)─┐│ 12.123 │ 12.002 │ 12.0025 │ 12.002501 │└──────────────────────────────┴──────────────────────────────┴──────────────────────────────┴──────────────────────────────────────┘--示例：SELECTround(toFloat32(10 3)),round(toFloat32(10 3), 2),round(toFloat32(10. 3), 3),round(toFloat32(10. 3), 6)Row 1:──────round(toFloat32(divide(10, 3))): 3round(toFloat32(divide(10, 3)), 2): 3.33round(toFloat32(divide(10., 3)), 3): 3.333round(toFloat32(divide(10., 3)), 6): 3.333333--roundToExp2() 接受一个数字。如果数字小于1，则返回0。否则，它将数字向下舍入到最接近的（整个非负）2的x次幂。SELECTroundToExp2(12.0129),roundToExp2(toFloat32(0.01));┌─roundToExp2(12.0129)─┬─roundToExp2(toFloat32(0.01))─┐│ 8 │ 0.0078125 │└──────────────────────┴──────────────────────────────┘--3.向上取整：ceil(x[, N]) 或者 ceiling(x[, N])SELECTceil(12.34343, 3),ceil(toFloat64(12.34343), 3),ceil(toFloat32(12.34343), 3),ceil(12.0011, 3);┌─ceil(12.34343, 3)─┬─ceil(toFloat64(12.34343), 3)─┬─ceil(toFloat32(12.34343), 3)─┬─ceil(12.0011, 3)─┐│ 12.344 │ 12.344 │ 12.344 │ 12.002 │└───────────────────┴──────────────────────────────┴──────────────────────────────┴──────────────────┘
10.随机函数
>>>>>> 随机函数-- 解释：随机函数使用非加密方式生成【伪随机】数字。-- ① 所有随机函数都只接受一个参数或不接受任何参数。-- ② 您可以向它传递任何类型的参数，但传递的参数将不会使用在任何随机数生成过程中。-- ③ 此参数的唯一目的是防止公共子表达式消除，以便在相同的查询中使用相同的随机函数生成不同的随机数-- rand() 函数：返回一个UInt32类型的随机数字，所有UInt32类型的数字被生成的概率均相等。-- rand64() 函数：返回一个UInt64类型的随机数字，所有UInt64类型的数字被生成的概率均相等。-- randConstant() 函数：返回一个UInt32类型的随机数字，该函数不同之处在于仅为每个数据块参数一个随机数。SELECTrand(),rand(10),rand64(),rand64(10),randConstant(),randConstant();┌─────rand()─┬──rand(10)─┬─────────────rand64()─┬───────────rand64(10)─┬─randConstant()─┬─randConstant()─┐│ 2049392190 │ 540304723 │ 13956764868050898212 │ 18408881627839072904 │ 3624250077 │ 3624250077 │└────────────┴───────────┴──────────────────────┴──────────────────────┴────────────────┴────────────────┘
文章转载自ClickHouse周边，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
