三、ClickHouse基础：数据类型

作者: nieniemin | 来源:发表于2021-07-22 13:56 被阅读0次

3.1 数据类型

MySQL	ClickHouse
byte	Int8
short	Int16
int	Int32
long	Int64
varchar	String
timestamp	DateTime
float	Float32
double	Float64
boolean	无

3.1.1 整形

固定长度的整型，包括有符号整型或无符号整型。

整型范围（-2n-1~2n-1-1）：

Int8 - [-128 : 127]

Int16 - [-32768 : 32767]

Int32 - [-2147483648 : 2147483647]

Int64 - [-9223372036854775808 : 9223372036854775807]

无符号整型范围（0~2n-1）：

固定长度的整型，包括有符号整型或无符号整型。

整型范围（-2n-1~2n-1-1）：

Int8 - [-128 : 127]

Int16 - [-32768 : 32767]

Int32 - [-2147483648 : 2147483647]

Int64 - [-9223372036854775808 : 9223372036854775807]

无符号整型范围（0~2n-1）：

UInt8 - [0 : 255]

UInt16 - [0 : 65535]

UInt32 - [0 : 4294967295]

UInt64 - [0 : 18446744073709551615]

3.1.2 浮点型

Float32 - float

Float64 – double

建议尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，如时间用毫秒为单位表示，因为浮点型进行计算时可能引起四舍五入的误差。

3.1.3 布尔型

没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。

3.1.4 字符串

1）String

字符串可以任意长度的。它可以包含任意的字节集，包含空字节。

2）FixedString(N)

固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字符串末尾添加空字节来达到 N 字节长度。当服务端读取长度大于 N 的字符串时候，将返回错误消息。

与String相比，极少会使用FixedString，因为使用起来不是很方便。

3.1.5 枚举类型

包括 Enum8 和 Enum16 类型。Enum 保存 'string'= integer 的对应关系。

Enum8 用 'String'= Int8 对描述。

Enum16 用 'String'= Int16 对描述。

创建一个带有一个枚举 Enum8('hello' = 1, 'world' = 2) 类型的列：

CREATE TABLE t_enum
 (
  x Enum8('hello' = 1, 'world' = 2)
 )
 ENGINE = TinyLog

这个 x 列只能存储类型定义中列出的值：'hello'或'world'。如果尝试保存任何其他值，ClickHouse 抛出异常。

img

3.1.4 数组

Array(T)由 T 类型元素组成的数组。

T 可以是任意类型，包含数组类型。但不推荐使用多维数组，ClickHouse 对多维数组的支持有限。例如，不能在 MergeTree 表中存储多维数组。

可以使用array函数来创建数组：array(T)

也可以使用方括号：[]

创建数组案例：

img

3.1.5 元组

Tuple(T1, T2, ...):元组，其中每个元素都有单独的类型。

创建元组的示例：

img

3.1.6 Date

日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。

3.2 SQL语法

3.2.1 ALL 子句

SELECT ALL 和 SELECT 不带 DISTINCT 是一样的。

如果指定了 ALL ，则忽略它。
如果同时指定了 ALL 和 DISTINCT ，则会抛出异常。

3.2.2 ARRAY JOIN子句

对于包含数组列的表来说是一种常见的操作，用于生成一个新表，该表具有包含该初始列中的每个单独数组元素的列，而其他列的值将被重复显示。这是 ARRAY JOIN 语句最基本的场景。

ARRAY JOIN - 一般情况下，空数组不包括在结果中 JOIN.
LEFT ARRAY JOIN - 的结果 JOIN 包含具有空数组的行。空数组的值设置为数组元素类型的默认值（通常为0、空字符串或NULL）。

3.2.3 DISTINCT子句

如果 SELECT DISTINCT 被声明，则查询结果中只保留唯一行。因此，在结果中所有完全匹配的行集合中，只有一行被保留。

3.2.4 FORMAT子句

ClickHouse支持广泛的序列化格式可用于查询结果等。有多种方法可以选择格式化 SELECT 的输出，其中之一是指定 FORMAT format 在查询结束时以任何特定格式获取结果集。

3.2.5 FORM子句

FROM 子句指定从以下数据源中读取数据:

JOIN 和 ARRAY JOIN 子句也可以用来扩展 FROM 的功能。

