sql-server - long - sql text type length




왜 SQL에서 199.96-0=200입니까? (2)

나는 이것을 풀어서 무슨 일이 일어나는지 살펴볼 필요가있다.

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.96numeric(5,2) 이고 더 긴 Floor(Cast(etc))numeric(38,1) 입니다.

빼기 연산 의 결과 인 정밀도와 스케일 대한 규칙 (예 : e1 - e2 )은 다음과 같습니다.

정밀도 : max (s1, s2) + max (p1-s1, p2-s2) +1
규모 : 최대 (s1, s2)

이것은 다음과 같이 평가됩니다.

정밀도 : 최대 (1,2) + 최대 (38-1, 5-2) +1 => 2 + 37 + 1 => 40
규모 : 최대 (1,2) => 2

또한 규칙 링크를 사용하여 처음에 numeric(38,1) 출처를 파악할 수 있습니다 (힌트 : 두 개의 정밀도 19를 곱한 값).

그러나:

  • 결과 정밀도와 스 I 일은 절대 최대 값이 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는 오버플로를 피하기 위해 산술 연산 중에 이러한 유형을 변경하기 때문에 큰 정밀도 숫자로 이동하는 것은 의미가 없습니다.

추가 정보 :

나는 이상한 청구서를받는 고객이있다. 핵심 문제를 격리 할 수있었습니다.

SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 200 what the?
SELECT 199.96 - (0.0 * FLOOR(1.0 * CAST(199.96 AS DECIMAL(19, 4)))) -- 199.96
SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * 199.96)) -- 199.96

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

-- It gets weirder...
SELECT (0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 0
SELECT (0 * FLOOR(1.0 * CAST(199.96 AS DECIMAL(19, 4))))                         -- 0
SELECT (0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * 199.96))                         -- 0

-- so... ... 199.06 - 0 equals 200... ... right???
SELECT 199.96 - 0 -- 199.96 ...NO....

누구 한테 단서가있어, 도대체 무슨 일이 벌어지고 있니? 내 말은, 확실히 decimal 데이터 유형과 관련이있다. 그러나 내 머리를 감쌀 수 없다.

숫자 리터럴이 어떤 데이터 유형인지에 대한 많은 혼란이 있었기 때문에 실제 선을 표시하기로 결정했습니다.

PS.SharePrice - (CAST((@InstallmentCount - 1) AS DECIMAL(19, 4)) * CAST(FLOOR(@InstallmentPercent * PS.SharePrice) AS DECIMAL(19, 4))))

PS.SharePrice DECIMAL(19, 4)

@InstallmentCount INT

@InstallmentPercent DECIMAL(19, 4)

DECIMAL(19, 4) 과 다른 타입의 피연산자를 갖는 각 연산의 결과가 외부 컨텍스트에 적용되기 전에 명시 적으로 형 DECIMAL(19, 4) 되는지 확인했습니다.

그럼에도 불구하고 결과는 200.00 입니다.

나는 당신이 컴퓨터에서 실행할 수있는 끓인 샘플을 만들었습니다.

DECLARE @InstallmentIndex INT = 1
DECLARE @InstallmentCount INT = 1
DECLARE @InstallmentPercent DECIMAL(19, 4) = 1.0
DECLARE @PS TABLE (SharePrice DECIMAL(19, 4))
INSERT INTO @PS (SharePrice) VALUES (599.96)

-- 2000
SELECT
  IIF(@InstallmentIndex < @InstallmentCount,
  FLOOR(@InstallmentPercent * PS.SharePrice),
  1999.96)
FROM @PS PS

-- 2000
SELECT
  IIF(@InstallmentIndex < @InstallmentCount,
  FLOOR(@InstallmentPercent * CAST(599.96 AS DECIMAL(19, 4))),
  1999.96)
FROM @PS PS

-- 1996.96
SELECT
  IIF(@InstallmentIndex < @InstallmentCount,
  FLOOR(@InstallmentPercent * 599.96),
  1999.96)
FROM @PS PS

-- Funny enough - with this sample explicitly converting EVERYTHING to DECIMAL(19, 4) - it still doesn't work...
-- 2000
SELECT
  IIF(@InstallmentIndex < @InstallmentCount,
  FLOOR(@InstallmentPercent * CAST(199.96 AS DECIMAL(19, 4))),
  CAST(1999.96 AS DECIMAL(19, 4)))
FROM @PS PS

이제 뭔가가있어.

-- 2000
SELECT
  IIF(1 = 2,
  FLOOR(CAST(1.0 AS decimal(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))),
  CAST(1999.96 AS DECIMAL(19, 4)))

-- 1999.9600
SELECT
  IIF(1 = 2,
  CAST(FLOOR(CAST(1.0 AS decimal(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))) AS INT),
  CAST(1999.96 AS DECIMAL(19, 4)))

어쨌든 지옥은 정수를 반환해야합니다. 무슨 일 이니? :-디

나는 이제 그것을 정말로 본질적으로 끓여야한다고 생각한다 .-D

-- 1.96
SELECT IIF(1 = 2,
  CAST(1.0 AS DECIMAL (36, 0)),
  CAST(1.96 AS DECIMAL(19, 4))
)

-- 2.0
SELECT IIF(1 = 2,
  CAST(1.0 AS DECIMAL (37, 0)),
  CAST(1.96 AS DECIMAL(19, 4))
)

-- 2
SELECT IIF(1 = 2,
  CAST(1.0 AS DECIMAL (38, 0)),
  CAST(1.96 AS DECIMAL(19, 4))
)

다음 문과 관련된 데이터 유형을주의 깊게 살펴보십시오.

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.0NUMERIC(1, 1)
    • NUMERIC(1, 1) * NUMERIC(38, 0)NUMERIC(38, 1)
  3. 199.96NUMERIC(5, 2)
    • NUMERIC(5, 2) - NUMERIC(38, 1)NUMERIC(38, 1) (아래 참조)

이것은 199.96 대신에 200.0 ( 10 진수가 아닌 1 자리, 0이 아님)으로 끝나는 이유를 설명합니다.

노트:

FLOOR 는 지정된 숫자 표현식보다 작거나 같은 가장 큰 정수를 리턴하고 결과는 입력과 동일한 유형을 갖습니다. INT에 대해서는 INT를, FLOAT에 대해서는 FLOAT를, 그리고 NUMERIC (x, y)에 대해서는 NUMERIC (x, 0)을 리턴합니다.

알고리즘 에 따르면 :

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)

* 결과 정밀도와 스 I 일은 절대 최대 값이 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);





sqldatatypes