Потеря числовой точности в SQL Server 2005
Отладка некоторого кода SQL, связанного с финансами, обнаружила странную проблему с числовой математической точностью (24,8).
Выполнив следующий запрос на своем MSSQL, вы получите результат выражения A + B * C, равный 0,123457
ВЫБЕРИТЕ, B, C, А + В * С ИЗ ( ВЫБЕРИТЕ CAST (0,12345678 КАК ЧИСЛО (24,8)) КАК A, CAST (0 КАК ЧИСЛО (24,8)) КАК B, CAST (500 КАК ЧИСЛО (24,8)) КАК C ) Т
Итак, мы потеряли 2 значимых символа. Пытаясь исправить это разными способами, я получил, что преобразование промежуточного результата умножения (который равен нулю!) В числовой (24,8) будет работать нормально.
И, наконец, есть решение. Но все же у меня возникает вопрос - почему MSSQL так себя ведет и какие преобразования типов действительно происходили в моем образце?
Ответы 3
Так же, как добавление типа с плавающей запятой неточно, умножение десятичных типов может быть неточным (или вызывать неточность), если вы превысите точность. См. Преобразование типов данных и десятичный и числовой.
Поскольку вы умножили NUMERIC(24,8) и NUMERIC(24,8), и SQL Server будет проверять только тип, а не содержимое, он, вероятно, попытается сохранить потенциальные 16 недесятичных цифр (24-8), когда не сможет сохранить все 48 цифр точности (макс. составляет 38). Объединив два из них, вы получите 32 недесятичных цифры, в результате чего у вас останется только 6 десятичных цифр (38-32).
Таким образом, исходный запрос
SELECT A, B, C, A + B * C
FROM ( SELECT CAST(0.12345678 AS NUMERIC(24,8)) AS A,
CAST(0 AS NUMERIC(24,8)) AS B,
CAST(500 AS NUMERIC(24,8)) AS C ) T
сводится к
SELECT A, B, C, A + D
FROM ( SELECT CAST(0.12345678 AS NUMERIC(24,8)) AS A,
CAST(0 AS NUMERIC(24,8)) AS B,
CAST(500 AS NUMERIC(24,8)) AS C,
CAST(0 AS NUMERIC(38,6)) AS D ) T
Опять же, между NUMERIC(24,8) и NUMERIC(38,6) SQL Server попытается сохранить возможные 32 цифры недесятичных чисел, поэтому A + D сокращается до
SELECT CAST(0.12345678 AS NUMERIC(38,6))
что дает вам 0.123457 после округления.
@ Edmondo1984 Пожалуйста, прочтите ссылки и поймите, что означают оба числа.
Вы говорите, что при умножении двух числовых (24,8) сервер попытается сохранить 16 бит и выдаст (32,6), как это становится 38,6? Спасибо
@ Edmondo1984 В NUMERIC(p, s)p означает точное число цифр общее. Итак, в NUMERIC(24, 8) есть 16 цифр не десятичных знаков. Никаких битов. Опять же, прочтите ссылку на документы msdn.
Следуя логике, указанной eed3si9n, и тому, что вы сказали в своем вопросе, кажется, что лучший подход при выполнении математических операций - извлечь их в функцию и дополнительно указать точность после каждой операции,
В этом случае функция могла бы выглядеть примерно так:
create function dbo.myMath(@a as numeric(24,8), @b as numeric(24,8), @c as numeric(24,8))
returns numeric(24,8)
as
begin
declare @d as numeric(24,8)
set @d = @b* @c
return @a + @d
end
Такой подход может не решить проблему, с которой SQL Server отрубает десятичные части. Чтобы сохранить десятичные части, может потребоваться преобразование в @a и @b в double.
Спасибо, я буду иметь это в виду при работе над некоторыми точными математическими вычислениями в SQL. Пока мне не нужно его использовать, но сейчас хорошо, что могут возникнуть некоторые проблемы, которые следует учитывать.
Несмотря на то, что написано на Точность, масштаб и длина (Transact-SQL). Я считаю, что он также применяет минимальный масштаб (количество знаков после запятой), равный 6, к результирующему типу NUMERIC для умножения так же, как и для деления и т. д.
ты имел ввиду ЧИСЛОВЫЙ (32,6)) ?? Если сумма должна быть 38