ClickHouse类型转换函数

69 阅读 0 评论 46 点赞

我是靠谱客的博主活力白羊，最近开发中收集的这篇文章主要介绍ClickHouse类型转换函数，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

ClickHouse类型转换函数

数值类型转换常见的问题：当你把某个值从一个类型转换为另外一个类型的时候，你需要注意的是这是一个不安全的操作，可能导致数据的丢失。数据丢失一般发生在你将一个大的数据类型转换为小的数据类型的时候，或者你把两个不同的数据类型相互转换的时候。

1. toInt(8|16|32|64)
转换一个输入值为Int类型。这个函数包括：

toInt8(expr) — 结果为Int8数据类型。
toInt16(expr) — 结果为Int16数据类型。
toInt32(expr) — 结果为Int32数据类型。
toInt64(expr) — 结果为Int64数据类型。

参数：expr — 表达式返回一个数字或者代表数值类型的字符串。不支持二进制、八进制、十六进制的数字形式，有效数字之前的0也会被忽略。
返回值：整形在Int8, Int16, Int32, Int64 的数据类型。
该函数使用 rounding towards zero 原则，意味着会截断丢弃小数部分的数值。

SELECT toInt64(nan), toInt32(32), toInt16('16'), toInt8(8.8);
┌─────────toInt64(nan)─┬─toInt32(32)─┬─toInt16('16')─┬─toInt8(8.8)─┐
│ -9223372036854775808 │
32 │
16 │
8 │
└──────────────────────┴─────────────┴───────────────┴─────────────┘

2. toInt(8|16|32|64)OrZero
这个函数需要一个字符类型的入参，然后尝试把它转为Int (8 | 16 | 32 | 64)，如果转换失败直接返回0。

select toInt64OrZero('123123'), toInt8OrZero('123qwe123');
┌─toInt64OrZero('123123')─┬─toInt8OrZero('123qwe123')─┐
│
123123 │
0 │
└─────────────────────────┴───────────────────────────┘

3. toInt(8|16|32|64)OrNull
这个函数需要一个字符类型的入参，然后尝试把它转为Int (8 | 16 | 32 | 64)，如果转换失败直接返回NULL。

select toInt64OrNull('123123'), toInt8OrNull('123qwe123');
┌─toInt64OrNull('123123')─┬─toInt8OrNull('123qwe123')─┐
│
123123 │
ᴺᵁᴸᴸ │
└─────────────────────────┴───────────────────────────┘

4. toUInt(8|16|32|64)
转换一个输入值到UInt类型。这个函数包括：

toUInt8(expr) — 结果为UInt8数据类型。
toUInt16(expr) — 结果为UInt16数据类型。
toUInt32(expr) — 结果为UInt32数据类型。
toUInt64(expr) — 结果为UInt64数据类型。

参数：expr — 表达式返回一个数字或者代表数值类型的字符串。不支持二进制、八进制、十六进制的数字形式，有效数字之前的0也会被忽略。
返回值：整形在UInt8, UInt16, UInt32，或者 UInt64 的数据类型。函数使用 rounding towards zero 原则，这意味着会截断丢弃小数部分的数值。对于负数和NaN and Inf来说转换的结果是不确定的。如果你传入一个负数，比如：’-32’，ClickHouse会抛出异常。

SELECT toUInt64(nan), toUInt32(-32), toUInt16('16'), toUInt8(8.8);
┌───────toUInt64(nan)─┬─toUInt32(-32)─┬─toUInt16('16')─┬─toUInt8(8.8)─┐
│ 9223372036854775808 │
4294967264 │
16 │
8 │
└─────────────────────┴───────────────┴────────────────┴──────────────┘

5.toDecimal(32|64|128)
转换 value 到Decimal类型的值，其中精度为S。value可以是一个数字或者一个字符串。S 指定小数位的精度。

toDecimal32(value, S)
toDecimal64(value, S)
toDecimal128(value, S)

select toDecimal32(12.32362,3);
┌─toDecimal32(12.32362, 3)─┐
│
12.323 │
└──────────────────────────┘

6. toDecimal(32|64|128)OrNull
转换一个输入的字符到Nullable(Decimal(P,S))类型的数据。这个函数包括：

