hive-sql查询结果保留特点⼩数位数的⽅法
⼀、ceil：向上取整
ceil(DOUBLE d): d是DOUBLE类型的，返回>=d的最⼩的BIGINT值
spark-hive> lect ceil(123.58);
_c0
124
Time taken: 0.093 s
spark-hive> lect ceil(123.28);
_c0
124
Time taken: 0.109 s
⼆、floor：向下取整
floor(DOUBLE d): d是DOUBLE类型的，返回<=d的最⼤的BIGINT值
spark-hive> lect floor(123.58);
_c0驻马店景点
123
Time taken: 2.748 s
楚山秦山皆白云三、cast(column_name as decimal(10,2)) cast函数四舍五⼊（推荐使⽤）
spark-hive> lect cast(68.666666666666668 as decimal(10,2));
_c0
68.67
Time taken: 2.735 s
spark-hive> lect 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> lect regexp_extract('68.666666666666668','([0-9]*.[0-9][0-9])',1);
19/12/17 12:47:33 INFO SparkHiveShell: current SQL: lect regexp_extract('68.666666666666668','([0-9]*.[0-9] [0-9])',1)
_c0
68.66
Time taken: 0.264 s
spark-hive> lect regexp_extract(68.666666666666668,'([0-9]*.[0-9][0-9])',1);
19/12/17 12:47:40 INFO SparkHiveShell: current SQL: lect 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> lect round(68.666666666666668,2);
项目档案_c0
68.67
霍金是什么病Time taken: 3.53 s
spark-hive> lect round(68.666666666666668,3);
_c0
68.667
Time taken: 0.606 s
六、取随机数
rand() rand(INT ed): 每⾏返回⼀个DOUBLE型的随机数，整数ed是随机因⼦
哲学王spark-hive> lect rand();
代理费计算_c0
0.5666506054002023
Time taken: 8.303 s
spark-hive> lect rand(10);
_c0
0.41371264720975787
Time taken: 0.493 s
spark-hive> lect rand(10);
_c0
0.41371264720975787
Time taken: 0.159 s
成都犀浦
七、综合⽰例：
每天直播时长超过半⼩时算半⼩时，不到半⼩时舍去算直播时长。
spark-hive> lect floor(123.58);
_c0
123
Time taken: 0.136 s
spark-hive> lecT round(123.58,1);
_c0
123.6
Time taken: 3.372 s孢子攻略
spark-hive> lect substr(round(123.58,1),-1,1);
_c0
6
Time taken: 2.265 s
spark-hive> SELECT ca when int(substr(round(123.58,1),-1,1))>=5 then 0.5 el 0 end;
_c0
0.5
Time taken: 0.36 s
spark-hive> lect floor(123.58)+(ca when int(substr(round(123.58,1),-1,1))>=5 then 0.5 el 0 end); _c0
123.5
Time taken: 0.184 s
