Почему 199.96 - 0 = 200 в SQL?

Ответ 1

Мне нужно начать с разворачивания этого, чтобы я мог видеть, что происходит:

SELECT 199.96 - 
    (
        0.0 * 
        FLOOR(
            CAST(1.0 AS DECIMAL(19, 4)) * 
            CAST(199.96 AS DECIMAL(19, 4))
        )
    ) 

Теперь давайте посмотрим, какие типы SQL Server используют для каждой стороны операции вычитания:

SELECT  SQL_VARIANT_PROPERTY (199.96     ,'BaseType'),
    SQL_VARIANT_PROPERTY (199.96     ,'Precision'),
    SQL_VARIANT_PROPERTY (199.96     ,'Scale')

SELECT  SQL_VARIANT_PROPERTY (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))  ,'BaseType'),
    SQL_VARIANT_PROPERTY (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))  ,'Precision'),
    SQL_VARIANT_PROPERTY (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))  ,'Scale')

Результаты:

numeric 5   2
numeric 38  1

Таким образом, 199.96 является numeric(5,2) а более длинный Floor(Cast(etc)) Является numeric(38,1).

Правила для получения точности и масштаба операции вычитания (т. e1 - e2) выглядят следующим образом:

Точность: max (s1, s2) + max (p1-s1, p2-s2) + 1
Масштаб: max (s1, s2)

Это оценивается следующим образом:

Точность: max (1,2) + max (38-1, 5-2) + 1 => 2 + 37 + 1 => 40
Масштаб: max (1,2) => 2

Вы также можете использовать ссылку на правила, чтобы определить, откуда в первую очередь появилось numeric(38,1) (подсказка: вы умножили два значения точности 19).

Но:

• Точность и масштаб результата имеют абсолютный максимум 38. Когда точность результата больше 38, она уменьшается до 38, и соответствующая шкала уменьшается, чтобы предотвратить усечение интегральной части результата. В некоторых случаях, таких как умножение или деление, коэффициент масштабирования не уменьшается, чтобы сохранить десятичную точность, хотя ошибка переполнения может быть повышена.

К сожалению. Точность 40. Мы должны уменьшить ее, и поскольку уменьшение точности всегда должно отсекать наименее значимые цифры, что также означает уменьшение масштаба. Конечный результирующий тип выражения будет numeric(38,0), что для 199.96 раундов до 200.

Вероятно, вы можете исправить это, перемещая и консолидируя операции CAST() изнутри большого выражения в один CAST() вокруг всего результата выражения. Итак, это:

SELECT 199.96 - 
    (
        0.0 * 
        FLOOR(
            CAST(1.0 AS DECIMAL(19, 4)) * 
            CAST(199.96 AS DECIMAL(19, 4))
        )
    ) 

становится:

SELECT CAST( 199.96 - ( 0.0 * FLOOR(1.0 * 199.96) ) AS decimial(19,4))

Я мог бы даже удалить внешний литой.

Мы узнаем здесь, что мы должны выбирать типы, соответствующие точности и масштабам, которые мы имеем сейчас, а не ожидаемому результату. Не имеет смысла просто переходить на большие числа точности, потому что SQL Server будет мутировать эти типы во время арифметических операций, чтобы избежать переполнения.

Дополнительная информация:

• Точность, масштаб и длина с использованием Sql_Variant_Property()
• Приоритет оператора
• Превышение типа данных

Ответ 2

Следите за типами данных для следующего утверждения:

SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))))

1. NUMERIC(19, 4) * NUMERIC(19, 4) NUMERIC(38, 7) (см. Ниже)
   • FLOOR(NUMERIC(38, 7)) NUMERIC(38, 0) (см. Ниже)
2. 0.0 - это NUMERIC(1, 1)
   • NUMERIC(1, 1) * NUMERIC(38, 0) NUMERIC(38, 1)
3. 199.96 NUMERIC(5, 2)
   • NUMERIC(5, 2) - NUMERIC(38, 1) NUMERIC(38, 1) (см. Ниже)

Это объясняет, почему вы 199.96 200.0 (одна цифра после десятичной, а не ноль) вместо 199.96.

Заметки:

FLOOR возвращает наибольшее целое число, меньшее или равное указанному числовому выражению, и результат имеет тот же тип, что и input. Он возвращает INT для INT, FLOAT для FLOAT и NUMERIC (x, 0) для NUMERIC (x, y).

По алгоритму:

