SQL هي اللغة العالمية في عالم البيانات وهي أهم مهارة يجب إتقانها كمحترف بيانات.

السبب وراء أهمية SQL هو أنها المهارة الرئيسية المطلوبة أثناء مرحلة مناظرة البيانات. يتم إجراء الكثير من استكشاف البيانات ومعالجة البيانات وتطوير خطوط الأنابيب وإنشاء لوحة المعلومات من خلال SQL.

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

مع ذلك ، دعنا نتعمق فيه!

1. دلتاس مع LEAD () و LAG ()

 
يتم استخدام LEAD () و LAG () في الغالب عند مقارنة فترة زمنية واحدة مع الفترة الزمنية السابقة لمقياس معين. لإعطاء بعض الأمثلة ...

• يمكنك الحصول على الدلتا بين مبيعات كل عام ومبيعات العام السابق
• يمكنك الحصول على الدلتا في عدد الاشتراكات / التحويلات / زيارات موقع الويب على أساس شهري
• يمكنك مقارنة زخم المستخدم على أساس شهري

على سبيل المثال:

يوضح الاستعلام التالي كيف يمكنك الاستعلام عن النسبة المئوية للتغير الشهري في التكاليف

with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs
)SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange
FROM monthly_costs

2. المبالغ التراكمية مع SUM () أو COUNT ()

 
يمكن حساب الإجماليات الجارية ببساطة من خلال وظيفة windows تبدأ بـ SUM () أو COUNT (). هذه أداة قوية عندما تريد إظهار نمو مقياس معين بمرور الوقت. وبشكل أكثر تحديدًا ، يكون مفيدًا في الظروف التالية:

• احصل على إجمالي الإيرادات والتكاليف الجارية بمرور الوقت
• احصل على إجمالي الوقت المستغرق في التطبيق لكل مستخدم
• احصل على إجمالي مستمر للتحويلات بمرور الوقت

على سبيل المثال:

يوضح المثال التالي كيف يمكنك تضمين عمود المجموع التراكمي للتكاليف الشهرية:

SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts
FROM cost_table

3. المتوسطات المتحركة مع AVG ()

 
يعد AVG () قويًا حقًا في وظائف windows حيث يسمح لك بالحساب خطوط المتوسط متأخر , بعد فوات الوقت.

المتوسطات المتحركة هي طريقة بسيطة لكنها فعالة للتنبؤ بالقيم على المدى القصير. كما أنها مفيدة للغاية في تجانس المنحنيات المتقلبة على الرسم البياني. بشكل عام ، يتم استخدام المتوسطات المتحركة لقياس الاتجاه العام للمكان الذي تتحرك فيه الأشياء.

اكثر تحديدا…

• يمكن استخدامها لمعرفة الاتجاه العام للمبيعات الأسبوعية (هل يرتفع المتوسط ​​بمرور الوقت؟). هذا من شأنه أن يشير إلى النمو كشركة.
• يمكن استخدامها أيضًا للحصول على الاتجاه العام للتحويلات الأسبوعية أو زيارات الموقع.

على سبيل المثال:

الاستعلام التالي هو مثال على الحصول على المتوسط ​​المتحرك لمدة 10 أيام للتحويلات.

SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage
FROM conversions

4. ROW_NUMBER ()

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

على سبيل المثال:

يوضح هذا المثال كيف يمكنك استخدام ROW_NUMBER () للحصول على أول تاريخ لزيارة كل عضو (مستخدم).

with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits
)SELECT *
FROM numbered_visits
WHERE rowNumber = 1

للتلخيص ، إذا احتجت في أي وقت إلى الحصول على السجل الأول أو الأخير ، فإن ROW_NUMBER () هي طريقة رائعة لتحقيق ذلك.

5. ترتيب السجلات مع DENSE_RANK ()

 
DENSE_RANK () مشابه لـ ROW_NUMBER () فيما عدا أنه يُرجع نفس الترتيب لقيم متساوية. يعد الترتيب الكثيف مفيدًا جدًا عندما يتعلق الأمر باسترداد أفضل السجلات ، على سبيل المثال:

• إذا كنت ترغب في سحب أفضل 10 عروض Netflix الأكثر مشاهدة هذا الأسبوع
• إذا كنت ترغب في الحصول على أفضل 100 مستخدم على أساس الدولارات التي يتم إنفاقها
• إذا كنت تريد أن ترى سلوك 1000 مستخدم أقل نشاطًا

على سبيل المثال:

إذا كنت ترغب في ترتيب أفضل العملاء لديك حسب إجمالي المبيعات ، فستكون DENSE_RANK () وظيفة مناسبة للاستخدام.

SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank
FROM customers

شكرا للقراءة!

 
و هذا كل شيء! آمل أن يساعدك هذا في الإعداد للمقابلة - أنا متأكد من أنك إذا كنت تعرف هذه المفاهيم الخمسة من الداخل إلى الخارج ، فستبلي بلاءً حسنًا عندما يتعلق الأمر بمعظم مشاكل وظائف نافذة SQL الموجودة هناك.

كما هو الحال دائمًا ، أتمنى لك الأفضل في مساعيك التعليمية!

 
 
تيرينس شين هو متحمس للبيانات مع 3 سنوات من الخبرة في SQL و 2+ سنوات من الخبرة في Python ، ومدون في Towards Data Science و KDnuggets.

أصلي. تم إعادة النشر بإذن.

المصدر: https://www.kdnuggets.com/2022/01/top-five-sql-window-functions-know-data-science-interviews.html