SQL中带有concat(substrb)的函数时的一种优化思路

用户sql如下:

INSERT INTO gb_middle.tmp_ft_mid_mob_imei_d_vcall (user_id, IMEI, voc_call_cnt) SELECT user_id, concat(to_char(substrb(trim(nullif(IMEI,'')),1,14)),'0'), sum(voc_call_cnt) FROM gb_middle.Ft_mid_vcall_detail_daily where sum_Date between '20241201' and '20241217' and length(substrb(trim(nullif(IMEI,'')),1,14))=14 GROUP BY user_id, concat(to_char(substrb(trim(nullif(IMEI,'')),1,14)),'0')

可见投影列和group by列都使用了 concat(to_char(substrb(trim(nullif(IMEI,'')),1,14)),'0')，此列被评估为longtext，导致计算式占用了很大的内存资源，导致执行性能低下，几千秒不能执行出结果，通过数据比对发现，此列最大长度为 15，所以，强制给此列定已成char(100)后，30s可以出结果，改写如下：

INSERT INTO gb_middle.tmp_ft_mid_mob_imei_d_vcall (user_id, IMEI, voc_call_cnt) SELECT user_id, cast(concat(to_char(substrb(trim(nullif(IMEI,'')),1,14)),'0') as char(100)), sum(voc_call_cnt) FROM gb_middle.Ft_mid_vcall_detail_daily where sum_Date between '20241201' and '20241217' and length(substrb(trim(nullif(IMEI,'')),1,14))=14 GROUP BY user_id, cast(concat(to_char(substrb(trim(nullif(IMEI,'')),1,14)),'0') as char(100)) ；

本次主要是苹果列宽导致的性能问题，如遇此类问题，可以考虑手动指定最大长度。