|
مقدمه
در مقاله ی پیشین این سری مقالات به توضیح روش های
استانداردی پرداختم که 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);
|
|
|
|
|
|