חמש הפונקציות המובילות של חלון SQL שכדאי להכיר לראיונות במדעי הנתונים

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() שימושי במיוחד כאשר אתה רוצה לקבל את הרשומה הראשונה או האחרונה. לדוגמה, אם יש לכם טבלה של מתי חברי חדר כושר הגיעו לחדר הכושר ואתם רוצים לקבל את התאריך של היום הראשון שהם הגיעו לחדר הכושר, תוכלו PARTITION BY לקוח (שם/מזהה) ו-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 תוכניות נטפליקס הנצפות ביותר השבוע
• אם אתה רוצה להשיג את 100 המשתמשים המובילים על סמך דולרים שהושקעו
• אם אתה רוצה לראות את ההתנהגות של 1000 המשתמשים הפחות פעילים

דוגמא:

אם תרצה לדרג את הלקוחות המובילים שלך לפי סך המכירות, DENSE_RANK() תהיה פונקציה מתאימה לשימוש.

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

תודה שקראת!

 
וזה הכל! אני מקווה שזה יעזור לך בהכנת הראיון שלך - אני בטוח שאם אתה מכיר את 5 המושגים הללו מבפנים, אתה תצליח בכל מה שקשור לרוב בעיות פונקציית חלון 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