محاسبات پلکانی در SQL SERVER

پنجشنبه, ۳ بهمن ۱۴۰۴، ۱۲:۵۲ ب.ظ

شرایطی را در نظر بگیرید که مودی قبلا ساختی داشته است و عوارض مربوط به آن ساخت را پرداخت کرده است. عوارض ساخت جدید برای آن به صورت پلکانی محاسبه شود

مساحت ناخالص/کل: S_total

مساحت موجود/ساخت قبل: S_exist

پلکان محاسبه

0تا200 متر مربع

200 تا 250

250 تا 300

300 تا 400

400 تا 500

500 تا 600

و 600 به بالا

اگر ساخت قبلی کمتر از 200 مثلا 160 متر مربع باشد. و ساخت کل مثلا 670 متر

پله های عوارض به این صورت محاسبه خواهد شد

200 - 160 = 40

250 - 200 = 50

300 - 250 = 50

400 - 300 = 100

500 - 400 = 100

600 - 500 = 100

670 - 600 = 70

اگر ساخت قبلی کمتر از 400 مثلا 360 متر مربع باشد. و ساخت کل مثلا 570 متر

پله های عوارض به این صورت محاسبه خواهد شد

200 - 200 = 0

250 - 250 = 0

300 - 300 = 0

400 - 360 = 40

500 - 400 = 100

570 - 500 = 70

600 - 600 = 0

می خواهیم تابع SQL بنویسیم که این شرایط را شامل شود

