Почему ms-sql-server (все версии) преобразует 1.0 / 12.0 как числовое (8,6)?

Я пытаюсь объяснить разницу в стоимости 101,10.

303'300'000 / 12 - это примерно 25'275'000.
Однако, согласно MS-SQL, это 25 274 898,90.

Рассмотрим этот (упрощенный) SQL-оператор:

SELECT 
     303300000.00/12.0 AS a 
    ,1.0/12.0*303300000.00 AS b
    ,1.0/12.0 AS omg 
    ,1.0/CAST(12.0 AS float) AS expected 
    ,0.083333*303300000.0 AS r1 
    ,0.083333333333333300 * 303300000.0 AS r2

astounding results

Я думал, так как я написал 1.0 / 12.0, он будет отображаться как плавать
(что само по себе глупо, но это уже другая история),
но видимо это decimal (8,6)

CREATE VIEW dbo._Test
AS
SELECT 1.0/12.0 as xxx



SELECT 
     COLUMN_NAME
    ,DATA_TYPE
    ,NUMERIC_PRECISION
    ,NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '_Test' 


DROP VIEW dbo._Test

Есть ли причина для этого безумия?
Как он определяет точность?
Есть ли какие-либо обозначения для принудительного смещения с плавающей запятой без необходимости в операторе приведения?

Если я запустил тот же запрос в PostgreSQL, pg все сделает правильно ...

postgre does it right

Это задокументировано в разделе «Точность, масштаб и длина». Правила нелегко усвоить, и они пытаются сохранить столько цифр до десятичной точки, прежде чем они перейдут к сохранению такого же количества цифр после десятичной точки. Константы FLOAT могут быть построены с использованием нотации E, которая может быть такой же простой, как 1e. Конечно, двоичные числа с плавающей запятой имеют свои проблемы с точностью ...

Jeroen Mostert 10.08.2018 15:55

Кстати, более удобный способ определения типа вашего выражения, чем создание таблицы и запрос метаданных, - это использование SQL_VARIANT_PROPERTY: SELECT SQL_VARIANT_PROPERTY(1.0/12.0, 'BaseType'), SQL_VARIANT_PROPERTY(1.0/12.0, 'Precision'), SQL_VARIANT_PROPERTY(1.0/12.0, 'Scale').

Jeroen Mostert 10.08.2018 16:03

@Jeroen Mostert: Я знаю, что такое машина epsilon, так что никаких сюрпризов. Интересно, так что правильно 1.0e / 12.0e

Stefan Steiger 10.08.2018 16:04
4
3
142
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Установка точности и масштаба для умножения и деления decimal / numeric - это мистическое искусство. SQL Server действительно объясняет логику документация:

In multiplication and division operations we need precision - scale places to store the integral part of the result. The scale might be reduced using the following rules:

  1. The resulting scale is reduced to min(scale, 38 – (precision-scale)) if the integral part is less than 32, because it cannot be greater than 38 – (precision-scale). Result might be rounded in this case.

  2. The scale will not be changed if it is less than 6 and if the integral part is greater than 32. In this case, overflow error might be raised if it cannot fit into decimal(38, scale)

  3. The scale will be set to 6 if it is greater than 6 and if the integral part is greater than 32. In this case, both integral part and scale would be reduced and resulting type is decimal(38,6). Result might be rounded to 6 decimal places or overflow error will be thrown if integral part cannot fit into 32 digits.

Поскольку у меня никогда не было необходимости реализовывать эту функциональность, я должен честно сказать, что я не уверен, что когда-либо действительно понимал логику (я уверен, что это имеет большой смысл). Мой вывод из объяснения: если я забочусь о точности и масштабе результата, я буду использовать тип после арифметической операции.

Где-то в этом объяснении я вижу цифру «6», так что это объясняет масштабную часть. Я уверен, что точность следует из столь же прямой логики.

Ответ принят как подходящий

Это объясняется в документации: Точность, масштаб и длина (Transact-SQL)

В частности, в нем говорится:

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.

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)

Важная часть здесь - последняя. В вашем случае у вас есть decimal(2,1) и decimal(3,1). Для точности это приводит к:

 2 - 1 + 1 + max(6,1 + 3 + 1) = 2 + max(6,5) = 2 + 6 = 8

Для шкалы получаем:

max(6,1+3+1) = max(6,5) = 6

Возьмите полученные значения и в результате вы получите decimal(8,6).

Учтите, что литералы, такие как 1.0 и 12.6, имеют десятичный (числовой) тип. 1.0 - это точность 2 и масштаб 1, 12.0 - точность 3 и масштаб 2.

Результат выражения 1.0 / 12.0 имеет тип десятичный (числовой) потому что наивысший приоритет типов - десятичный (оба типа десятичные).

Что касается точность и масштаб выражения, формулы следующие:

expr        precision                           scale
e1 / e2     p1 - s1 + s2 + max(6, s1 + p2 + 1)  max(6, s1 + p2 + 1)

Так,

precision=2-1+1+max(6,1+3+1)=2-1+1+6=8

и

scale=max(6,1+3+1)=6

Другие вопросы по теме