ماده ی جدید OVER که به SQL Server 2005 اضافه شد یک قابلیت و امکان فوق العاده ای به T-SQL بخشید. این ماده همراه با توابع تجمعی و توابع Ranknig مورد استفاده قرار می گیرد. که در مورد توابع Ranking در این مقاله بطور نسبتا مفصلی پرداخته شد. اکنون زمان آن فرا رسیده که به بحث Aggregate Window Function به پردازیم. (OVER() clause جزء استاندارد زبان SQL بشمار می آید)
با کمک این ماده می توانیم توابع تجمعی را برای تمام سطرها با قابلیت Partition بندی اعمال کنیم. اجازه بدید با یک مثال مساله را بطور کاملا شفاف مطرح کنم.
فرض کنید جدولی داریم که تعداد پست های کاربر در ماه های مختلف سال را در یک forum را نگهداری می کند. DDL و Schema ی جدول به این شکل می باشد:
CREATE TABLE Statistic
(user_identifier VARCHAR(50) NOT NULL,
post_month INTEGER NOT NULL
CHECK (post_month BETWEEN 1 AND 12),
post_cnt INTEGER NOT NULL
DEFAULT 0
CHECK (post_cnt >= 0),
PRIMARY KEY (user_identifier, post_month));
یکسری داده های آزمایشی نیز در جدول انتشار می دهیم:
INSERT INTO Statistic
VALUES ('user_1', 1, 50), ('user_1', 2, 200), ('user_1', 3, 0), ('user_1', 4, 0), ('user_1', 5, 95),
('user_2', 1, 350), ('user_2', 2, 150), ('user_2', 3, 10), ('user_2', 4, 50), ('user_2', 5, 0),
('user_3', 1, 25), ('user_3', 2, 250), ('user_3', 3, 85), ('user_3', 4, 40), ('user_3', 5, 300),
('user_4', 1, 80), ('user_4', 2, 100), ('user_4', 3, 90), ('user_4', 4, 110), ('user_4', 5, 150);
چندی قبل یک Feedback ای از دوست عزیزم آقای مرتضی صحراگرد در مورد یکی از مقالاتم دریافت کردم که به result set هایی که در text قرار گرفته بودند و تاثیر آن در درک بهتر مطالب، اشاره کرده بودند. به همین علت سعی می کنم داده ها را در سطر و ستون نمایش دهم تا بسادگی و بطور شفاف مساله درک شود.
user_identity post_month post_cnt
------------- ----------- -----------
user_1 1 50
user_1 2 200
user_1 3 0
user_1 4 0
user_1 5 95
user_2 1 350
user_2 2 150
user_2 3 10
user_2 4 50
user_2 5 0
user_3 1 25
user_3 2 250
user_3 3 85
user_3 4 40
user_3 5 300
user_4 1 80
user_4 2 100
user_4 3 90
user_4 4 110
user_4 5 150
(20 row(s) affected)
حالا نتیجه ی مورد نظر ما این است که نسبت تعداد پست های هر کاربر در هر ماه به تعداد کل پست ها در آن ماه به درصد، همچنین نسبت تعداد پست جاری کاربر به کل پست ها به درصد و نیز اختلاف تعداد پست های ماه جاری کاربر از میانگین تعداد پست های ارسال شده در همان ماه را محاسبه کرده و نمایش دهیم. بطور مثال تعداد پست های کاربر user_1 در ماه اول نسبت به کل پست ها در آن ماه که توسط کاربران دیگر صورت گرفته است کمتر از 10 درصد است. یعنی 50 تقسیم بر 50+350+25+80.
پس نتیجه ی مطلوب و مورد نظر ما به این شکل است:
user_identity post_month post_cnt pct_month pct_total diff
--------------- ----------- ----------- --------- --------- -------
user_2 1 350 69.31 16.39 224
user_4 1 80 15.84 3.75 -46
user_1 1 50 9.90 2.34 -76
user_3 1 25 4.95 1.17 -101
user_3 2 250 35.71 11.71 75
user_1 2 200 28.57 9.37 25
user_2 2 150 21.43 7.03 -25
user_4 2 100 14.29 4.68 -75
user_4 3 90 48.65 4.22 44
user_3 3 85 45.95 3.98 39
user_2 3 10 5.41 0.47 -36
user_1 3 0 0.00 0.00 -46
user_4 4 110 55.00 5.15 60
user_2 4 50 25.00 2.34 0
user_3 4 40 20.00 1.87 -10
user_1 4 0 0.00 0.00 -50
user_3 5 300 55.05 14.05 164
user_4 5 150 27.52 7.03 14
user_1 5 95 17.43 4.45 -41
user_2 5 0 0.00 0.00 -136
(20 row(s) affected)
برای خلق این چنین Query هایی در SQL Server 2000 دو روش وجود دارد. یکی Correlated Scalar Subquery و دیگری ترکیب Derived Table، GROUP BY و JOIN است. که روش دوم به مراتب کاربردی تر و موثر تر از روش اول است.
Correlated Scalar Subquery
SELECT S.*,
CAST((post_cnt + 0.0) / (SELECT SUM(post_cnt)
FROM Statistic
WHERE post_month = S.post_month) * 100 AS NUMERIC(5, 2)) AS pct_month,
CAST((post_cnt + 0.0) / (SELECT SUM(post_cnt)
FROM Statistic) * 100 AS NUMERIC(5, 2)) AS pct_total,
CAST(post_cnt - (SELECT AVG(post_cnt)
FROM Statistic
WHERE post_month = S.post_month) AS NUMERIC(4, 0)) AS diff
FROM Statistic S
ORDER BY post_month ASC,
post_cnt DESC,
user_identifier ASC ;
Derived Table + GROUP BY + JOIN
SELECT S.*,
CAST((post_cnt + 0.0) / D.sum_post * 100 AS NUMERIC(5, 2)) AS pct_month,
CAST((post_cnt + 0.0) / T.cnt * 100 AS NUMERIC(5, 2)) AS pct_total,
post_cnt - avg_post AS diff
FROM (SELECT SUM(post_cnt), AVG(post_cnt), post_month
FROM Statistic
GROUP BY post_month) AS D(sum_post, avg_post, post_month)
INNER JOIN
Statistic S
ON D.post_month = S.post_month
CROSS JOIN
(SELECT SUM(post_cnt) AS cnt FROM Statistic) AS T
ORDER BY post_month ASC,
post_cnt DESC,
user_identifier ASC ;
OVER Clause
SELECT S.*,
CAST((post_cnt + 0.0) / SUM(post_cnt) OVER(PARTITION BY post_month) * 100 AS NUMERIC(5, 2)) AS pct_month,
CAST((post_cnt + 0.0) / SUM(post_cnt) OVER() * 100 AS NUMERIC(5, 2)) AS pct_total,
post_cnt - AVG(post_cnt) OVER(PARTITION BY post_month) AS Diff
FROM Statistic S
ORDER BY post_month ASC,
post_cnt DESC,
user_identifier ASC ;
همانطوری که از Query فوق پیداست، استفاده از این تکنیک کد را به شکل قابل توجهی کوتاه تر، قابل توسعه تر و موثر تر کرده است.
موفق باشید!