مثالهای عملی
مثال 1: شمارش اعضای هر واحد
COUNT با PARTITION BY تعداد کارکنان هر واحد را کنار جزئیات هر کارمند نمایش میدهد.
SELECT EmployeeID, DepartmentID,
COUNT(*) OVER (PARTITION BY DepartmentID) AS DepartmentCount
FROM (VALUES (1,10),(2,10),(3,20)) E(EmployeeID,DepartmentID);
| EmployeeID | DepartmentID | DepartmentCount |
|---|
| 1 | 10 | 2 |
| 2 | 10 | 2 |
| 3 | 20 | 1 |
این خروجی برای نمایش جزئیات و شاخص گروه در یک Grid مفید است و به Join با زیرپرسوجوی تجمیعی نیاز ندارد.
مثال 2: مجموع خرید مشتری
هر سفارش حفظ میشود و CustomerTotal برای مشتری مربوط تکرار میگردد.
SELECT CustomerID, OrderID, Amount,
SUM(Amount) OVER (PARTITION BY CustomerID) AS CustomerTotal
FROM (VALUES (1,101,40),(1,102,60),(2,103,25)) O(CustomerID,OrderID,Amount);
| CustomerID | OrderID | Amount | CustomerTotal |
|---|
| 1 | 101 | 40 | 100 |
| 1 | 102 | 60 | 100 |
| 2 | 103 | 25 | 25 |
برای محاسبه درصد، Amount را بر NULLIF(CustomerTotal,0) تقسیم کنید تا خطای تقسیم بر صفر رخ ندهد.
مثال 3: سهم هر ردیف از پارتیشن
مجموع پارتیشن در مخرج استفاده میشود تا درصد سهم هر فروش محاسبه شود.
SELECT RegionID, Amount,
CAST(100.0 * Amount / NULLIF(SUM(Amount) OVER (PARTITION BY RegionID),0) AS decimal(6,2)) AS SharePct
FROM (VALUES (1,30),(1,70),(2,50)) S(RegionID,Amount);
| RegionID | Amount | SharePct |
|---|
| 1 | 30 | 30.00 |
| 1 | 70 | 70.00 |
| 2 | 50 | 100.00 |
استفاده از 100.0 محاسبه را اعشاری میکند. نوع decimal را متناسب با دقت مالی سامانه انتخاب کنید.
مثال 4: بالاترین فروش هر گروه
رتبه داخل هر گروه از نو شروع میشود و CTE رتبه اول هر منطقه را برمیگرداند.
WITH R AS (
SELECT RegionID, SellerID, Amount,
ROW_NUMBER() OVER (PARTITION BY RegionID ORDER BY Amount DESC, SellerID) AS rn
FROM (VALUES (1,1,90),(1,2,80),(2,3,70)) S(RegionID,SellerID,Amount)
)
SELECT RegionID,SellerID,Amount FROM R WHERE rn=1;
| RegionID | SellerID | Amount |
|---|
| 1 | 1 | 90 |
| 2 | 3 | 70 |
برای نگهداشتن تمام نفرات همرتبه میتوان بهجای ROW_NUMBER از RANK استفاده کرد و سیاست تساوی را مستند نمود.
مثال 5: پارتیشن چندستونی
ترکیب سال و منطقه یک پارتیشن مستقل برای هر دوره و محل میسازد.
SELECT SalesYear, RegionID, Amount,
AVG(1.0*Amount) OVER (PARTITION BY SalesYear,RegionID) AS AvgAmount
FROM (VALUES (2025,1,10),(2025,1,20),(2026,1,40)) S(SalesYear,RegionID,Amount);
| SalesYear | RegionID | Amount | AvgAmount |
|---|
| 2025 | 1 | 10 | 15.0 |
| 2025 | 1 | 20 | 15.0 |
| 2026 | 1 | 40 | 40.0 |
افزودن هر ستون Cardinality پارتیشنها را افزایش میدهد. فقط ابعادی را وارد کنید که مرز واقعی کسبوکار هستند.
مثال 6: پارتیشن مقادیر NULL
تمام RegionIDهای NULL یک پارتیشن مشترک میسازند و جدا از مقادیر شناختهشده محاسبه میشوند.
SELECT ItemID,RegionID,
COUNT(*) OVER (PARTITION BY RegionID) AS GroupCount
FROM (VALUES (1,NULL),(2,NULL),(3,1)) D(ItemID,RegionID);
| ItemID | RegionID | GroupCount |
|---|
| 1 | NULL | 2 |
| 2 | NULL | 2 |
| 3 | 1 | 1 |
اگر NULL معنای چند وضعیت متفاوت دارد، پیش از پارتیشنبندی باید با داده مرجع یا CASE آنها را به دستههای معتبر تبدیل کنید.
مثال 7: پارتیشن بر اساس سال تاریخ
YEAR تاریخ سفارش مرز سالانه ایجاد میکند؛ برای گزارش کوچک خواناست اما ستون محاسبهشده میتواند ایندکسپذیری را بهتر کند.
SELECT OrderID,OrderDate,Amount,
SUM(Amount) OVER (PARTITION BY YEAR(OrderDate)) AS YearTotal
FROM (VALUES (1,CONVERT(date,'2025-01-01'),10),(2,'2025-03-01',20),(3,'2026-01-01',30)) O(OrderID,OrderDate,Amount);
| OrderID | OrderDate | Amount | YearTotal |
|---|
| 1 | 2025-01-01 | 10 | 30 |
| 2 | 2025-03-01 | 20 | 30 |
| 3 | 2026-01-01 | 30 | 30 |
برای جداول بزرگ، ذخیره سال بهصورت ستون محاسبهشده پایدار و ایندکسشده ممکن است Sort و محاسبه تکراری را کاهش دهد.
مثال 8: گزارش جزئیات و میانگین واحد
حقوق هر کارمند همراه میانگین واحد در یک Dataset گزارش میشود.
SELECT DepartmentID,EmployeeID,Salary,
AVG(CAST(Salary AS decimal(12,2))) OVER (PARTITION BY DepartmentID) AS DeptAvg
FROM (VALUES (10,1,80),(10,2,100),(20,3,120)) E(DepartmentID,EmployeeID,Salary);
| DepartmentID | EmployeeID | Salary | DeptAvg |
|---|
| 10 | 1 | 80 | 90.00 |
| 10 | 2 | 100 | 90.00 |
| 20 | 3 | 120 | 120.00 |
این الگو امکان Highlight کردن کارکنان بالاتر از میانگین را در لایه گزارش فراهم میکند و جزئیات را از بین نمیبرد.
مثال 9: مقایسه با GROUP BY
دو Query هدف متفاوت دارند: GROUP BY یک ردیف برای هر گروه و PARTITION BY جزئیات بههمراه مجموع را میدهد.
SELECT DepartmentID,EmployeeID,Salary,
SUM(Salary) OVER (PARTITION BY DepartmentID) AS DeptTotal
FROM (VALUES (10,1,80),(10,2,100)) E(DepartmentID,EmployeeID,Salary);
-- خلاصه مستقل:
SELECT DepartmentID,SUM(Salary) AS DeptTotal
FROM (VALUES (10,1,80),(10,2,100)) E(DepartmentID,EmployeeID,Salary)
GROUP BY DepartmentID;
| نوع خروجی | تعداد ردیف |
|---|
| PARTITION BY | 2 |
| GROUP BY | 1 |
انتخاب باید بر اساس شکل Dataset باشد. استفاده از پنجره برای گزارشی که فقط خلاصه میخواهد ممکن است داده اضافی تولید کند.
مثال 10: ایندکس POC برای پارتیشن
ایندکس با DepartmentID آغاز و سپس ترتیب Salary و EmployeeID را پوشش میدهد.
CREATE TABLE #E(DepartmentID int,EmployeeID int,Salary int);
INSERT #E VALUES(10,1,80),(10,2,100),(20,3,90);
CREATE INDEX IX_E_POC ON #E(DepartmentID,Salary DESC,EmployeeID);
SELECT EmployeeID,ROW_NUMBER() OVER(PARTITION BY DepartmentID ORDER BY Salary DESC,EmployeeID) AS rn FROM #E;
DROP TABLE #E;
کلیدهای فیلتر ثابت میتوانند پیش از POC قرار گیرند. تصمیم نهایی را با Query Store و طرح واقعی پرتکرارترین بار کاری بگیرید.