工作中遇到的impala的小问题

最新推荐文章于 2023-07-04 16:30:14 发布

原创最新推荐文章于 2023-07-04 16:30:14 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

sql 专栏收录该内容

1 篇文章

订阅专栏

在工作中处理Impala数据库时，发现在从中间表vl_stat_tmp查询并保留double字段2位小数后，数据在插入到最终表volte_nei190130时出现异常，某值的小数位数超过了2位。尽管查询时显示正确，但实际存储时出现问题。插入操作使用的SQL语句未给出。

我从一个中间表vl_stat_tmp，需要将几个字段保留2位小数，数据类型是double类型的，查询的时候，结果是保留了2位小数。但是插入到最终表volte_nei190130的时候，发现有一个值变成了小数点后不止有2位小数。

下面这张截图是从中间表查询:

然后是插入到最终表后，查询的结果:

这两个字段的数据类型都是double类型。

sql语句如下:

insert into volte_nei_pro{yymmdd}
select 
province_id,
20{yymmdd}00000000,
coalesce(sum(VoLteAudCallSuccNbr),0) as VoLteAudCallSuccNbr,
coalesce(sum(VoLteAudCallNbr),0) as VoLteAudCallNbr,
coalesce(sum(VoLteAudSuccRate),0) as VoLteAudSuccRate,
coalesce(sum(VoLteAudOffLineNbr),0) as VoLteAudOffLineNbr,
coalesce(sum(VoLteAudCallReplyNbr),0) as VoLteAudCallReplyNbr,
coalesce(sum(VoLteAudOffLineRate),0) as VoLteAudOffLineRate,
coalesce(sum(VoLteOrigCallTimeVtoV),0) as VoLteOrigCallTimeVtoV,
coalesce(sum(VoLteVidCallSuccNbr),0) as VoLteVidCallSuccNbr,
coalesce(sum(VoLteVidCallNbr),0) as VoLteVidCallNbr,
coalesce(sum(VoLteVidSuccRate),0) as VoLteVidSuccRate,
coalesce(sum(VoLteVidOffLineNbr),0) as VoLteVidOffLineNbr,
coalesce(sum(VoLteVidCallReplyNbr),0) as VoLteVidCallReplyNbr,
coalesce(sum(VoLteVidOffLineRate),0) as VoLteVidOffLineRate,
coalesce(sum(VOLTEIniRegisterSuccNbr),0) as VOLTEIniRegisterSuccNbr,
coalesce(sum(VOLTEIniRegisterAttNbr),0) as VOLTEIniRegisterAttNbr,
round(coalesce(sum(VOLTEIniRegisterSuccRate),0),2) as VOLTEIniRegisterSuccRate,
coalesce(sum(SRVCCSwitchSuccNbr),0) as SRVCCSwitchSuccNbr,
coalesce(sum(SRVCCSwitchAttNbr),0) as SRVCCSwitchAttNbr,
coalesce(sum(SRVCCSwitchSuccRate),0) as SRVCCSwitchSuccRate
from vl_stat_pro_tmp group by province_id;