SQL查询调用SUM函数时出现数值数据类型转换溢出错误如何解决？

你的报错核心是数值累计后超出了字段精度上限，和你之前推测的单条记录小数位过长无关，主要有两个触发点：

• 单条SALES_GROWTH_PERCENT的精度设置不足：你用的DECIMAL(10,1)最大可存储的数值为999999999.9，如果遇到单个客户注册后消费额增长上千倍的情况，计算出来的增长率直接就超出该精度范围，触发溢出。
• SUM聚合后的结果溢出：即使单条记录的数值没有超出DECIMAL(10,1)的范围，当你对数千条以上的记录做SUM聚合时，累计值会远大于DECIMAL(10,1)的上限，数据库默认推导的SUM返回精度不够就会报错。

你可以按照以下步骤修改语句：

1. 调高高增长率字段的存储精度，建议将DECIMAL(10,1)替换为DECIMAL(18,2)，足够覆盖绝大多数业务场景下的增长率数值
2. 聚合时优先用AVG函数替代SUM/COUNT的写法，既简化代码，也能减少超大累计值出现的概率
3. 可根据业务逻辑增加异常增长率截断逻辑，避免极端异常值导致溢出

SELECT  AVG(TRANS_GROWTH_PERCENT) AS AVG_TRANS_GROWTH_PERCENT,
        AVG(SALES_GROWTH_PERCENT) AS AVG_SALES_GROWTH_PERCENT    
FROM
        (
        SELECT  CUSTOMER_ID,
                CASE    WHEN POST_SIGNUP_TRANS <=0
                        THEN 0  
                        WHEN POST_SIGNUP_TRANS >0 
                        THEN CAST(
                            LEAST(
                                GREATEST(
                                    (CAST(POST_SIGNUP_TRANS AS DECIMAL (18,5)) - CAST(PRE_SIGNUP_TRANS AS DECIMAL (18,5))) / CAST(PRE_SIGNUP_TRANS AS DECIMAL (18,5)) * 100,
                                -100), -- 下限：最多跌100%即归零
                            100000) -- 上限：最多按涨1000倍计算，可根据业务调整
                            AS DECIMAL (18,2))    
                        ELSE NULL 
                        END AS TRANS_GROWTH_PERCENT,
                CASE    WHEN POST_SIGNUP_SALES <=0 
                        THEN 0
                        WHEN POST_SIGNUP_SALES >0
                        THEN CAST(
                            LEAST(
                                GREATEST(
                                    (CAST(POST_SIGNUP_SALES AS DECIMAL (18,5)) - CAST(PRE_SIGNUP_SALES AS DECIMAL (18,5))) / CAST(PRE_SIGNUP_SALES AS DECIMAL (18,5)) * 100,
                                -100),
                            100000)
                            AS DECIMAL (18,2))
                        ELSE NULL
                        END AS SALES_GROWTH_PERCENT
        FROM
                (
                SELECT      CUSTOMER_ID, 
                            SUM(PRE_SIGNUP_TRANS) AS PRE_SIGNUP_TRANS, SUM(PRE_SIGNUP_SALES) AS PRE_SIGNUP_SALES,
                            SUM(POST_SIGNUP_TRANS) AS POST_SIGNUP_TRANS, SUM(POST_SIGNUP_SALES) AS POST_SIGNUP_SALES
                FROM
                            (
                            SELECT  CUSTOMER_ID,
                                    CASE WHEN TRANSACTION_DATE >= ANALYSIS_START_DATE THEN 1 ELSE 0 END AS ANALYSIS_FLAG,
                                    CASE WHEN TRANSACTION_DATE < SIGNUP_DATE THEN 1 ELSE 0 END AS PRE_SIGNUP_TRANS,
                                    CASE WHEN TRANSACTION_DATE < SIGNUP_DATE THEN SALES ELSE 0 END AS PRE_SIGNUP_SALES,
                                    CASE WHEN TRANSACTION_DATE >= SIGNUP_DATE THEN 1 ELSE 0 END AS POST_SIGNUP_TRANS,
                                    CASE WHEN TRANSACTION_DATE >= SIGNUP_DATE THEN SALES ELSE 0 END AS POST_SIGNUP_SALES
                            FROM    TRANSACTIONS_TABLE
                            )
                WHERE       ANALYSIS_FLAG = 1
                GROUP BY    CUSTOMER_ID
                )
        WHERE   PRE_SIGNUP_TRANS >0
        AND     PRE_SIGNUP_SALES >0
        )       
;

如果你的数据库不支持LEAST/GREATEST函数，用嵌套CASE判断替换即可。

内容的提问来源于stack exchange，提问作者SRJCoding