toDecimal32OrNull(expr, S) — 结果为Nullable(Decimal32(S))数据类型。
ttoDecimal64OrNull(expr, S) — 结果为Nullable(Decimal64(S))数据类型。
ttoDecimal128OrNull(expr, S) — 结果为Nullable(Decimal128(S))数据类型。

如果在解析输入值发生错误的时候你希望得到一个NULL值而不是抛出异常，你可以使用该函数。
参数：

expr — 表达式返回一个String类型的数据。 ClickHouse倾向于文本类型的表示带小数类型的数值，比如’1.111’。
S — 小数位的精度。

返回值：Nullable(Decimal(P,S))类型的数据，该值包含：

如果有的话，小数位S。
如果解析错误或者输入的数字的小数位多于S,那结果为NULL。

SELECT toDecimal32OrNull(toString(-1.111), 5) AS val, toTypeName(val);
┌──────val─┬─toTypeName(toDecimal32OrNull(toString(-1.111), 5))─┐
│ -1.11100 │ Nullable(Decimal(9, 5))
│
└──────────┴────────────────────────────────────────────────────┘
SELECT toDecimal32OrNull(toString(-1.111), 2) AS val, toTypeName(val);
┌──val─┬─toTypeName(toDecimal32OrNull(toString(-1.111), 2))─┐
│ ᴺᵁᴸᴸ │ Nullable(Decimal(9, 2))
│
└──────┴────────────────────────────────────────────────────┘

7. toDecimal(32|64|128)OrZero
转换输入值为Decimal(P,S)类型数据。这个函数包括：

toDecimal32OrZero( expr, S) — 结果为Decimal32(S) 数据类型。
toDecimal64OrZero( expr, S) — 结果为Decimal64(S) 数据类型。
toDecimal128OrZero( expr, S) — 结果为Decimal128(S) 数据类型。

当解析错误的时候，你不需要抛出异常而希望得到0值，你可以使用该函数。

参数:

expr — 表达式返回一个String类型的数据。 ClickHouse倾向于文本类型的表示带小数类型的数值，比如’1.111’。
S — 小数位的精度。

返回值
Nullable(Decimal(P,S)) 数据类型中的值。该值包含：

如果有的话，小数位S。
如果解析错误或者输入的数字的小数位多于S,那结果为小数位精度为S的0。

SELECT toDecimal32OrZero(toString(-1.111), 5) AS val, toTypeName(val);
┌──────val─┬─toTypeName(toDecimal32OrZero(toString(-1.111), 5))─┐
│ -1.11100 │ Decimal(9, 5)
│
└──────────┴────────────────────────────────────────────────────┘
SELECT toDecimal32OrZero(toString(-1.111), 2) AS val, toTypeName(val);
┌──val─┬─toTypeName(toDecimal32OrZero(toString(-1.111), 2))─┐
│ 0.00 │ Decimal(9, 2)
│
└──────┴────────────────────────────────────────────────────┘

8. toString
函数用于在数字、字符串（不包含FixedString）、Date以及DateTime之间互相转换。所有的函数都接受一个参数。
当将其他类型转换到字符串或从字符串转换到其他类型时，使用与制表分隔符格式相同的规则对字符串的值进行格式化或解析。如果无法解析字符串则抛出异常并取消查询。
当将Date转换为数字或反之，Date对应Unix时间戳的天数。
将DataTime转换为数字或反之，DateTime对应Unix时间戳的秒数。

toDate/toDateTime函数的日期和日期时间格式定义如下：

YYYY-MM-DD
YYYY-MM-DD hh:mm:ss

例外的是，如果将UInt32、Int32、UInt64或Int64类型的数值转换为Date类型，并且其对应的值大于等于65536，则该数值将被解析成unix时间戳（而不是对应的天数）。这意味着允许写入’toDate(unix_timestamp)‘这种常见情况，否则这将是错误的，并且需要编写更加繁琐的’toDate(toDateTime(unix_timestamp))’。
Date与DateTime之间的转换以更为自然的方式进行：通过添加空的time或删除time。
此外，DateTime参数的toString函数可以在第二个参数中包含时区名称。例如：Asia/Yekaterinburg在这种情况下，时间根据指定的时区进行格式化。

