hive-sql查询结果保留特点小数位数的方法

一、ceil:向上取整

ceil(DOUBLE d): d是DOUBLE类型的,返回>=d的最小的BIGINT值 

spark-hive> select ceil(123.58); 
_c0
124
Time taken: 0.093 s
spark-hive> select ceil(123.28);
_c0
124
Time taken: 0.109 s

二、floor:向下取整

floor(DOUBLE d): d是DOUBLE类型的,返回<=d的最大的BIGINT值 

spark-hive> select floor(123.58);
_c0
123
Time taken: 2.748 s

三、cast(column_name as decimal(10,2)) cast函数四舍五入(推荐使用)

spark-hive> select cast(68.666666666666668 as decimal(10,2));
_c0
68.67
Time taken: 2.735 s
spark-hive> select cast(68.666666666666668 as decimal(10,3));
_c0
68.667
Time taken: 0.125 s

四、regexp_extract(column_name,'([0-9]*.[0-9][0-9])',1)  正则匹配截取,不做四舍五入,只是单纯的当作字符串截取

 对于读者提到cast函数也会对字段做四舍五入的情况,特补充正则表达式截取。

spark-hive> select regexp_extract('68.666666666666668','([0-9]*.[0-9][0-9])',1);
19/12/17 12:47:33 INFO SparkHiveShell: current SQL: select regexp_extract('68.666666666666668','([0-9]*.[0-9][0-9])',1)
_c0
68.66
Time taken: 0.264 s
spark-hive> select regexp_extract(68.666666666666668,'([0-9]*.[0-9][0-9])',1);  
19/12/17 12:47:40 INFO SparkHiveShell: current SQL: select regexp_extract(68.666666666666668,'([0-9]*.[0-9][0-9])',1)
_c0
68.66
Time taken: 0.168 s

五、round:四舍五入

    round(DOUBLE d) : 返回DOUBLE型的d的BIGINT类型的近似值 
    round(DOUBLE d,INT) : 返回DOUBLE型的d的保留n位小数的DOUBLE类型的近似值 四舍五入截取(这种方法慎用,有时候结果不是你想要的)

spark-hive> select round(68.666666666666668,2);              
_c0
68.67
Time taken: 3.53 s
spark-hive> select round(68.666666666666668,3);
_c0
68.667
Time taken: 0.606 s

六、取随机数

 rand() rand(INT seed): 每行返回一个DOUBLE型的随机数,整数seed是随机因子 

spark-hive> select rand();
_c0
0.5666506054002023
Time taken: 8.303 s
spark-hive> select rand(10);
_c0
0.41371264720975787
Time taken: 0.493 s
spark-hive> select rand(10);
_c0
0.41371264720975787
Time taken: 0.159 s

七、综合示例:

每天直播时长超过半小时算半小时,不到半小时舍去算直播时长。

spark-hive> select floor(123.58); 
_c0
123
Time taken: 0.136 s
spark-hive> selecT round(123.58,1);
_c0
123.6
Time taken: 3.372 s
spark-hive> select substr(round(123.58,1),-1,1);
_c0
6
Time taken: 2.265 s
spark-hive> SELECT case when int(substr(round(123.58,1),-1,1))>=5 then 0.5 else 0 end;
_c0
0.5
Time taken: 0.36 s
spark-hive> select floor(123.58)+(case when int(substr(round(123.58,1),-1,1))>=5 then 0.5 else 0 end);
_c0
123.5
Time taken: 0.184 s

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页