Sql округляет до целого как исправить - Исправление недочетов и поиск решений вместе с Examum.ru

Время прочтения
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

Источник

Here question is How did you define the precision of the DECIMAL column?

If it is DECIMAL(10, 2) it will have a total of 11 numbers of which 2 are decimal values (with 2 decimal rounding meaning that 10.215 is saved as 11.22 and 11.214 becomes 11.21).

If it is DECIMAL(10) it will not have any decimal values and be rounded to an integer.

In your question you defined toleranceRegion DECIMAL so it is rounded to next integer If you declare table like

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

)

Then it will not rounded of and you will get the result as you want

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

output:

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

Note:- If you use FLOAT or DOUBLE PRECISION you don’t have to specify the number of decimal values but it has its own flaws.

For more details you can click here

Источник

Есть таблица с полем col1 типа real,
(я её соединяю саму с собой по методу нарастающего итога — разницу между строками нахожу)
MAX(T2.col1) — MAX(T1.col1) AS col1
T1.col1 = -821171496.4
T2.col1 = -821171638.5

результирующий col1 вместо 142.1 содержит 200,0!
То есть указанный запрос разности в качестве исходных данных попадают не -821171496.4 а -821171400.0, то есть округляются! Ещё есть такая закономерность, что, если сделать «select * from t1», то в результатах запроса это число выглядит как -8.211714Е+08!

Как избавиться от округления при вычислении (max-max) и как сделать, чтобы в результатах запроса больше цифр отображалось (полная хранимая точность)?

Добавлено через 1 час 7 минут
Сейчас опытным путём установил, что округление происходит именно при выполнении оператора Subtract.
Сами агрегаты возвращают полную точность!
Как же вычесть без предварительного округления аргументов?

Источник

MariaDB 10.6.

Создал следующую функцию:

CREATE
    FUNCTION count_sum()
    RETURNS DECIMAL(19, 3)
BEGIN
    DECLARE result DECIMAL DEFAULT 0.000;

    SET result = (SELECT SUM(ba.amount) FROM billing_account AS ba);
    RETURN result;
END;

Здесь поле billing_account.amount имеет тип DECIMAL(19, 3). Однако, в результате вызова функции я получаю округлённое целочисленное значение. Каким образом я могу избежать округления, чтобы получить точное число с долями?

задан 16 сен 2022 в 10:02

ZhenyriaZhenyria

2,0642 золотых знака13 серебряных знаков41 бронзовый знак

Дело не в sum. Если тип просто DECIMAL, то в нем не хранится дробная часть (по умолчанию хранится 0 знаков после запятой). Используйте DECIMAL(32, 4) чтоб хранить 4 знака после запятой.

При вставке нецелых чисел в колонку типа DECIMAL значение обрезается и выдается предупреждение:

Data truncated for column

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

ответ дан 16 сен 2022 в 10:12

Источник

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

Количество просмотров 34K

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

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

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

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

Какого?

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)

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

Источник