3.2.6 GROUP BY子句

GROUP BY 子句将 SELECT 查询结果转换为聚合模式，其工作原理如下:

GROUP BY 子句包含表达式列表（或单个表达式 -- 可以认为是长度为1的列表）。这份名单充当 “grouping key”，而每个单独的表达式将被称为 “key expressions”.
在所有的表达式在 SELECT, HAVING，和 ORDER BY 子句中必须基于键表达式进行计算或上聚合函数在非键表达式（包括纯列）上。换句话说，从表中选择的每个列必须用于键表达式或聚合函数内，但不能同时使用。
聚合结果 SELECT 查询将包含尽可能多的行，因为有唯一值 “grouping key” 在源表中。通常这会显着减少行数，通常是数量级，但不一定：如果所有行数保持不变 “grouping key” 值是不同的。

3.2.7 HAVING 子句

允许过滤由 GROUP BY 生成的聚合结果. 它类似于 WHERE ，但不同的是 WHERE 在聚合之前执行，而 HAVING 之后进行。

可以从 SELECT 生成的聚合结果中通过他们的别名来执行 HAVING 子句。或者 HAVING 子句可以筛选查询结果中未返回的其他聚合的结果

3.2.8 INTO OUTFILE 子句

添加 INTO OUTFILE filename 子句（其中filename是字符串） SELECT query 将其输出重定向到客户端上的指定文件。

3.2.9 JOIN子句

Join通过使用一个或多个表的公共值合并来自一个或多个表的列来生成新表。它是支持SQL的数据库中的常见操作，它对应于关系代数加入。一个表连接的特殊情况通常被称为 “self-join”.

所有标准 SQL JOIN 支持类型:

INNER JOIN，只返回匹配的行。
LEFT OUTER JOIN，除了匹配的行之外，还返回左表中的非匹配行。
RIGHT OUTER JOIN，除了匹配的行之外，还返回右表中的非匹配行。
FULL OUTER JOIN，除了匹配的行之外，还会返回两个表中的非匹配行。
CROSS JOIN，产生整个表的笛卡尔积, “join keys” 是不指定。

3.2.10 LIMIT子句

LIMIT m 允许选择结果中起始的 m 行。

LIMIT n, m 允许选择个 m 从跳过第一个结果后的行 n 行。与 LIMIT m OFFSET n 语法是等效的。

LIMIT … WITH TIES 修饰符:

