Hive SQL中浮点数比较大小：double与float比较大小

---

1.hive浮点数比较规则

hive浮点数比较规则依照java规则，当float和doule进行比较时，会统一转换为double进行比较。

float精度不如double高
比如同样是0.2
float可能会表示成0.200001(实际精度会更高，此处仅说明原理)
而double则会表示成0.20000000000001(实际精度会更高，此处仅说明原理)
所以0.2f 不等于0.2d

2.问题描述

0.2f与0.2d进行比较

在hive中对float和double类型的0.2进行了比较，说明二者不相等，且0.2f大于0.2d

3.避坑指南
要将浮点数用cast转换成统一数据类型进行比较

将0.2统一成float或者double，就可以相等了。
实际工作中要注意，不同类型的浮点数，一定要统一类型后进行比较，否则可能出现0.2！=0.2的尴尬局面！

4.本文用到的HiveQL

select case when(cast(0.2 as double)>cast(0.2 as float)) then 0
            when(cast(0.2 as float)>cast(0.2 as double)) then 1
            else -1 end;

select case when(cast(0.2 as double)=cast(0.2 as float)) then 0
            when(cast(0.2 as float)=cast(0.2 as double)) then 1
            else -1 end;

select case when(cast(0.2 as float)=cast(0.2 as float)) then 0
            else -1 end;


select case  when(cast(0.2 as double)!=cast(0.2 as double)) then 1
            else -1 end;