ReSQL.ru
     
powered by simpleCommunicator - 2.0.18     © 2024 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Map
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Деление DESIMAL с заданной точностью - борьба с неявным округлением
20 сообщений из 20, страница 1 из 1
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38131382
OlM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
OlM
Гость
Рад приветствовать, коллеги!

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

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

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

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

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

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

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

Есть идеи?

С искренним уважением...
...
Рейтинг: 0 / 0
30.01.2013, 20:42
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38131488
OlM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
OlM
Гость
Угу, кажется есть свет в конце тоннеля. Если кому интересно, вот вам две функции. Первая постороена, как я уже говорил, по лекалам Никиты Зимина (еще раз спасибо!), вторая - результат размышлений. Первая работает без потери точности, вторая теряет последние два знака после запятой. Ну, два - не двенадцать. С учетом того, что от нас требуют 14 знаков, можно сказать, что проблема решена. Если, конечно, не обращать внимание на быстродействие.

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
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
...
Рейтинг: 0 / 0
30.01.2013, 22:58
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38131497
Владимир Затуливетер
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Затуливетер
Участник
OlM,

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

Вот мой вариант решения, он оказался таким же как и первый вариант Никиты Зимина :)
Ну и функция деления, вроде нормально отрабатывает.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
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
...
Рейтинг: 0 / 0
30.01.2013, 23:08
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38131506
Владимир Затуливетер
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Затуливетер
Участник
ой тут ошибка у меня...
abs конечно надо заменить на что-то более подходящее :D

declare @a decimal(20, 0) = abs(@X)
declare @c decimal(20, 0) = abs(@Y)
...
Рейтинг: 0 / 0
30.01.2013, 23:16
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38131513
OlM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
OlM
Гость
Владимир Затуливетер,

Признаю ошибку, со ссылкой я был неправ. А вот насчет деления, я тоже рассматривал Ваш вариант, но он не всегда дает хорошие результаты. Вот проверьте с такими параметрами:
Код: sql
1.
2.
3.
4.
5.
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)
...
Рейтинг: 0 / 0
30.01.2013, 23:20
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38131532
Владимир Затуливетер
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Затуливетер
Участник
OlM,

да не пашет...
...
Рейтинг: 0 / 0
30.01.2013, 23:31
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38131556
OlM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
OlM
Гость
Владимир Затуливетер,

Кстати, утверждая, что произведение работает без потери точности, я тоже погорячился - все те же последние два знака показывают ерунду.
...
Рейтинг: 0 / 0
30.01.2013, 23:50
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38131576
Владимир Затуливетер
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Затуливетер
Участник
OlM,
Нулей и кастов побольше тогда нормально
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
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
...
Рейтинг: 0 / 0
31.01.2013, 00:15
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38131582
Владимир Затуливетер
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Затуливетер
Участник
OlMВ данный момент проблема решена вынесением калькуляции за пределы SQL сервера.

А может CLR-ку написать?
Вы как решили проблему с вычислениями на клиенте?
...
Рейтинг: 0 / 0
31.01.2013, 00:30
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38131595
OlM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
OlM
Гость
Владимир Затуливетер,

Ну как... Висит Windows service, когда надо, по запросу от клиента - тянет данные из базы, обрабатывает из и запихивает результат назад в базу. Но фактически, такая архитектура сводит базу к простому хранилищу данных (некоторый querying для отчетов принципиально картину не меняет).
...
Рейтинг: 0 / 0
31.01.2013, 01:02
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38131665
aleks2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks2
Участник
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(хрен знает скока). Механика целочисленного умножения/деления весьма проста.
...
Рейтинг: 0 / 0
31.01.2013, 06:37
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38131707
OlM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
OlM
Гость
aleks2Все перевести в DECIMAL(38,0). Никтож не запрещает считать в японских нанокопейках или расейских килойенах.

