主页 / dba / 问题 / 345430
Accepted
nam
Asked: 2025-02-25 10:01:28 +0800 CST

为什么 SUM 函数返回 4 位小数，而 AVG 函数返回 6 位小数

  • 772

问题：为什么SUM返回的是 4 位小数（如预期），但Avg返回的是 6 位小数？

CREATE TABLE #t(Col DECIMAL(19,4))
INSERT #t VALUES (123456.1200),(654321.3400)
SELECT SUM(Col), AVG(Col) FROM #t
总金额 平均金额
777777.4600 388888.730000

备注：请注意，问题不是我们如何显示AverageAmount4 位小数，而是为什么AverageAmount返回 6 位小数。这是一个错误还是背后有一些合理的原因？我在 Windows 11 上使用最新版本的 SQL Server 2022

sql-server

2 个回答

  1. Best Answer

    的数据类型SUM(Col)为DECIMAL(38,4)。

    这是有道理的，因为添加DECIMAL(x,4)永远不会增加所需的规模。 4 位小数始终就足够了（并且允许的精度38最大化在遇到溢出之前可以添加的元素数量）。

    然后AVG(Col)获取该DECIMAL(38,4)结果并对其进行除法运算。

    CASE WHEN COUNT_BIG(Col) = 0 THEN NULL ELSE SUM(Col)/COUNT_BIG(Col) END

    COUNT_BIG是，BIGINT因此具有的精度为19，比例为0。

    你可以通过

    SET SHOWPLAN_TEXT ON

GO

SELECT AVG(Col)  FROM #t

    返回

    |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1004]=(0) THEN NULL ELSE [Expr1005]/CONVERT_IMPLICIT(decimal(19,0),[Expr1004],0) END))
    |--Stream Aggregate(DEFINE:([Expr1004]=COUNT_BIG([#t].[Col]), [Expr1005]=SUM([#t].[Col])))
        |--Table Scan(OBJECT:([tempdb].[dbo].[#t]))

    根据此处的规则，此除法的结果将具有假设的数据类型decimal(58,24) 

    DECLARE @p1 int = 38, @s1 int = 4,
        @p2 int = 19, @s2 int = 0;

SELECT @p1 - @s1 + @s2 + greatest(6, @s1 + @p2 + 1),
       greatest(6, @s1 + @p2 + 1)

    但是这种数据类型是不可能的，因为最大精度是38，而且你进一步满足了规则

    如果小数部分大于 6 并且整数部分大于 32，则小数部分设置为 6。在这种情况下，整数部分和小数部分都会减小，结果类型为 decimal(38, 6)

    这些截断规则的完整扩展如下。

    DECLARE @p1 int = 38, @s1 int = 4,
        @p2 int = 19, @s2 int = 0,
        @p_new int  , @s_new int = 0;

SELECT @p_new = @p1 - @s1 + @s2 + greatest(6, @s1 + @p2 + 1),
       @s_new = greatest(6, @s1 + @p2 + 1)

IF @p_new > 38
BEGIN
DECLARE @integral_part INT = @p_new - @s_new;

IF @integral_part < 32
    BEGIN
    --Multiplication/ Division truncation rule 1
    SELECT @s_new = least(@s_new, 38 - (@integral_part)) 
    SELECT @p_new = @s_new + @integral_part
    END
ELSE
    --Multiplication/ Division truncation rules 2 and 3
     SELECT @p_new = 38, @s_new = least(@s_new, 6);
END

SELECT CONCAT('(',@p_new,',',@s_new,')') AS [(p,s)]

    对于任何decimal规范@p1，@p2和@s2始终具有与 相同的值，SUM始终decimal具有精度38，并且它始终会被 除以bigint。尝试不同的 值@s1表明结果确实始终为decimal(38, greatest(s,6))

    • 14

  2. https://learn.microsoft.com/en-us/sql/t-sql/functions/avg-transact-sql?view=sql-server-ver16

    对小数（p，s）进行 AVG 计算，结果为小数类型（38，max（s，6））

    对 decimal(p,s) 进行求和，结果为 decimal(38,s)

    所以这不是一个错误，只是它的工作方式而已。

    • 3

相关问题