工作中遇到的impala的小问题

「爱情、让人受尽委屈。」 2022-03-21 07:17 490阅读 0赞

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZU1hbk9mQ29kaW5n_size_16_color_FFFFFF_t_70][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZU1hbk9mQ29kaW5n_size_16_color_FFFFFF_t_70 1][]

这两个字段的数据类型都是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;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZU1hbk9mQ29kaW5n_size_16_color_FFFFFF_t_70]: /images/20220321/468096f8dbab4aef8adbf42bb4317cec.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZU1hbk9mQ29kaW5n_size_16_color_FFFFFF_t_70 1]: /images/20220321/a8c45c189fc64c818e9e3879f3daeb85.png