SQL str() против функции round()

#sql #rounding

#sql #округление

Вопрос:

При работе с функцией str() я обнаружил, что в некоторых случаях она округляется неправильно, в то время как функция round() работает должным образом. Пожалуйста, посмотрите на пример:

 declare @v decimal(18,2) = 29.95
select str(@v, 18, 1)
--29.9
select round(@v, 1)
--30.00

set @v = 39.95
select str(@v, 18, 1)
--40.00
select round(@v, 1)
--40.00

Кто-нибудь может объяснить, почему это происходит?

EDIT1: я протестировал различные обходные пути со следующим базовым кодом:

 declare @v decimal(18,2) = 9.95
declare @r varchar(100)
declare @c int = 1000000
declare @ms int
declare @dt datetime2

set @dt = sysdatetime()

while @c > 0
begin
    set @r = --different roundings
    set @c = @c - 1
end

set @ms = DATEDIFF(ms, @dt, sysdatetime())
select @ms, @r

Вариант 1 (исходный, в некоторых случаях округляется неправильно):

 str(@v, 18, 1)

Вариант 2 (слегка измененный, но округляется правильно):

 str(round(@v, 1), 18, 1)

Вариант 3 (двойное преобразование и округление):

 convert(varchar(20), convert(decimal(18,1), round(@v, 1)))

Вариант 4 (только двойное преобразование):

 convert(varchar(20), convert(decimal(18,1), @v))

Результаты:
Варианты 1 и 2 примерно в 2 раза медленнее, чем два последних, но результат выравнивается по правому краю. Самым быстрым является вариант 4.

Ответ №1:

Параметр для str() является плавающим, поэтому ваше десятичное значение неявно преобразуется в значение с плавающей точкой (53), которое затем преобразуется в строку. Таким образом, вы видите ошибку округления с плавающей запятой.

Внесите небольшое изменение в свой запрос, и вы сможете увидеть, что происходит в реальном плане выполнения.

 declare @v decimal(18,2) = 29.95
select top(1) str(@v, 18, 1)

 <ScalarOperator ScalarString="str(CONVERT_IMPLICIT(float(53),[@v],0),(18),(1))">

1. Как мне использовать последнюю строку кода, которую вы написали? И каким будет обходной путь, чтобы я получил правильно округленный varchar с максимально возможной производительностью?

2. @DmitryTimofeev Вы не должны использовать последнюю строку. Это ответ на ваш вопрос «почему это происходит?».

3. Я на самом деле пришел к тому же самому :), просто подумал, может быть, есть лучшее решение.

4. @DmitryTimofeev Так — то лучше cast(cast(round(@v, 1) as decimal(18, 1)) as varchar(10)) . Это позволяет полностью избежать преобразований с плавающей запятой,

5. Разве это не было бы еще лучше: convert(varchar, convert(decimal(18,1), @v))

Ответ №2:

Синтаксис STR(); STR ( float_expression [ , длина [ , десятичное число ] ] ) ясно говорит о том, что число является float_expression . Поэтому, какое бы число вы ни указали, оно сначала будет преобразовано в число с плавающей запятой (n), где значение по умолчанию n = 53.

Итак

ВЫБЕРИТЕ STR(4.65,5,1), ВЫБЕРИТЕ STR(3.65,5,1)

Равный:

ВЫБЕРИТЕ STR(CAST(4.65 КАК FLOAT(53)),5,1) , STR(CAST(3.65 КАК FLOAT(53)),5,1)

Если вы укажете n, скажем, n = 4, это даст ответ, который вы ожидаете (т. Е.; 4.7 и 3.7)

ВЫБЕРИТЕ STR(ПРИВЕДЕНИЕ(4.65 КАК ЗНАЧЕНИЕ С ПЛАВАЮЩЕЙ ЗАПЯТОЙ(4)),5,1) , STR(ПРИВЕДЕНИЕ(3,65 В КАЧЕСТВЕ ПОПЛАВКА(4)),5,1) —4.7, 3.7