Деление DESIMAL с заданной точностью - борьба с неявным округлением

Рад приветствовать, коллеги!

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

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

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

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

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

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

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

Есть идеи?

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

Угу, кажется есть свет в конце тоннеля. Если кому интересно, вот вам две функции. Первая постороена, как я уже говорил, по лекалам Никиты Зимина (еще раз спасибо!), вторая - результат размышлений. Первая работает без потери точности, вторая теряет последние два знака после запятой. Ну, два - не двенадцать. С учетом того, что от нас требуют 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

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

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

declare @a decimal(20, 0) = abs(@X)
declare @c decimal(20, 0) = abs(@Y)

Владимир Затуливетер,

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

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)

OlM,

да не пашет...

Владимир Затуливетер,

Кстати, утверждая, что произведение работает без потери точности, я тоже погорячился - все те же последние два знака показывают ерунду.

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

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

А может CLR-ку написать?
Вы как решили проблему с вычислениями на клиенте?

Владимир Затуливетер,

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