مقدمة عن وظائف نافذة SQL

الميزة القوية للغاية التي تحب أن تكرهها (ولكن عليك أن تعرفها)

توفر وظائف نافذة SQL بعض الميزات القوية والمفيدة للغاية. لكن بالنسبة للكثيرين ، نظرًا لأنهم غريبون جدًا عن لغة SQL القياسية ، فمن الصعب تعلمهم وفهمهم ، ولديهم بناء جملة غريب - وغالبًا ما يتم تجنبهم.

يمكن شرح وظائف النافذة ببساطة على أنها وظائف حسابية مشابهة للتجميع ، ولكن عندما يتحد التجميع العادي عبر جملة GROUP BY ثم يخفي الصفوف الفردية التي يتم تجميعها ، فإن وظائف النافذة لها حق الوصول إلى الصفوف الفردية ويمكنها إضافة بعض السمات من تلك الصفوف إلى مجموعة النتائج.

في هذا البرنامج التعليمي لوظائف نافذة SQL ، سأبدأ في استخدام وظائف النافذة ، وأشرح الفوائد ومتى تستخدمها ، وأعطيك أمثلة حقيقية للمساعدة في المفاهيم.

نافذة على بياناتك

من أكثر الميزات استخدامًا وأهمية في SQL هي القدرة على تجميع صفوف البيانات أو تجميعها بطرق معينة. ومع ذلك ، في بعض الحالات ، يمكن أن يصبح التجميع شديد التعقيد ، اعتمادًا على ما هو مطلوب.

هل سبق لك أن أردت تكرار نتائج استعلامك للحصول على ترتيب أو قائمة X أعلى أو ما شابه ذلك؟ هل كان لديك أي مشاريع تحليلية حيث أردت إعداد بياناتك بشكل صحيح لأداة التصور ، ولكن وجدت أنها شبه مستحيلة أو معقدة للغاية بحيث لا تستحق ذلك؟

وظائف النوافذ يمكن أن تجعل الأمور أسهل. بعد الحصول على نتيجة الاستعلام الخاص بك - على سبيل المثال ، بعد WHERE وأي تجميع قياسي ، ستعمل وظائف النافذة على الصفوف المتبقية ( نافذة البيانات) وتحصل على ما تريد.

تتضمن بعض وظائف النافذة التي سنلقي نظرة عليها ما يلي:

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

سهل للغاية

جملة OVER هي التي تحدد وظيفة النافذة ويجب دائمًا تضمينها في العبارة. الافتراضي في عبارة OVER هو مجموعة الصفوف بأكملها. كمثال ، دعنا نلقي نظرة على جدول الموظف في قاعدة بيانات الشركة ونعرض العدد الإجمالي للموظفين في كل صف ، جنبًا إلى جنب مع معلومات كل موظف ، بما في ذلك وقت بدء العمل مع الشركة.

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

ما سبق ، مثله مثل العديد من وظائف النوافذ ، يمكن أيضًا كتابته بطريقة غير مألوفة أكثر - والتي ، في هذا المثال البسيط ، ليست سيئة للغاية:

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

ولكن الآن ، لنفترض أننا نرغب في إظهار عدد الموظفين الذين بدأوا في نفس الشهر الذي بدأ فيه الموظف في الصف. سنحتاج إلى تضييق أو تقييد العد على هذا الشهر فقط لكل صف. كيف يتم ذلك؟ نستخدم فقرة window PARTITION ، مثل:

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

تسمح لك الأقسام بتصفية النافذة إلى أقسام حسب قيمة أو قيم معينة. غالبًا ما يطلق على كل قسم إطار النافذة.

للمضي قدمًا في الأمر ، لنفترض أننا لم نرغب فقط في معرفة عدد الموظفين الذين بدأوا في نفس الشهر ، ولكننا نريد أن نعرض الترتيب الذي بدأوا به ذلك الشهر. لذلك ، يمكننا استخدام جملة ORDER BY المألوفة. ومع ذلك ، في وظيفة النافذة ، يعمل ORDER BY بشكل مختلف قليلاً عما هو عليه في نهاية الاستعلام.

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

في هذه الحالة ، يعدل ORDER BY النافذة بحيث تنتقل من بداية القسم (في هذه الحالة الشهر والسنة التي بدأ فيها الموظف) إلى الصف الحالي. وبالتالي ، يتم إعادة تشغيل العد في كل قسم.

رتبها

يمكن أن تكون وظائف النافذة مفيدة جدًا لأغراض الترتيب. رأينا سابقًا أن استخدام وظيفة التجميع COUNT مكننا من معرفة الترتيب الذي انضم إليه الموظفون إلى الشركة. يمكننا أيضًا استخدام وظائف ترتيب النوافذ ، مثل ROW_NUMBER() و RANK() و DENSE_RANK() .

يمكن ملاحظة الاختلافات بعد إضافة موظف جديد في الشهر التالي وإزالة القسم:

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

