رند کردن در SQL Server تفاوت ROUND و Bankers Rounding

بهینه‌سازی دقت اعداد در SQL Server: بررسی تفاوت‌های تابع ROUND و Bankers Roundin

تابع استاندارد `ROUND()` در SQL Server می‌تواند هنگام رند کردن اعدادی که به 0.5 ختم می‌شوند، رفتاری غیرمنتظره داشته باشد. این مقاله به جزئیات تابع `ROUND()` در SQL Server می‌پردازد، توابع رند کردن دیگر مانند `TRUNCATE()`، `FLOOR()` و `CEILING()` را بررسی می‌کند و سپس رند کردن بانکی (Bankers Rounding) را معرفی می‌کند. رند کردن بانکی یک روش از نظر آماری دقیق‌تر برای رند کردن مقادیری است که به 0.5 ختم می‌شوند، به این صورت که به نزدیک‌ترین عدد زوج رند می‌شود. ما همچنین عملکرد یک تابع سفارشی رند کردن بانکی را در مقایسه با تابع بومی `ROUND()` در SQL Server بررسی خواهیم کرد تا کارایی و بهینگی آن را برای مجموعه‌های داده بزرگ نشان دهیم.

تابع `ROUND()` در SQL Server اغلب برای رند کردن مقادیر عددی به یک طول یا دقت مشخص استفاده می‌شود. با این حال، رفتار آن هنگام رند کردن اعدادی با قسمت اعشاری 0.5 می‌تواند تعجب‌آور باشد، زیرا همواره برای اعداد مثبت و منفی که به 0.5 ختم می‌شوند، به سمت بالا (دور از صفر) رند می‌کند. برای درک بهتر این رفتار، به مثال‌های زیر توجه کنید:

SELECT ROUND(2.5, 0) AS Round2_5;
SELECT ROUND(3.5, 0) AS Round3_5;
SELECT ROUND(2.4, 0) AS Round2_4;
SELECT ROUND(2.6, 0) AS Round2_6;
SELECT ROUND(-2.5, 0) AS RoundMinus2_5;
SELECT ROUND(-3.5, 0) AS RoundMinus3_5;

اگرچه تابع `TRUNCATE()` مستقیمی در SQL Server مانند برخی سیستم‌های پایگاه داده دیگر وجود ندارد، می‌توانید با تبدیل (casting) به یک نوع داده Integer یا استفاده از ترکیبی از توابع دیگر، نتایج مشابهی را به دست آورید. این کار به طور موثری بخش اعشاری یک عدد را حذف می‌کند:

SELECT CAST(2.5 AS INT) AS Truncate2_5;
SELECT CONVERT(INT, 2.5) AS Truncate2_5_Convert;
SELECT FLOOR(2.5) AS Floor2_5_TruncatePositive;
SELECT CEILING(-2.5) AS CeilingMinus2_5_TruncateNegative;

تابع `FLOOR()` بزرگترین عدد صحیح کوچکتر یا مساوی با عبارت عددی مشخص شده را برمی‌گرداند. این تابع به طور موثری به سمت پایین‌ترین عدد صحیح رند می‌کند:

SELECT FLOOR(2.1) AS Floor2_1;
SELECT FLOOR(2.9) AS Floor2_9;
SELECT FLOOR(-2.1) AS FloorMinus2_1;
SELECT FLOOR(-2.9) AS FloorMinus2_9;

تابع `CEILING()` کوچکترین عدد صحیح بزرگتر یا مساوی با عبارت عددی مشخص شده را برمی‌گرداند. این تابع به طور موثری به سمت بالاترین عدد صحیح رند می‌کند:

SELECT CEILING(2.1) AS Ceiling2_1;
SELECT CEILING(2.9) AS Ceiling2_9;
SELECT CEILING(-2.1) AS CeilingMinus2_1;
SELECT CEILING(-2.9) AS CeilingMinus2_9;

رند کردن بانکی، که به عنوان “Round Half to Even” نیز شناخته می‌شود، یک روش رند کردن است که اعداد ختم شده به 0.5 را به نزدیک‌ترین عدد صحیح زوج رند می‌کند. این روش اغلب در کاربردهای مالی و آماری ترجیح داده می‌شود زیرا به جلوگیری از خطای سیستماتیک که می‌تواند در هنگام رند کردن مداوم 0.5 به سمت بالا (دور از صفر) رخ دهد، کمک می‌کند. این روش توزیع متعادل‌تری از خطاهای رند کردن را در یک مجموعه بزرگ از اعداد فراهم می‌کند. به عنوان مثال، 2.5 به 2 رند می‌شود و 3.5 به 4 رند می‌شود.

از آنجایی که SQL Server تابع داخلی برای رند کردن بانکی ندارد، می‌توانیم یک تابع تعریف شده توسط کاربر (UDF) برای پیاده‌سازی این منطق ایجاد کنیم. تابع T-SQL زیر یک عدد اعشاری و دقت مورد نظر را به عنوان ورودی می‌گیرد و رند کردن بانکی را اعمال می‌کند:

