이상한 청구서를받는 고객이 있습니다. 핵심 문제를 분리 할 수있었습니다.
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....
누구에게 단서가 있습니까? 여기서 무슨 일이 일어나고 있습니까? 내 말은, 그것은 십진수 데이터 유형과 확실히 관련이 있지만 실제로 그것을 둘러 쌀 수는 없습니다 …
숫자 리터럴이 어떤 데이터 유형인지에 대해 많은 혼란이 있었기 때문에 실제 행을 표시하기로 결정했습니다.
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)외부 컨텍스트에 적용하기 전에 다른 유형의 피연산자를 갖는 각 연산의 결과 가 명시 적으로 캐스트 되는지 확인했습니다 .
그럼에도 불구하고 결과는 남아 있습니다 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)))
도대체 바닥은 어쨌든 정수를 반환해야합니다. 여기서 무슨 일이 일어나고 있습니까? :-디
이제 정말 본질로 끓인 것 같아요 😀
-- 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))
)
)
이제 빼기 연산의 각 측면에 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')
결과 :
숫자 5 2 숫자 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는 오버플로를 방지하기 위해 산술 연산 중에 이러한 형식을 변경하므로 큰 정밀도 숫자 만 사용하는 것은 의미가 없습니다.
추가 정보:
답변
다음 문과 관련된 데이터 유형을 주시하십시오.
SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))))
NUMERIC(19, 4) * NUMERIC(19, 4)이다NUMERIC(38, 7)(아래 참조)FLOOR(NUMERIC(38, 7))이다NUMERIC(38, 0)(아래 참조)
0.0이다NUMERIC(1, 1)NUMERIC(1, 1) * NUMERIC(38, 0)이다NUMERIC(38, 1)
199.96이다NUMERIC(5, 2)NUMERIC(5, 2) - NUMERIC(38, 1)이다NUMERIC(38, 1)(아래 참조)
당신이 결국 이유를 설명 200.0( 소수점 이하 한 자리가 아닌 제로 대신) 199.96.
메모:
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)* 결과 정밀도 및 스케일의 절대 최대 값은 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);