如果为 LIMIT n[,m] 设置了 WITH TIES ，并且声明了 ORDER BY expr_list, 除了得到无修饰符的结果（正常情况下的 limit n, 前n行数据), 还会返回与第n行具有相同排序字段的行(即如果第n+1行的字段与第n行拥有相同的排序字段，同样返回该结果.

此修饰符可以与： ORDER BY … WITH FILL modifier 组合使用.

3.2.11 LIMIT BY子句

与查询 LIMIT n BY expressions 子句选择第一个 n 每个不同值的行 expressions. LIMIT BY 可以包含任意数量的表达式.

SELECT * from hits_v1 limit 17,1 BY CounterID;
SELECT * from hits_v1 limit 1 OFFSET 17 BY CounterID;
--上面sql查询结果一致。假如表中有18条数据CounterID为1964542331，上面sql语句含义为跳过17条数据，limit 1:查询一条。

3.2.12 时间或日期截取函数（to）—— 返回非日期

函数	用途	举例	结果
toYear()	取日期或时间日期的年份	toYear(toDateTime(‘2018-12-11 11:12:13’)) toYear(toDate(‘2018-12-11’))	返回 2018
toMonth()	取日期或时间日期的月份	toMonth(toDateTime(‘2018-12-11 11:12:13’)) toMonth(toDate(‘2018-12-11’))	返回 12
toDayOfMonth()	取日期或时间日期的天（1-31）	toMonth(toDayOfMonth(‘2018-12-11 11:12:13’))	返回 11
toDayOfWeek()	取日期或时间日期的星期（星期一为1，星期日为7）。	toDayOfWeek(toDateTime(‘2018-12-11 11:12:13’))	返回 2
toHour()	取时间日期的小时	toHour(toDateTime(‘2018-12-11 11:12:13’))	返回 11
toMinute()	取时间日期的分钟	toMinute(toDateTime(‘2018-12-11 11:12:13’))	返回 12
toSecond()	取时间日期的秒	toSecond(toDateTime(‘2018-12-11 11:12:13’))	返回 13
toMonday()	取时间日期最近的周一（返回日期）	toMonday(toDate(‘2018-12-11’)) toMonday(toDateTime(‘2018-12-11 11:12:13’))	返回 2018-12-10
toTime()	将时间日期的日期固定到某一天，保留原始时间	toTime(toDateTime(‘2018-12-11 11:12:13’))	返回 1970-01-02 11:12:13

3.2.13 时间或日期截取函数（toStartOf）—— 返回日期

函数	用途	举例	结果
toStartOfMonth()	取日期或时间日期的月份的第一天，返回日期	toStartOfMonth(toDateTime(‘2018-12-11 11:12:13’))toStartOfMonth(toDate(‘2018-12-11’))	2018-12-01;
toStartOfQuarter()	取日期或时间日期的季度的第一天，返回日期	toStartOfQuarter(toDateTime(‘2018-12-11 11:12:13’))	返回 2018-10-01
toStartOfYear()	取日期或时间日期的年份的第一天，返回日期	toStartOfYear(toDateTime(‘2018-12-11 11:12:13’))toStartOfYear(toDate(‘2018-12-11’))	返回 2018-01-01
toStartOfMinute()	截取时间日期到分钟（之后归零），返回日期	toStartOfMinute(toDateTime(‘2018-12-11 11:12:13’))	返回 2018-12-11 11:12:00
toStartOfFiveMinute()	截取时间日期到最近的5的倍数分钟（之后归零），返回日期	toStartOfFiveMinute(toDateTime(‘2018-12-11 11:12:13’))	返回 2018-12-11 11:10:00
toStartOfFifteenMinutes()	截取时间日期到最近的15的倍数分钟（之后归零），返回日期	toStartOfFifteenMinutes(toDateTime(‘2018-12-11 11:12:13’))	返回 2018-12-11 11:00:00
toStartOfHour()	截取时间日期到小时（之后归零），返回日期	toStartOfHour(toDateTime(‘2018-12-11 11:12:13’))	返回 2018-12-11 11:00:00
toStartOfDay()	截取时间日期到天（之后归零），返回日期	toStartOfDay(toDateTime(‘2018-12-11 11:12:13’))	返回 2018-12-11 00:00:00
timeSlot()	将时间日期中，分钟大于等于30的归于30，分钟数小于30的归为00	timeSlot(toDateTime(‘2018-12-11 11:33:13’))timeSlot(toDateTime(‘2018-12-11 11:33:13’))	返回 2018-12-11 11:00:00返回 2018-12-11 11:30:00

3.2.14 日期或时间日期生成函数

函数	用途	举例	结果
now()	生成当前时间日期	now()	返回 2018-12-13 10:10:12
today()	生成今天的日期	today()	返回 2018-12-13
yesterday()	生成昨天的日期	yesterday()	返回 2018-12-12

3.2.15 精度保留（非四舍五入）

函数	用途	举例	结果
toDecimal32(‘whdwjfew’,8)	将数值型或者含有非数字的字符串进行精度保留	toDecimal32(23.12291, 3) ; toDecimal32(’_23.12291’, 3)	返回 23.122返回 0.000
toDecimal64(‘whdwjfew’,8)	将数值型或者含有非数字的字符串进行精度保留	toDecimal64(23.12291, 3); toDecimal64(’_23.12291’, 3)	返回 23.122返回 0.000
toDecimal128(‘whdwjfew’,8)	将数值型或者含有非数字的字符串进行精度保留	toDecimal128(23.12291, 3); toDecimal128(’_23.12291’, 3)	返回 23.122返回 0.000

3.2.16 字符串转化为整数（非整数的字符串返回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.2.17 日期与时间日期转化

函数	用途	举例	结果
toDate()	将字符型日期转化为日期型	toDate(‘2018-12-24’)	返回 2018-12-24
toDateTime()	将字符型时间日期转化为时间日期型	toDateTime(‘2018-12-24 10:10:00’)	返回 2018-12-24 10:10:00

3.2.18 转化为字符型

函数	用途	举例	结果
toString()	将数值型、字符型、日期等转化为字符型	toString(‘2018-12-24’)toString(‘123’)	返回 2018-12-24返回 123

3.2.19 查看数据类型

函数	用途	举例	结果
toTypeName()	返回数据的类型	toTypeName(toString(‘123’))toTypeName(toDate(‘2018-12-24’))	返回 String返回 Date

3.2.20 基本字符串操作

函数	用途	举例	结果
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

3.2.21 字符串查找

函数	用途	举例	结果
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.2.22 字符串替换

函数	用途	举例	结果
replaceOne(haystack,pattern,replacement)	替换第一个匹配到的pattern	replaceOne(‘asd123cbbj464sd’, ‘sd’, ‘-’)	返回 a-123cbbj464sd
replaceAll(haystack,pattern,replacement)	替换所有匹配到的pattern	replaceOne(‘asd123cbbj464sd’, ‘sd’, ‘-’)	返回 a-123cbbj464-
replaceRegexpOne(haystack, pattern, replacement)	正则匹配替换第一个匹配到的pattern	replaceRegexpOne(‘Hello, World!’, ‘o’, '- ')	返回 Hell- , World!
replaceRegexpAll(haystack,pattern,replacement)	正则匹配替换所有匹配到的pattern	replaceRegexpAll(‘Hello, World!’, ‘^’, 'here: ')replaceRegexpAll(‘Hello, World!’, ‘o’, '-- ')	返回 here: Hello, World!返回 Hell-- , W-- rld!

3.2.23 字符串分割

函数	用途	举例	结果
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’]