Увы, не пойдет - при перемножении будет переполнение регистра. Так система отбрасывает знаки справа, а при (38,0) она не сможет втиснуть знаки слева.
...
Рейтинг: 0 / 0
31.01.2013, 08:46
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38131738
Владимир Затуливетер
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Затуливетер
Участник
OlMВладимир Затуливетер,

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

Я спрашивал про математику :)
Просто мопробовать перенести ее в CLR функции.
Также как мы делали создать 2 функции умножения и деления только CLR.
...
Рейтинг: 0 / 0
31.01.2013, 09:27
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38131790
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
OlM,

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

Задача вряд ли уникальна.
...
Рейтинг: 0 / 0
31.01.2013, 10:12
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38132570
OlM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
OlM
Гость
MasterZiv,

Да я что угодно готов поставить, что в денежных полях заказчикам на самом деле НЕ НУЖНА такая точность. Она может быть нужна в ценах, рейтах, shares, но не в деньгах. Денежные поля к этому требованию просто пристегнули "до кучи". Но раз уж пристегнули, то здесь вступает в действие принцип: "любой каприз за ваши деньги". За действительно большие деньги, замечу. Так что если я пойду к руководству с идеей "полечить" клиента, то мне просто ответят, что проще и выгоднее "полечить" IT department. Вплоть до 100% замены. Что называется - просто бизнес и ничего личного.
...
Рейтинг: 0 / 0
31.01.2013, 15:53
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38132589
Crimean
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Crimean
Участник
OlM,

так все же, вариант вынесения математики в CLR рассматривался или нет?
...
Рейтинг: 0 / 0
31.01.2013, 16:06
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38132604
OlM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
OlM
Гость
Владимир Затуливетер,

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

Но честно говоря, я все равно не понимаю 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), точно так, как написано в документации.

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

Или я все-таки недостаточно долго медитировал над документацией?
...
Рейтинг: 0 / 0
31.01.2013, 16:18
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38132608
OlM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
OlM
Гость
Crimeanтак все же, вариант вынесения математики в CLR рассматривался или нет?
Я не могу сказать, рассматривался ли он авторами действующей системы, но совершенно точно, он будет рассмотрен мной :)
...
Рейтинг: 0 / 0
31.01.2013, 16:26
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38132845
OlM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
OlM
Гость
На этом, похоже, поток идей иссяк. Ну что же, есть как минимум два рабочих варианта - уже опубликованные функции и CLR. Будем экспериментировать и сравнивать бубны разных форм.

Спасибо всем, кто откликнулся!
...
Рейтинг: 0 / 0
31.01.2013, 18:57
| Ответить | Цитировать | Написать  
Деление DESIMAL с заданной точностью - борьба с неявным округлением
    #38132867
Crimean
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Crimean
Участник
я за CLR :) если надо поучаствовать - привлекайте, мне результат тоже очень интересен
...
Рейтинг: 0 / 0
31.01.2013, 19:10
| Ответить | Цитировать | Написать  
20 сообщений из 20, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Деление DESIMAL с заданной точностью - борьба с неявным округлением
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (1): Анонимы (1)
Читали форум (3): Анонимы (1), Yandex Bot 7 мин., Google Bot 9 мин.
Пользователи онлайн (8): Анонимы (4), Yandex Bot 1 мин., Bing Bot 1 мин., RePredeclared 1 мин., Google Bot 9 мин.
Польз. соглашение
NoSQL.ru, Чат Кролика       Пашэ: Рисунки, Чат       Фотоальбом: Участники, Встречи
Закрыть
Start [/forum/topic.php?fid=46&tid=1708510&gotonew=1]:
 0ms
get settings:
 2ms
get forum list:
 7ms
check forum access:
 1ms
check topic access:
 1ms
track hit:
 40ms
get topic data:
 7ms
get first new msg:
 2ms
get forum data:
 1ms
get page messages:
 20ms
update_topic_read_status (1708510): 31.01.2013 19:10:42:
 0ms
get tp. blocked users:
 0ms
get online users:
 19ms
check new:
 1ms
others: 102ms
total:  203ms
созд. / загр.: 203ms (нв.: 1ms)
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]