T-SQL. Деление. Частное - 17 значащих десятичных разрядов - Базы данных

Здравствуйте, Воронин Иван, Вы писали:

ВИ>Сейчас стал перепроверять и обнаружил мне не понятное поведение.
ВИ>Если играть с точность и масштабом, то точность вычислений меняется и не больше значит больше.
ВИ>Нужно смотреть реализацию, но мне это не до этого.

подробности

ВИ>

ВИ>declare @N1 Numeric(20,2)
ВИ>declare @N2 Numeric(20,2)

ВИ>set @N1 = 42183246.61
ВИ>set @N2 = 41761414.14

ВИ>select @N2 * 100 / @N1

ВИ>результат: 98.99999999075462
ВИ>

ВИ>

ВИ>declare @N1 Numeric(17,2)
ВИ>declare @N2 Numeric(17,2)

ВИ>set @N1 = 42183246.61
ВИ>set @N2 = 41761414.14

ВИ>select @N2 * 100 / @N1

ВИ>результат: 98.99999999075462342
ВИ>

ВИ>Результат в калькуляторе от Win7 в инженерном режиме: 98,999999990754623426553748609157

Предположим, у нас @N1 и @N2 имеют тип Numeric(x,2) (рубли с копейками).

Согласно справочнику, типы результатов выводятся следующим образом:

numeric(x, 2) * numeric(3, 0) => numeric(x+4, 2)
numeric(x+4, 2) / numeric(x, 2) => numeric(x + 4 + max(6, x + 3), max(6, x + 3))

Если x >= 3, то выражение упрощается до numeric(2x+7, x+3)
То есть, для x = 17 тип должен получаться numeric(41, 20). Но максимум точности — 38, и тип будет урезан с потерей младших разрядов, то есть в реальности получится numeric(38, 17).
Для x = 20 получаем numeric(47, 23), который урезается до (38, 14)
Давайте повнимательнее посмотрим на процесс урезания. От x+3 мы отнимаем превышение 2x+7 над 38.
То есть если x <= 15, то урезания не произойдёт.

А вот если x > 15, то результирующий масштаб (scale) будет равен x+3 — (2x+7-38) = 34 — x, при точности 38.
Из-за этого вы и наблюдаете такой парадокс: чем больше исходная точность (precision), тем хуже финальный масштаб (scale). SQL server резервирует для вас лидирующие цифры в целой части.

Вы хотите получить 15 знаков после запятой. Решение уравнения напрямую даёт нам x = 19.
Если от вас не требуется оперировать суммами более десяти квадриллионов рублей, то можно просто вычислять вот так:

print cast(41761414.14 as dec(19, 2)) * 100 / cast(42183246.61 as dec(19,2))

Здравствуйте, Sinclair, Вы писали:
P.S.
Исследовать поведение автовывода типов удобно при помощи вспомогательной процедуры:

IF OBJECT_ID (N'dbo.Analyse') IS NOT NULL
    DROP PROCEDURE dbo.Analyse;
GO
create procedure Analyse(@what sql_variant)
as 
begin
  print cast(@what as varchar(max))

  print
    cast(SQL_VARIANT_PROPERTY(@what, 'BaseType') as varchar(max)) +'('+
    cast(SQL_VARIANT_PROPERTY(@what, 'Precision') as varchar(max))+ ', ' +
    cast(SQL_VARIANT_PROPERTY(@what, 'Scale') as varchar(max)) + ')'
end
go

DECLARE 
  @foo decimal(19,2) = 41761414.14, 
  @bar decimal(19,2) = 42183246.61, 
  @what sql_variant
set @what = @foo / @bar * 100
exec Analyse @what

print cast(41761414.14 as dec(19, 2)) * 100 / cast(42183246.61 as dec(19,2))

Есть:

Требуется:
На стороне сервера вычислить процент меньшего от большего с 15 знаками после запятой (всего 17). Самое "длинное" число — 99.999999999999999.

Двойной точности для этого не хватает. В какой-нибудь СУБД есть четверная точность? Судя по поведению, Numeric для деления конвертируется туда и обратно в двойную точность.

Реализовывать аналог BigInteger?
М.б. есть готовые решения?

Здравствуйте, Воронин Иван, Вы писали:

