SQL中常用的数学函数 round ceil floor truncate mod cast(x as y)

round 四舍五入

0: jdbc:hive2://hadoop102:10000> select round(1.5);
+------+
| _c0  |
+------+
| 2    |
+------+
1 row selected (2.056 seconds)
0: jdbc:hive2://hadoop102:10000> select round(3.24523,3);
+--------+
|  _c0   |
+--------+
| 3.245  |
+--------+
1 row selected (0.161 seconds)

ceil 向上取整 返回>=该参数的最小整数

0: jdbc:hive2://hadoop102:10000> select ceil(1.1);
+------+
| _c0  |
+------+
| 2    |
+------+
1 row selected (0.104 seconds)

floor 向下取整 返回<=该参数的最大整数

0: jdbc:hive2://hadoop102:10000> select floor(1.1);
+------+
| _c0  |
+------+
| 1    |
+------+
1 row selected (0.101 seconds)
0: jdbc:hive2://hadoop102:10000> select floor(-1.1);
+------+
| _c0  |
+------+
| -2   |
+------+
1 row selected (0.102 seconds)

truncate 截断(Hive 中无法使用)

mysql> select truncate(1.23234,3);
+---------------------+
| truncate(1.23234,3) |
+---------------------+
|               1.232 |
+---------------------+
1 row in set (0.00 sec)

mod 取余

0: jdbc:hive2://hadoop102:10000> select mod(10,3);
+------+
| _c0  |
+------+
| 1    |
+------+
1 row selected (0.111 seconds)
0: jdbc:hive2://hadoop102:10000> select 10%3;
+------+
| _c0  |
+------+
| 1    |
+------+
1 row selected (0.101 seconds)

cast(x as y) （hive中可以使用）

这个函数是一个重点，它可以用来做类型转换
0: jdbc:hive2://hadoop102:10000> select cast(1.0 as int) field;
+--------+
| field  |
+--------+
| 1      |
+--------+
0: jdbc:hive2://hadoop102:10000> select cast ('2021-10-13 12:12:12' as date) `time`;
+-------------+
|    time     |
+-------------+
| 2021-10-13  |
+-------------+