Operation | Result precision                    | Result scale*
e1 * e2   | p1 + p2 + 1                         | s1 + s2
e1 - e2   | max(s1, s2) + max(p1-s1, p2-s2) + 1 | max(s1, s2)

* Точность и масштаб результата имеют абсолютный максимум 38. Когда точность результата превышает 38, она уменьшается до 38, а соответствующий масштаб уменьшается, чтобы попытаться предотвратить усечение неотъемлемой части результата.

Описание также содержит детали того, как именно масштаб уменьшается в операциях сложения и умножения. На основании этого описания:

• NUMERIC(19, 4) * NUMERIC(19, 4) является NUMERIC(39, 8) и прикреплен к NUMERIC(38, 7)
• NUMERIC(1, 1) * NUMERIC(38, 0) является NUMERIC(40, 1) и прикреплен к NUMERIC(38, 1)
• NUMERIC(5, 2) - NUMERIC(38, 1) является NUMERIC(40, 2) и прикреплен к NUMERIC(38, 1)

Вот моя попытка реализовать алгоритм в JavaScript. Я проверил результаты по SQL Server. Это отвечает самой сути вашего вопроса.

// https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-2017

function numericTest_mul(p1, s1, p2, s2) {
  // e1 * e2
  var precision = p1 + p2 + 1;
  var scale = s1 + s2;

  // see notes in the linked article about multiplication operations
  var newscale;
  if (precision - scale < 32) {
    newscale = Math.min(scale, 38 - (precision - scale));
  } else if (scale < 6 && precision - scale > 32) {
    newscale = scale;
  } else if (scale > 6 && precision - scale > 32) {
    newscale = 6;
  }

  console.log("NUMERIC(%d, %d) * NUMERIC(%d, %d) yields NUMERIC(%d, %d) clamped to NUMERIC(%d, %d)", p1, s1, p2, s2, precision, scale, Math.min(precision, 38), newscale);
}

function numericTest_add(p1, s1, p2, s2) {
  // e1 + e2
  var precision = Math.max(s1, s2) + Math.max(p1 - s1, p2 - s2) + 1;
  var scale = Math.max(s1, s2);

  // see notes in the linked article about addition operations
  var newscale;
  if (Math.max(p1 - s1, p2 - s2) > Math.min(38, precision) - scale) {
    newscale = Math.min(precision, 38) - Math.max(p1 - s1, p2 - s2);
  } else {
    newscale = scale;
  }

  console.log("NUMERIC(%d, %d) + NUMERIC(%d, %d) yields NUMERIC(%d, %d) clamped to NUMERIC(%d, %d)", p1, s1, p2, s2, precision, scale, Math.min(precision, 38), newscale);
}

function numericTest_union(p1, s1, p2, s2) {
  // e1 UNION e2
  var precision = Math.max(s1, s2) + Math.max(p1 - s1, p2 - s2);
  var scale = Math.max(s1, s2);

  // my idea of how newscale should be calculated, not official
  var newscale;
  if (precision > 38) {
    newscale = scale - (precision - 38);
  } else {
    newscale = scale;
  }

  console.log("NUMERIC(%d, %d) + NUMERIC(%d, %d) yields NUMERIC(%d, %d) clamped to NUMERIC(%d, %d)", p1, s1, p2, s2, precision, scale, Math.min(precision, 38), newscale);
}

/*
 * first example in question
 */

// CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))
numericTest_mul(19, 4, 19, 4);

// 0.0 * FLOOR(...)
numericTest_mul(1, 1, 38, 0);

// 199.96 * ...
numericTest_add(5, 2, 38, 1);

/*
 * IIF examples in question
 * the logic used to determine result data type of IIF / CASE statement
 * is same as the logic used inside UNION operations
 */

// FLOOR(DECIMAL(38, 7)) UNION CAST(1999.96 AS DECIMAL(19, 4)))
numericTest_union(38, 0, 19, 4);

// CAST(1.0 AS DECIMAL (36, 0)) UNION CAST(1.96 AS DECIMAL(19, 4))
numericTest_union(36, 0, 19, 4);

// CAST(1.0 AS DECIMAL (37, 0)) UNION CAST(1.96 AS DECIMAL(19, 4))
numericTest_union(37, 0, 19, 4);

// CAST(1.0 AS DECIMAL (38, 0)) UNION CAST(1.96 AS DECIMAL(19, 4))
numericTest_union(38, 0, 19, 4);