ВИ>Реализовывать аналог BigInteger?
ВИ>М.б. есть готовые решения?

Именно BigInteger? На каком языке? Или готовое тебе чтобы сразу всё заработало?

29.05.2023 16:49, "Воронин Иван" пишет:
> Двойной точности для этого не хватает. В какой-нибудь СУБД есть четверная точность? Судя по поведению, Numeric для деления конвертируется туда и обратно в двойную точность.
>
> Реализовывать аналог BigInteger?
> М.б. есть готовые решения?

DECIMAL

Posted via RSDN NNTP Server 2.1 beta

Здравствуйте, Marty, Вы писали:

ВИ>>Реализовывать аналог BigInteger?
ВИ>>М.б. есть готовые решения?

M>Именно BigInteger?

Аналог.
Ограничим делимое 128 битами, а делитель и частное 64. Пусть не расширятся.

M>На каком языке?

Я же сказал, T-SQL.

M>Или готовое тебе чтобы сразу всё заработало?

Конечно

Здравствуйте, Alex.Che, Вы писали:

AC>29.05.2023 16:49, "Воронин Иван" пишет:
>> Двойной точности для этого не хватает. В какой-нибудь СУБД есть четверная точность? Судя по поведению, Numeric для деления конвертируется туда и обратно в двойную точность.

AC>DECIMAL

В чём отличие от Numeric в MS-SQL?

29.05.2023 18:17, "Воронин Иван" пишет:
>
> В чём отличие от Numeric в MS-SQL?

он резиновый.
https://learn.microsoft.com/ru-ru/sql/t-sql/data-types/decimal-and-numeric-transact-sql

Posted via RSDN NNTP Server 2.1 beta

Здравствуйте, Alex.Che, Вы писали:

AC>29.05.2023 18:17, "Воронин Иван" пишет:
>>
>> В чём отличие от Numeric в MS-SQL?

AC>он резиновый.
AC>https://learn.microsoft.com/ru-ru/sql/t-sql/data-types/decimal-and-numeric-transact-sql

Типы числовых данных с фиксированными точностью и масштабом. Типы decimal и numeric являются взаимозаменяемыми синонимами.

И не резиновый:

Максимальное общее число хранимых десятичных разрядов. Это число включает символы слева и справа от десятичной запятой. Точность должна быть значением в диапазоне от 1 до максимум 38.

Здравствуйте, Воронин Иван, Вы писали:

ВИ>Аналог.
ВИ>Ограничим делимое 128 битами, а делитель и частное 64. Пусть не расширятся.

Ну, тогда просто сделай одну операцию и не парься. Это если тебе одна операция нужна

M>>На каком языке?

ВИ>Я же сказал, T-SQL.

Есть на плюсиках. Можешь сканпелять в DLL и всуячить в сиквел сервер как расширение

M>>Или готовое тебе чтобы сразу всё заработало?

ВИ>Конечно

Какой ты хитрый

Сейчас стал перепроверять и обнаружил мне не понятное поведение.
Если играть с точность и масштабом, то точность вычислений меняется и не больше значит больше.
Нужно смотреть реализацию, но мне это не до этого.

declare @N1 Numeric(20,2)
declare @N2 Numeric(20,2)

set @N1 = 42183246.61
set @N2 = 41761414.14

select @N2 * 100 / @N1

результат: 98.99999999075462

declare @N1 Numeric(17,2)
declare @N2 Numeric(17,2)

set @N1 = 42183246.61
set @N2 = 41761414.14

select @N2 * 100 / @N1

результат: 98.99999999075462342

Результат в калькуляторе от Win7 в инженерном режиме: 98,999999990754623426553748609157

29.05.2023 18:30, "Воронин Иван" пишет:

> И не резиновый:

резиновый, резиновый.
если при вычислении получившаяся "точность" будет больше указанного значения,
то в результате будет использоваться большая "точность".

Posted via RSDN NNTP Server 2.1 beta

Здравствуйте, Marty, Вы писали:

M>Есть на плюсиках. Можешь сканпелять в DLL и всуячить в сиквел сервер как расширение

Я того ещё ни разу не делал.
Что, потом можно будет использовать в коде как скалярную функцию?

Здравствуйте, Alex.Che, Вы писали:

