Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
OlM
Guest
Рад приветствовать, коллеги!

Здесь уже не раз обсуждалась проблема точности вычислений при типах FLOAT и DECIMAL, а так же проблема неяного округления результата вычислений. Рискну поднять тему еще раз.

Имеем - набор полей типа DECIMAL(38,18). К моему прискорбию, это не излишество. Точнее - почти не излишество. Одни наши клиенты действительно требуют до 14 знаков после запятой (застрелите меня, я не знаю, зачем так считать деньги!), другие способны практически полностью израсходовать знаки перед запятыми (японские иены, знаете ли), и все это - в пределах одного и того же поля. С учетом запаса прочности, лишние знаки не выглядят лишними. Кое-какие поля коэффициентов может и можно ужать слева от запятой, но никак не справа от нее. И проблемы в целом это не решит - я уже копал в этом направлении.

Задача - вычислять произведения и деления значений полей типа DECIMAL(38,18) с результирующей эффективной точностью DECIMAL(38,18).

В данный момент проблема решена вынесением калькуляции за пределы SQL сервера. Что меня совсем не радует, сами понимаете.

Заранее говорю - промежуточную конвертацию во FLOAT отбрасываем, потому что нужной точности она не дает.

Копал литературу в направлении определение минимального SCALE при загрублении вычислений, нашел только упоминание, что разработчики MS SQL выбрали этот параметр равным 6. Причем упоминалось это даже не в BOL. Возможности устанавливать этот параметр вручную - не нашел, хотя это сразу решило бы проблему. Плохо искал?

В блоге Никиты Зимина нашел очень приятный алгоритм умножения без потери точности. Осталось решить проблему деления.

Есть идеи?

С искренним уважением...
30 янв 13, 20:42    [13853181]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
OlM
Guest
Угу, кажется есть свет в конце тоннеля. Если кому интересно, вот вам две функции. Первая постороена, как я уже говорил, по лекалам Никиты Зимина (еще раз спасибо!), вторая - результат размышлений. Первая работает без потери точности, вторая теряет последние два знака после запятой. Ну, два - не двенадцать. С учетом того, что от нас требуют 14 знаков, можно сказать, что проблема решена. Если, конечно, не обращать внимание на быстродействие.

Но неужели нет возможности избежать этих танцев с бубнами?! Я по-прежнему буду рад любым альтернативным мнениям.

CREATE FUNCTION dbo.calc_Multiply  
(	@x	DECIMAL (38,18)
,	@y	DECIMAL (38,18)
)
RETURNS DECIMAL (38,18)
AS
BEGIN

DECLARE	@a		DECIMAL(18,0)
	,	@b		DECIMAL(18,18)
	,	@c		DECIMAL(18,0)
	,	@d		DECIMAL(18,18)
	,	@RES	DECIMAL(38,18)
;

	SET	@a = ROUND(@x, 0, 1)
	SET	@c = ROUND(@y, 0, 1)

	SET	@b = @x - @a
	SET	@d = @y - @c 

	SELECT @RES 	= CAST(@a * @c AS numeric(38,18)) 
			+ CAST(@a * @d AS numeric(38,18))
			+ CAST(@b * @c AS numeric(38,18))
			+ CAST(@b * @d AS numeric(38,18))

	RETURN @RES
END
GO

CREATE FUNCTION dbo.calc_Divide  
(	@x	DECIMAL (38,18)
,	@y	DECIMAL (38,18)
)
RETURNS DECIMAL (38,18)
AS
BEGIN

DECLARE @x1		DECIMAL(38,6)
	,	@cff	DECIMAL(1,1)
	,	@R1		DECIMAL(38,6)
	,	@RES	DECIMAL(38,18)

	SET	@cff = 0.1
	SET	@x1 = @x * 1000000000000

	SET	@R1 = @x1 / @y
	SET	@RES = CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( 
		@R1	* @cff AS DECIMAL(38,7)) 
			* @cff AS DECIMAL(38,8)) 
			* @cff AS DECIMAL(38,9)) 
			* @cff AS DECIMAL(38,10))
			* @cff AS DECIMAL(38,11))
			* @cff AS DECIMAL(38,12)) 
			* @cff AS DECIMAL(38,13)) 
			* @cff AS DECIMAL(38,14)) 
			* @cff AS DECIMAL(38,15)) 
			* @cff AS DECIMAL(38,16)) 
			* @cff AS DECIMAL(38,17)) 
			* @cff AS DECIMAL(38,18))

	RETURN @RES
END
GO
30 янв 13, 22:58    [13853718]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
OlM,

В следующий раз ссылку кидайте на ресурсы, а то попарился я пока нашел
http://nzeemin.livejournal.com/271417.html

Вот мой вариант решения, он оказался таким же как и первый вариант Никиты Зимина :)
Ну и функция деления, вроде нормально отрабатывает.
if exists ( select  *
            from    sys.objects
            where   object_id = object_id(N'dbo.TrueMultiply')
                    and type in ( N'FN', N'IF', N'TF', N'FS', N'FT' ) ) 
    begin
 	drop function dbo.TrueMultiply
    end
go
 
create function dbo.TrueMultiply
(
    @X decimal(38, 18)
  , @Y decimal(38, 18)
)
returns decimal(38, 18)
as
begin

	declare @a decimal(20, 0) = abs(@X)
	declare @b decimal(18, 18) = @X - @a 
	declare @c decimal(20, 0) = abs(@Y)
	declare @d decimal(18, 18) = @Y - @c

	--select ( @a + @b ) * ( @c + @d ) 
	return( convert(decimal(38, 18), @a * @c)
			 + convert(decimal(38, 18), @b * @c) 
			 + convert(decimal(38, 18), @d * @a)
			 + convert(decimal(38, 18), @d * @b) 
		  )

end

go


if exists ( select  *
            from    sys.objects
            where   object_id = object_id(N'dbo.TrueDivide')
                    and type in ( N'FN', N'IF', N'TF', N'FS', N'FT' ) ) 
    begin
 	drop function dbo.TrueDivide
    end
go
 
create function dbo.TrueDivide
(
    @X decimal(38, 18)
  , @Y decimal(38, 18)
)
returns decimal(38, 18)
as
begin
	return dbo.TrueMultiply(@X, 1.0 / @Y);
end
go


-- tests
declare @x decimal(38, 18) = 232342344.123456789012345678
declare @y decimal(38, 18) = 34123434.123456789012345678

select @x / @y , dbo.TrueDivide(@x, @y)
select @x * @y , dbo.TrueMultiply(@x, @y)
go
30 янв 13, 23:08    [13853753]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
ой тут ошибка у меня...
abs конечно надо заменить на что-то более подходящее :D

declare @a decimal(20, 0) = abs(@X)
declare @c decimal(20, 0) = abs(@Y)
30 янв 13, 23:16    [13853779]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
OlM
Guest
Владимир Затуливетер,

Признаю ошибку, со ссылкой я был неправ. А вот насчет деления, я тоже рассматривал Ваш вариант, но он не всегда дает хорошие результаты. Вот проверьте с такими параметрами:
SELECT dbo.calc_Multiply(11111111111111.111111111111111111, 111.111111111111111111)
SELECT dbo.calc_Divide	(11111111111111.111111111111111111, 111.111111111111111111)

SELECT dbo.TrueMultiply	(11111111111111.111111111111111111, 111.111111111111111111)
SELECT dbo.TrueDivide	(11111111111111.111111111111111111, 111.111111111111111111)
30 янв 13, 23:20    [13853796]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
OlM,

да не пашет...
30 янв 13, 23:31    [13853848]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
OlM
Guest
Владимир Затуливетер,

Кстати, утверждая, что произведение работает без потери точности, я тоже погорячился - все те же последние два знака показывают ерунду.
30 янв 13, 23:50    [13853952]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
OlM,
Нулей и кастов побольше тогда нормально
alter FUNCTION dbo.calc_Divide  
(	@x	DECIMAL (38,18)
,	@y	DECIMAL (38,18)
)
RETURNS DECIMAL (38,18)
AS
BEGIN

DECLARE @x1		DECIMAL(38,4)
	,	@cff	DECIMAL(1,1)
	,	@R1		DECIMAL(38,6)
	,	@RES	DECIMAL(38,18)

	SET	@cff = 0.1
	SET	@x1 = @x * 100000000000000

	SET	@R1 = @x1 / @y
	SET	@RES = CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( CAST ( CAST (
		@R1	* @cff AS DECIMAL(38,7)) 
			* @cff AS DECIMAL(38,8)) 
			* @cff AS DECIMAL(38,9)) 
			* @cff AS DECIMAL(38,10))
			* @cff AS DECIMAL(38,11))
			* @cff AS DECIMAL(38,12)) 
			* @cff AS DECIMAL(38,13)) 
			* @cff AS DECIMAL(38,14)) 
			* @cff AS DECIMAL(38,15)) 
			* @cff AS DECIMAL(38,16)) 
			* @cff AS DECIMAL(38,17)) 
			* @cff AS DECIMAL(38,18))
			* @cff AS DECIMAL(38,19)) 
			* @cff AS DECIMAL(38,20))

	RETURN @RES
END
31 янв 13, 00:15    [13854023]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
OlM
В данный момент проблема решена вынесением калькуляции за пределы SQL сервера.


А может CLR-ку написать?
Вы как решили проблему с вычислениями на клиенте?
31 янв 13, 00:30    [13854077]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
OlM
Guest
Владимир Затуливетер,

Ну как... Висит Windows service, когда надо, по запросу от клиента - тянет данные из базы, обрабатывает из и запихивает результат назад в базу. Но фактически, такая архитектура сводит базу к простому хранилищу данных (некоторый querying для отчетов принципиально картину не меняет).
31 янв 13, 01:02    [13854129]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
aleks2
Guest
OlM
Задача - вычислять произведения и деления значений полей типа DECIMAL(38,18) с результирующей эффективной точностью DECIMAL(38,18).
Есть идеи?