ALTER FUNCTION [dbo].[FS100]
(
@S_total AS FLOAT, -- مساحت کل
@S_exist AS FLOAT, -- مساحت موجود
@C AS SMALLINT -- پله ی مساحت
)
RETURNS FLOAT
AS
BEGIN
	DECLARE @B FLOAT
	BEGIN

	IF @S_total <= 200
	BEGIN
	IF @S_exist < = 200	
	BEGIN	
	SET @B = CASE @C 	
		WHEN	1	THEN	@S_total - @S_exist
		--WHEN	2	THEN	50
		--WHEN	3	THEN	50
		--WHEN	4	THEN	100
		--WHEN	5	THEN	100
		--WHEN	6	THEN	100
		--ELSE	@S_total - @S_exist
	END
	END
	END
	-- --============================================
	ELSE IF @S_total <= 250
	BEGIN
	IF @S_exist < = 200	
	BEGIN	
		SET @B = CASE @C 	
		WHEN	1	THEN	200 - @S_exist
		WHEN	2	THEN	@S_total - 200
		--WHEN	3	THEN	50
		--WHEN	4	THEN	100
		--WHEN	5	THEN	100
		--WHEN	6	THEN	100
		--ELSE	@S_total - @S_exist
		END
	END
	ELSE -- @S_exist < = 250
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	@S_total - @S_exist
		--WHEN	3	THEN	50
		--WHEN	4	THEN	100
		--WHEN	5	THEN	100
		--WHEN	6	THEN	100
		--ELSE	@S_total - @S_exist
		END
	END
	END
	 --============================================
	ELSE IF @S_total <= 300
	BEGIN
	IF @S_exist < = 200	
	BEGIN	
		SET @B = CASE @C 	
		WHEN	1	THEN	200 - @S_exist
		WHEN	2	THEN	250 - 200
		WHEN	3	THEN	@S_total - 250
		--WHEN	4	THEN	100
		--WHEN	5	THEN	100
		--WHEN	6	THEN	100
		--ELSE	@S_total - @S_exist
		END
	END
	ELSE IF @S_exist < = 250
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	250 - @S_exist
		WHEN	3	THEN	@S_total - 250
		--WHEN	4	THEN	100
		--WHEN	5	THEN	100
		--WHEN	6	THEN	100
		--ELSE	@S_total - @S_exist
		END
	END
	ELSE --IF @S_exist < = 300
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	@S_total - @S_exist
		--WHEN	4	THEN	100
		--WHEN	5	THEN	100
		--WHEN	6	THEN	100
		--ELSE	@S_total - @S_exist
		END
	END
	END
	-- --============================================
	ELSE IF @S_total <= 400
	BEGIN
	IF @S_exist < = 200	
	BEGIN	
		SET @B = CASE @C 	
		WHEN	1	THEN	200 - @S_exist
		WHEN	2	THEN	250 - 200
		WHEN	3	THEN	300 - 250
		WHEN	4	THEN	@S_total - 300
		--WHEN	5	THEN	100
		--WHEN	6	THEN	100
		--ELSE	@S_total - @S_exist
		END
	END
	ELSE IF @S_exist < = 250
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	250 - 200
		WHEN	3	THEN	300 - 250
		WHEN	4	THEN	@S_total - 300
		--WHEN	5	THEN	100
		--WHEN	6	THEN	100
		--ELSE	@S_total - @S_exist
		END
	END
	ELSE IF @S_exist < = 300
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	300 - 250
		WHEN	4	THEN	@S_total - @S_exist
		--WHEN	5	THEN	100
		--WHEN	6	THEN	100
		--ELSE	@S_total - @S_exist
		END
	END
	ELSE --IF @S_exist < = 400
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	0
		WHEN	4	THEN	@S_total - @S_exist
		--WHEN	5	THEN	100
		--WHEN	6	THEN	100
		--ELSE	@S_total - @S_exist
		END
	END
	END
	-- --============================================
	ELSE IF @S_total <= 500
	BEGIN
	IF @S_exist < = 200	
	BEGIN	
		SET @B = CASE @C 	
		WHEN	1	THEN	200 - @S_exist
		WHEN	2	THEN	250 - 200
		WHEN	3	THEN	300 - 250
		WHEN	4	THEN	400 - 300
		WHEN	5	THEN	@S_total - 400
		--WHEN	6	THEN	100
		--ELSE	@S_total - @S_exist
		END
	END
	ELSE IF @S_exist < = 250
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	250 - @S_exist
		WHEN	3	THEN	300 - 250
		WHEN	4	THEN	400 - 300
		WHEN	5	THEN	@S_total - 400
		--WHEN	6	THEN	100
		--ELSE	@S_total - @S_exist
		END
	END
	ELSE IF @S_exist < = 300
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	300 - @S_exist
		WHEN	4	THEN	400 - 300
		WHEN	5	THEN	@S_total - 400
		--WHEN	6	THEN	100
		--ELSE	@S_total - @S_exist
		END
	END
	ELSE IF @S_exist < = 400
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	0
		WHEN	4	THEN	400 - @S_exist
		WHEN	5	THEN	@S_total - 400
		--WHEN	6	THEN	100
		--ELSE	@S_total - @S_exist
		END
	END
	ELSE --IF @S_exist < = 500
	SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	0
		WHEN	4	THEN	0
		WHEN	5	THEN	@S_total - @S_exist
	--	WHEN	6	THEN	100
	--ELSE	@S_total - @S_exist
	END
	END
	
	-- --============================================
	ELSE IF @S_total <= 600
	BEGIN
	IF @S_exist < = 200	
	BEGIN	
		SET @B = CASE @C 	
		WHEN	1	THEN	200 - @S_exist
		WHEN	2	THEN	250 - 200
		WHEN	3	THEN	300 - 250
		WHEN	4	THEN	400 - 300
		WHEN	5	THEN	500 - 400
		WHEN	6	THEN	@S_total - 500
		--ELSE	@S_total - @S_exist
		END
	END
	ELSE IF @S_exist < = 250
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	250 - @S_exist
		WHEN	3	THEN	300 - 250
		WHEN	4	THEN	400 - 300
		WHEN	5	THEN	500 - 400
		WHEN	6	THEN	@S_total - 500
		--ELSE	@S_total - @S_exist
		END
	END
	ELSE IF @S_exist < = 300
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	300 - @S_exist
		WHEN	4	THEN	400 - 300
		WHEN	5	THEN	500 - 400
		WHEN	6	THEN	@S_total - 500
		--ELSE	@S_total - @S_exist
		END
	END
	ELSE IF @S_exist < = 400
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	0
		WHEN	4	THEN	400 - @S_exist
		WHEN	5	THEN	500 - 400
		WHEN	6	THEN	@S_total - 500
		--ELSE	@S_total - @S_exist
		END
	END
	ELSE IF @S_exist < = 500
	SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	0
		WHEN	4	THEN	0
		WHEN	5	THEN	500 - @S_exist
		WHEN	6	THEN	@S_total - 500
	--ELSE	@S_total - @S_exist
	END
	ELSE --IF @S_exist < = 600
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	0
		WHEN	4	THEN	0
		WHEN	5	THEN	0
		WHEN	6	THEN	@S_total - @S_exist
		--ELSE	@S_total - @S_exist
		END
	END
	END
	
	-- --============================================
	ELSE --> 600
	BEGIN
	IF @S_exist < = 200	
	BEGIN	
		SET @B = CASE @C 	
		WHEN	1	THEN	200 - @S_exist
		WHEN	2	THEN	250 - 200
		WHEN	3	THEN	300 - 250
		WHEN	4	THEN	400 - 300
		WHEN	5	THEN	500 - 400
		WHEN	6	THEN	600 - 500
		ELSE	@S_total - 600
		END
	END
	ELSE IF @S_exist < = 250
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	250 - @S_exist
		WHEN	3	THEN	300 - 250
		WHEN	4	THEN	400 - 300
		WHEN	5	THEN	500 - 400
		WHEN	6	THEN	600 - 500
		ELSE	@S_total - 600
		END
	END
	ELSE IF @S_exist < = 300
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	300 - @S_exist
		WHEN	4	THEN	400 - 300
		WHEN	5	THEN	500 - 400
		WHEN	6	THEN	600 - 500
		ELSE	@S_total - 600
		END
	END
	ELSE IF @S_exist < = 400
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	0
		WHEN	4	THEN	400 - @S_exist
		WHEN	5	THEN	500 - 400
		WHEN	6	THEN	600 - 500
		ELSE	@S_total - 600
		END
	END
	ELSE IF @S_exist < = 500
	SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	0
		WHEN	4	THEN	0
		WHEN	5	THEN	500 - @S_exist
		WHEN	6	THEN	600 - 500
		ELSE	@S_total - 600
	END
	ELSE IF @S_exist < = 600
	BEGIN
		SET @B = CASE @C 	
		WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	0
		WHEN	4	THEN	0
		WHEN	5	THEN	0
		WHEN	6	THEN	600 - @S_exist
		ELSE	@S_total - 600
		END
	END
	ELSE 
	BEGIN
	SET @B = CASE @C 	
	WHEN	1	THEN	0
		WHEN	2	THEN	0
		WHEN	3	THEN	0
		WHEN	4	THEN	0
		WHEN	5	THEN	0
		WHEN	6	THEN	0
		ELSE	@S_total - @S_exist
	END
	END
	END
	END
	RETURN @B
END

----------------------------------------------------------------------------------------------

اما نظر دیپ سیک وقتی ازش پرسیدم این تابع منو یاد ماتریس پایین مثلثی می اندازه:

کد تابعی که نوشتید واقعاً شبیه یک ماتریس پایین‌مثلثی (lower triangular matrix) است. در واقع منطق کار به این شکل است:

تحلیل ساختار ماتریس پایین‌مثلثی در کد شما:

1. پارامترهای ورودی:

@S_total: مقدار اصلی (مثلاً مساحت یا درآمد)
@S_exist: مقدار پایه یا مرجع
@C: شماره پله یا سطر (1 تا 7)

2. ساختار ماتریس‌گونه:

هر بلوک ELSE IF @S_total <= X مانند یک سطر از ماتریس عمل می‌کند، و شرط‌های داخلی IF @S_exist <= Y مانند ستون‌ها هستند.

3. مقادیر ثابت (نقطه‌های شکست):

200, 250, 300, 400, 500, 600

این مقادیر مرزهای هر پله را تعیین می‌کنند.

4. منطق محاسباتی:

تابع در واقع تفاضل بخش‌بندی شده بین @S_total و @S_exist را محاسبه می‌کند:

اگر @C = 1: فقط تفاضل در پله اول (0-200) محاسبه می‌شود
اگر @C = 2: فقط تفاضل در پله دوم (200-250) محاسبه می‌شود
و به همین ترتیب...

5. ماتریس پایین‌مثلثی:

اگر این مقادیر را به صورت ماتریس نمایش دهیم:

P12\P13   <=200   <=250   <=300   <=400   <=500   <=600   >600
<=200     Δ       -       -       -       -       -       -
<=250     Δ       Δ       -       -       -       -       -
<=300     Δ       Δ       Δ       -       -       -       -
<=400     Δ       Δ       Δ       Δ       -       -       -
<=500     Δ       Δ       Δ       Δ       Δ       -       -
<=600     Δ       Δ       Δ       Δ       Δ       Δ       -
>600      Δ       Δ       Δ       Δ       Δ       Δ       Δ

که در آن:

Δ = مقدار غیرصفر (محاسبه تفاضل)
- = مقدار صفر (چون @S_exist از @S_total بزرگتر است)

پیشنهاد بهینه‌سازی:

اگر تعداد پله‌ها زیاد شود، این کد بسیار پیچیده خواهد شد. می‌توانید از یک جدول پلکانی استفاده کنید:

CREATE TABLE TaxBrackets (
    BracketOrder INT PRIMARY KEY,
    MinValue FLOAT,
    MaxValue FLOAT
);

INSERT INTO TaxBrackets VALUES
(1, 0, 200),
(2, 200, 250),
(3, 250, 300),
(4, 300, 400),
(5, 400, 500),
(6, 500, 600),
(7, 600, NULL);

سپس تابع را ساده‌تر کنید.

کاربرد عملی:

این نوع تابع معمولاً برای محاسبات پلکانی مالیاتی، تعرفه‌بندی تصاعدی یا محاسبه پاداش بر اساس بازه‌ها استفاده می‌شود.
--------------

این هم تابع ساده تر و جمع و جور تر

ALTER FUNCTION [dbo].[FS100]
(
    @S_total FLOAT,
    @S_exist FLOAT,
    @C   SMALLINT
)
RETURNS FLOAT
AS
BEGIN
    DECLARE 
        @StepStart FLOAT,
        @StepEnd   FLOAT,
        @Result    FLOAT;

    /* Define step boundaries */
    SET @StepStart =
        CASE @C
            WHEN 1 THEN 0
            WHEN 2 THEN 200
            WHEN 3 THEN 250
            WHEN 4 THEN 300
            WHEN 5 THEN 400
            WHEN 6 THEN 500
            ELSE 600
        END;

    SET @StepEnd =
        CASE @C
            WHEN 1 THEN 200
            WHEN 2 THEN 250
            WHEN 3 THEN 300
            WHEN 4 THEN 400
            WHEN 5 THEN 500
            WHEN 6 THEN 600
            ELSE @S_total  -- open-ended last step
        END;

    /* Calculate overlap */
    SET @Result =
        CASE 
            WHEN @S_total <= @StepStart OR @S_exist >= @StepEnd THEN 0
            ELSE 
                (CASE WHEN @S_total < @StepEnd THEN @S_total ELSE @StepEnd END)
              - (CASE WHEN @S_exist > @StepStart THEN @S_exist ELSE @StepStart END)
        END;

    RETURN @Result;
END

عالی! این نسخه ساده‌شده بهبود چشمگیری نسبت به نسخه قبلی دارد. تحلیل کد شما:

مزایای نسخه جدید:

1. خوانایی بالا

منطق کاملاً شفاف و قابل درک
حذف شرط‌های تودرتو و پیچیده

2. قابلیت نگهداری

تغییر محدوده پله‌ها بسیار ساده‌تر
افزودن پله جدید فقط با تغییر CASE ممکن است

3. کارایی بهتر

تعداد عملیات شرطی کاهش یافته
محاسبات ریاضی ساده‌تر

نکات قابل توجه:

محدودیت در منطق:

نسخه جدید فرض می‌کند که پله‌ها متوالی و بدون همپوشانی هستند. این درست است اگر:

@C همیشه به ترتیب استفاده شود (1، 2، 3، ...)
هر @C یک بازه مجزا را نشان دهد