CREATE FUNCTION dbo.BankersRound (@Value DECIMAL(38, 10), @Precision INT)
RETURNS DECIMAL(38, 10)
AS
BEGIN
    DECLARE @RoundedValue DECIMAL(38, 10);
    DECLARE @FractionalPart DECIMAL(38, 10);
    DECLARE @IntegerPart DECIMAL(38, 10);
    DECLARE @Factor DECIMAL(38, 10);

    SET @Factor = POWER(CAST(10 AS DECIMAL(38, 10)), @Precision);
    SET @RoundedValue = @Value * @Factor;

    SET @FractionalPart = @RoundedValue - FLOOR(@RoundedValue);
    SET @IntegerPart = FLOOR(@RoundedValue);

    IF @FractionalPart = 0.5
    BEGIN
        IF CAST(@IntegerPart AS INT) % 2 = 0
            SET @RoundedValue = @IntegerPart; -- Round to nearest even
        ELSE
            SET @RoundedValue = @IntegerPart + 1; -- Round to nearest even (odd + 0.5 becomes odd + 1)
    END
    ELSE
    BEGIN
        SET @RoundedValue = ROUND(@RoundedValue, 0); -- Use standard rounding for other cases
    END

    RETURN @RoundedValue / @Factor;
END;

پس از ایجاد تابع `dbo.BankersRound`، می‌توانید آن را دقیقاً مانند هر تابع دیگری در کوئری‌های خود استفاده کنید:

SELECT dbo.BankersRound(2.5, 0) AS BankersRound2_5; -- Expected: 2
SELECT dbo.BankersRound(3.5, 0) AS BankersRound3_5; -- Expected: 4
SELECT dbo.BankersRound(2.4, 0) AS BankersRound2_4; -- Expected: 2
SELECT dbo.BankersRound(2.6, 0) AS BankersRound2_6; -- Expected: 3
SELECT dbo.BankersRound(-2.5, 0) AS BankersRoundMinus2_5; -- Expected: -2 (rounds to nearest even)
SELECT dbo.BankersRound(-3.5, 0) AS BankersRoundMinus3_5; -- Expected: -4 (rounds to nearest even)

بیایید با طیف وسیع‌تری از مقادیر آزمایش کنیم تا اطمینان حاصل شود که تابع طبق انتظار عمل می‌کند:

SELECT
    Value,
    ROUND(Value, 0) AS SQL_ROUND,
    dbo.BankersRound(Value, 0) AS Bankers_Round
FROM (VALUES
    (1.5), (2.5), (3.5), (4.5), (5.5), (6.5), (7.5), (8.5), (9.5), (10.5),
    (1.4), (2.4), (3.4), (4.4), (5.4),
    (1.6), (2.6), (3.6), (4.6), (5.6),
    (-1.5), (-2.5), (-3.5), (-4.5), (-5.5),
    (-1.4), (-2.4), (-3.4), (-4.4), (-5.4),
    (-1.6), (-2.6), (-3.6), (-4.6), (-5.6)
) AS TestValues(Value);

برای کاربردهایی که با میلیون‌ها مقدار اعشاری سروکار دارند، عملکرد بسیار مهم است. بیایید زمان اجرای تابع سفارشی رند کردن بانکی خود را در مقایسه با تابع بومی `ROUND()` در SQL Server مقایسه کنیم. ابتدا یک جدول آزمایشی با یک میلیون مقدار اعشاری تصادفی ایجاد و پر می‌کنیم:

SET NOCOUNT ON;
-- Create a test table
IF OBJECT_ID('dbo.TestNumbers', 'U') IS NOT NULL
    DROP TABLE dbo.TestNumbers;

CREATE TABLE dbo.TestNumbers (
    ID INT IDENTITY(1,1),
    DecimalValue DECIMAL(38, 10)
);

-- Populate with 1 million random decimal values
DECLARE @i INT = 0;
WHILE @i < 1000000
BEGIN
    INSERT INTO dbo.TestNumbers (DecimalValue)
    VALUES (CAST(RAND(CHECKSUM(NEWID())) * 100000 AS DECIMAL(38, 10)));
    SET @i = @i + 1;
END;
GO

اکنون، عملکرد تابع رند کردن بانکی را اندازه‌گیری می‌کنیم:

-- Test Bankers Rounding
DECLARE @StartTime DATETIME = GETDATE();
SELECT ID, dbo.BankersRound(DecimalValue, 0) AS RoundedValue
FROM dbo.TestNumbers
OPTION (MAXDOP 1); -- Ensure single-threaded execution for consistent timing
DECLARE @EndTime DATETIME = GETDATE();
SELECT DATEDIFF(ms, @StartTime, @EndTime) AS BankersRoundTimeMs;

در مرحله بعد، عملکرد تابع `ROUND()` داخلی SQL Server را بررسی می‌کنیم:

-- Test SQL Server ROUND
SET @StartTime = GETDATE();
SELECT ID, ROUND(DecimalValue, 0) AS RoundedValue
FROM dbo.TestNumbers
OPTION (MAXDOP 1);
SET @EndTime = GETDATE();
SELECT DATEDIFF(ms, @StartTime, @EndTime) AS SQLRoundTimeMs;
GO

بر اساس آزمایش‌های معمول، تابع بومی `ROUND()` به طور قابل توجهی سریع‌تر از تابع سفارشی تعریف شده توسط کاربر برای رند کردن بانکی است. این امر قابل انتظار است، زیرا توابع داخلی به شدت بهینه شده‌اند و در سطح پایین‌تری در موتور SQL Server پیاده‌سازی شده‌اند. در حالی که UDF سفارشی منطق مورد نظر رند کردن بانکی را فراهم می‌کند، سربار عملکرد آن باید برای عملیات با حجم بسیار بالا در نظر گرفته شود. برای سناریوهایی که دقت آماری رند کردن حیاتی‌تر از سرعت خام است و پردازش دسته‌ای می‌تواند بخشی از سربار را کاهش دهد، UDF یک راه‌حل قابل اعتماد و کاربردی است.