sql-server - समझ - संबंधपरक डेटाबेस प्रबंधन प्रणाली




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)))) -- 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) तुलना 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

मुझे लगता है कि मैं अब इसे वास्तव में बहुत सार तक उबालने में कामयाब रहा :-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.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 निर्दिष्ट संख्यात्मक अभिव्यक्ति से कम या बराबर सबसे बड़ा पूर्णांक लौटाता है और परिणाम में इनपुट के समान प्रकार होता है। यह INT के लिए INT, FLOAT के लिए FLOAT और NUMERIC (x, 0) के लिए FLOAT (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(38, 7)
  • NUMERIC(1, 1) * NUMERIC(38, 0) NUMERIC(40, 1) और NUMERIC(38, 1) से NUMERIC(38, 1)
  • NUMERIC(5, 2) - NUMERIC(38, 1) NUMERIC(40, 2) और NUMERIC(38, 1) से NUMERIC(38, 1)

यहाँ जावास्क्रिप्ट में एल्गोरिथ्म को लागू करने का मेरा प्रयास है। मैंने SQL सर्वर के खिलाफ परिणामों की जांच की है। यह आपके प्रश्न के बहुत सार भाग का जवाब देता है।

// 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);


मुझे इसे थोड़ा खोलकर शुरू करने की ज़रूरत है ताकि मैं देख सकूँ कि क्या चल रहा है:

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

अब देखते हैं कि SQL सर्वर घटाव ऑपरेशन के प्रत्येक पक्ष के लिए किस प्रकार का उपयोग कर रहा है:

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 ) इस तरह दिखते हैं:

परिशुद्धता: अधिकतम (s1, s2) + अधिकतम (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 मानों को गुणा किया है)।

परंतु:

  • परिणाम परिशुद्धता और पैमाने का निरपेक्ष अधिकतम 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 सर्वर उन प्रकारों को उत्परिवर्ती संचालन के दौरान उत्परिवर्तित करने से बचने की कोशिश करेगा।

अधिक जानकारी:








sqldatatypes