3.2.24 字符串拼接

函数	用途	举例	结果
concat(s1,s2,…)	将字符串拼接	concat(‘123’, ‘abc’, ‘ABC’)	返回 123abcABC

3.2.25 条件语句

函数	用途	举例	结果
if(cond,then,else)	条件输出	if(1 > 2, ‘正确’, ‘错误’)	返回错误
multiIf(cond_1, then_1, cond_2, then_2…else)	多条件输出	multiIf(1 > 2, ‘正确’, 2 < 0, ‘正确’, ‘错误’)	返回错误

3.2.26 数学函数

函数	用途	举例	结果
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

3.2.27 舍入函数

函数	用途	举例	结果
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

3.2.28 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()	返回“第一个重要子域”。	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=SQL%E4%B8%AD%E7%9A%84split’)	返回 /s
pathFull()	返回URL的完整路径	pathFull(‘https://www.baidu.com/s?wd=SQL%E4%B8%AD%E7%9A%84split’)	返回 /s?wd=SQL%E4%B8%AD%E7%9A%84split
queryString()	返回URL的参数（查询字符串）	queryString(‘https://www.baidu.com/s?wd=SQL%E4%B8%AD%E7%9A%84split’)	返回 wd=SQL%E4%B8%AD%E7%9A%84split
extractURLParameters()	以列表的形式返回URL的参数	extractURLParameters(‘https://www.baidu.com/s?wd=SQL%E4%B8%AD%E7%9A%84split&ur=qwguq’)	返回 [‘wd=SQL%E4%B8%AD%E7%9A%84split’,‘ur=qwguq’]
extractURLParameterNames()	以列表的形式返回URL的参数名	extractURLParameterNames(‘https://www.baidu.com/s?wd=SQL%E4%B8%AD%E7%9A%84split&ur=qwguq’)	返回 [‘wd’,‘ur’]
cutQueryString()	返回URL？（参数）前面的内容	cutQueryString(‘https://www.baidu.com/s?wd=SQL%E4%B8%AD%E7%9A%84split&ur=qwguq’)	返回 https://www.baidu.com/s

3.2.29 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

3.2.30 表连接操作

函数	用途	举例	结果
INNER JOIN	内连接	A表 INNER JOIN B表	A表与B表的公共部分
LEFT OUTER JOIN	左外连接	A 表 LEFT OUTER JOIN B表	A表不在B表中的部分
RIGHT OUTER JOIN	右外连接	A 表 RIGHT OUTER JOIN B表	B表不在A表中的部分
FULL OUTER JOIN	全外连接	A 表 FULL OUTER JOIN B表	A与B表全部，没有为NULL

3.2.31 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的域名