С искренним уважением...

Насрать на DECIMAL(38,18) и десятичную точку.
Все перевести в DECIMAL(38,0). Никтож не запрещает считать в японских нанокопейках или расейских килойенах.
Точку ставить тока в момент отображения. Т.е. под s (scale) завести отдельное поле, причем зарезервировать два разряда под округление. Т.е. если считаем в копейках s = 10^-4. Если в микрокопейках s = 10^-7
И радоваться жизни.

ЗЫ. При очень горячем желании можно пользовать два поля DECIMAL(38,0) или ваще binary(хрен знает скока). Механика целочисленного умножения/деления весьма проста.
31 янв 13, 06:37    [13854341]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
OlM
Guest
aleks2
Все перевести в DECIMAL(38,0). Никтож не запрещает считать в японских нанокопейках или расейских килойенах.

Увы, не пойдет - при перемножении будет переполнение регистра. Так система отбрасывает знаки справа, а при (38,0) она не сможет втиснуть знаки слева.
31 янв 13, 08:46    [13854507]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
OlM
Владимир Затуливетер,

Ну как... Висит Windows service, когда надо, по запросу от клиента - тянет данные из базы, обрабатывает из и запихивает результат назад в базу. Но фактически, такая архитектура сводит базу к простому хранилищу данных (некоторый querying для отчетов принципиально картину не меняет).


Я спрашивал про математику :)
Просто мопробовать перенести ее в CLR функции.
Также как мы делали создать 2 функции умножения и деления только CLR.
31 янв 13, 09:27    [13854658]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34707
OlM,

Я думаю тебе все таки надо покопать в сторону понимания, зачем заказчику нужна такая бешенная точность.

Задача вряд ли уникальна.
31 янв 13, 10:12    [13854854]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
OlM
Guest
MasterZiv,

Да я что угодно готов поставить, что в денежных полях заказчикам на самом деле НЕ НУЖНА такая точность. Она может быть нужна в ценах, рейтах, shares, но не в деньгах. Денежные поля к этому требованию просто пристегнули "до кучи". Но раз уж пристегнули, то здесь вступает в действие принцип: "любой каприз за ваши деньги". За действительно большие деньги, замечу. Так что если я пойду к руководству с идеей "полечить" клиента, то мне просто ответят, что проще и выгоднее "полечить" IT department. Вплоть до 100% замены. Что называется - просто бизнес и ничего личного.
31 янв 13, 15:53    [13857881]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
Crimean
Member

Откуда:
Сообщений: 13147
OlM,

так все же, вариант вынесения математики в CLR рассматривался или нет?
31 янв 13, 16:06    [13857961]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
OlM
Guest
Владимир Затуливетер,

Да, кстати, это тоже может быть выходом (эх, %"№;%). Надо поэкспериментировать.

Но честно говоря, я все равно не понимаю MS. Ведь они ввели защиту шести знаков после запятой в случае явного загрубления, хоть никому об этом в документации и не сказали.

Чтобы вы поняли, что я имею в виду, они пишут (что здесь, что здесь):
MS
* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Теперь вопрос: каким будет выходной формат, если калькуляция по формулам даст размерность DECIMAL (50,20)?
Ответ (который не следует из документации): это будет формат DECIMAL(38,6), потому что шесть знаков после запятой принудительно защищены от загрубления. Появилась эта защита начиная с версии 2005 или 2008 - не знаю точно, увы. До того на выходе было бы DECIMAL(38,0), точно так, как написано в документации.

Значит теперь есть параметр, "играя" которым можно получать на выходе неявных приведений типов нужный формат. Т.е. о существовании проблемы ребята знают. Так почему не дать возможность устанавливать этот параметр вручную? Не понимаю.

Или я все-таки недостаточно долго медитировал над документацией?
31 янв 13, 16:18    [13858046]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
OlM
Guest
Crimean
так все же, вариант вынесения математики в CLR рассматривался или нет?

Я не могу сказать, рассматривался ли он авторами действующей системы, но совершенно точно, он будет рассмотрен мной :)
31 янв 13, 16:26    [13858078]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
OlM
Guest
На этом, похоже, поток идей иссяк. Ну что же, есть как минимум два рабочих варианта - уже опубликованные функции и CLR. Будем экспериментировать и сравнивать бубны разных форм.

Спасибо всем, кто откликнулся!
31 янв 13, 18:57    [13858882]     Ответить | Цитировать Сообщить модератору
 Re: Деление DESIMAL с заданной точностью - борьба с неявным округлением  [new]
Crimean
Member

Откуда:
Сообщений: 13147
я за CLR :) если надо поучаствовать - привлекайте, мне результат тоже очень интересен
31 янв 13, 19:10    [13858954]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить