محتویات سایت
        برچسب های محبوب 








 
   اتصال سطر جاری با چندین سطر بعدی و قبلی
  اتصال سطر جاری با چند سطر بعدی و قبلی بر اساس مقداری منحصر بفرد به ازای هر گروه در جدول توسط سه تکنیک متفاوت
   SQL Server
   ۱۶۳۳۶
   این مقاله حاوی فایل ضمیمه نمی باشد
   محمد سلیم آبادی
   ۱۳۹۰/۲/۲۳
نسخه قابل چاپ نسخه قابل چاپ

مقدمه

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

روشهای پیشین برای زمانی مناسب بوده اند که بخواهیم سطرجاری را با سطرهای بلافاصله بعدی و بلافاصله قبلی (بر اساس مقدار کلید جدول) جدول اتصال دهیم. ولی چه میشود زمانی که ما بخواهیم سطرجاری را با چندین سطر بعدی و چندین سطر قبلی (بر اساس مقداری منحصر بفرد به ازای هر گروه) اتصال بدهیم؟

مساله

جدولی داریم که سود کلی روزانه فروشندگان را ذخیره و نگهداری می کند. ما نیاز داریم که مبلغ فروش چند روز قبل و چند روز بعد هر فروشنده را در سطر فعلی به نمایش در بیاوریم.

ساختار جدول:
طبق توضیح خط قبل، جدول شامل سه ستون است: 1. شماره فروشنده 2. شماره روز سال جاری (یعنی آن روز چندمین روز سال است) 3. سود کلی آن روز
به ترتیب سه ستون با نام های
user_nbr و day_nbr و value مشخص شده اند.
رای سادگی هر چه بیشتر من به جای تاریخ از یک عدد که نمایان روز سال جاری است استفاده کردم که بتوانیم راحت تر تکنیک ها را آموزش دهیم.
از آنجایی که یک فروشنده در یک روز بیشتر از یک سود روزانه و خالص نخواهد داشت و نباید داشته باشد من ترکیب دو ستون
user_nbr و day_nbr را کلید اصلی جدول در نظر گرفته ام.

نکته:
ممکن است یک فروشنده در تمام روزها بطور مداوم و مستمر مشغول به کار نباشد در نتیجه سطری نیز در جدول برای او در آن روز در نظر گرفته نخواهد شد. ولی اگر روزی مشغول به کار بوده و سودی حاصل نکرده باشد مقدار
value را با صفر نشان خواهیم داد. و سود هیچگاه در این سناریو منفی نخواهد شد. و توسط یک قید آن را تظمین می کنیم.

پس با توضیحات فوق DDL جدول به این شکل در می آید:

CREATE TABLE Sales
(user_nbr INTEGER NOT NULL,
day_nbr INTEGER NOT NULL
CHECK(day_nbr BETWEEN 1 AND 365),
Value INTEGER NOT NULL
CHECK(value >= 0),
PRIMARY KEY (user_nbr, day_nbr));
 

برای نمونه سطرهای زیر را برای سه فروشنده درج می کنیم:
 

INSERT INTO Sales VALUES 
(1, 12, 60),
(1, 15, 70),
(1, 16, 80),
(1, 17, 83),
(1, 19, 86),
(1, 20, 90),
(1, 21, 90),

(2, 12, 80),
(2, 13, 70),
(2, 14, 70),
(2, 17, 50),
(2, 18, 60),
(2, 19, 70),
(2, 20, 80),
(2, 21, 0),

(3, 12, 70),
(3, 13, 60),
(3, 14, 50),
(3, 15, 60),
(3, 16, 60),
(3, 17, 65),
(3, 21, 65);
 

نتیجه ی مورد نیاز و مطلوبمان آوردن فروش (value) سه روز قبل و سه روز بعد به ازای هر مشتری است. فقط باید توجه داشته باشید که اگر بین روزها فضای خالی وجود داشته باشد وظیفه شما حذف این فضاهای خالی و متوالی کردن روزهاست.

پس با این توضیحات نتیجه ی ما این است:

 

user_nbr    value       value       value       value       value       value       value
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           NULL        NULL        NULL        60          70          80          83
1           NULL        NULL        60          70          80          83          86
1           NULL        60          70          80          83          86          90
1           60          70          80          83          86          90          90
1           70          80          83          86          90          90          NULL
1           80          83          86          90          90          NULL        NULL
1           83          86          90          90          NULL        NULL        NULL
2           NULL        NULL        NULL        80          70          70          50
2           NULL        NULL        80          70          70          50          60
2           NULL        80          70          70          50          60          70
2           80          70          70          50          60          70          80
2           70          70          50          60          70          80          0
2           70          50          60          70          80          0           NULL
2           50          60          70          80          0           NULL        NULL
2           60          70          80          0           NULL        NULL        NULL
3           NULL        NULL        NULL        70          60          50          60
3           NULL        NULL        70          60          50          60          60
3           NULL        70          60          50          60          60          65
3           70          60          50          60          60          65          65
3           60          50          60          60          65          65          NULL
3           50          60          60          65          65          NULL        NULL
3           60          60          65          65          NULL        NULL        NULL
 


 

 

راه حلها:

راه حل غیر استاندارد:
ویزگی ها: 1.
portability ضعیف. 2.غیر استاندارد بودن 3.نمایش مناسب از APPLY و ORDER BY همراه با TOP

SELECT S.user_nbr, D3.value, D2.value, D1.value, S.value, D4.value, D5.value, D6.value
  FROM Sales S
    OUTER APPLY (SELECT TOP 1 value, day_nbr
                 FROM Sales
                 WHERE user_nbr = S.user_nbr
                 AND day_nbr < S.day_nbr
                 ORDER BY day_nbr DESC) D1
    OUTER APPLY (SELECT TOP 1 value, day_nbr
                 FROM Sales
                 WHERE user_nbr = S.user_nbr
                 AND day_nbr < D1.day_nbr    
                 ORDER BY day_nbr DESC) D2             
    OUTER APPLY (SELECT TOP 1 value
                 FROM Sales
                 WHERE user_nbr = S.user_nbr
                 AND day_nbr < D2.day_nbr    
                 ORDER BY day_nbr DESC) D3     
    OUTER APPLY (SELECT TOP 1 value, day_nbr
                 FROM Sales
                 WHERE user_nbr = S.user_nbr
                 AND day_nbr > S.day_nbr
                 ORDER BY day_nbr ASC) D4       
    OUTER APPLY (SELECT TOP 1 value, day_nbr
                 FROM Sales
                 WHERE user_nbr = S.user_nbr
                 AND day_nbr > D4.day_nbr
                 ORDER BY day_nbr ASC) D5   
    OUTER APPLY (SELECT TOP 1 value
                 FROM Sales
                 WHERE user_nbr = S.user_nbr
                 AND day_nbr > D5.day_nbr
                 ORDER BY day_nbr ASC) D6;
 

راه حل استاندارد 1:
ویزگی ها: 1.
portability متوسط 2. استاندارد بودن 3.خوانایی خوب 4.هزینه توسعه ی پایین 5. کوتاهی کد

 

WITH Cte AS
(
    SELECT ROW_NUMBER()
           OVER(PARTITION BY user_nbr
                ORDER BY day_nbr) AS rnk, *
      FROM Sales
)
SELECT C.user_nbr, C3.value, C2.value, C1.value, C.value, C4.value, C5.value, C6.value
  FROM Cte C
  -- fist match rows with pres
    LEFT JOIN Cte C1
      ON C.user_nbr = C1.user_nbr
      AND C.rnk = C1.rnk + 1
    LEFT JOIN Cte C2
      ON C1.user_nbr = C2.user_nbr
      AND C1.rnk = C2.rnk + 1
    LEFT JOIN Cte C3
      ON C2.user_nbr = C3.user_nbr
      AND C2.rnk = C3.rnk + 1
  -- last match rows with nexts
    LEFT JOIN Cte C4
      ON C.user_nbr = C4.user_nbr
      AND C.rnk = C4.rnk - 1
    LEFT JOIN Cte C5
      ON C4.user_nbr = C5.user_nbr
      AND C4.rnk = C5.rnk - 1
    LEFT JOIN Cte C6
      ON C5.user_nbr = C6.user_nbr
      AND C5.rnk = C6.rnk - 1;
      
    
 

راه حل استاندارد 2:
ویزگیها: 1.
portability بسیار بالا 2.Standard بودن 3.طولانی بودن کد 4.Readability قابل قبول

SELECT S.user_nbr, S3.value, S2.value, S1.value, S.value, S4.value, S5.value, S6.value
  FROM Sales S
    LEFT JOIN Sales S1
      ON S1.user_nbr = S.user_nbr
      AND S1.day_nbr = (SELECT MAX(day_nbr) 
                          FROM Sales
                         WHERE user_nbr = S.user_nbr
                           AND day_nbr < S.day_nbr)
    LEFT JOIN Sales S2
      ON S2.user_nbr = S.user_nbr
      AND S2.day_nbr = (SELECT MAX(day_nbr) 
                          FROM Sales
                         WHERE user_nbr = S.user_nbr
                           AND day_nbr < S1.day_nbr)
    LEFT JOIN Sales S3
      ON S3.user_nbr = S.user_nbr
      AND S3.day_nbr = (SELECT MAX(day_nbr) 
                          FROM Sales
                         WHERE user_nbr = S.user_nbr
                           AND day_nbr < S2.day_nbr)   
    LEFT JOIN Sales S4
      ON S4.user_nbr = S.user_nbr
      AND S4.day_nbr = (SELECT MIN(day_nbr)
                          FROM Sales
                         WHERE user_nbr = S.user_nbr
                           AND day_nbr > S.day_nbr)                 
    LEFT JOIN Sales S5
      ON S5.user_nbr = S.user_nbr
      AND S5.day_nbr = (SELECT MIN(day_nbr)
                          FROM Sales
                         WHERE user_nbr = S.user_nbr
                           AND day_nbr > S4.day_nbr)
    LEFT JOIN Sales S6
      ON S6.user_nbr = S.user_nbr
      AND S6.day_nbr = (SELECT MIN(day_nbr)
                          FROM Sales
                         WHERE user_nbr = S.user_nbr
                           AND day_nbr > S5.day_nbr);
 

 

برچسب های مرتبط