SQL Server / المتوسط / الدرس 10
CTE ودوال النوافذ والنتائج المؤقتة
عبّر عن تحليل متعدد المراحل دون خلط الاستعلام المسمى بالبيانات المخزنة، ورتب الصفوف بحتمية واحسب المجاميع التراكمية بإطار نافذة صريح.
في هذه الصفحة
أهداف الدرس
- شرح نطاق CTE وحدود تخزينها.
- استخدام ROW_NUMBER للاختيار داخل المجموعة.
- اختيار إطار النافذة والتخزين المؤقت بقصد.
استخدم CTE لإظهار التعليل
تعطي CTE اسماً لتعبير استعلام تستخدمه عبارة تالية واحدة. تحسن القراءة عندما تحتاج التصفية والتجميع والترتيب مراحل منطقية منفصلة. ليست عرضاً دائماً ولا ذاكرة صفوف مخزنة مضمونة. قد يسبب تكرار الإشارة إليها عملاً متكرراً؛ فافحص الخطة بدلاً من افتراض أن الاسم يعني تخزين الصفوف.
أنه العبارة السابقة بفاصلة منقوطة؛ وتوضح الصيغة الشائعة ;WITH الحد. يمكن أن تشترك تعريفات عدة في WITH واحدة تفصلها فواصل. تستطيع التعريفات اللاحقة الإشارة إلى السابقة، لكن الإشارات الأمامية المعتادة غير مدعومة. اختر أسماء تصف وحدة المرحلة مثل CustomerTotals بدلاً من أسماء رقمية عامة.
اختر صفاً لكل مجموعة بحتمية
تعطي ROW_NUMBER أرقاماً متسلسلة داخل كل قسم وفق ORDER BY الخاص بالنافذة. يجد المثال أحدث طلب لكل عميل. يكسر OrderId تعادل الأوقات فيختار العميل 1 الطلب 11. يحتاج شرط rn استعلاماً خارجياً أو CTE لأن نتيجة النافذة غير متاحة لـ WHERE في كتلتها نفسها.
تعبّر ROW_NUMBER وRANK وDENSE_RANK عن سياسات تعادل مختلفة. تعطي الأولى أرقاماً متميزة دائماً؛ وتمنح RANK المتعادلين رتبة واحدة وتترك فجوات، بينما لا تترك DENSE_RANK فجوات. يحدد ترتيب النافذة الحساب ويحدد ORDER BY النهائي العرض. استخدم كليهما عندما يحتاج الناتج المعروض نفسه ترتيباً مستقراً.
;WITH Orders AS (
SELECT * FROM (VALUES (10,1,'20260101'),(11,1,'20260101'),
(12,2,'20260102'))v(OrderId,CustomerId,CreatedOn)
), Ranked AS (
SELECT *,ROW_NUMBER() OVER
(PARTITION BY CustomerId ORDER BY CreatedOn DESC,OrderId DESC) AS rn
FROM Orders
)
SELECT CustomerId,OrderId FROM Ranked WHERE rn=1 ORDER BY CustomerId;CustomerId OrderId
1 11
2 12تحمل النصوص المصطنعة صيغة تاريخ ثابتة يتطابق ترتيبها هنا مع الزمن؛ وينبغي استخدام أنواع تاريخ فعلية في المخطط.
حدد إطار المجموع التراكمي
يحافظ تجميع النافذة على صفوف التفاصيل أثناء الحساب عبر صفوف مرتبطة. تختلف SUM(Amount) OVER (...) عن GROUP BY لأنها لا تختزل الناتج. حدد PARTITION BY للمجاميع المستقلة وORDER BY للتسلسل وROWS UNBOUNDED PRECEDING للتراكم صفاً بعد صف من بداية القسم.
يستخدم المثال تسلسلاً فريداً فينتج 10 و30 و35. قد يضم إطار RANGE الافتراضي جميع الصفوف المتعادلة دفعة واحدة عند ترتيب غير فريد، فيسبب قفزات تفاجئ من يتوقع خطوات فردية. يوضح إطار ROWS وفاصل تعادل فريد عقد الحساب. ولا يغني ترتيب النافذة أو الإطار عن ترتيب الناتج النهائي.
SELECT Seq,Amount,
SUM(Amount) OVER (ORDER BY Seq ROWS UNBOUNDED PRECEDING) AS running_total
FROM (VALUES (1,10),(2,20),(3,5))v(Seq,Amount)
ORDER BY Seq;Seq Amount running_total
1 10 10
2 20 30
3 5 35يضيف كل صف مبلغه إلى مبالغ كل الصفوف السابقة في التسلسل المحدد.
اختر متى تخزن الصفوف الوسيطة
يخزن الجدول المؤقت المحلي # صفوفاً وسيطة في tempdb ويدعم الفهارس والإحصاءات. يختلف متغير الجدول عنه في النطاق وسلوك المحسّن. وليس أي منهما تلقائياً كائناً في الذاكرة معفى من الكلفة. استخدم التخزين الوسيط عندما يبرر التكرار أو التحويل المرحلي أو تحسين التقدير كلفة الكتابة والتنظيف.
قدم SQL Server 2019 مع مستوى التوافق 150 الترجمة المؤجلة لمتغيرات الجداول، وقد تحسن تقدير العدد الأولي. لكنها لا تمنحها جميع خصائص الجداول المؤقتة ولا تضمن خططاً جيدة لتنفيذات لاحقة مختلفة الحجم. حدد افتراضات الإصدار والتوافق وقارن عملاً ممثلاً بدلاً من الاعتماد على أقوال قديمة عن تقدير صف ثابت.
قيّد الاستدعاء الذاتي وتحقق من الوحدة
تجمع CTE العودية عضواً ابتدائياً وآخر عودياً لاجتياز بنى كالهيكل الهرمي. صمم شرط انتهاء وفكّر في الدورات غير الصالحة. يمكن أن تحد MAXRECURSION العمل لكنها لا تصلح نموذجاً دورياً. اجعل عمق الهيكل والمسارات المكررة متطلبات صريحة قبل اجتياز كبير.
اكتب وحدة الصف بعد كل مرحلة وتحقق ببيانات صغيرة تتضمن التعادل والمجموعات الغائبة والإشارات المتكررة. قد يكون الاستعلام الأوضح خاطئ المعنى أو أعلى كلفة. افصل تحسين القراءة عن ادعاءات التخزين أو الأداء، واستخدم الخطط بعد تأكيد الصفوف المتوقعة.
الأخطاء الشائعة وإصلاحها
Invalid object name for a CTE.
- لماذا يحدث؟
- حاولت عبارة ثانية استخدام الاسم بعد انتهاء نطاقه.
- كيف تصلحه؟
- أعد تعريف CTE أو خزّن الصفوف المعاد استخدامها في جدول مؤقت.
- كيف تتجنب تكراره؟
- وثّق نطاق العبارة الواحدة.
Latest-row selection changes on ties.
- لماذا يحدث؟
- ترتيب النافذة غير فريد.
- كيف تصلحه؟
- أضف فاصل تعادل فريداً معتمداً تجارياً.
- كيف تتجنب تكراره؟
- أدرج أوقاتاً متعادلة في الأمثلة.
Running totals jump across equal values.
- لماذا يحدث؟
- يجمع الإطار المتعادلين وفق RANGE.
- كيف تصلحه؟
- حدد إطار ROWS والترتيب المقصودين.
- كيف تتجنب تكراره؟
- راجع معنى الإطار صراحة.
اختبر فهمك
1. قارن RANK وDENSE_RANK تنازلياً للدرجات 90 و90 و80.
اعرض الحل المشروح
تعيد RANK القيم 1،1،3 وتعطي DENSE_RANK القيم 1،1،2. أما ROW_NUMBER فتعطي مواضع مختلفة وتحتاج قاعدة لتحديد أي 90 يأتي أولاً.
2. تستخدم أربع عبارات لاحقة نتيجة وسيطة مكلفة. هل تكفي CTE واحدة؟
اعرض الحل المشروح
لا؛ نطاقها عبارة واحدة وليست ذاكرة نتائج. فكر في جدول مؤقت بمفتاح مناسب وتنظيف، ثم قس هل يعوض الاستخدام المتكرر كلفة تخزينه.
أهم ما تعلمته
- تسمي CTE تعبيرات ولا تضمن تخزينها.
- تحافظ دوال النوافذ على وحدة التفاصيل.
- قواعد التعادل والأطر جزء من الصحة.