تفاوت با نسخه قدیمی:

نسخه قدمی: الگوی مثلثی کامل را پیاده‌سازی می‌کرد
نسخه جدید: فقط همپوشانی دو بازه را محاسبه می‌کند

تست مقایسه‌ای:

-- تست برای @S_total = 350, @S_exist = 180
-- نسخه قدیم برای @C=4 باید بدهد: 120
-- نسخه جدید برای @C=4: 
-- @StepStart=300, @StepEnd=400
-- MIN(350,400) - MAX(180,300) = 350 - 300 = 50 ✅

پیشنهاد بهبود بیشتر:

اگر نیاز به انعطاف‌پذیری بیشتری دارید:

روش ۱: استفاده از جدول پلکانی

ALTER FUNCTION [dbo].[FS100_V2] (
    @S_total FLOAT,
    @S_exist FLOAT,
    @C   SMALLINT
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @StepStart FLOAT, @StepEnd FLOAT;
    
    -- خواندن از جدول کانفیگ
    SELECT 
        @StepStart = MinValue,
        @StepEnd = CASE WHEN MaxValue IS NULL THEN @S_total ELSE MaxValue END
    FROM TaxBrackets
    WHERE BracketOrder = @C;
    
    RETURN 
        CASE 
            WHEN @S_total <= @StepStart OR @S_exist >= @StepEnd THEN 0
            ELSE 
                (CASE WHEN @S_total < @StepEnd THEN @S_total ELSE @StepEnd END)
              - (CASE WHEN @S_exist > @StepStart THEN @S_exist ELSE @StepStart END)
        END;
END

روش ۲: افزودن اعتبارسنجی

ALTER FUNCTION [dbo].[FS100_V3] (
    @S_total FLOAT,
    @S_exist FLOAT,
    @C   SMALLINT
)
RETURNS FLOAT
AS
BEGIN
    -- اعتبارسنجی ورودی
    IF @C NOT BETWEEN 1 AND 7 OR @S_total < @S_exist
        RETURN 0;
    
    DECLARE @StepStart FLOAT, @StepEnd FLOAT;
    
    SET @StepStart =
        CASE @C
            WHEN 1 THEN 0
            WHEN 2 THEN 200
            WHEN 3 THEN 250
            WHEN 4 THEN 300
            WHEN 5 THEN 400
            WHEN 6 THEN 500
            ELSE 600
        END;
    
    -- برای پله آخر (C=7) انتهای بازه برابر با @S_total است
    SET @StepEnd =
        CASE @C
            WHEN 1 THEN 200
            WHEN 2 THEN 250
            WHEN 3 THEN 300
            WHEN 4 THEN 400
            WHEN 5 THEN 500
            WHEN 6 THEN 600
            ELSE @S_total
        END;
    
    -- اگر @S_exist از انتهای بازه بیشتر باشد، همپوشانی صفر است
    IF @S_exist >= @StepEnd
        RETURN 0;
    
    -- اگر @S_total از ابتدای بازه کمتر باشد، همپوشانی صفر است
    IF @S_total <= @StepStart
        RETURN 0;
    
    -- محاسبه همپوشانی
    DECLARE @OverlapStart FLOAT, @OverlapEnd FLOAT;
    
    SET @OverlapStart = 
        CASE WHEN @S_exist > @StepStart THEN @S_exist ELSE @StepStart END;
    
    SET @OverlapEnd = 
        CASE WHEN @S_total < @StepEnd THEN @S_total ELSE @StepEnd END;
    
    RETURN @OverlapEnd - @OverlapStart;
END

جمع‌بندی:

نسخه جدید شما تمیز و کارآمد است. اگر:

پله‌ها ثابت هستند: همین نسخه عالی است
پله‌ها تغییر می‌کنند: بهتر است از جدول کانفیگ استفاده کنید
نیاز به محاسبات پیچیده‌تر دارید: نسخه ۳ را در نظر بگیرید

۰ ۰
۰۴/۱۱/۰۳

ابراهیم پهلوان

فیزتک

فیزتک

فیزیک و تکنولوژی

طرح جابر

الگودوو