Sql округляет до целого как исправить - Исправление ошибок и поиск оптимальных решений проблем

Время прочтения
2 мин

Просмотры 33K

Доброго дня, хабровчане! Пришлось мне в проекте столкнуться с точностью вычислений в MS SQL Server и я обнаружил не совсем интуитивное поведение при выполнении казалось бы интуитивных операций.

Для затравки вопрос (попробуйте ответить на него, не выполняя):
Каков будет результат операции?

declare @var1 decimal(38,10) = 0.0000007,
        @var2 decimal(38,10) = 1;
select @var1 * @var2;

Ответ и объяснение под катом

Итак, сначала ответ: 0.000001

Какого?

На самом деле ответ достаточно прост, но от этого не легче. Все дело в том, что при выполнении арифметических операций с десятичными числами результат может быть сильно больше исходных значений, например, если умножить 10^6 и 10^6, то получим 10^12. Это аж на 6 разрядов больше, чем исходные значения. Аналогично и с делением. Поэтому MS SQL при вычислении типа результирующего выражения применяет следующие правила:

Operation	Result precision	Result scale *
e1 + e2	max(s1, s2) + max(p1-s1, p2-s2) + 1	max(s1, s2)
e1 — e2	max(s1, s2) + max(p1-s1, p2-s2) + 1	max(s1, s2)
e1 * e2	p1 + p2 + 1	s1 + s2
e1 / e2	p1 — s1 + s2 + max(6, s1 + p2 + 1)	max(6, s1 + p2 + 1)
e1 { UNION \| EXCEPT \| INTERSECT } e2	max(s1, s2) + max(p1-s1, p2-s2)	max(s1, s2)
e1 % e2	min(p1-s1, p2 -s2) + max( s1,s2 )	max(s1, s2)

* precision и scale результата имеют абсолютный максимум, равный 38. Если значение precision превышает 38, то соответствующий scale уменьшается, чтобы по возможности предотвратить усечение интегральной части результата.

В документации нет подробного описания как происходит округление и до каких пределов, но экспериментально у меня не получилось достичь округления больше, чем decimal(38,6).

Отсюда и результат выражения в начале: 0.000001 ровно 6 знаков после запятой. Чтобы не быть голословным, выполним следующий запрос:

declare @var1 decimal(38,10) = 0.0000007,
        @var2 decimal(38,10) = 1,
        @res sql_variant;
set @res = @var1 * @var2;
select @res, 
       SQL_VARIANT_PROPERTY(@res, 'BaseType') as BaseType, 
       SQL_VARIANT_PROPERTY(@res, 'Precision') as Precision,
       SQL_VARIANT_PROPERTY(@res, 'Scale') as Scale;

Получим следующий результат:

res	BaseType	Precision	Scale
0.000001	decimal	38	6

Как же с этим жить?

Придется с этим смириться и всегда (абсолютно всегда!) очень деликатно выставлять точность. В нашем случае вот такой скрипт вернет ожидаемый результат:

declare @var1 decimal(18,10) = 0.0000007,
        @var2 decimal(18,10) = 1,
        @res sql_variant;
set @res = @var1 * @var2;
select @res, 
       SQL_VARIANT_PROPERTY(@res, 'BaseType') as BaseType, 
       SQL_VARIANT_PROPERTY(@res, 'Precision') as Precision,
       SQL_VARIANT_PROPERTY(@res, 'Scale') as Scale;

res	BaseType	Precision	Scale
0.00000070000000000000	decimal	37	20

Вместо послесловия

И на последок еще немного sql-магии. Что будет в результате выполнения вот такого скрипта:

declare @var1 decimal(38,10) = 0.0000007,
        @var2 int = 1;
select @var1 * @var2, @var1 * 1;

Ответ

@var1 *
@var2 = 0.000001
@var1 * 1 = 0.00000070

PS: так же надо быть внимательным с операциями аггрегации, потому что они тоже меняют точность результата. Вот хорошая статья, описывающая проблему.

Источник

Я использую SQL Server Management Studio и имею следующую схему:

