مقدمه
برای حل بعضی از مسائل نیاز داریم که
مقادیر سطرهایی که نزدیکترین مقدار را به کلید سطر جاری دارند را در سطر فعلی
انتقال دهیم.
در حالت معمول برای تولید ای نتیجه
نیاز به self-join هستیم. البته توابع Window
ی LEAD و LAG که استاندارد نیز
هستند نیاز ما را به self-join بر طرف می کند. هنوز
SQL Server از این دو تابع پیشتیبانی نمی کند. ولی در برخی دیگر از
RDBMS ها این دو تابع پیاده سازی شده اند.
اگر ما جدولی داشته باشیم که دارای یک
ستونی به نام nbr بوده و اعداد متوالی نه دقیقا پشت سر هم
داشته باشد و ستونی به نام val که مقداری در آن ذخیره می
شود. آنگاه هدف ما آوردن مقادیر سطرهایی که نزدیک ترین nbr
را به سطر فعلی دارند به سطر جاری است.
خب، اگر مقادیر دقیقا بطور متوالی و
پشت سر هم باشند و هیچگونه فضای هرزی بین ارقام وجود نداشته باشد کافیست که جدول با
خودش دو بار join شود. ولی در این مورد اینگونه نیست.
مساله
در کلاس درس پایگاه داده های دانشگاهی
استاد مساله SQL فوق را مطرح می کند و ذکر می کند جوابهایی
پذیرفته خواهند بود که 1.استاندارد بوده 2.راه حل تنها از یک SELECT
تشکلیل شده باشد 3.استفاده از توابع Window ممنوع است
4.تنها عبارت جدولی قابل بکارگیری derived table میباشد.
جدول داریم شامل دو ستون با نام های
nbr و val که اولی برای نگهداری
مقداری متوالی و دیگری مقدار مرتبط به سطر بکار می روند.
CREATE
TABLE Nums
(nbr
INTEGER NOT
NULL PRIMARY
KEY,
val INTEGER NOT
NULL);
INSERT
INTO Nums
(nbr, val)
VALUES
(1,
0),
(5, 7),
(9, 4
نتیجه مورد نظر همانیست که در مقدمه
مطرح شد.
راه حل ها
جواب اول:
توسط subquery
های وابسته در ماده select مقادیر بعدی و قبلی را بدست می
آوریم یعنی:
SELECT (
SELECT MAX(nbr)
FROM Nums
WHERE nbr < A.nbr
) AS pre_nbr,
(
SELECT val
FROM Nums
WHERE nbr = (
SELECT MAX(nbr)
FROM Nums
WHERE nbr < A.nbr
)
),
nbr,
val,
(
SELECT MIN(nbr)
FROM Nums
WHERE nbr > A.nbr
) AS nxt_nbr,
(
SELECT val
FROM Nums
WHERE nbr = (
SELECT MIN(nbr)
FROM Nums
WHERE nbr > A.nbr
)
) AS nxt_val
FROM Nums AS A;
این روش
مشکل عملکردی جدی دارد به دلیل اینکه subquery های زیادی در
ماده select شرکت کرده اند.
جواب دوم:
برای اینکه
از تکرار subquery هایی که مقادیر pre_nbr
و nxt_nbr را تولید می کنند جلوگیری کنیم ابتدا در یک جدول
مشتق شده این دو عبارت را بدست آورده سپس در از آن در subquery
هایی که توسط انها val را محاسبه می کنیم استفاده می کنیم
به این شکل:
SELECT pre_nbr,
(
SELECT val
FROM Nums
WHERE nbr = pre_nbr
),
nbr,
val,
nxt_nbr,
(
SELECT val
FROM Nums
WHERE nbr = nxt_nbr
) AS nxt_val
FROM (
SELECT (
SELECT MAX(nbr)
FROM Nums
WHERE nbr < A.nbr
) AS pre_nbr,
nbr,
val,
(
SELECT MIN(nbr)
FROM Nums
WHERE nbr > A.nbr
) AS nxt_nbr
FROM Nums AS A
) AS D;
این روش هم نتوسنته هنوز از بکارگیری
subquery در ماده select خودداری
کند.
جواب سوم:
برای رفع کردن نیاز به subquery هایی که مقادیر ستون
val را بدست می آورند از JOIN به شکل زیر
استفاده کرد:
SELECT pre_nbr,
A.val AS pre_val,
D.nbr,
D.val,
nxt_nbr,
B.val AS nxt_val
FROM (
SELECT (
SELECT MAX(nbr)
FROM Nums
WHERE nbr < A.nbr
) AS pre_nbr,
nbr,
val,
(
SELECT MIN(nbr)
FROM Nums
WHERE nbr > A.nbr
) AS nxt_nbr
FROM Nums AS A
) AS D
LEFT JOIN Nums AS A
ON D.pre_nbr = A.nbr
LEFT JOIN Nums AS B
ON D.nxt_nbr = B.nbr;
جواب چهارم:
یک روش آن است که جدول را با خودش به
شرط اینکه nbr جدول فعلی برابر باشد با nbr-1
جدول بعدی برای دستری به سطرهای بعد و... .
از آنجایی که هدف خلق راه حلی است که portable بوده و حق
استفاده از CTE، View و
Ranking را نداریم ناچارا کوئری به این شکل در می آید:
SELECT A.nbr, A.val,
B.nbr, B.val,
C.nbr, C.val
FROM (
SELECT *,
(SELECT COUNT(*)
FROM Nums
WHERE nbr <= A.nbr) AS rnk
FROM Nums AS A
) AS A
RIGHT JOIN
(
SELECT *,
(SELECT COUNT(*)
FROM Nums
WHERE nbr <= A.nbr) AS rnk
FROM Nums AS A
) AS B ON B.rnk = A.rnk + 1
LEFT JOIN
(
SELECT *,
(SELECT COUNT(*)
FROM Nums
WHERE nbr <= A.nbr) AS rnk
FROM Nums AS A
) AS C ON B.rnk = C.rnk - 1;
جواب پنجم:
در نهایت بشکل هوشمندانه ای مساله حل شد:
SELECT T2.*, T1.*, T3.*
FROM Nums AS T1
LEFT JOIN Nums AS T2
ON T2.nbr = (SELECT MAX(nbr)
FROM Nums
WHERE nbr < T1.nbr)
LEFT JOIN Nums AS T3
ON T3.nbr = (SELECT MIN(nbr)
FROM Nums
WHERE nbr > T1.nbr);