sql server - 199.96-0=200 এসকিউএল এ কেন?




sql-server tsql (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) চেয়ে আলাদা আলাদা ধরণের প্রতিটি অপারেশনের ফলাফল এটি বাহ্যিক প্রসঙ্গে প্রয়োগের আগে স্পষ্টভাবে নিক্ষেপ করা হয়।

তবুও, ফলাফলটি 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))
        )
    ) 

এখন আসুন দেখুন এসকিউএল সার্ভার বিয়োগের অপারেশনের প্রতিটি পক্ষের জন্য ঠিক কী প্রকারগুলি ব্যবহার করছে:

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) Floor(Cast(etc)) হ'ল numeric(38,1)

বিয়োগের অপারেশনের ফলাফলের যথাযথতা এবং স্কেল সম্পর্কিত নিয়মগুলি (যেমন: e1 - e2 ) এর মতো দেখতে:

যথার্থতা: সর্বাধিক (এস 1, এস 2) + সর্বাধিক (পি 1-এস 1, পি 2-এস 2) + 1
স্কেল: সর্বাধিক (এস 1, এস 2)

এটি এর মতো মূল্যায়ন করে:

যথার্থতা : সর্বাধিক (1,2) + সর্বাধিক (38-1, 5-2) + 1 => 2 + 37 + 1 => 40
স্কেল: সর্বাধিক (1,2) => 2

আপনি numeric(38,1) প্রথম স্থান থেকে এসেছেন তা নির্ধারণের জন্য numeric(38,1) নিয়মগুলির লিঙ্কটি ব্যবহার করতে পারেন (ইঙ্গিত: আপনি দুটি যথার্থ 19 মানকে গুণিত করেছেন)।

কিন্তু:

  • ফলাফলের নির্ভুলতা এবং স্কেলটির নিখুঁত সর্বাধিক 38 থাকে a যখন ফলাফলের যথার্থতা 38 এর চেয়ে বেশি হয়, তখন তা 38 এ কমিয়ে আনা হয় এবং ফলাফলের অবিচ্ছেদ্য অংশকে কাটা থেকে রক্ষা করার চেষ্টা করার জন্য সংশ্লিষ্ট স্কেল হ্রাস করা হয়। কিছু ক্ষেত্রে যেমন গুণ বা বিভাগ হিসাবে, দশমিক নির্ভুলতা ধরে রাখার জন্য স্কেল ফ্যাক্টর হ্রাস করা হবে না, যদিও ওভারফ্লো ত্রুটি উত্থাপিত হতে পারে।

উফ। যথার্থতা 40 হয় We আমাদের এটি হ্রাস করতে হবে, এবং যথার্থতা হ্রাস করার সাথে সর্বদা সর্বনিম্ন উল্লেখযোগ্য সংখ্যাগুলি কেটে নেওয়া উচিত যার অর্থ স্কেল হ্রাস করাও। এক্সপ্রেশনটির জন্য চূড়ান্ত ফলাফলের প্রকারটি numeric(38,0) , যা 199.96 200 to

আপনি সম্ভবত সম্পূর্ণ এক্সপ্রেশন ফলাফলের চারপাশে বড় এক্সপ্রেশনটির ভিতরে থেকে একটি 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))

আমি এমনকি বাইরের কাস্টও মুছে ফেলতে পারি।

আমরা এখানে শিখি আমাদের প্রত্যাশিত ফলাফলের পরিবর্তে প্রকৃতপক্ষে যথাযথতা এবং স্কেলটি মিলানোর জন্য ধরণের পছন্দ করা উচিত। কেবল বৃহত্তর নির্ভুলতার সংখ্যার দিকে যাওয়া অর্থবোধ করে না, কারণ এসকিউএল সার্ভার ওভারফ্লোগুলি এড়ানোর চেষ্টা করার জন্য পাটিগণিতের ক্রিয়াকলাপগুলির সময় এই ধরণেরগুলিকে পরিবর্তন করতে পারে।

অধিক তথ্য:


নিম্নলিখিত বিবৃতি জন্য জড়িত তথ্য ধরণের উপর নজর রাখুন:

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 পরিবর্তে 199.96 ( দশমিকের পরে এক অঙ্ক, শূন্য নয় ) দিয়ে শেষ করে তা ব্যাখ্যা করে।

নোট:

FLOOR সুনির্দিষ্ট সংখ্যাসূচক প্রকাশের চেয়ে কম বা তার সমান বৃহত্তম পূর্ণসংখ্যা প্রদান করে এবং ফলাফলটি ইনপুট হিসাবে একই ধরণের থাকে। এটি INT এর জন্য INT, ফ্লাটের জন্য ফ্লাট এবং 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 থাকে a যখন ফলাফলের যথার্থতা 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)

জাভাস্ক্রিপ্টে আমার অ্যালগরিদম বাস্তবায়নের চেষ্টা এখানে। আমি এসকিউএল সার্ভারের বিপরীতে ফলাফলগুলি পরীক্ষা করে দেখেছি। এটি আপনার প্রশ্নের একেবারে অংশের উত্তর দেয়।

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