SELECT now() AS now_local,toString(now(), 'Asia/Yekaterinburg') AS now_yekat;
┌───────────now_local─┬─now_yekat───────────┐
│ 2021-07-27 11:30:13 │ 2021-07-27 08:30:13 │
└─────────────────────┴─────────────────────┘

9. CAST(x, T)
将’x’转换为’t’数据类型，还支持语法CAST（x AS t）。

SELECT '2016-06-15 23: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────────┐
│ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00 │
└─────────────────────┴─────────────────────┴────────────┴─────────────────────┴─────────────────────┘

语法:

toIntervalSecond(number)
toIntervalMinute(number)
toIntervalHour(number)
toIntervalDay(number)
toIntervalWeek(number)
toIntervalMonth(number)
toIntervalQuarter(number)
toIntervalYear(number)

参数: number — 正整数，持续的时间。
返回值：时间的Interval值。

WITH
toDate('2019-01-01') AS date,
INTERVAL 1 WEEK AS interval_week,
toIntervalWeek(1) AS interval_to_week
SELECT
date + interval_week,
date + interval_to_week;
┌─plus(date, interval_week)─┬─plus(date, interval_to_week)─┐
│
2019-01-08 │
2019-01-08 │
└───────────────────────────┴──────────────────────────────┘

11. parseDateTimeBestEffort
把String类型的时间日期转换为DateTime数据类型。
该函数可以解析ISO 8601，RFC 1123 - 5.2.14 RFC-822 Date and Time Specification或者ClickHouse的一些别的时间日期格式。

语法:

parseDateTimeBestEffort(time_string [, time_zone]);

参数：

time_string — 字符类型的时间和日期。
time_zone — 字符类型的时区。

非标准格式的支持

9位或者10位的数字时间，unix timestamp.
时间和日期组成的字符串： YYYYMMDDhhmmss, DD/MM/YYYY hh:mm:ss, DD-MM-YY hh:mm, YYYY-MM-DD hh:mm:ss等。
只有日期的字符串： YYYY, YYYYMM, YYYY*MM, DD/MM/YYYY, DD-MM-YY 等。
只有天和时间： DD, DD hh, DD hh:mm。这种情况下 YYYY-MM 默认为 2000-01。
包含时间日期以及时区信息： YYYY-MM-DD hh:mm:ss ±h:mm等。例如： 2020-12-12 17:36:00 -5:00。

对于所有的格式来说，这个函数通过全称或者第三个字符的月份名称来解析月份，比如：24/DEC/18, 24-Dec-18, 01-September-2018。

返回值：

DateTime类型数据。

例子：

SELECT parseDateTimeBestEffort('12/12/2020 12:12:57') AS parseDateTimeBestEffort;
┌─parseDateTimeBestEffort─┐
│
2020-12-12 12:12:57 │
└─────────────────────────┘
SELECT parseDateTimeBestEffort('Sat, 18 Aug 2018 07:22:16 GMT', 'Asia/Shanghai') AS parseDateTimeBestEffort;
┌─parseDateTimeBestEffort─┐
│
2018-08-18 15:22:16 │
└─────────────────────────┘
SELECT parseDateTimeBestEffort('1484101485') AS parseDateTimeBestEffort;
┌─parseDateTimeBestEffort─┐
│
2017-01-11 10:24:45 │
└─────────────────────────┘
SELECT parseDateTimeBestEffort('2018-12-12 10:12:12') AS parseDateTimeBestEffort;
┌─parseDateTimeBestEffort─┐
│
2018-12-12 10:12:12 │
└─────────────────────────┘
SELECT parseDateTimeBestEffort('10 20:19');
┌─parseDateTimeBestEffort('10 20:19')─┐
│
2000-01-10 20:19:00 │
└─────────────────────────────────────┘

parseDateTimeBestEffortOrNull:这个函数和parseDateTimeBestEffort基本一致，除了无法解析时返回结果为NULL。
parseDateTimeBestEffortOrZero ：这个函数和parseDateTimeBestEffort基本一致，除了无法解析时返回结果为0。

更多类型转换函数参考：ClickHouse类型转换函数