我的书签
添加书签
移除书签数据类型转换
CAST
函数简介
当前IoTDB支持6种数据类型，其中包括INT32、INT64、FLOAT、DOUBLE、BOOLEAN以及TEXT。当我们对数据进行查询或者计算时可能需要进行数据类型的转换， 比如说将TEXT转换为INT32，或者提高数据精度，比如说将FLOAT转换为DOUBLE。所以，IoTDB支持使用cast函数对数据类型进行转换。
| 函数名 | 必要的属性参数 | 输出序列类型 | 功能类型 |
|---|---|---|---|
| CAST | type:输出的数据点的类型，只能是 INT32 / INT64 / FLOAT / DOUBLE / BOOLEAN / TEXT |
由输入属性参数type决定 |
将数据转换为type参数指定的类型。 |
类型转换说明
1.当INT32、INT64类型的值不为0时，FLOAT与DOUBLE类型的值不为0.0时，TEXT类型不为空字符串或者”false”时，转换为BOOLEAN类型时值为true，否则为false。
1. IoTDB> show timeseries root.sg.d1.*;
2. +-------------+-----+-------------+--------+--------+-----------+----+----------+
3. | timeseries|alias| database|dataType|encoding|compression|tags|attributes|
4. +-------------+-----+-------------+--------+--------+-----------+----+----------+
5. |root.sg.d1.s3| null| root.sg| FLOAT| RLE| SNAPPY|null| null|
6. |root.sg.d1.s4| null| root.sg| DOUBLE| RLE| SNAPPY|null| null|
7. |root.sg.d1.s5| null| root.sg| TEXT| PLAIN| SNAPPY|null| null|
8. |root.sg.d1.s6| null| root.sg| BOOLEAN| RLE| SNAPPY|null| null|
9. |root.sg.d1.s1| null| root.sg| INT32| RLE| SNAPPY|null| null|
10. |root.sg.d1.s2| null| root.sg| INT64| RLE| SNAPPY|null| null|
11. +-------------+-----+-------------+--------+--------+-----------+----+----------+
12. Total line number = 6
13. It costs 0.006s
14. IoTDB> select * from root.sg.d1;
15. +-----------------------------+-------------+-------------+-------------+-------------+-------------+-------------+
16. | Time|root.sg.d1.s3|root.sg.d1.s4|root.sg.d1.s5|root.sg.d1.s6|root.sg.d1.s1|root.sg.d1.s2|
17. +-----------------------------+-------------+-------------+-------------+-------------+-------------+-------------+
18. |1970-01-01T08:00:00.001+08:00| 1.1| 1.1| test| false| 1| 1|
19. |1970-01-01T08:00:00.002+08:00| -2.2| -2.2| false| true| -2| -2|
20. |1970-01-01T08:00:00.003+08:00| 0.0| 0.0| true| true| 0| 0|
21. +-----------------------------+-------------+-------------+-------------+-------------+-------------+-------------+
22. Total line number = 3
23. It costs 0.009s
24. IoTDB> select cast(s1, 'type'='BOOLEAN'), cast(s2, 'type'='BOOLEAN'), cast(s3, 'type'='BOOLEAN'), cast(s4, 'type'='BOOLEAN'), cast(s5, 'type'='BOOLEAN') from root.sg.d1;
25. +-----------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+
26. | Time|cast(root.sg.d1.s1, "type"="BOOLEAN")|cast(root.sg.d1.s2, "type"="BOOLEAN")|cast(root.sg.d1.s3, "type"="BOOLEAN")|cast(root.sg.d1.s4, "type"="BOOLEAN")|cast(root.sg.d1.s5, "type"="BOOLEAN")|
27. +-----------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+
28. |1970-01-01T08:00:00.001+08:00| true| true| true| true| true|
29. |1970-01-01T08:00:00.002+08:00| true| true| true| true| false|
30. |1970-01-01T08:00:00.003+08:00| false| false| false| false| true|
31. +-----------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+
32. Total line number = 3
33. It costs 0.012s
2.当BOOLEAN类型的值为true时，转换为INT32与INT64类型的值为1，转换为FLOAT或者DOUBLE类型时值为1.0，转换为TEXT类型时值为”true”。当BOOLEAN类型的值为false时，转换为INT32与INT64类型的值为0，转换为FLOAT或者DOUBLE类型时值为0.0，转换为TEXT类型时值为”false”。
1. IoTDB> select cast(s6, 'type'='INT32'), cast(s6, 'type'='INT64'), cast(s6, 'type'='FLOAT'), cast(s6, 'type'='DOUBLE'), cast(s6, 'type'='TEXT') from root.sg.d1;
2. +-----------------------------+-----------------------------------+-----------------------------------+-----------------------------------+------------------------------------+----------------------------------+
3. | Time|cast(root.sg.d1.s6, "type"="INT32")|cast(root.sg.d1.s6, "type"="INT64")|cast(root.sg.d1.s6, "type"="FLOAT")|cast(root.sg.d1.s6, "type"="DOUBLE")|cast(root.sg.d1.s6, "type"="TEXT")|
4. +-----------------------------+-----------------------------------+-----------------------------------+-----------------------------------+------------------------------------+----------------------------------+
5. |1970-01-01T08:00:00.001+08:00| 0| 0| 0.0| 0.0| false|
6. |1970-01-01T08:00:00.002+08:00| 1| 1| 1.0| 1.0| true|
7. |1970-01-01T08:00:00.003+08:00| 1| 1| 1.0| 1.0| true|
8. +-----------------------------+-----------------------------------+-----------------------------------+-----------------------------------+------------------------------------+----------------------------------+
9. Total line number = 3
10. It costs 0.016s
3.当TEXT类型转换为INT32、INT64、FLOAT类型时，会先将TEXT类型的数据转换为DOUBLE类型，然后再转换为对应的类型，此时可能会存在损失精度的问题。如果无法转换的话则直接跳过。
1. IoTDB> select cast(s5, 'type'='INT32'), cast(s5, 'type'='INT64'), cast(s5, 'type'='FLOAT') from root.sg.d1;
2. +----+-----------------------------------+-----------------------------------+-----------------------------------+
3. |Time|cast(root.sg.d1.s5, "type"="INT32")|cast(root.sg.d1.s5, "type"="INT64")|cast(root.sg.d1.s5, "type"="FLOAT")|
4. +----+-----------------------------------+-----------------------------------+-----------------------------------+
5. +----+-----------------------------------+-----------------------------------+-----------------------------------+
6. Empty set.
7. It costs 0.009s
使用示例
测试数据：
1. IoTDB> select text from root.test;
2. +-----------------------------+--------------+
3. | Time|root.test.text|
4. +-----------------------------+--------------+
5. |1970-01-01T08:00:00.001+08:00| 1.1|
6. |1970-01-01T08:00:00.002+08:00| 1|
7. |1970-01-01T08:00:00.003+08:00| hello world|
8. |1970-01-01T08:00:00.004+08:00| false|
9. +-----------------------------+--------------+
SQL语句：
1. select cast(text, 'type'='BOOLEAN'), cast(text, 'type'='INT32'), cast(text, 'type'='INT64'), cast(text, 'type'='FLOAT'), cast(text, 'type'='DOUBLE') from root.test;
结果：
1. +-----------------------------+--------------------------------------+------------------------------------+------------------------------------+------------------------------------+-------------------------------------+
2. | Time|cast(root.test.text, "type"="BOOLEAN")|cast(root.test.text, "type"="INT32")|cast(root.test.text, "type"="INT64")|cast(root.test.text, "type"="FLOAT")|cast(root.test.text, "type"="DOUBLE")|
3. +-----------------------------+--------------------------------------+------------------------------------+------------------------------------+------------------------------------+-------------------------------------+
4. |1970-01-01T08:00:00.001+08:00| true| 1| 1| 1.1| 1.1|
5. |1970-01-01T08:00:00.002+08:00| true| 1| 1| 1.0| 1.0|
6. |1970-01-01T08:00:00.003+08:00| true| null| null| null| null|
7. |1970-01-01T08:00:00.004+08:00| false| null| null| null| null|
8. +-----------------------------+--------------------------------------+------------------------------------+------------------------------------+------------------------------------+-------------------------------------+
9. Total line number = 4
10. It costs 0.078s
