HiveSQL/SparkSQL的 round() 、floor()和 ceil()的 用法

HiveSQL/SparkSQL的 round() 、floor()和 ceil()的 用法

1、概述

round  四舍五入
floor 取左值
ceil 取右值

2、在SparkSQL中的示例 

spark版本：

spark-2.3.4
Hive client (version 1.2.2)

spark-sql> select round(1.2356);
1
Time taken: 0.788 seconds, Fetched 1 row(s)
spark-sql> select round(1.6356);
2
Time taken: 0.046 seconds, Fetched 1 row(s)

spark-sql> select floor(1.2356);
1
Time taken: 0.082 seconds, Fetched 1 row(s)
spark-sql> select floor(1.6356);
1
Time taken: 0.038 seconds, Fetched 1 row(s)

spark-sql> select ceil(1.2356);
2
Time taken: 0.039 seconds, Fetched 1 row(s)
spark-sql> select ceil(1.6356);
2
Time taken: 0.035 seconds, Fetched 1 row(s)

3、在HiveSQL中的示例

hive版本：

Apache Hive (version 1.2.2-mdh2.0.2-SNAPSHOT)
Beeline version 1.2.2-mdh2.0.2-SNAPSHOT by Apache Hive

0: jdbc:hive2://test-hadoop.hive.m> select round(1.2356);
+------+--+
| _c0  |
+------+--+
| 1.0  |
+------+--+
1 row selected (2.521 seconds)
0: jdbc:hive2://test-hadoop.hive.m> select round(1.6356);
+------+--+
| _c0  |
+------+--+
| 2.0  |
+------+--+
1 row selected (0.093 seconds)


0: jdbc:hive2://test-hadoop.hive.m> select floor(1.2356);
+------+--+
| _c0  |
+------+--+
| 1    |
+------+--+
1 row selected (3.735 seconds)
0: jdbc:hive2://test-hadoop.hive.m> select floor(1.6356);
+------+--+
| _c0  |
+------+--+
| 1    |
+------+--+
1 row selected (0.092 seconds)


0: jdbc:hive2://test-hadoop.hive.m> select ceil(1.2356);
+------+--+
| _c0  |
+------+--+
| 2    |
+------+--+
1 row selected (0.096 seconds)
0: jdbc:hive2://test-hadoop.hive.m> select ceil(1.6356);
+------+--+
| _c0  |
+------+--+
| 2    |
+------+--+
1 row selected (2.294 seconds)
0: jdbc:hive2://test-hadoop.hive.m>