CREATE TABLE tmp(
    id int NOT NULL IDENTITY(1,1)PRIMARY KEY,
    toleranceRegion DECIMAL
)

Затем выполняю следующие прошивки:

INSERT INTO tmp VALUES(3.2); 
INSERT INTO tmp VALUES(5.678);
INSERT INTO tmp VALUES(1.95);

Ожидаемый результат:

id  toleranceRegion
--  ---------------
1   3.2
2   5.678
3   1.95

Фактический выход:

id  toleranceRegion
--  ---------------
1   3
2   6
3   2

Почему вставленные значения поля допуска округляются до ближайшего целого числа?

3 ответа

Лучший ответ

Вы не определили масштаб / точность для десятичной дроби. Если вам нужно 3 цифры после десятичной дроби, вы должны определить ее как DECIMAL (9,3), что даст вам 6 разрядов перед десятичной дробью и десятичную дробь до 3 разрядов. Вам необходимо проанализировать ожидаемые данные и, исходя из ваших ожиданий, указать правильную точность и масштаб для определения столбца.

CREATE TABLE tmp(
    id int NOT NULL IDENTITY(1,1)PRIMARY KEY,
    toleranceRegion DECIMAL(9,3)
)

См. Документацию по серверу Sql для decimal здесь.

Igor
27 Июн 2016 в 20:33

Это связано с тем, что вы не устанавливаете масштаб , что означает, что система использует масштаб по умолчанию, равный нулю:

s (масштаб) Количество десятичных цифр, которые будут сохранены справа от десятичной точки. Это число вычитается из p, чтобы определить максимальное количество цифр слева от десятичной точки. Максимальное количество десятичных цифр, которое может храниться справа от десятичной точки. Масштаб должен иметь значение от 0 до p. Масштаб можно указать, только если указана точность. Масштаб по умолчанию — 0 . (курсив мой)

Другими словами, SQL Server хранит нулевые цифры справа от десятичной точки.

Sergey Kalinichenko
27 Июн 2016 в 20:33

Установите свою точность

Десятичный (18,4)

Это будут десятичные дроби

John Cappelletti
27 Июн 2016 в 20:33

Источник

Функция ROUND используется для округления дробей до нужного знака в дробной части.

См. также функцию truncate,
которая усекает числа до нужного знака.

См. также функцию CEILING,
которая округляет дроби в большую сторону.

См. также функцию FLOOR,
которая округляет дроби в меньшую сторону.

Синтаксис

Округление до целого:

SELECT ROUND(поле) FROM имя_таблицы WHERE условие

Округление до определенного знака в дробной части:

SELECT ROUND(поле, сколько_знаков_оставить) FROM имя_таблицы WHERE условие

Примеры

Все примеры будут по этой таблице workers, если не сказано иное:

id айди	name имя	age возраст	salary зарплата
1	Дима	23	100.129
2	Петя	24	200.391
3	Вася	25	300.934

Пример

В данном примере дробная зарплата округляется до целых при выборке:

SELECT *, ROUND(salary) as salary FROM workers

SQL запрос выберет следующие строки:

id айди	name имя	age возраст	salary зарплата
1	Дима	23	100
2	Петя	24	200
3	Вася	25	301

Пример

В данном примере дробная зарплата округляется до двух знаков после точки:

SELECT *, ROUND(salary, 2) as salary FROM workers

SQL запрос выберет следующие строки:

id айди	name имя	age возраст	salary зарплата
1	Дима	23	100.13
2	Петя	24	200.39
3	Вася	25	300.93

Пример

В данном примере дробная зарплата округляется до одного знака после точки:

SELECT *, ROUND(salary, 1) as salary FROM workers

SQL запрос выберет следующие строки:

id айди	name имя	age возраст	salary зарплата
1	Дима	23	100.1
2	Петя	24	200.4
3	Вася	25	300.9

Пример

В данном примере дробная зарплата округляется до целых (0 соответствует целому числу):

SELECT *, ROUND(salary, 0) as salary FROM workers

SQL запрос выберет следующие строки:

id айди	name имя	age возраст	salary зарплата
1	Дима	23	100
2	Петя	24	200
3	Вася	25	301

Источник