clickhouse日期函数
生活随笔
收集整理的這篇文章主要介紹了
clickhouse日期函数
小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.
轉(zhuǎn)載：日期函數(shù)
參考：ck官網(wǎng)
1 Clickhouse 時(shí)間日期函數(shù)
2
3 注：所有的時(shí)間日期函數(shù)都可以在第二個(gè)可選參數(shù)中接受時(shí)區(qū)參數(shù)。示例：Asia / Yekaterinburg。在這種情況下，它們使用指定的時(shí)區(qū)而不是本地（默認(rèn)）時(shí)區(qū)。
4
5 SELECT
6 toDateTime('2016-06-15 23:00:00') AS time,
7 toDate(time) AS date_local,
8 toDate(time, 'Asia/Yekaterinburg') AS date_yekat,
9 toString(time, 'US/Samoa') AS time_samoa
10
11 ┌────────────────time─┬─date_local─┬─date_yekat─┬─time_samoa──────────┐
12 │ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-16 │ 2016-06-15 09:00:00 │
13 └─────────────────────┴────────────┴────────────┴─────────────────────┘
14
15
16 常用時(shí)間函數(shù)：
17
18 now() // 2020-04-01 17:25:40 取當(dāng)前時(shí)間
19 toYear() // 2020 取日期中的年份
20 toMonth() // 4 取日期中的月份
21 today() // 2020-04-01 今天的日期
22 yesterday() // 2020-03-31 昨天的額日期
23 toDayOfYear() // 92 取一年中的第幾天
24 toDayOfWeek() // 3 取一周中的第幾天
25 toHour() //17 取小時(shí)
26 toMinute() //25 取分鐘
27 toSecond() //40 取秒
28 toStartOfYear() //2020-01-01 取一年中的第一天
29 toStartOfMonth() //2020-04-01 取當(dāng)月的第一天
30
31 formatDateTime(now(),'%Y-%m-%d') // 2020*04-01 指定時(shí)間格式
32 toYYYYMM() //202004
33 toYYYYMMDD() //20200401
34 toYYYYMMDDhhmmss() //20200401172540
35 dateDiff()
36 ......
37
38 SELECT
39 toDateTime('2019-07-30 10:10:10') AS time,
40
41 -- 將DateTime轉(zhuǎn)換成Unix時(shí)間戳
42 toUnixTimestamp(time) as unixTimestamp,
43
44 -- 保留 時(shí)-分-秒
45 toDate(time) as date_local,
46 toTime(time) as date_time, -- 將DateTime中的日期轉(zhuǎn)換為一個(gè)固定的日期，同時(shí)保留時(shí)間部分。
47
48 -- 獲取年份，月份，季度，小時(shí)，分鐘，秒鐘
49 toYear(time) as get_year,
50 toMonth(time) as get_month,
51
52 -- 一年分為四個(gè)季度。1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）
53 toQuarter(time) as get_quarter,
54 toHour(time) as get_hour,
55 toMinute(time) as get_minute,
56 toSecond(time) as get_second,
57
58 -- 獲取 DateTime中的當(dāng)前日期是當(dāng)前年份的第幾天，當(dāng)前月份的第幾日，當(dāng)前星期的周幾
59 toDayOfYear(time) as "當(dāng)前年份中的第幾天",
60 toDayOfMonth(time) as "當(dāng)前月份的第幾天",
61 toDayOfWeek(time) as "星期",
62 toDate(time, 'Asia/Shanghai') AS date_shanghai,
63 toDateTime(time, 'Asia/Shanghai') AS time_shanghai,
64
65 -- 得到當(dāng)前年份的第一天,當(dāng)前月份的第一天，當(dāng)前季度的第一天，當(dāng)前日期的開始時(shí)刻
66 toStartOfYear(time),
67 toStartOfMonth(time),
68 toStartOfQuarter(time),
69 toStartOfDay(time) AS cur_start_daytime,
70 toStartOfHour(time) as cur_start_hour,
71 toStartOfMinute(time) AS cur_start_minute,
72
73 -- 從過去的某個(gè)固定的時(shí)間開始，以此得到當(dāng)前指定的日期的編號(hào)
74 toRelativeYearNum(time),
75 toRelativeQuarterNum(time);
76
77 獲取未來時(shí)間的函數(shù)：
78
79 -- 第一種，日期格式（指定日期，需注意時(shí)區(qū)的問題）
80 WITH
81 toDate('2019-09-09') AS date,
82 toDateTime('2019-09-09 00:00:00') AS date_time
83 SELECT
84 addYears(date, 1) AS add_years_with_date,
85 addYears(date_time, 0) AS add_years_with_date_time;
86
87 -- 第二種，日期格式（當(dāng)前，本地時(shí)間）
88 WITH
89 toDate(now()) as date,
90 toDateTime(now()) as date_time
91 SELECT
92 now() as now_time,-- 當(dāng)前時(shí)間
93 -- 之后1年
94 addYears(date, 1) AS add_years_with_date,
95 addYears(date_time, 1) AS add_years_with_date_time,
96
97 -- 之后1月
98 addMonths(date, 1) AS add_months_with_date,
99 addMonths(date_time, 1) AS add_months_with_date_time,
100
101 --之后1周
102 addWeeks(date, 1) AS add_weeks_with_date,
103 addWeeks(date_time, 1) AS add_weeks_with_date_time,
104
105 -- 之后1天
106 addDays(date, 1) AS add_days_with_date,
107 addDays(date_time, 1) AS add_days_with_date_time,
108
109 --之后1小時(shí)
110 addHours(date_time, 1) AS add_hours_with_date_time,
111
112 --之后1分中
113 addMinutes(date_time, 1) AS add_minutes_with_date_time,
114
115 -- 之后10秒鐘
116 addSeconds(date_time, 10) AS add_seconds_with_date_time,
117
118 -- 之后1個(gè)季度
119 addQuarters(date, 1) AS add_quarters_with_date,
120 addQuarters(date_time, 1) AS add_quarters_with_date_time;
121
122 獲取過去時(shí)間的函數(shù)：
123
124 WITH
125 toDate(now()) as date,
126 toDateTime(now()) as date_time
127 SELECT
128 subtractYears(date, 1) AS subtract_years_with_date,
129 subtractYears(date_time, 1) AS subtract_years_with_date_time,
130 subtractQuarters(date, 1) AS subtract_Quarters_with_date,
131 subtractQuarters(date_time, 1) AS subtract_Quarters_with_date_time,
132 subtractMonths(date, 1) AS subtract_Months_with_date,
133 subtractMonths(date_time, 1) AS subtract_Months_with_date_time,
134 subtractWeeks(date, 1) AS subtract_Weeks_with_date,
135 subtractWeeks(date_time, 1) AS subtract_Weeks_with_date_time,
136 subtractDays(date, 1) AS subtract_Days_with_date,
137 subtractDays(date_time, 1) AS subtract_Days_with_date_time,
138 subtractHours(date_time, 1) AS subtract_Hours_with_date_time,
139 subtractMinutes(date_time, 1) AS subtract_Minutes_with_date_time,
140 subtractSeconds(date_time, 1) AS subtract_Seconds_with_date_time;
141
142 SELECT toDate('2019-07-31', 'Asia/GuangZhou') as date_guangzhou;
143 SELECT toDate('2019-07-31'), toDate('2019-07-31', 'Asia/Beijing') as date_beijing;
144
145 -- 亞洲只能加載上海的timezone？？？
146 SELECT toDateTime('2019-07-31 10:10:10', 'Asia/Shanghai') as date_shanghai;
147
148 計(jì)算連個(gè)時(shí)刻在不同時(shí)間單位下的差值
149
150 -- 第一種：指定時(shí)間計(jì)算差值示例
151 WITH
152 toDateTime('2019-07-30 10:10:10', 'Asia/Shanghai') as date_shanghai_one,
153 toDateTime('2020-10-31 11:20:30', 'Asia/Shanghai') as date_shanghai_two
154 SELECT
155 dateDiff('year', date_shanghai_one, date_shanghai_two) as diff_years,
156 dateDiff('month', date_shanghai_one, date_shanghai_two) as diff_months,
157 dateDiff('week', date_shanghai_one, date_shanghai_two) as diff_week,
158 dateDiff('day', date_shanghai_one, date_shanghai_two) as diff_days,
159 dateDiff('hour', date_shanghai_one, date_shanghai_two) as diff_hours,
160 dateDiff('minute', date_shanghai_one, date_shanghai_two) as diff_minutes,
161 dateDiff('second', date_shanghai_one, date_shanghai_two) as diff_seconds;
162
163 -- 第二種：本地當(dāng)前時(shí)間示例
164 WITH
165 now() as date_time
166 SELECT
167 dateDiff('year', date_time, addYears(date_time, 1)) as diff_years,
168 dateDiff('month', date_time, addMonths(date_time, 2)) as diff_months,
169 dateDiff('week', date_time, addWeeks(date_time, 3)) as diff_week,
170 dateDiff('day', date_time, addDays(date_time, 3)) as diff_days,
171 dateDiff('hour', date_time, addHours(date_time, 3)) as diff_hours,
172 dateDiff('minute', date_time, addMinutes(date_time, 30)) as diff_minutes,
173 dateDiff('second', date_time, addSeconds(date_time, 35)) as diff_seconds;
1 -------------------------------------------------
2 --------------------------------------------------
3 ---------------Clickhouse基礎(chǔ)知識(shí)：函數(shù)學(xué)習(xí)-------------
4 --官址學(xué)習(xí)文檔：https://clickhouse.yandex/docs/zh/
5 --------------------------------------------------
6 --------------------------------------------------
7
8
9
10
11 ----------------------------------------------------------------------------
12 -- DBeaver6.1.2操作快捷鍵：
13 -- 常用快捷鍵須知：（Ctrl + Shift + L 顯示快捷鍵列表）
14 -- 1.刪除光標(biāo)所在行：Ctrl + D
15 -- 2.復(fù)制光標(biāo)所在行：Ctrl + Alt + ↓
16 -- 3.移動(dòng)光標(biāo)所在行：Ctrl + Shift + ↑/↓
17 -- 4.縮小SQL編輯器的文本字體大小：Ctrl + -/+
18 -- 5.查找：Ctrl + F
19 -- 6.選中上或下的SQL執(zhí)行語句：Alt + ↑/↓
20 -- 7.執(zhí)行當(dāng)前光標(biāo)所在SQL語句（無論是否格式化過，前提是此SQL語句和上一條有空行或者上一條SQL語句有分號(hào)“;”隔開）
21 ----------------------------------------------------------------------------
22
23 -- 零、檢測函數(shù)類型（clickhouse中數(shù)據(jù)的類型）
24 SELECT toTypeName(0);-- UInt8(三位數(shù)為8)
25 SELECT toTypeName(-0);-- Int8
26 SELECT toTypeName(-343);-- Int16
27 SELECT toTypeName(12.43); -- Float64(默認(rèn)浮點(diǎn)型的數(shù)據(jù)為64)，所以一般在處理浮點(diǎn)型的數(shù)據(jù)的時(shí)候盡量轉(zhuǎn)成toFloat32(12.43)
28 SELECT toTypeName(12.34343); -- Float64
29 SELECT toTypeName(toDateTime(1502396027)); -- DateTime
30
31 -- 一、算數(shù)函數(shù)
32 -->>>>>> 算數(shù)函數(shù)(數(shù)學(xué)上的計(jì)算)
33 --求和
34 SELECT plus(12, 21), plus(10, -10), plus(-10, -10);
35 --差值
36 SELECT minus(10, 5), minus(10, -10),minus(-10, -10);
37 --積
38 SELECT multiply(12, 2), multiply(12, -2), multiply(-12, -2);
39 --平均值
40 SELECT divide(12, 4), divide(10, 3), divide(2, 4), divide(-4, -2), divide(-4, 2), divide(-4.5, 3);
41 SELECT intDiv(10, 3), divide(10, 3); -- 3, 3.333(保留四位有效數(shù)字)
42 SELECT divide(10, 0), divide(-10, 0); -- 出現(xiàn)無窮大字符“ ∞ ”或“ -∞ ”
43 SELECT divide(0, 0); -- 特殊字符（類似亂碼）
44 SELECT intDivOrZero(10, 0); -- 0
45 --求余數(shù)
46 SELECT modulo(10, 3); --1
47 SELECT modulo(10.5, 3); --1
48 --取反
49 SELECT negate(10), negate(-10); -- -10 10
50 --絕對(duì)值
51 SELECT abs(-10), abs(10);
52 --最大公約數(shù)
53 SELECT gcd(12, 24), gcd(-12, -24), gcd(-12, 24);
54 --最小公倍數(shù)
55 SELECT lcm(12, 24), lcm(-12, -24), lcm(-3, 4);
56
57 -- 二、比較函數(shù)
58 -->>>>>> 比較函數(shù)(始終返回0表示false 或 1表示true)
59 SELECT 12 == 12, 12 != 10, 12 == 132, 12 != 12, 12 <> 12;
60 SELECT equals(12, 12), notEquals(12, 10), equals(12, 10), notEquals(12,123);
61 SELECT greater(12, 10), greater(10, 12), greater(12, 12);-- 前者是否大于后者
62 SELECT greaterOrEquals(12,10), greaterOrEquals(12,12);-- 前者是否大于或等于后者
63 SELECT less(12, 21), less(12, 10), less(120, 120);-- 前者是否小于后者
64 SELECT lessOrEquals(12, 120), lessOrEquals(12, 12);-- 前世是否小于或等于或者
65
66 -- 三、邏輯函數(shù)
67 -->>>>>> 邏輯操作符（返回0表示false 或 1表示true）
68 SELECT 12==12 or 12!=10;
69 SELECT 12==12 and 12!=10;
70 SELECT not 12, not 0;
71 SELECT or(equals(12, 12), notEquals(12, 10)); --函數(shù)表示法：或
72 SELECT and(equals(12, 12), notEquals(12, 10));--函數(shù)表示法：且
73 SELECT not(12), not(0);
74
75 -- 四、類型轉(zhuǎn)換函數(shù)
76 -->>>>>> 類型轉(zhuǎn)換函數(shù)部分示例：
77 SELECT toInt8(12.3334343), toFloat32(10.001), toFloat64(1.000040);
78 SELECT toString(now());
79 SELECT now() AS now_local, toString(now(), 'Asia/Yekaterinburg') AS now_yekat;
80 SELECT now() AS now_local, toDate(now()), toDateTime(now()), toUnixTimestamp(now());
81
82 SELECT
83 '2016-06-15 23:00:00' AS timestamp,
84 CAST(timestamp AS DateTime) AS datetime,
85 CAST(timestamp AS Date) AS date,
86 CAST(timestamp, 'String') AS string,
87 CAST(timestamp, 'FixedString(22)') AS fixed_string;
88
89 WITH
90 toDate('2019-01-01') AS date,
91 INTERVAL 1 WEEK AS interval_week,
92 toIntervalWeek(1) AS interval_to_week,
93 toIntervalMonth(1) AS interval_to_month
94 SELECT
95 date + interval_week,
96 date + interval_to_week,
97 date + interval_to_month;
98
99 WITH
100 toDateTime('2019-01-01 12:10:10') as datetime,
101 INTERVAL 1 HOUR AS interval_hour,
102 toIntervalHour(1) as invterval_to_hour
103 SELECT
104 plus(datetime, interval_hour),
105 plus(datetime, invterval_to_hour);
106
107 -- 五、時(shí)間日期函數(shù)
108 --->>>>>> 時(shí)間日期函數(shù)
109 SELECT
110 toDateTime('2019-07-30 10:10:10') AS time,
111 -- 將DateTime轉(zhuǎn)換成Unix時(shí)間戳
112 toUnixTimestamp(time) as unixTimestamp,
113 -- 保留 時(shí)-分-秒
114 toDate(time) as date_local,
115 toTime(time) as date_time,-- 將DateTime中的日期轉(zhuǎn)換為一個(gè)固定的日期，同時(shí)保留時(shí)間部分。
116 -- 獲取年份，月份，季度，小時(shí)，分鐘，秒鐘
117 toYear(time) as get_year,
118 toMonth(time) as get_month,
119 -- 一年分為四個(gè)季度。1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）
120 toQuarter(time) as get_quarter,
121 toHour(time) as get_hour,
122 toMinute(time) as get_minute,
123 toSecond(time) as get_second,
124 -- 獲取 DateTime中的當(dāng)前日期是當(dāng)前年份的第幾天，當(dāng)前月份的第幾日，當(dāng)前星期的周幾
125 toDayOfYear(time) as "當(dāng)前年份中的第幾天",
126 toDayOfMonth(time) as "當(dāng)前月份的第幾天",
127 toDayOfWeek(time) as "星期",
128 toDate(time, 'Asia/Shanghai') AS date_shanghai,
129 toDateTime(time, 'Asia/Shanghai') AS time_shanghai,
130 -- 得到當(dāng)前年份的第一天,當(dāng)前月份的第一天，當(dāng)前季度的第一天，當(dāng)前日期的開始時(shí)刻
131 toStartOfYear(time),
132 toStartOfMonth(time),
133 toStartOfQuarter(time),
134 toStartOfDay(time) AS cur_start_daytime,
135 toStartOfHour(time) as cur_start_hour,
136 toStartOfMinute(time) AS cur_start_minute,
137 -- 從過去的某個(gè)固定的時(shí)間開始，以此得到當(dāng)前指定的日期的編號(hào)
138 toRelativeYearNum(time),
139 toRelativeQuarterNum(time);
140
141 SELECT
142 toDateTime('2019-07-30 14:27:30') as time,
143 toISOYear(time) AS iso_year,
144 toISOWeek(time) AS iso_week,
145 now() AS cur_dateTime1, -- 返回當(dāng)前時(shí)間yyyy-MM-dd HH:mm:ss
146 today() AS cur_dateTime2, -- 其功能與'toDate(now())'相同
147 yesterday() AS yesterday, -- 當(dāng)前日期的上一天
148 -- timeSlot(1) AS timeSlot_1, -- 出現(xiàn)異常！！將時(shí)間向前取整半小時(shí)
149 toDate(time) as getY_M_d;
150
151 -- 目前只有這三種格式，沒有什么toYYYY(),toYYYddmm()之類的函數(shù)，不要想當(dāng)然。
152 SELECT
153 now() as nowTime,
154 -- 將Date或DateTime轉(zhuǎn)換為包含年份和月份編號(hào)的UInt32類型的數(shù)字（YYYY * 100 + MM）
155 toYYYYMMDDhhmmss(nowTime),
156 toYYYYMMDD(nowTime),
157 toYYYYMM(nowTime);
158
159 -- formatDateTime(Time, Format[,Timezone])函數(shù)引用
160 SELECT
161 now() as now_time,
162 toDateTime('2019-07-31 18:20:30') AS def_datetime,
163 formatDateTime(now_time, '%D') AS now_time_day_month_year,-- 07/30/19
164 -- toDateTime('2019-07-31 18:20:30', 'Asia/Shanghai') AS def_datetime1, -- 指定時(shí)區(qū)
165 formatDateTime(def_datetime, '%Y') AS def_datetime_year, -- 2019（指定日期為2019年）
166 formatDateTime(def_datetime, '%y') AS def_datetime_year_litter, -- 19（指定日期為19年,Year, last two digits (00-99),本世紀(jì)的第19年）
167 formatDateTime(def_datetime, '%H') AS hour24, -- 18 下午六點(diǎn)
168 formatDateTime(def_datetime, '%I') AS hour12, -- 06下午六點(diǎn)
169 formatDateTime(def_datetime, '%p') AS PMorAM, -- 指定時(shí)間是上午還是下午
170 formatDateTime(def_datetime, '%w') AS def_datetime_get_curWeek,-- 3(指定日期為星期三)
171 formatDateTime(def_datetime, '%F') AS def_datetime_get_date,-- 2019-07-31
172 formatDateTime(def_datetime, '%T') AS def_datetime_get_time,-- 18:20:30
173 formatDateTime(def_datetime, '%M') AS def_datetime_get_minute,-- 20(得到指定事件的“分”，minute (00-59))
174 formatDateTime(def_datetime, '%S') AS def_datetime_get_second;-- 30(得到指定事件的“秒”，second (00-59))
175
176
177
178 -- 1.跳轉(zhuǎn)到之后的日期函數(shù)
179 -- 第一種，日期格式（指定日期，需注意時(shí)區(qū)的問題）
180 WITH
181 toDate('2019-09-09') AS date,
182 toDateTime('2019-09-09 00:00:00') AS date_time
183 SELECT
184 addYears(date, 1) AS add_years_with_date,
185 addYears(date_time, 0) AS add_years_with_date_time;
186 -- 第二種，日期格式（當(dāng)前，本地時(shí)間）
187 WITH
188 toDate(now()) as date,
189 toDateTime(now()) as date_time
190 SELECT
191 now() as now_time,-- 當(dāng)前時(shí)間
192 addYears(date, 1) AS add_years_with_date,-- 之后1年
193 addYears(date_time, 1) AS add_years_with_date_time,
194 addMonths(date, 1) AS add_months_with_date,-- 之后1月
195 addMonths(date_time, 1) AS add_months_with_date_time,
196 addWeeks(date, 1) AS add_weeks_with_date,--之后1周
197 addWeeks(date_time, 1) AS add_weeks_with_date_time,
198 addDays(date, 1) AS add_days_with_date,-- 之后1天
199 addDays(date_time, 1) AS add_days_with_date_time,
200 addHours(date_time, 1) AS add_hours_with_date_time,--之后1小時(shí)
201 addMinutes(date_time, 1) AS add_minutes_with_date_time,--之后1分中
202 addSeconds(date_time, 10) AS add_seconds_with_date_time,-- 之后10秒鐘
203 addQuarters(date, 1) AS add_quarters_with_date, -- 之后1個(gè)季度
204 addQuarters(date_time, 1) AS add_quarters_with_date_time;
205
206 -- 2.跳轉(zhuǎn)到當(dāng)前日期之前的函數(shù)(函數(shù)將Date/DateTime減去一段時(shí)間間隔，然后返回Date/DateTime)
207 WITH
208 toDate(now()) as date,
209 toDateTime(now()) as date_time
210 SELECT
211 subtractYears(date, 1) AS subtract_years_with_date,
212 subtractYears(date_time, 1) AS subtract_years_with_date_time,
213 subtractQuarters(date, 1) AS subtract_Quarters_with_date,
214 subtractQuarters(date_time, 1) AS subtract_Quarters_with_date_time,
215 subtractMonths(date, 1) AS subtract_Months_with_date,
216 subtractMonths(date_time, 1) AS subtract_Months_with_date_time,
217 subtractWeeks(date, 1) AS subtract_Weeks_with_date,
218 subtractWeeks(date_time, 1) AS subtract_Weeks_with_date_time,
219 subtractDays(date, 1) AS subtract_Days_with_date,
220 subtractDays(date_time, 1) AS subtract_Days_with_date_time,
221 subtractHours(date_time, 1) AS subtract_Hours_with_date_time,
222 subtractMinutes(date_time, 1) AS subtract_Minutes_with_date_time,
223 subtractSeconds(date_time, 1) AS subtract_Seconds_with_date_time;
224
225 SELECT toDate('2019-07-31', 'Asia/GuangZhou') as date_guangzhou;
226 SELECT toDate('2019-07-31'), toDate('2019-07-31', 'Asia/Beijing') as date_beijing;
227 -- 亞洲只能加載上海的timezone？？？
228 SELECT toDateTime('2019-07-31 10:10:10', 'Asia/Shanghai') as date_shanghai;
229
230
231 -- 計(jì)算連個(gè)時(shí)刻在不同時(shí)間單位下的差值
232 -- 第一種：指定時(shí)間計(jì)算差值示例
233 WITH
234 toDateTime('2019-07-30 10:10:10', 'Asia/Shanghai') as date_shanghai_one,
235 toDateTime('2020-10-31 11:20:30', 'Asia/Shanghai') as date_shanghai_two
236 SELECT
237 dateDiff('year', date_shanghai_one, date_shanghai_two) as diff_years,
238 dateDiff('month', date_shanghai_one, date_shanghai_two) as diff_months,
239 dateDiff('week', date_shanghai_one, date_shanghai_two) as diff_week,
240 dateDiff('day', date_shanghai_one, date_shanghai_two) as diff_days,
241 dateDiff('hour', date_shanghai_one, date_shanghai_two) as diff_hours,
242 dateDiff('minute', date_shanghai_one, date_shanghai_two) as diff_minutes,
243 dateDiff('second', date_shanghai_one, date_shanghai_two) as diff_seconds;
244
245 -- 第二種：本地當(dāng)前時(shí)間示例
246 WITH
247 now() as date_time
248 SELECT
249 dateDiff('year', date_time, addYears(date_time, 1)) as diff_years,
250 dateDiff('month', date_time, addMonths(date_time, 2)) as diff_months,
251 dateDiff('week', date_time, addWeeks(date_time, 3)) as diff_week,
252 dateDiff('day', date_time, addDays(date_time, 3)) as diff_days,
253 dateDiff('hour', date_time, addHours(date_time, 3)) as diff_hours,
254 dateDiff('minute', date_time, addMinutes(date_time, 30)) as diff_minutes,
255 dateDiff('second', date_time, addSeconds(date_time, 35)) as diff_seconds;
256
257 -- timeSlot(StartTime, Duration, [,Size])
258 -- 它返回一個(gè)時(shí)間數(shù)組，其中包括從從“StartTime”開始到“StartTime + Duration 秒”內(nèi)的所有符合“size”（以秒為單位）步長的時(shí)間點(diǎn)
259 -- 作用:搜索在相應(yīng)會(huì)話中綜合瀏覽量是非常有用的。
260 SELECT
261 timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600)) as dateTimeArray,
262 dateTimeArray[0] as arr_index_0, -- no result.
263 dateTimeArray[1] as arr_index_1, -- 2012-01-01 20:00:00
264 dateTimeArray[2] as arr_index_2, -- 2012-01-01 20:30:00
265 dateTimeArray[3] as arr_index_3, -- no result.
266 dateTimeArray[4] as arr_index_4; -- no result.
267 -- toUInt32(600) 表示之后間距20秒的時(shí)刻
268 SELECT
269 timeSlots(now(), toUInt32(600), 20) as dateTimeArray, -- 類似于：引用地址
270 dateTimeArray[0] as arr_index_0, -- no result.為什么？
271 dateTimeArray[1] as arr_index_1,
272 dateTimeArray[2] as arr_index_2,
273 dateTimeArray[3] as arr_index_3,
274 dateTimeArray[4] as arr_index_4,
275 dateTimeArray[5] as arr_index_5;
276 -- 指定時(shí)間為基準(zhǔn)，之后每個(gè)元素增加20秒
277 SELECT
278 timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600), 20) as cur_dateTimeArray, -- 類似于：引用地址
279 cur_dateTimeArray[0] as arr_index_0, -- no result.為什么？
280 cur_dateTimeArray[1] as arr_index_1, -- 2012-01-01 20:20:00
281 cur_dateTimeArray[2] as arr_index_2, -- 2012-01-01 20:20:20
282 cur_dateTimeArray[3] as arr_index_3, -- 2012-01-01 20:20:40
283 cur_dateTimeArray[4] as arr_index_4, -- 2012-01-01 20:21:00
284 cur_dateTimeArray[5] as arr_index_5; -- 2012-01-01 20:21:20
285
286
287 -- 六、字符串函數(shù)
288 --->>>>>> 字符串函數(shù)：
289 SELECT
290 length('hello world') as str_length, -- 按照Unicode編碼計(jì)算長度“你好”的長度為6
291 empty('hello world'),-- 判斷字符串是否為空，空為1，非空為0
292 notEmpty('hello world'),
293 lengthUTF8('hello world'), -- 按照實(shí)際字符計(jì)算長度“你好”為2
294 char_length('hello world'), -- 同 lengthUTF8()
295 character_length('hello world'), -- 同 lengthUTF8(),
296 lower('abcd123--'),--字母全部小寫（將字符串中的ASCII轉(zhuǎn)換為小寫。）
297 upper('abcd123--'),--字母全部大寫（將字符串中的ASCII轉(zhuǎn)換為大寫。）
298 lowerUTF8('abcd123-/*8asd-\'), -- abcd123-/*8asd-
299 upperUTF8('abcd123--'), -- ABCD123--
300 isValidUTF8('abcd123--/**'); --檢查字符串是否為有效的UTF-8編碼，是則返回1，否則返回0。
301 SELECT notEmpty(''), notEmpty(NULL), notEmpty('he'); -- 0,空,1
302 SELECT toValidUTF8('x61xF0x80x80x80b');
303 -- reverseUTF8():以Unicode字符為單位反轉(zhuǎn)UTF-8編碼的字符串。如果字符串不是UTF-8編碼，則可能獲取到一個(gè)非預(yù)期的結(jié)果（不會(huì)拋出異常）
304 SELECT reverse('abcdefg'), reverseUTF8('abcdefg');
305 -- 2.字符串維度自定義安排
306 SELECT format('{1} {0} {1}', 'World', 'Hello'); -- 輸出：Hello World Hello
307 SELECT format('{0} {0} {1} {1}', 'one', 'two'); -- 輸出：one one two two
308 SELECT format('{} {}', 'Hello', 'World'); -- 輸出：Hello World
309 -- 3.字符串拼接 concat(s1,s2,s3,...)
310 SELECT concat('Hello',' ','World', '!');-- Hello World!
311 -- 與concat相同，區(qū)別在于，你需要保證concat(s1, s2, s3) -> s4是單射的，它將用于GROUP BY的優(yōu)化。
312 SELECT concatAssumeInjective('Hello',' ','World', '!');-- Hello World!
313 -- 4.字符串截取：substring(s, offset, length), mid(s, offset, length), substr(s, offset, length)
314 -- 以字節(jié)為單位截取指定位置字符串，返回以‘offset’位置為開頭，長度為‘length’的子串。‘offset’從1開始（與標(biāo)準(zhǔn)SQL相同）。‘offset’和‘length’參數(shù)必須是常量。
315 SELECT
316 substring('abcdefg', 1, 3),-- abc
317 substring('你好，世界', 1, 3),-- 你
318 substringUTF8('你好，世界', 1, 3); -- 你好，
319 -- 5.字符串拼接：appendTrailingCharIfAbsent(s, c)
320 -- 如果‘s’字符串非空并且末尾不包含‘c’字符，則將‘c’字符附加到末尾。
321 SELECT
322 appendTrailingCharIfAbsent('good','c'), -- goodc
323 appendTrailingCharIfAbsent('goodccc','c'); -- goodccc
324 -- 6.字符串編碼轉(zhuǎn)換：convertCharset(s, from, to) 返回從‘from’中的編碼轉(zhuǎn)換為‘to’中的編碼的字符串‘s’。
325 SELECT
326 convertCharset('hello', 'UTF8','Unicode'),-- ??h
327 convertCharset('hello', 'Unicode', 'UTF8'),-- 橋汬?
328 convertCharset('hello', 'Unicode', 'ASCII'),--
329 convertCharset('hello', 'ascii', 'ascii'),--hello
330 convertCharset('hello', 'UTF8','UTF8');-- hello
331 SELECT
332 base64Encode('username+password'),-- dXNlcm5hbWUrcGFzc3dvcmQ=
333 base64Decode('dXNlcm5hbWUrcGFzc3dvcmQ='), -- username+password
334 -- 使用base64將字符串解碼成原始字符串。但如果出現(xiàn)錯(cuò)誤，將返回空字符串。
335 tryBase64Decode('dXNlcm5hbWUrcGFzc3dvcmQ=');
336 -- 7.判斷字符串是否已什么結(jié)尾或結(jié)束，返回1：true，0：flase
337 -- endsWith(s, suffix) 返回是否以指定的后綴結(jié)尾。如果字符串以指定的后綴結(jié)束，則返回1，否則返回0
338 -- startWith(s, prefix) 返回是否以指定的前綴開頭。如果字符串以指定的前綴開頭，則返回1，否則返回0。
339 SELECT
340 endsWith('string','g'),
341 startsWith('string', 'str'); -- 1 true
342 -- 8.刪除左側(cè)空白字符
343 -- trimLeft(s) 返回一個(gè)字符串，用于刪除左側(cè)的空白字符
344 -- trimRight(s) 返回一個(gè)字符串，用于刪除右側(cè)的空白字符
345 -- trimBoth(s) 返回一個(gè)字符串，用于刪除左側(cè)和右側(cè)的空白字符
346 SELECT
347 trimLeft(' sdfdgs'), -- sdfdgs
348 trimRight('abcd '), -- abcd
349 trimBoth(' abcd '); -- abcd
350
351 -- 七、字符串搜索函數(shù)
352 --->>>>>> 字符串搜索函數(shù)
353 -- pasition(haystack, needle), 顯示needle在haystack的第一個(gè)出現(xiàn)的位置。
354 SELECT
355 POSITION('2121stringstrstrstrstr','str') AS positionSearch, -- 5
356 POSITION('你好，hello,12323-你好，你，好sdfd*dg', '你，好'),-- 31
357 positionUTF8('n12你好','你好') AS positionUTF8,-- 4
358 positionCaseInsensitive('ABCDCDEFABCD','bc') AS positionCaseInsensitive, --2
359 locate('hellohellohellohello','ello'); -- 2
360 -- multiSearchAllPositions(haystack, [needle1, needle2, ..., needlen])
361 -- 注意：在所有multiSearch*函數(shù)中，由于實(shí)現(xiàn)規(guī)范，needles的數(shù)量應(yīng)小于2^8。
362 -- 函數(shù)返回一個(gè)數(shù)組，其中包含所有匹配needlei的位置
363 SELECT
364 multiSearchAllPositions('goodnamegoodnamegoodhellohihihi', ['dn', 'good']) as multiSearch,-- [4,1]
365 multiSearchAllPositionsCaseInsensitive('nameSsdfagpSSDFDFetgfderef', ['SS','fa']) as multiCaseInsensitive,
366 multiSearchAllPositionsUTF8('nameSsdfazz軸功率gpSSDFDFetgfderef', ['Ss','fa', 'zz軸']) AS multiSearchUTF8,
367 multiSearchAllPositionsCaseInsensitiveUTF8('nameSsdfazz軸功率gpSSDFDFetgfderef', ['Ss','fa', 'zz軸']) AS multiCaseInsensitiveUTF8;
368 -- 檢查字符串是否與pattern正則表達(dá)式匹配。pattern可以是一個(gè)任意的re2正則表達(dá)式。 re2正則表達(dá)式的語法比Perl正則表達(dá)式的語法存在更多限制。
369 -- match(haystack, pattern) 匹配到了則返回1，否則返回0
370 SELECT
371 match('1232434sadgaDDFSrefds', '[0-9a-zA-Z]'), -- 存在匹配的字符，返回1
372 match('1232321', '[a-z]'); -- 不存在匹配的字符，返回0
373 -- 與match相同，但如果所有正則表達(dá)式都不匹配，則返回0；如果任何模式匹配，則返回1。它使用hyperscan庫。對(duì)于在字符串中搜索子字符串的模式，最好使用“multisearchany”，因?yàn)樗咝А? 374 -- multiMatchAny(haystack, [pattern1, pattern2, ..., patternn])
375 -- 注意：任何haystack字符串的長度必須小于232字節(jié)，否則拋出異常。這種限制是因?yàn)閔yperscan API而產(chǎn)生的。
376 -- 多個(gè)正則表達(dá)式對(duì)原始字符進(jìn)行匹配，如若只有一個(gè)正則表達(dá)式匹配上了則返回1，否則返回0
377 SELECT
378 multiMatchAny('abcABC',['[0-9]','[a-zA-Z]']) AS multiMatchAnyOne, -- 1
379 multiMatchAny('123abcABC',['[0-9]','[a-zA-Z]']) AS multiMatchAnyTwo, --1
380 -- 與multiMatchAny相同，但返回與haystack匹配的任何內(nèi)容的索引位置。
381 multiMatchAnyIndex('123abcABC', ['[0-9]','[a-zA-Z]']) as multiMatchAnyIndex; --2
382 -- 模糊匹配：like()函數(shù)，注意大寫敏感。
383 -- % 表示任何字節(jié)數(shù)（包括零字符）
384 -- _ 表示任何一個(gè)字節(jié)
385 SELECT
386 'hello' LIKE '%h%' as LIKE_UP, -- 1
387 'hello' like 'he' AS like_low, -- 0
388 'hello' not like 'he' AS not_like, -- 1
389 'hello' like '%he%' AS like_litter, -- 1
390 like('adgadgadfa1232', '_12_') AS like_func,
391 like('sdfasdfasd', '[a-z]') AS like_func2, -- 0
392 notLike('1232423', '[a-zA-Z]') AS not_like_func; -- 1
393 -- 使用字符串截取字符串：extract(haystack, pattern)
394 -- 使用正則表達(dá)式截取字符串。如果‘haystack’與‘pattern’不匹配，則返回空字符串。如果正則表達(dá)式中不包含子模式，它將獲取與整個(gè)正則表達(dá)式匹配的子串。否則，它將獲取與第一個(gè)子模式匹配的子串。
395 SELECT
396 extractAll('hellogoodaimantIdeaIDEAfasd123232', '[0-9]'), -- ['1','2','3','2','3','2']
397 extractAll('12323dSDFRE', '[A-Z]'),-- ['S','D','F','R','E']
398 extract('helloclickhouse', '[a-z]');-- h
399 -- ngramSearch(haystack, needle)
400 -- 基于4-gram計(jì)算haystack和needle之間的距離：計(jì)算兩個(gè)4-gram集合之間的對(duì)稱差異，并用它們的基數(shù)和對(duì)其進(jìn)行歸一化。
401 -- 返回0到1之間的任何浮點(diǎn)數(shù) -- 越接近0則表示越多的字符串彼此相似。
402 -- 如果常量的needle或haystack超過32KB，函數(shù)將拋出異常。如果非常量的haystack或needle字符串超過32Kb，則距離始終為1。
403 SELECT
404 ngramDistance('hello123456789','123') AS ngramDistance,
405 ngramDistanceCaseInsensitive('hello123456789','123') AS ngramDistanceCaseInsensitive,
406 ngramDistanceUTF8('hello123456789','123') AS ngramDistanceUTF8,
407 ngramDistanceCaseInsensitiveUTF8('hello123456789','123') AS ngramDistanceCaseInsensitiveUTF8;
408 -- 注意：對(duì)于UTF-8，我們使用3-gram。所有這些都不是完全公平的n-gram距離。
409 -- 我們使用2字節(jié)哈希來散列n-gram，然后計(jì)算這些哈希表之間的（非）對(duì)稱差異 - 可能會(huì)發(fā)生沖突。
410 -- 對(duì)于UTF-8不區(qū)分大小寫的格式，我們不使用公平的tolower函數(shù)
411 -- 我們將每個(gè)Unicode字符字節(jié)的第5位（從零開始）和字節(jié)的第一位歸零
412 -- 這適用于拉丁語，主要用于所有西里爾字母。
413
414 --八、字符串替換函數(shù)
415 --->>>>>> 字符串替換函數(shù)
416 -- 替換匹配到的字符串
417 -- replaceOne(haystack, pattern, replacement)
418 -- 用‘replacement’子串替換‘haystack’中與‘pattern’子串第一個(gè)匹配的匹配項(xiàng)（如果存在）。 ‘pattern’和‘replacement’必須是常量。
419 -- replaceAll(haystack, pattern, replacement), replace(haystack, pattern, replacement)
420 -- 用‘replacement’子串替換‘haystack’中出現(xiàn)的所有‘pattern’子串。
421 SELECT
422 replaceOne('hed1234544', '4', '*') AS replaceOne,-- hed123*544
423 replaceRegexpOne('hed1234544', '4', '*') AS replaceRegexpOne,-- hed123*544
424 replace('hed1234544', '4', '*') AS replace, -- hed123*5**
425 replaceAll('hed1234544', '4', '*') AS replaceAll;-- hed123*5**
426
427 -- 實(shí)例：2019-07-31 改變成 07/31/2019
428 SELECT
429 toDate(now()) AS now_date,
430 replaceRegexpOne(toString(now_date), '(\d{4})-(\d{2})-(\d{2})', '\2/\3/\1') AS format_date;
431 -- 示例：賦值字符串10次
432 SELECT replaceRegexpOne('Hello, World!', '.*', '\0\0\0\0\0\0\0\0\0\0') AS res;
433 -- replaceRegexpAll(haystack, pattern, replacement)
434 -- 與replaceRegexpOne相同，但會(huì)替換所有出現(xiàn)的匹配項(xiàng)。例如：
435 SELECT replaceRegexpAll('hello,world!', '.', '\0\0') as res; -- hheelllloo,,wwoorrlldd!!
436 SELECT replaceRegexpAll('hello o o, world.', ' ', '*') as res; -- hello*o*o,*world.
437
438 -- 函數(shù)：regexpQuoteMeta(s) 該函數(shù)用于在字符串中的某些預(yù)定義字符之前添加反斜杠。
439 -- 預(yù)定義字符：'0'，''，'|'，'('，')'，'^'，'$'，'。'，'['，']'，'？'，'* '，'+'，'{'，'：'，' - '。
440 -- 這個(gè)實(shí)現(xiàn)與re2 :: RE2 :: QuoteMeta略有不同。它以而不是x00轉(zhuǎn)義零字節(jié)，它只轉(zhuǎn)義所需的字符
441 ---- 簡言之，就是不處理轉(zhuǎn)義字符，一般如果沒有用的這個(gè)函數(shù)，都會(huì)有轉(zhuǎn)義的情況出現(xiàn)。
442 SELECT regexpQuoteMeta('\\|[]{}+_-=@!~`&^*%$#'); -- \\|[]{}+_-=@!~`&^*%$#
443 SELECT toString('\\'); -- \
444
445
446 --九、條件函數(shù)
447 --->>>>>> 條件函數(shù)
448 -- 1. if(cond, then, else)函數(shù)：類似于三元操作符。
449 -- 中文字符使用雙引號(hào)，英文字符可不使用引號(hào)也可使用當(dāng)引號(hào)或雙引號(hào)，根據(jù)具體情況而定。
450 -- 如果cond ！= 0則返回then，如果cond = 0則返回else。 cond必須是UInt8類型，then和else必須存在最低的共同類型。
451 -- 注意：then和else可以是NULL
452 SELECT
453 12 > 10 ? 'desc' : 'asc' AS "三元操作符",
454 if(12 > 10, 'desc' , 'asc') AS "if()函數(shù)",
455 if(12 > 10, NULL, NULL);
456 -- 2. multiIf(cond_1, then_1, cond_2, then_2...else)
457 -- 允許您在查詢中更緊湊地編寫CASE運(yùn)算符。類似于java中的switch語法（可以接受2n+1個(gè)參數(shù)）
458 SELECT multiIf(1,'one',2,'two',3,'three','not this index');-- 關(guān)聯(lián)case條件表達(dá)式
459
460 --十、數(shù)學(xué)函數(shù)
461 --->>>>>> 數(shù)學(xué)函數(shù)
462 SELECT
463 1 * e() AS E,
464 1 * pi() AS PI,
465 sqrt(25) AS sqrt_25, --接受一個(gè)數(shù)值類型的參數(shù)并返回它的平方根。
466 cbrt(27) AS cbrt_27, --接受一個(gè)數(shù)值類型的參數(shù)并返回它的立方根。
467 exp(10), --接受一個(gè)數(shù)值類型的參數(shù)并返回它的指數(shù)
468 exp10(10), --接受一個(gè)數(shù)值類型的參數(shù)并返回它的10的x次冪。
469 log(10) AS LOG,
470 log2(10) AS LOG2, --接受一個(gè)數(shù)值類型的參數(shù)并返回它的底2對(duì)數(shù)。
471 ln(e()) AS LOG10; --接受一個(gè)數(shù)值類型的參數(shù)并返回它的自然對(duì)數(shù)
472 -- 示例：三西格瑪準(zhǔn)則
473 SELECT erf(3 / sqrt(2)); -- 0.997
474 SELECT
475 sin(90), -- 返回x的三角正弦值。
476 cos(90), -- 返回x的三角余弦值。
477 tan(90), -- 返回x的三角正切值
478 acos(0), -- 返回x的反三角余弦值。
479 asin(1), -- 返回x的反三角正弦值。
480 atan(45); -- 返回x的反三角正切值。
481 -- pow(x, y), power(x, y) 接受x和y兩個(gè)參數(shù)。返回x的y次方。
482 SELECT
483 pow(2, 3), -- 2的三次方
484 pow(3, 2); -- 3的平方
485 SELECT
486 intExp2(4), --2^4 接受一個(gè)數(shù)值類型的參數(shù)并返回它的2的x次冪（UInt64）。
487 intExp10(2);--10^2 接受一個(gè)數(shù)值類型的參數(shù)并返回它的10的x次冪（UInt64）。
488
489 -- 十一、取整函數(shù)
490 --->>>>>> 取整函數(shù)
491 -- 1.向下取整：floor(x[,N])
492 SELECT
493 floor(toFloat32(12.08098), 2), -- 12.08
494 floor(toFloat32(12.2323), 2), -- 12.23
495 floor(toFloat32(12.89788), -1), -- 10
496 floor(toFloat32(12.09590), 3), -- 12.095 (注意：如果按照正常的四舍五入，則應(yīng)該是12.096，為什么呢？)
497 floor(toFloat32(12.0987), 3),-- 12.098
498 floor(10, 2); -- 10
499 -- 2.四舍五入：round(expression [, decimal_places])
500 -- 如果decimal_places=0,則取整數(shù)；
501 -- 如果>0,則將值舍入小數(shù)點(diǎn)右側(cè)；
502 -- 如果<0,則將小數(shù)點(diǎn)左側(cè)的值四舍五入。
503 SELECT
504 round(toFloat32(12.1234), 3),
505 round(toFloat32(12.0025), 3), -- 12.002(注意：為什么不是12.003呢？)
506 -- round函數(shù)只會(huì)最多保留三位有效數(shù)字
507 round(toFloat32(12.0025), 4), -- 12.002
508 round(toFloat32(12.0025002323), 100); -- 12.003
509 -- 示例：
510 SELECT
511 round(toFloat32(10 / 3)), -- 3
512 round(toFloat32(10 / 3), 2), -- 3.33
513 round(toFloat32(10.000/3), 3), -- 3.333
514 round(toFloat32(10.000/3), 6); -- 3.333
515 -- roundToExp2() 接受一個(gè)數(shù)字。如果數(shù)字小于1，則返回0。否則，它將數(shù)字向下舍入到最接近的（整個(gè)非負(fù)）2的x次冪。
516 SELECT
517 roundToExp2(12.0129), -- 8 = 2^3
518 roundToExp2(toFloat32(0.01)); -- 0.008
519 -- 3.向上取整：ceil(x[, N]) 或者 ceiling(x[, N])
520 SELECT
521 ceil(12.34343, 3), -- 12.344
522 ceil(toFloat64(12.34343), 3), -- 12.344
523 ceil(toFloat32(12.34343), 3), -- 12.344
524 ceil(12.0011, 3); -- 12.002
525
526
527 ---十二、數(shù)組函數(shù)
528 --->>>>>> 數(shù)組函數(shù)
529 -- 1.數(shù)組非空判斷相關(guān)函數(shù)（真為1，假為0）
530 SELECT empty([]), empty([1,2,3]), notEmpty([1,2,3]), notEmpty([]);
531 -- 2.數(shù)組長度 length() 返回?cái)?shù)組中的元素個(gè)數(shù)。 結(jié)果類型是UInt64。 該函數(shù)也適用于字符串。
532 SELECT
533 -- length(), -- 出現(xiàn)異常
534 -- length([true, false]), -- 異常
535 -- length([1,2,,4]), --出現(xiàn)異常！
536 length([]), -- 0
537 length(['a','b','c']), -- 3
538 length([1,2,3]); -- 3
539 -- 3.擴(kuò)展判斷非空的部分函數(shù)如下：不接受任何參數(shù)并返回適當(dāng)類型的空數(shù)組
540 SELECT
541 emptyArrayUInt8(), -- UInt8的空數(shù)組
542 emptyArrayUInt16(),
543 emptyArrayUInt32(),
544 emptyArrayUInt64(),
545 emptyArrayDate(),
546 emptyArrayDateTime(),
547 emptyArrayInt8(),
548 emptyArrayInt16(),
549 emptyArrayInt32(),
550 emptyArrayInt64();
551 -- 接受一個(gè)空數(shù)組并返回一個(gè)僅包含一個(gè)默認(rèn)值元素的數(shù)組。(以下是部分示例)
552 SELECT
553 emptyArrayToSingle(emptyArrayInt32()), -- 0
554 emptyArrayToSingle(emptyArrayUInt32()), -- 0
555 emptyArrayToSingle(emptyArrayDate()), -- 0002-11-30
556 emptyArrayToSingle(emptyArrayDateTime()); --0002-11-30 08:00:00
557 -- 4.生成一個(gè)含有N個(gè)元素的數(shù)組,元素從0開始增長，步長尾1.
558 -- range(N) 返回從0到N-1的數(shù)字?jǐn)?shù)組。 以防萬一，如果在數(shù)據(jù)塊中創(chuàng)建總長度超過100,000,000個(gè)元素的數(shù)組，則拋出異常
559 SELECT
560 range(10), -- [0,1,2,3,4,5,6,7,8,9]
561 range(2), -- [0,1]
562 -- range(5.5), -- 出現(xiàn)異常，N為Int8的數(shù)據(jù)類型，正整數(shù)
563 -- range(-10), -- 出現(xiàn)異常，DB::Exception: Illegal type Int8 of argument of function range
564 range(1); -- 0
565 -- 5.新建一個(gè)數(shù)組的函數(shù)：array(x1,……) 類似于 直接[x1,……]
566 -- 注意：新建數(shù)組的每個(gè)元素的數(shù)據(jù)類型需保持一致性。
567 SELECT
568 array(1,2,2,3,4) AS "array()函數(shù)",
569 -- [1,'hello',3], -- 出現(xiàn)異常，DB::Exception: There is no supertype for types UInt8, String, UInt8 because some of them are String/FixedString and some of them are not (version 19.10.1.5 (official build))
570 [1,2,3,4] AS "[ ]";
571 -- 6.合并N個(gè)數(shù)組 arrayConcat(arrays) 合并參數(shù)中傳遞的所有數(shù)組。跟java的數(shù)組差不多的合并，不會(huì)自動(dòng)去重，不會(huì)自動(dòng)排序
572 SELECT
573 arrayConcat(array(1,2),array(2,3),array(4,5)), -- [1,2,2,3,4,5]（第一種情況）
574 arrayConcat(array(1,1),array(2,2),array(3,3)), -- [1,1,2,2,3,3]
575 -- arrayConcat(array(1,2),['a','c'],array(3,3)), -- 出現(xiàn)異常，不能將不同類型的數(shù)組進(jìn)行合并
576 arrayConcat(array(1,1),[2,3],array(4,5)); -- [1,1,2,3,4,5]
577 -- 7.從數(shù)組arr中獲取索引為“n”的元素。
578 -- n必須是任何整數(shù)類型。 數(shù)組中的索引從一開始。 支持負(fù)索引。在這種情況下，它選擇從末尾開始編號(hào)的相應(yīng)元素。例如，arr [-1]是數(shù)組中的最后一項(xiàng)。
579 -- 如果索引超出數(shù)組的邊界，則返回默認(rèn)值（數(shù)字為0，字符串為空字符串等）.
580 SELECT
581 arrayElement(array(10,20,3), 1), -- 10
582 arrayElement(array(1,20,3), 2), -- 20
583 arrayElement(array(1,2,30), 3), -- 30
584 arrayElement(array(10,20,3), 0), -- 0
585 arrayElement(array(10,20,3), -3), -- 10
586 arrayElement(array(10,20,3), -2), -- 20
587 arrayElement(array(10,20,3), -1);-- 3
588 -- 8.檢查在數(shù)組中是否含有此元素。has(arr, elem) 包含此元素則返回1，否則返回0
589 -- has() 檢查'arr'數(shù)組是否具有'elem'元素。 如果元素不在數(shù)組中，則返回0;如果在，則返回1。
590 -- hasAny(arr1, arr2) 返回1表示arr1和arr2存在交集。否則返回0.
591 --注意：特殊的定義：
592 -- ① “NULL”作為數(shù)組中的元素值進(jìn)行處理。
593 -- ② 忽略兩個(gè)數(shù)組中的元素值的順序
594 -- hasAll(set, subset) 檢查一個(gè)數(shù)組是否是另一個(gè)數(shù)組的子集。返回1，表示set包含subset中所有的元素
595 -- set – 具有一組元素的任何類型的數(shù)組。
596 -- subset – 任何類型的數(shù)組，其元素應(yīng)該被測試為set的子集。
597 -- 注意：特殊的定義：
598 -- ① 空數(shù)組是任何數(shù)組的子集。
599 -- ② “NULL”作為數(shù)組中的元素值進(jìn)行處理。
600 -- ③ 忽略兩個(gè)數(shù)組中的元素值的順序。
601 SELECT
602 has([1,2,3], 2), -- 1
603 has(array(1,2,3),2), -- 1
604 has([1,2,NULL], NULL), -- 1 (注意：null值的處理)
605 -- has([], 2), -- 出現(xiàn)異常，DB::Exception: Types of array and 2nd argument of function has must be identical up to nullability or numeric types or Enum and numeric type. Passed: Array(Nothing) and UInt8
606 has([1,2], 3); -- 0
607 SELECT
608 hasAll([], []), -- 1
609 hasAll([1,NULL,NULL], [NULL]), -- 1
610 hasAll([1,2,3], [1,2]), -- 1
611 hasAll([1,2,2,3], [2]), -- 1
612 hasAll(array(1,2,2,3), [2]), -- 1
613 hasAll([1,2,3], [4,5]); -- 0
614 -- 多重?cái)?shù)組（如下的二維數(shù)組）。
615 SELECT hasAll([[1, 2], [3, 4]], [[1, 2], [3, 5]]); -- 0
616 SELECT
617 hasAny(array(1,2,3), array(1)), -- 1
618 hasAny(array(1,2,3), array(1,4,56,80)), -- 1
619 -- []與array()是一樣的含義，本質(zhì)上是一直的。只不過[]更加簡便而已。
620 hasAny(array(), array()), -- 0
621 hasAny([],[]), -- 0
622 hasAny([1],[]), -- 0
623 -- 空數(shù)組跟null不是一樣的對(duì)象
624 hasAny([1,NULL],[]), -- 0
625 hasAny([1,NULL],[NULL,2]); -- 1
626
627
628 -- 9.返回?cái)?shù)組指定元素的索引
629 -- indexOf(arr, x) 返回?cái)?shù)組中第一個(gè)‘x’元素的索引（從1開始），如果‘x’元素不存在在數(shù)組中，則返回0。
630 SELECT indexOf(['one','two','three'], 'one'); -- 1
631 SELECT indexOf([1, 2, 4], 4); -- 3
632 SELECT
633 indexOf(['one','two','three'], 'one'), -- 1
634 indexOf(['one',NULL,NULL], NULL),-- 1返回第一個(gè)找到的元素的索引位置
635 indexOf([1, 2, 4], 4); -- 3
636 -- 數(shù)組元素的以第一個(gè)和最后一個(gè)元素。
637 SELECT length([12,3,4,4,4]);
638 SELECT array(12,22,31)[1];
639
640 WITH
641 [23,43,565,2,32,34] AS arr
642 SELECT
643 arr[1], -- 去除數(shù)組中的第一個(gè)元素
644 arr[length(arr)]; -- 提取元素中的最后一個(gè)元素
645
646 -- 10.計(jì)算數(shù)組中包含指定元素的個(gè)數(shù)
647 -- countEqual(arr, x) 返回?cái)?shù)組中等于x的元素的個(gè)數(shù)。相當(dāng)于arrayCount（elem - > elem = x，arr）。
648 -- 注意：null值將作為單獨(dú)的元素值處理。
649 SELECT
650 countEqual([1, 2, 2, 2, 3, 4], 2), -- 3
651 countEqual([1, 2, NULL, NULL], NULL); -- 2
652
653 -- 11.arrayEnumerate(arr) 返回 Array [1, 2, 3, ..., length (arr) ] 此功能通常與ARRAY JOIN一起使用。它允許在應(yīng)用ARRAY JOIN后為每個(gè)數(shù)組計(jì)算一次。
654 SELECT arrayEnumerate([1,20,20,3]); -- [1,2,3,4]
655 SELECT arrayEnumerate(array(11,20,13)); -- [1,2,3]
656 SELECT arrayEnumerate(array(11,20,13,NULL)); -- [1,2,3,4] 注意：null也算是一個(gè)元素。
657 --arrayEnumerateUniq(arr) 返回與源數(shù)組大小相同的數(shù)組，其中每個(gè)元素表示與其下標(biāo)對(duì)應(yīng)的源數(shù)組元素在源數(shù)組中出現(xiàn)的次數(shù)
658 SELECT arrayEnumerateUniq([1,1,2,2]); -- [1,2]
659
660 -- 12.刪除數(shù)組的元素
661 -- arrayPopBack(array) 刪除數(shù)組array的最后一項(xiàng)
662 SELECT arrayPopBack(array(1,2,3,0)) AS res; -- [1,2,3]
663 -- arrayPopFront(array) 從數(shù)組中刪除第一項(xiàng)
664 SELECT arrayPopFront(array(0,1,2,3)) AS res; -- [1,2,3]
665
666 -- 13.添加數(shù)組的元素 arrayPushFront(array, single_value) single_value是單個(gè)值
667 SELECT arrayPushBack([1,2,3], 0) AS res; -- [1,2,3,0]
668 SELECT arrayPushFront([1,2,3], 0) AS res; -- [0,1,2,3]
669
670 -- 14.更改數(shù)組的長度 arrayResize(arr, size[, extender])
671 -- 如果arr的長度 > size,則會(huì)對(duì)arr截取size的長度；
672 -- 如果arr的長度 < size，則其余位置用對(duì)應(yīng)數(shù)據(jù)類型的默認(rèn)值填充。
673 -- 注意：extender含義是擴(kuò)展元素的值。如果沒有指定extender，則默認(rèn)按照對(duì)應(yīng)的數(shù)據(jù)類型的默認(rèn)值進(jìn)行賦值。否則按照extender進(jìn)行填充。
674 SELECT arrayResize([1,2,3], 5); -- [1,2,3,0,0]
675 SELECT arrayResize([1,2,3], 2); -- [1,2]
676 SELECT arrayResize([1,2,3], 3); -- [1,2,3]
677 --↓↓↓ RuntimeException: Parse exception: ByteFragment{[[[1,2],[3,4],[5,6],[],[]]], start=0, len=25}
678 SELECT arrayResize([array(1,2),array(3,4),array(5,6)], 5);
679 SELECT arrayResize([1,2,3], 5, 12); -- [1,2,3,12,12]
680 SELECT arrayResize(['one','two','three'], 5); -- ['one','two','three','','']
681 SELECT arrayResize(['one','two','three'], 5, 'default'); -- ['one','two','three','default','default']
682
683 -- 15.截取數(shù)組的部分元素，得到一個(gè)新的子數(shù)組
684 -- arraySlice(array, offset[, length])
685 -- 解釋：
686 -- array: 數(shù)組，
687 -- offset – 數(shù)組的偏移。正值表示左側(cè)的偏移量，負(fù)值表示右側(cè)的縮進(jìn)值。數(shù)組下標(biāo)從1開始。
688 -- length - 子數(shù)組的長度。如果指定負(fù)值，則該函數(shù)返回[offset，array_length - length。如果省略該值，則該函數(shù)返回[offset，the_end_of_array]。
689 SELECT
690 arraySlice([1,2,3,4,5,6], 0, 3), -- 無返回值
691 arraySlice([1,2,NULL,5,6], 1, 3), -- [1,2,0]
692 arraySlice(['one','two',NULL], 1, 3), -- ['one','two','']
693 arraySlice([1,2,3,4,5,6], 1, 3); -- [1,2,3]
694
695 -- 16.數(shù)組排序：arraySort([func,] arr, ……)
696 -- 注意：如果在字符串?dāng)?shù)組中，''和NULL是需要特別對(duì)待的，''需要放在最前面，而NULL則是按順序存放到最后的。
697 -- arraySort是高階函數(shù)。您可以將lambda函數(shù)作為第一個(gè)參數(shù)傳遞給它。在這種情況下，排序順序由lambda函數(shù)的調(diào)用結(jié)果決定。
698 SELECT
699 arraySort(['a','',NULL,'c','b']) AS hasNullempty1, --['','a','b','c',''] (第一個(gè)是'',最后一個(gè)''起始是NULL)
700 arraySort(array('ac','ab','bc','ad',NULL)) AS hasNull, -- ['ab','ac','ad','bc','']
701 arraySort(array('ac','','ab',NULL,'bc','ad',NULL)) AS hasNullempty2, -- ['','ab','ac','ad','bc','','']
702 arraySort([5,4,3,2,1]) AS numSorted,-- [1,2,3,4,5] (數(shù)字排序)
703 arraySort(['ca','bb','ac']) AS strSorted;-- ['ac','bb','ca'] (字符串排序)
704 SELECT
705 arraySort([NULL, 1, 3, NULL, 2]) AS sortedArr, -- [1,2,3,0,0]
706 arrayReverse(sortedArr) AS reverseSortdArr;-- [0,0,3,2,1]
707 -- 下面這種排序的實(shí)質(zhì)，正數(shù)轉(zhuǎn)成負(fù)數(shù)，再在數(shù)學(xué)上比較升序排序。
708 SELECT arraySort(x -> -x, [1,2,3]) as res; -- [3,2,1] 降序：（高階函數(shù)用法）
709 SELECT arraySort((x) -> -x, [1,2,3]) as res; -- [3,2,1] 降序：（高階函數(shù)用法）
710 SELECT arraySort(x -> x, [5,4,3,1,2,3]) as res; -- [1,2,3,3,4,5] 升序：（高階函數(shù)用法）
711 SELECT arraySort((x) -> x, [5,4,3,1,2,3]) as res; -- [1,2,3,3,4,5] 升序：（高階函數(shù)用法）
712 -- arraySort(lambda, arr1, arr2)
713 SELECT arraySort((x, y) -> y, ['hello', 'world'], [2, 1]) as res; -- ['world','hello']
714 SELECT arraySort((x, y) -> -y, [0, 1, 2], [1, 2, 3]) as res; -- [2,1,0]
715 -- 再次提醒：NULL, NaN, Inf的排序順序：
716 -- 含義：
717 -- -Inf 是數(shù)組中的第一個(gè)。
718 -- NULL 是數(shù)組中的最后一個(gè)。
719 -- NaN 在NULL的前面。
720 -- Inf 在NaN的前面。
721 -- 出現(xiàn)異常：RuntimeException: Parse exception:
722 -- ByteFragment{[[-inf,-4,1,2,3,inf,nan,nan,NULL,NULL]], start=0, len=37}
723 SELECT arraySort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf]);
724
725 -- 17.數(shù)組翻轉(zhuǎn)：arrayReverse([func,] arr, ……)
726 -- 如果是NULL的話在排序的過程中，根據(jù)數(shù)組的數(shù)據(jù)類型進(jìn)行默認(rèn)值填充。
727 SELECT
728 arrayReverse(array('a','b','c',NULL)) AS hasOneNull, -- ['','c','b','a']
729 arrayReverse(array('ac','ab','bc','ad',NULL)) AS hasNull, -- ['','ad','bc','ab','ac']
730 --網(wǎng)格視圖： ['[NULL]','ad','bc','','ab','[NULL]','','ac']；文本視圖 ：['','ad','bc','','ab','','','ac']
731 arrayReverse(array('ac','',NULL,'ab','','bc','ad',NULL)) AS hasNullEmpty,
732 arrayReverse(array(NULL, 3, NULL, 2, 1)),-- [1,2,0,3,0]
733 arrayReverse([1,2,3,4]);-- [4,3,2,1]
734
735 -- 18.數(shù)組排序并翻轉(zhuǎn)：arraySort([func,] arr, ...)
736 SELECT arrayReverseSort([1, 3, 3, 0]); -- [3,3,1,0]
737 SELECT arrayReverseSort(['hello', 'world', '!']); -- ['world','hello','!']
738 --RuntimeException: Parse exception: ByteFragment{[[inf,3,2,1,-4,-inf,nan,nan,NULL,NULL]], start=0, len=37}
739 SELECT arrayReverseSort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf]) as res;-- [inf,3,2,1,-4,-inf,nan,nan,NULL,NULL]
740 -- 下面的執(zhí)行順序?yàn)椋? 741 -- 1.首先，根據(jù)lambda函數(shù)的調(diào)用結(jié)果對(duì)源數(shù)組（[1, 2, 3]）進(jìn)行排序。 結(jié)果是[3, 2, 1]。
742 -- 2.反轉(zhuǎn)上一步獲得的數(shù)組。 所以，最終的結(jié)果是[1, 2, 3]。
743 SELECT arrayReverseSort((x) -> -x, [1, 2, 3]) as res; -- [1,2,3]
744 SELECT arrayReverseSort((x) -> x, [1, 2, 3]) as res; -- [1,2,3]
745 -- 下面的執(zhí)行順序?yàn)椋? 746 -- 1.首先，根據(jù)lambda函數(shù)的調(diào)用結(jié)果對(duì)源數(shù)組（['hello'，'world']）進(jìn)行排序。 其中，在第二個(gè)數(shù)組（[2,1]）中定義了源數(shù)組中相應(yīng)元素的排序鍵。 所以，排序結(jié)果['world'，'hello']。
747 -- 2.反轉(zhuǎn)上一步驟中獲得的排序數(shù)組。 所以，最終的結(jié)果是['hello'，'world']。
748 SELECT arrayReverseSort((x, y) -> y, ['hello', 'world'], [2, 1]) as res;-- ['hello','world']
749 SELECT arrayReverseSort((x, y) -> -y, ['hello', 'world'], [2, 1]) as res;-- ['world','hello']
750 SELECT arrayReverseSort((x, y) -> x, ['hello', 'world'], [2, 1]) as res;-- ['world','hello']
751 --出現(xiàn)異常：Illegal type String of argument
752 --SELECT arrayReverseSort((x, y) -> -x, ['hello', 'world'], [2, 1]) as res;
753 SELECT arrayReverseSort((x, y) -> x, ['hello', 'world'], [1, 2]) as res;-- ['world','hello']
754
755 -- 19.統(tǒng)計(jì)數(shù)組中不重復(fù)元素的個(gè)數(shù)。arrayUniq(arr,……)
756 -- ① 如果傳遞一個(gè)參數(shù)，則計(jì)算數(shù)組中不同元素的數(shù)量。
757 -- ② 如果傳遞了多個(gè)參數(shù)，則它計(jì)算多個(gè)數(shù)組中相應(yīng)位置的不同元素元組的數(shù)量
758 SELECT
759 arrayUniq([1,2,3]), -- 3
760 arrayUniq([1,2,2,2,3]); -- 3
761 SELECT
762 arrayUniq([1,2,3],[2,3,4]),
763 arrayUniq([1,2,2],[1,3,3]);
764
765 -- 20.數(shù)組的特殊功能：arrayJoin(arr) 這是一個(gè)非常有用的函數(shù)。
766 -- 解釋：此函數(shù)將數(shù)組作為參數(shù)，并將該行在結(jié)果集中復(fù)制數(shù)組元素個(gè)數(shù)
767 SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src;
768 -- 每個(gè)元素?cái)U(kuò)大兩倍；
769 SELECT arrayJoin([1,2,3]) * 2;
770 SELECT arrayJoin([-1,-2,0,1,2]) * 2;
771 --出現(xiàn)異常： Illegal types Array(UInt8) and Array(UInt8) of arguments of function multiply
772 --SELECT multiply(array(1,2,3), 2);
773 SELECT multiply(arrayJoin([-1,-2,0,1,2]), 2);
774 -- 每個(gè)元素縮小兩倍
775 SELECT arrayJoin([-4,-2,0,2,4]) / 2;
776 SELECT divide(arrayJoin([-4,-2,0,2,4]) , 2);
777
778
779 -- 21.arrayDifference(arr)
780 -- 返回一個(gè)數(shù)組，其中包含所有相鄰元素對(duì)之間的差值
781 SELECT arrayDifference([1,2,3,4]);-- [0,1,1,1]
782 SELECT arrayDifference([1,3,10,50]);-- [0,2,7,40]
783
784
785 -- 22. arrayDistinct(arr)返回一個(gè)包含所有數(shù)組中不同元素的數(shù)組.
786 -- 類似于java的Set集合，對(duì)list集合進(jìn)行去重。
787 SELECT arrayDistinct(array(1,2,3,4,4,4)); -- [1,2,3,4]
788 SELECT arrayDistinct([1,2,2,3,4,2,2,5,4,5]); -- [1,2,3,4,5]
789 SELECT arrayDistinct(array(0,1,NULL,3,4,4,4)); -- [0,1,3,4]
790 -- 數(shù)組去重統(tǒng)計(jì)元素個(gè)數(shù)
791 SELECT uniq(arrayJoin([1,2,3,6,3])); -- 4 表示數(shù)組去重后元素的個(gè)數(shù)
792 SELECT uniqArray([1,2,3,4,1,2,3,4]); -- 4 表示數(shù)組去重后元素的個(gè)數(shù)
793 -- 數(shù)組元素累計(jì)
794 SELECT sumArray([1,2,3,4,5]);-- 15
795 SELECT sum(arraySum([1,2,3,4,5])); -- 15
796
797
798 -- 23. arrayEnumerateDense(arr) 返回與源數(shù)組大小相同的數(shù)組，指示每個(gè)元素首次出現(xiàn)在源數(shù)組中的位置
799 SELECT
800 arrayEnumerateDense([10,20,20,10,30]) AS numArrEnumDense,-- [1,2,2,1,3]
801 -- [1,1,2,3,4,1,3,5,5]
802 arrayEnumerateDense([10,10,2,12,3,10,12,NULL,NULL]) as arrEnumDenseHasNull,
803 -- [1,2,1,1,2,3]
804 arrayEnumerateDense([10,20,10,10,20,30]) AS arrEnumDese2;
805
806 -- 24. arrayIntersect(arr,……) 返回所有數(shù)組元素的交集。
807 -- 如果arr的數(shù)目只有一個(gè)，則返回它本身；如果有多個(gè)數(shù)組,則返回所有數(shù)組中元素的交集。
808 SELECT
809 -- 注意：最后得到的數(shù)組元素的順序。（有什么影響嗎？）
810 arrayIntersect(['one','two'],['one','two','three']) as uniStrArr1, -- ['two','one']
811 arrayIntersect(['aaa','bbb'],['bbb','aaa','three']) as uniStrArr2, -- ['bbb','aaa']
812 arrayIntersect([1,2],[1,2,3]) as uniArr1, -- [1,2]
813 arrayIntersect([1,2],[1,2,3],[2,3,4],[2,3,4]) as uniArr2; -- 2
814 SELECT
815 arrayIntersect([1,2], [3,4]), -- []
816 arrayIntersect([1,2]);-- [1,2]
817
818 -- 25.arrayReduce(agg_func, arr1, ...)
819 -- agg_func 為聚合函數(shù)，傳入到數(shù)組當(dāng)中。
820 -- 將聚合函數(shù)應(yīng)用于數(shù)組并返回其結(jié)果.如果聚合函數(shù)具有多個(gè)參數(shù)，則此函數(shù)可應(yīng)用于相同大小的多個(gè)數(shù)組。
821 SELECT
822 arrayReduce('max', [1,2,3]) AS minNum,--最大值 3
823 arrayReduce('min', [1,2,3]) AS maxNum,--最小值 1
824 arrayReduce('sum', [1,2,3]) AS sumNum;--求和 6
825
826 -- 十三、 字符串查分合并函數(shù)
827 --->>>>>> 字符串拆分合并函數(shù)
828 -- 1.splitByChar(separator, s) 將字符串以‘separator’拆分成多個(gè)子串。
829 -- ‘separator’必須為僅包含一個(gè)字符的字符串常量。 返回拆分后的子串的數(shù)組。
830 -- 如果分隔符出現(xiàn)在字符串的開頭或結(jié)尾，或者如果有多個(gè)連續(xù)的分隔符，則將在對(duì)應(yīng)位置填充空的子串。
831 SELECT splitByChar(',', 'hello,world!'); -- ['hello','world!']
832 --下面異常：Illegal separator for function splitByChar. Must be exactly one byte.
833 --SELECT splitByChar('or', 'hello,world!');
834
835 -- 2.splitByString(separator, s)
836 -- 與上面相同，但它使用多個(gè)字符的字符串作為分隔符。 該字符串必須為非空
837 SELECT splitByString('or','goodorniceorgreat'); -- ['good','nice','great']
838
839 -- 3.alphaTokens(s) 從范圍a-z和A-Z中選擇連續(xù)字節(jié)的子字符串。返回子字符串?dāng)?shù)組
840 SELECT alphaTokens('abca1abc'); -- ['abca','abc']
841 SELECT alphaTokens('abc1232abc2wer3rtty'); -- ['abc','abc','wer','rtty']
842
843 -- 4.數(shù)組元素合并函數(shù)：arrayStringConcat(arr[, sparator])
844 -- 使用separator將數(shù)組中列出的字符串拼接起來。
845 -- ‘separator’是一個(gè)可選參數(shù)：一個(gè)常量字符串，默認(rèn)情況下設(shè)置為空字符串。 返回拼接后的字符串
846 SELECT arrayStringConcat([1,2,3], '-'); -- 出現(xiàn)異常，要求數(shù)組必須是字符串string類型的元素
847 SELECT arrayStringConcat(['one','two','three']); -- onetwothree
848 SELECT arrayStringConcat(['one','two','three'], '-'); -- one-two-three
849 SELECT arrayStringConcat(['one','two','three',''], '-');-- one-two-three- 注意：NULL不能存在arr中
850
851
852 --十四、位操作符
853 --->>>>>> 位操作符
854 --位操作函數(shù)適用于UInt8，UInt16，UInt32，UInt64，Int8，Int16，Int32，Int64，F(xiàn)loat32或Float64中的任何類型。
855 --結(jié)果類型是一個(gè)整數(shù)，其位數(shù)等于其參數(shù)的最大位。
856 --如果至少有一個(gè)參數(shù)為有符數(shù)字，則結(jié)果為有符數(shù)字。如果參數(shù)是浮點(diǎn)數(shù)，則將其強(qiáng)制轉(zhuǎn)換為Int64。
857 SELECT
858 bitAnd(1,0), -- 0
859 bitAnd(1,1), -- 1
860 bitAnd(1,2), -- 0
861 bitAnd(-1,0), -- 0
862 bitAnd(-1,-2), -- -2
863 bitAnd(-10,-1), -- -10
864 bitOr(1,2), -- 3
865 bitOr(1,0), -- 1
866 bitOr(2,0), -- 2
867 bitOr(0,2); -- 2
868 SELECT bitXor(1, 2), bitXor(20, 15), bitNot(2);-- 3 27 253
869
870
871 --十五、Hash函數(shù)：可以用于將元素不可逆的偽隨機(jī)打亂。
872 -- 注意：偽隨機(jī)！
873 SELECT
874 -- 計(jì)算字符串的MD5值。（ 如果您不需要一定使用MD5，請(qǐng)使用‘sipHash64’函數(shù)。）
875 halfMD5('HELLO WORLD!'),
876 halfMD5(12);
877 SELECT
878 MD5('drew-zero,78967');
879
880 SELECT
881 -- 為任何類型的整數(shù)計(jì)算32位的哈希。 這是相對(duì)高效的非加密Hash函數(shù)
882 intHash32(1221232132132) AS intHash32,
883 -- 推薦：從任何類型的整數(shù)計(jì)算64位哈希碼。 它的工作速度比intHash32函數(shù)快。
884 intHash64(1221232132132) AS intHash64,
885 -- 計(jì)算任意數(shù)量字符串的CityHash64或使用特定實(shí)現(xiàn)的Hash函數(shù)計(jì)算任意數(shù)量其他類型的Hash。
886 cityHash64('username') AS cityHash64,
887 -- 1.使用sha1或者sha224加密的話，只能用于字符串
888 -- 2.字符串 需使用單引號(hào)。
889 SHA1('1232131') AS sha1,
890 SHA224('1232131') AS sha224,
891 SHA256('DREW-ZERO') AS sha256;
892
893 -- URLHash(url[, N]) 一種快速的非加密哈希函數(shù)，用于規(guī)范化的從URL獲得的字符串
894 -- 從一個(gè)字符串計(jì)算一個(gè)哈希，如果結(jié)尾存在尾隨符號(hào)/，？或#則忽略。 URLHash（s，N）
895 -- 計(jì)算URL層次結(jié)構(gòu)中字符串到N級(jí)別的哈希值，如果末尾存在尾隨符號(hào)/，？或#則忽略。 URL的層級(jí)與URLHierarchy中的層級(jí)相同
896 -- 用處：此函數(shù)被用于Yandex.Metrica。
897 SELECT
898 URLHash('www.baidu.com'), -- 11390370829909720855
899 URLHash('www.baidu.com', 0), -- 11390370829909720855
900 --
901 URLHash('www.baidu.com', 1); -- 11160318154034397263
902
903 -- farmHash64(s) 計(jì)算字符串的FarmHash64。 接受一個(gè)String類型的參數(shù)。返回UInt64。
904 SELECT farmHash64('www.runoob.com'); -- 6668483584160323388
905
906 -- javaHash(s) 計(jì)算字符串的JavaHash。 接受一個(gè)String類型的參數(shù)。返回Int32。
907 SELECT javaHash('www.baidu.com'); -- 270263191
908
909 -- hiveHash(s) 計(jì)算字符串的HiveHash。 接受一個(gè)String類型的參數(shù)。返回Int32。 與JavaHash相同，但不會(huì)返回負(fù)數(shù)
910 SELECT hiveHash('www.baidu.com'); -- 270263191
911
912
913 --十六、隨機(jī)函數(shù)
914 --->>>>>> 隨機(jī)函數(shù)
915 -- 解釋：隨機(jī)函數(shù)使用非加密方式生成【偽隨機(jī)】數(shù)字。
916 -- ① 所有隨機(jī)函數(shù)都只接受一個(gè)參數(shù)或不接受任何參數(shù)。
917 -- ② 您可以向它傳遞任何類型的參數(shù)，但傳遞的參數(shù)將不會(huì)使用在任何隨機(jī)數(shù)生成過程中。
918 -- ③ 此參數(shù)的唯一目的是防止公共子表達(dá)式消除，以便在相同的查詢中使用相同的隨機(jī)函數(shù)生成不同的隨機(jī)數(shù)
919 -- rand() 函數(shù)：返回一個(gè)UInt32類型的隨機(jī)數(shù)字，所有UInt32類型的數(shù)字被生成的概率均相等。
920 -- rand64() 函數(shù)：返回一個(gè)UInt64類型的隨機(jī)數(shù)字，所有UInt64類型的數(shù)字被生成的概率均相等。
921 -- randConstant() 函數(shù)：返回一個(gè)UInt32類型的隨機(jī)數(shù)字，該函數(shù)不同之處在于僅為每個(gè)數(shù)據(jù)塊參數(shù)一個(gè)隨機(jī)數(shù)。
922 SELECT
923 rand(), -- 1751687411
924 rand(10), -- 1124981728
925 rand64(),
926 rand64(10),
927 randConstant(),
928 randConstant();
929
930
931 -- 十七、編碼函數(shù)：
932 -- hex(), unhex(), UUIDStringToNum(str), UUIDNumToString(str),bitmaskToList(num) ...
933 -- 1.hex函數(shù)編碼
934 SELECT
935 -- 68656C6C6F20776F726C64212C68656C6C6F20636C69636B686F757365
936 hex('hello world!,hello clickhouse') AS hexStr,
937 hex(now()) AS hexDatetime, -- 5D414BA2
938 hex(toDate(now())) AS hexDate; --46BC
939
940 -- 2.接受包含36個(gè)字符的字符串，格式為“123e4567-e89b-12d3-a456-426655440000”，并將其轉(zhuǎn)化為FixedString（16）返回
941 SELECT UUIDStringToNum('123e4567-e89b-12d3-a456-426655440000');
942
943 -- 3. 接受一個(gè)整數(shù)。返回一個(gè)UInt64類型數(shù)組，其中包含一組2的冪列表，其列表中的所有值相加等于這個(gè)整數(shù)。數(shù)組中的數(shù)字按升序排列。
944 -- bitmaskToArray(num)
945 SELECT bitmaskToArray(10); -- [2,8]
946 SELECT bitmaskToArray(100); -- [4,32,64]
947
948 -- 4.接受一個(gè)整數(shù)。返回一個(gè)字符串，其中包含一組2的冪列表，其列表中的所有值相加等于這個(gè)整數(shù)。列表使用逗號(hào)分割，按升序排列。
949 -- bitmaskToList(num)
950 SELECT bitmaskToList(10); -- 2,8
951 SELECT bitmaskToList(100); -- 4,32,64
952 SELECT bitmaskToList(0); -- '' 空字符串
953
954
955 --十八、UUID函數(shù)
956 --->>>>>> UUID函數(shù)
957 -- 1.generateUUIDv4() 返回 UUID類型的值。
958 SELECT generateUUIDv4() as randomUUID; -- 隨機(jī)生成一個(gè)UUIDv4的字符串（b6940dfe-0dc9-4788-bac7-319d13235a2e）
959 SELECT replaceAll(toString(generateUUIDv4()), '-', '') AS replaceUUID; -- 9d1947ea4fcf450da5391feb6142cab6
960
961 -- 2.toUUID(s) 將string類型的值 轉(zhuǎn)換成UUID類型的值
962 SELECT toUUID('61f0c404-5cb3-11e7-907b-a6006ad3dba0') AS uuid;
963
964 -- 3.接受一個(gè)String類型的值，其中包含36個(gè)字符且格式為xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx，
965 -- 將其轉(zhuǎn)換為UUID的數(shù)值并以FixedString(16)將其返回。
966 SELECT
967 '612f3c40-5d3b-217e-707b-6a546a3d7b29' AS uuid, -- 612f3c40-5d3b-217e-707b-6a546a3d7b29
968 UUIDStringToNum(uuid) AS bytes; --a/<@];!~p{jTj={)
969
970 -- 4. UUIDNumToString() 接受一個(gè)FixedString(16)類型的值，返回其對(duì)應(yīng)的String表現(xiàn)形式。
971 SELECT 'a/<@];!~p{jTj={)' AS bytes,
972 UUIDNumToString(toFixedString(bytes, 16)) AS uuid;
973
974 --- 二十、 URL函數(shù)：所有這些功能都不遵循RFC。它們被最大程度簡化以提高性能。
975 --- 什么事RFC？
976 ---- Request For Comments（RFC），是一系列以編號(hào)排定的文件。文件收集了有關(guān)互聯(lián)網(wǎng)相關(guān)信息，以及UNIX和互聯(lián)網(wǎng)社區(qū)的軟件文件。
977 -- 1. 截取函數(shù)：如果URL中沒有要截取的內(nèi)容則返回空字符串。
978 SELECT protocol('http://www.baidu.com');-- http
979 SELECT protocol('https://www.baidu.com');-- https
980 SELECT protocol('www.baidu.com');-- ''
981 -- 獲取域名。
982 SELECT domain('http://www.baidu.com'); -- www.baidu.com
983 SELECT domain('https://www.google.com.cn'); -- www.google.com.cn
984 -- 返回域名并刪除第一個(gè)‘www.’
985 SELECT domainWithoutWWW('http://www.baidu.com');-- baidu.com
986 SELECT domainWithoutWWW('www.baidu.com');-- ''
987 -- 返回頂級(jí)域名。例如：.ru
988 SELECT topLevelDomain('http://www.runoob.com.cn'); -- cn
989 SELECT topLevelDomain('https://www.huse.edn'); -- edu
990 -- 返回“第一個(gè)有效子域名”
991 -- 如果頂級(jí)域名為‘com’，‘net’，‘org’或者‘co’則第一個(gè)有效子域名為二級(jí)域名。否則則返回三級(jí)域名
992 SELECT firstSignificantSubdomain('https://news.yandex.com.tr/'); -- yandex
993 -- 返回包含頂級(jí)域名與第一個(gè)有效子域名之間的內(nèi)容(參閱上面內(nèi)容)
994 SELECT cutToFirstSignificantSubdomain('https://news.yandex.com.tr/'); -- yandex.com.tr
995 -- 返回URL路徑
996 SELECT path('https://blog.csdn.net/u012111465/article/details/85250030');-- /u012111465/article/details/85250030
997 -- 與上面相同，但包括請(qǐng)求參數(shù)和fragment。
998 SELECT pathFull('https://clickhouse.yandex/#quick-start'); -- /#quick-start
999 -- 返回請(qǐng)求參數(shù)。例如：page=1&lr=213。請(qǐng)求參數(shù)不包含問號(hào)已經(jīng)# 以及# 之后所有的內(nèi)容。
1000 SELECT queryString('http://www.baidu.com/?page=1&lr=234'); -- page=1&lr=234 (根據(jù)？確定)
1001 SELECT queryString('http://www.baidu.com/page=1&lr=234'); -- ''
1002 -- 返回URL的fragment標(biāo)識(shí)。fragment不包含#。
1003 SELECT fragment('https://clickhouse.yandex/#quick-start'); -- quick-start
1004 -- 返回請(qǐng)求參數(shù)和fragment標(biāo)識(shí)。例如：page=1#29390。
1005 SELECT queryStringAndFragment('https://www.baidu.com/s?ie=utf-8&rsv_sug7=100#ei-ai'); -- ie=utf-8&rsv_sug7=100#ei-ai
1006
1007
1008 -- 2. 刪除URL中的部分內(nèi)容 (如果URL中不包含指定的部分，則URL不變。)
1009 SELECT cutWWW('www.baidu.com');-- www.baidu.com
1010 SELECT cutWWW('https://www.baidu.com');-- www.baidu.com
1011 SELECT cutWWW('https://www.baidu.com');-- www.baidu.com
1012 -- 刪除請(qǐng)求參數(shù)
1013 SELECT cutQueryString('http://www.baidu.com/1?page=1'); -- http://www.baidu.com/1
1014 -- 刪除fragment標(biāo)識(shí)。#同樣也會(huì)被刪除。
1015 SELECT cutFragment('http://www.baidu.com/#quick-demo'); -- http://www.baidu.com/
1016 -- 刪除請(qǐng)求參數(shù)以及fragment標(biāo)識(shí)。問號(hào)以及#也會(huì)被刪除。
1017 SELECT cutQueryStringAndFragment('http://www.baidu.com/1?page=23#we'); -- http://www.baidu.com/1
1018 -- cutURLParameter(URL, name) 刪除URL中名稱為‘name’的參數(shù)。下面例子中的參數(shù)是：&之后，resv,name
1019 SELECT cutURLParameter('http://www.baidu.com/1?page=1#erre&resv=23&name=user','resv');
1020
1021
1022 --二十一、IP函數(shù)
1023
1024
1025
1026 --二十二、條件函數(shù)
1027 SELECT IF(12 > 10 , 12, 20);
1028 SELECT 12 > 10 ? 12 : 10;
1029 SELECT if(greater(12, 10), 12, 10);
1030
1031
1032
1033 --二十三、操作符函數(shù)替換
1034 -- clickhouse自帶的計(jì)算操作符函數(shù)（對(duì)接mybatis的時(shí)候不用將“<”之類的符號(hào)轉(zhuǎn)換成 “age1 <![CDATA[ < ]] 2>”）
1035 -- 1.等于(注意函數(shù)名稱的大小，嚴(yán)格區(qū)分大小寫)
1036 SELECT
1037 equals('hello','hello'), -- 1
1038 equals('ab','ba'); -- 0
1039 -- 2.不等于
1040 SELECT
1041 notEquals('a','b'), -- 1
1042 notEquals('a','a'), -- 0
1043 notEquals(12, 12), -- 1
1044 notEquals(12, 1010); -- 0
1045 -- 3.大于( 如果前者大于后者，則返回1；否則返回0)
1046 SELECT
1047 greater(12, 10), -- 1
1048 greater(10, 12), -- 0
1049 greater(12, 12), -- 0
1050 greater('b','a'), -- 1
1051 greater('a','b'); -- 0
1052 -- 3.1 擴(kuò)展：提取兩者中最大的值
1053 SELECT greatest(12,11); -- 12
1054 -- 4.小于（如果前者小于后者，則返回1；否則返回0）
1055 SELECT less(12,23); -- 1
1056 SELECT less(120,23); -- 0
1057 -- 5.大于或等于
1058 SELECT greaterOrEquals(12,12); -- 1
1059 SELECT greaterOrEquals(120,12); -- 1
1060 -- 6.小于或等于
1061 SELECT lessOrEquals(12,12); -- 1
1062 SELECT lessOrEquals(12,129); -- 1
1063 -- ===== String操作
1064 -- *. a LIKE s
1065 SELECT like('a', 'abcd'); -- 0
1066 SELECT like('a', 'a'); -- 1
總結(jié)
以上是生活随笔為你收集整理的clickhouse日期函数的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： brew mysql 无法启动_MAC
- 下一篇： keil编译后生成的M51文件解析