AC>резиновый, резиновый.
AC>если при вычислении получившаяся "точность" будет больше указанного значения,
AC>то в результате будет использоваться большая "точность".

Результат по своим (точность, масштаб) отличается от аргументов? Я с этим не спорил.

Видимо, у нас разные понимания "резиновости".
Для меня резиновость — это типа BigInteger в .Net.

Здравствуйте, Воронин Иван, Вы писали:

ВИ>Есть:

ВИ>

ВИ>T-SQL (2005);

ВИ>2 Numeric'а (рубли с копейками).

ВИ>

ВИ>Требуется:
ВИ>На стороне сервера вычислить процент меньшего от большего с 15 знаками после запятой (всего 17). Самое "длинное" число — 99.999999999999999.

ВИ>Двойной точности для этого не хватает. В какой-нибудь СУБД есть четверная точность? Судя по поведению, Numeric для деления конвертируется туда и обратно в двойную точность.

Я бы делал CLR Stored Procedure, но не знаю какой dotnet поддерживает 2005 SQL Server и есть ли там длинная математика из коробки. В 2017 так можно.

Здравствуйте, Воронин Иван, Вы писали:

M>>Есть на плюсиках. Можешь сканпелять в DLL и всуячить в сиквел сервер как расширение

ВИ>Я того ещё ни разу не делал.
ВИ>Что, потом можно будет использовать в коде как скалярную функцию?

Вроде бы да. Но это не точно

На шарпе вроде точно можно делать такие расширения, на плюсах теоретически тоже должно быть возможно, но явно придется потрахаться

Здравствуйте, Sinclair, Вы писали:

S>Если от вас не требуется оперировать суммами более десяти квадриллионов рублей, то можно просто вычислять вот так:
S>

S>print cast(41761414.14 as dec(19, 2)) * 100 / cast(42183246.61 as dec(19,2))
S>

Спасибо! Всё подходит. Должно сработать.

	Тут политика, но она к месту
	Если монетарная политика не станет альтернативной (привет Реджепу), то ещё не скоро дойдём до триллиона.

S>print cast(41761414.14 as dec(19, 2)) * 100 / cast(42183246.61 as dec(19,2))
S>

Пока облом.
Забыл упомянуть, что это не своя ИС, а покупная (OLAP). В редакторе источников данных есть вычисляемые поля.

Попробовал реализовать, и выяснил.
При вычислениях (делении точно) они переводят Numeric во Float. К этому коду у меня доступа нет — это в "движке". Не понял зачем.

Именно поэтому у меня зародилось предположение: "Судя по поведению, Numeric для деления конвертируется туда и обратно в двойную точность."

Здравствуйте, Воронин Иван, Вы писали:
ВИ>Попробовал реализовать, и выяснил.
ВИ>При вычислениях (делении точно) они переводят Numeric во Float. К этому коду у меня доступа нет — это в "движке". Не понял зачем.
Посмотрите, какой SQL порождает этот движок, при помощи SQL Profiler. Если вычисления делаются внутри движка, а не опускаются на уровень SQL, то упс.
ВИ>Именно поэтому у меня зародилось предположение: "Судя по поведению, Numeric для деления конвертируется туда и обратно в двойную точность."
Ну, по крайней мере, вы знаете, где именно возникла проблема. Теперь можно сосредоточиться на этом месте:
1. Написать здесь или на stackoverflow конкретный вопрос с минимальным примером OLAP-запроса, на котором воспроизводится косяк
3. Написать в поддержку этого OLAP-решения

От:	Sinclair	https://github.com/evilguest/
Дата:	01.06.23 08:56
Оценка:	13 (2)

От:	Sinclair	https://github.com/evilguest/
Дата:	01.06.23 10:32
Оценка:	2 (1)

	От:	Воронин Иван
	Дата:	29.05.23 13:49
	Оценка:

От:	Marty	https://www.youtube.com/channel/UChp5PpQ6T4-93HbNF-8vSYg
Дата:	29.05.23 14:39
Оценка:

	От:	Alex.Che
	Дата:	29.05.23 14:55
	Оценка:

От:	BlackEric	http://black-eric.lj.ru
Дата:	29.05.23 15:52
Оценка: