تطبيق دوال النوافذ للتحليلات
غالبا ما تتطلب الاستعلامات التحليلية حسابات تمتد عبر عدة صفوف مع إعادة تفاصيل الصفوف الفردية. تقوم الدوال المجمعة التقليدية بطي الصفوف إلى مجموعات، مما يفقد معلومات على مستوى الصف. تحل دوال النوافذ هذا التحدي من خلال إجراء حسابات عبر مجموعة من الصفوف المرتبطة بالصف الحالي، دون إسقاط مجموعة النتائج.
فهم بناء جملة دالة النافذة
تحسب دوال النافذة القيم عبر "نافذة" من الصفوف المعرفة بواسطة OVER الجملة. على عكس دوال التجميع العادية، لا تقوم دوال النوافذ بتجميع الصفوف في صف إخراج واحد. بدلا من ذلك، تحسب القيم عبر الصفوف ذات الصلة مع الحفاظ على جميع الصفوف الأصلية في النتيجة.
الصياغة العامة لدالة النافذة هي:
function_name(arguments) OVER (
[PARTITION BY partition_expression]
[ORDER BY order_expression [ASC | DESC]]
[ROWS | RANGE frame_specification]
)
تتحكم مكونات OVER الجملة في كيفية تعريف النافذة:
- التقسيم بواسطة: يقسم الصفوف إلى مجموعات (تقسيمات) للحساب
- الترتيب حسب التصنيف: يحدد الترتيب المنطقي للصفوف داخل كل تقسيم
- الصفوف/النطاق: يحدد حدود الإطار بالنسبة للصف الحالي
توضح الاستعلام التالي دالة نافذة بسيطة تحسب مجموعا مستمرا لكميات الطلبات لكل عميل:
SELECT
CustomerID,
SalesOrderID,
OrderDate,
TotalDue,
SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM SalesLT.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
ملاحظة
عندما تحدد ORDER BY في الجملة OVER بدون مواصفات إطار، يكون RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW الإطار الافتراضي مخصصا لوظائف التجميع. وهذا يخلق حسابات تراكمية.
استخدام دوال الترتيب
تقوم دوال الترتيب بتعيين أرقام متتالية للصفوف بناء على موقعها داخل التقسيم. يوفر SQL Server أربع وظائف تصنيف. كل دالة تتعامل مع الروابط بطريقة مختلفة:
ROW_NUMBER() يخصص رقما متسلسلا وحيدا لكل صف، دون تكرار حتى للقيم المتعادلة:
SELECT
ProductID,
Name,
ListPrice,
ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
مجموعة النتائج تبدو كالتالي:
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 2
751 Road-150 Red, 48 3578.27 3
771 Mountain-100 Silver, 38 3399.99 4
يقوم هذا الاستعلام بترتيب جميع المنتجات حسب السعر من الأعلى إلى الأدنى. يحصل كل منتج على رقم فريد بغض النظر عما إذا كانت عدة منتجات تشترك في نفس السعر.
RANK() يخصص نفس الرتبة للقيم المتعادلة، ثم يتخطى الأرقام لأخذ التعادلات في الاعتبار:
SELECT
ProductID,
Name,
ListPrice,
RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
مجموعة النتائج تبدو كالتالي:
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 1
751 Road-150 Red, 48 3578.27 1
771 Mountain-100 Silver, 38 3399.99 4
عندما يكون سعر منتجين متطابقين، يحصل كلاهما على نفس الترتيب. يعكس ترتيب المنتج التالي العدد الإجمالي للمنتجات التي تم تصنيفها أعلى، مما يخلق فجوات في التسلسل.
DENSE_RANK() يمنح نفس الرتبة للقيم المرتبطة لكنه لا يتخطى الأرقام:
SELECT
ProductID,
Name,
ListPrice,
DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
مجموعة النتائج تبدو كالتالي:
ProductID Name ListPrice PriceRank
--------- ------------------------- --------- ---------
749 Road-150 Red, 62 3578.27 1
750 Road-150 Red, 44 3578.27 1
751 Road-150 Red, 48 3578.27 1
771 Mountain-100 Silver, 38 3399.99 2
مثلا RANK()، القيم المرتبطة تشترك في نفس الترتيب. ومع ذلك، DENSE_RANK() يستمر في الرقم التالي المتتالي، لذا يمكنك استخدامه لحساب مستويات الأسعار المختلفة.
NTILE(n) يوزع الصفوف إلى عدد محدد من المجموعات المتساوية تقريبا:
SELECT
ProductID,
Name,
ListPrice,
NTILE(4) OVER (ORDER BY ListPrice DESC) AS PriceQuartile
FROM SalesLT.Product
WHERE ListPrice > 0;
مجموعة النتائج تبدو كالتالي:
ProductID Name ListPrice PriceQuartile
--------- --------------------------- --------- -------------
749 Road-150 Red, 62 3578.27 1
771 Mountain-100 Silver, 38 3399.99 1
722 LL Road Frame - Black, 58 337.22 2
859 Half-Finger Gloves, S 24.49 4
يقسم هذا الاستعلام المنتجات إلى أربع مجموعات بناء على السعر. أعلى المنتجات سعرا في الربع 1، والأقل سعرا في الربع 4. استخدامه NTILE() لتحليل النسبة المئوية أو توزيع العمل بشكل متساو.
الدمج PARTITION BY مع دوال التصنيف يتيح تصنيفات لكل مجموعة:
SELECT
pc.Name AS Category,
p.Name AS Product,
p.ListPrice,
ROW_NUMBER() OVER (
PARTITION BY p.ProductCategoryID
ORDER BY p.ListPrice DESC
) AS CategoryPriceRank
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 0;
مجموعة النتائج تبدو كالتالي:
Category Product ListPrice CategoryPriceRank
--------------- ------------------------- --------- -----------------
Road Bikes Road-150 Red, 62 3578.27 1
Road Bikes Road-150 Red, 44 3578.27 2
Mountain Bikes Mountain-100 Silver, 38 3399.99 1
Mountain Bikes Mountain-100 Black, 38 3374.99 2
يقوم هذا الاستعلام بترتيب المنتجات ضمن كل فئة بشكل منفصل. يبدأ الترتيب من 1 لكل فئة، بحيث يمكنك تحديد أغلى منتج في كل فئة عن طريق تصفية .CategoryPriceRank = 1
نصيحة
استخدم ROW_NUMBER() عندما تحتاج إلى صف واحد بالضبط لكل رتبة (مثل إيجاد أعلى N لكل مجموعة). استخدم RANK() أو DENSE_RANK() عندما تحتاج إلى حفظ معلومات الربطات لأغراض التقارير.
تطبيق دوال نافذة التجميع
يمكن استخدام دوال التجميع القياسية مثل SUM، AVG، COUNT، MINو MAX كدوال نافذة بإضافة الجملة OVER . هذا يسمح لك بحساب المجمعات مع الاحتفاظ بتفاصيل الصفوف الفردية.
يوضح الاستعلام التالي كيفية حساب الإجماليات الجارية والمجمعات التراكمية:
SELECT
SalesOrderID,
OrderDate,
TotalDue,
SUM(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningTotal,
AVG(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningAverage,
COUNT(*) OVER (ORDER BY OrderDate, SalesOrderID) AS OrderNumber
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID;
مجموعة النتائج تبدو كالتالي:
SalesOrderID OrderDate TotalDue RunningTotal RunningAverage OrderNumber
------------ ---------- --------- ------------ -------------- -----------
71774 2008-06-01 972.785 972.785 972.785 1
71776 2008-06-01 87.083 1059.868 529.934 2
71780 2008-06-01 42452.65 43512.518 14504.172 3
71782 2008-06-01 43962.79 87475.308 21868.827 4
مهم
عند استخدام دوال نافذة تجميعية بدون ORDER BY في OVER الجملة، تقوم الدالة بحساب عبر التقسيم بأكمله. الإضافة ORDER BY تخلق حسابا جاريا من بداية القسم إلى الصف الحالي.
تعريف إطارات النوافذ باستخدام ROWS و RANGE
تتيح لك إطارات النوافذ تحديد بالضبط أي الصفوف بالنسبة للصف الحالي يجب تضمينها في الحساب. تعد الجملة ROWS صفوفا مادية، بينما RANGE تجمع الصفوف ذات القيم المتساوية.
يمكن تحديد حدود الإطار باستخدام:
-
UNBOUNDED PRECEDING: من بداية التقسيم -
n PRECEDING:nصفوف قبل الصف الحالي -
CURRENT ROW: الصف الحالي -
n FOLLOWING:nصفوف بعد الصف الحالي -
UNBOUNDED FOLLOWING: إلى نهاية التقسيم
يحسب الاستعلام التالي متوسطا متحركا على الأوامر الثلاثة الأخيرة:
SELECT
SalesOrderID,
OrderDate,
TotalDue,
AVG(TotalDue) OVER (
ORDER BY OrderDate
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS MovingAvg3Orders
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;
مجموعة النتائج تبدو كالتالي:
SalesOrderID OrderDate TotalDue MovingAvg3Orders
------------ ---------- --------- ----------------
71774 2008-06-01 972.785 972.785
71776 2008-06-01 87.083 529.934
71780 2008-06-01 42452.65 14504.172
71782 2008-06-01 43962.79 28834.174
يحسب هذا الاستعلام متوسطا متحركا ثلاثي الترتيبات بتضمين الصف الحالي والصفين قبله. بالنسبة للصف الأول، يتوفر قيمة واحدة فقط، لذا المتوسط يساوي TotalDue. بحلول الصف الثالث، تشمل النافذة جميع الصفوف الثلاثة.
استخدام الدوال التحليلية
تتيح لك الدوال التحليلية الوصول إلى بيانات من صفوف أخرى دون استخدام الروابط الذاتية أو الاستعلامات الفرعية. هذه الدوال مفيدة لتحليل السلاسل الزمنية، واكتشاف الاتجاهات، ومقارنة القيم الحالية بالقيم التاريخية أو المستقبلية. على عكس دوال النافذة المجمعة التي تحسب الملخصات، تقوم الدوال التحليلية باسترجاع قيم محددة من صفوف محددة في النافذة.
LAG() وقيم LEAD() الوصول من الصفوف السابقة أو اللاحقة، مثل هذه:
SELECT
SalesOrderID,
OrderDate,
TotalDue,
LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS PreviousOrderTotal,
LEAD(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS NextOrderTotal,
TotalDue - LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS ChangeFromPrevious
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;
مجموعة النتائج تبدو كالتالي:
SalesOrderID OrderDate TotalDue PreviousOrderTotal NextOrderTotal ChangeFromPrevious
------------ ---------- --------- ------------------ -------------- ------------------
71774 2008-06-01 972.785 0 87.083 972.785
71776 2008-06-01 87.083 972.785 42452.65 -885.702
71780 2008-06-01 42452.65 87.083 43962.79 42365.567
71782 2008-06-01 43962.79 42452.65 0 1510.14
LAG() يسترجع قيمة من صف سابق، بينما LEAD() يسترجع من الصف التالي. يحدد المعامل الثاني عدد الصفوف التي يجب النظر إليها للخلف أو للأمام (الافتراضي هو 1)، والمعلمة الثالثة توفر قيمة افتراضية عندما لا يوجد صف (مثل الصف الأول مع LAG()). استخدم هذه الوظائف لحساب التغيرات الزمنية المختلفة، وتحديد الاتجاهات، أو اكتشاف الشذوذات في البيانات المتسلسلة.
FIRST_VALUE() وتعيد LAST_VALUE() القيم من الصف الأول أو الأخير في الإطار:
SELECT
ProductID,
Name,
ListPrice,
ProductCategoryID,
FIRST_VALUE(Name) OVER (
PARTITION BY ProductCategoryID
ORDER BY ListPrice DESC
) AS MostExpensiveInCategory,
LAST_VALUE(Name) OVER (
PARTITION BY ProductCategoryID
ORDER BY ListPrice DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LeastExpensiveInCategory
FROM SalesLT.Product
WHERE ListPrice > 0;
مجموعة النتائج تبدو كالتالي:
ProductID Name ListPrice ProductCategoryID MostExpensiveInCategory LeastExpensiveInCategory
--------- ------------------------- --------- ----------------- ------------------------ ------------------------
749 Road-150 Red, 62 3578.27 5 Road-150 Red, 62 LL Road Frame - Red, 58
750 Road-150 Red, 44 3578.27 5 Road-150 Red, 62 LL Road Frame - Red, 58
722 LL Road Frame - Red, 58 337.22 5 Road-150 Red, 62 LL Road Frame - Red, 58
771 Mountain-100 Silver, 38 3399.99 6 Mountain-100 Silver, 38 Mountain-500 Black, 52
FIRST_VALUE() يعيد القيمة من الصف الأول في النافذة المرتبة، والتي في هذه الحالة هي أغلى منتج لكل فئة.
LAST_VALUE() يعيد الأقل تكلفة، لكنه يتطلب إطارا صريحا ليشمل جميع الصفوف. تساعدك هذه الدوال على مقارنة كل صف مع قيم المعيار مثل أعلى أو أدنى أو قيمة أساسية في مجموعة.
ملاحظة
LAST_VALUE() يتطلب وجود مواصفة إطار صريحة لتضمين صفوف بعد الصف الحالي. بدونه، يتضمن الإطار الافتراضي فقط صفوفا حتى الصف الحالي، مما يجعل LAST_VALUE() قيمة الصف الحالي يعيد.
PERCENT_RANK() و CUME_DIST() يحسبان الموقع النسبي داخل تقسيم:
SELECT
Name,
ListPrice,
PERCENT_RANK() OVER (ORDER BY ListPrice) AS PercentRank,
CUME_DIST() OVER (ORDER BY ListPrice) AS CumulativeDistribution
FROM SalesLT.Product
WHERE ListPrice > 0
ORDER BY ListPrice;
مجموعة النتائج تبدو كالتالي:
Name ListPrice PercentRank CumulativeDistribution
------------------------- --------- ----------- ----------------------
Patch Kit/8 Patches 2.29 0.0 0.0081
Road Tire Tube 3.99 0.0081 0.0162
Touring Tire Tube 4.99 0.0162 0.0243
Road-150 Red, 62 3578.27 0.9919 1.0
PERCENT_RANK() يعيد قيمة بين 0 و 1 تشير إلى نسبة الصفوف التي لها قيم أقل (0 تعني الأدنى، واحدة تعني الأعلى).
CUME_DIST() يعرض التوزيع التراكمي، مشيرا إلى النسبة المئوية من الصفوف التي قيمها أقل من أو تساوي الصف الحالي. استخدم هذه الوظائف لتحليل النسبة المئوية، أو تحديد القيم الشاذة، أو إنشاء تقارير توزيع.
لمزيد من المعلومات حول دوال النوافذ، انظر دوال النوافذ (Transact-SQL)ودوال الترتيب.