تستطيع أن ترى الاختلافات. يعطي ROW_NUMBER() عددًا تسلسليًا داخل قسم معين (ولكن مع عدم وجود قسم ، فإنه يمر عبر جميع الصفوف). تعطي RANK() رتبة كل صف بناءً على عبارة ORDER BY . يظهر العلاقات ، ثم يتخطى الترتيب التالي. يُظهر DENSE_RANK أيضًا الروابط ، لكنه يستمر بعد ذلك مع القيمة التالية المتتالية كما لو لم يكن هناك رابط.

تشمل وظائف الترتيب الأخرى ما يلي:

• CUME_DIST - لحساب الترتيب النسبي للصف الحالي داخل القسم
• NTILE - يقسم الصفوف لكل قسم من أقسام النافذة على قدم المساواة قدر الإمكان
• PERCENT_RANK - رتبة النسبة المئوية للصف الحالي

لاحظ أيضًا في هذا المثال أنه يمكن أن يكون لديك وظائف نافذة متعددة في استعلام واحد - ويمكن أن يكون كل من القسم والنظام مختلفين في كل منهما!

صفوف ونطاقات وإطارات ، يا بلدي

لتعريف أو تقييد إطار النافذة الخاص بك ضمن جملة OVER() ، يمكنك استخدام ROWS و RANGE . باستخدام عبارة ROWS ، يمكنك تحديد الصفوف المضمنة في القسم الخاص بك مثل الصفوف السابقة للصف الحالي أو بعده.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

في هذا المثال ، ينتقل إطار النافذة من الصف الأول إلى الصف الحالي مطروحًا منه 1 ، ويستمر حجم النافذة في الزيادة لكل صف.

النطاق يعمل بشكل مختلف قليلاً وقد نحصل على نتيجة مختلفة.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

سيتضمن النطاق تلك الصفوف في إطار النافذة التي لها نفس قيم ORDER BY مثل الصف الحالي. وبالتالي ، من الممكن أن تحصل على نسخ مكررة باستخدام RANGE إذا لم يكن ORDER BY فريدًا.

يصف البعض ROWS بأنه عامل مادي بينما RANGE هو عامل منطقي. في أي حال ، فإن القيم الافتراضية لـ ROWS و RANGE تكون دائمًا UNBOUNDED PRECEDING AND CURRENT ROW .

ماذا بعد؟

تعمل معظم الوظائف التجميعية القياسية مع وظائف Window. لقد رأينا COUNT في الأمثلة بالفعل. البعض الآخر يشمل SUM و AVG و MIN و MAX وما إلى ذلك.

باستخدام وظائف النافذة ، يمكنك أيضًا الوصول إلى كل من السجلات السابقة والسجلات اللاحقة باستخدام LAG و LEAD و FIRST_VALUE و LAST_VALUE . على سبيل المثال ، لنفترض أنك تريد أن تظهر في كل صف رقم مبيعات للشهر الحالي ، والفرق بين رقم مبيعات الشهر الماضي. قد تفعل شيئًا كهذا:

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

في الأساس ، وظائف نافذة SQL قوية جدًا

في حين أن هذه مقدمة سريعة لوظائف نافذة SQL ، نأمل أن تثير اهتمامك لرؤية كل ما يمكنهم فعله. لقد تعلمنا أن وظائف النافذة تؤدي عمليات حسابية مشابهة لطريقة عمل وظائف التجميع ، ولكن مع ميزة إضافية تتمثل في أنها تتمتع بإمكانية الوصول إلى البيانات داخل الصفوف الفردية ، مما يجعلها قوية جدًا. تحتوي دائمًا على جملة OVER ، وقد تحتوي على PARTITION BY و ORDER BY ومجموعة من التجميعات ( SUM و COUNT وما إلى ذلك) ووظائف موضعية أخرى ( LEAD و LAG ). تعلمنا أيضًا عن إطارات النوافذ وكيف تغلف أقسام البيانات.

لاحظ أن النكهات المختلفة لـ SQL قد تنفذ وظائف النافذة بشكل مختلف ، وبعضها قد لا ينفذ جميع وظائف أو جمل النافذة. تأكد من مراجعة وثائق النظام الأساسي الذي تستخدمه.

إذا كنت ، كمطور SQL ، مهتمًا بضبط أداء قاعدة بيانات SQL الخاصة بك ، فراجع ضبط أداء قاعدة بيانات SQL للمطورين .

نوافذ سعيدة!

لمزيد من المعلومات حول عمليات التنفيذ المحددة ، راجع:

• توثيق وظائف النافذة في PostgreSQL لتطبيق PostgreSQL.
• حدد - مستندات أكثر من جملة (Transact-SQL) بواسطة Microsoft.
• وظائف النافذة في SQL Server للحصول على نظرة عامة رائعة على تطبيقات SQL Server والجزء الثاني منه.