הקדמה
פרק זה הוא החלק השני בסדרת פוסטים בנושא יישומים אנליטים ב- שפת SQL.
לצפייה בחלק הקודם שמדבר על אנליזות אפשר לבצע באמצאות הוראת Group by – לחצו כאן.
בפרק זה אתמקד ביישומים אנליטים שאפשר לבצע ב- שפת SQL בעזרת פונקציית Rank.
פונקציית Rank היא פונקציית חלון ובאמצעותה ניתן לדרג את השורות בטבלת SQL.
דרוג של לקוחות בעזרת פונקציית Rank
הפעולה הבסיסית ביותר שאפשר לבצע עם פונקציית Rank היא דרוג בין שורות בטבלה.
בשאילתה הבאה נראה כיצד נדרג את הלקוחות (טבלת customers) לפי סכום הרכישה.
שאילתה
תוצאות השליפה:
הסבר
את דירוג הלקוחות לפי סכום הרכישה (שדה sum_orders) ניתן לראות במשתנה rank_orders. לפי משתנה זה גם מיינתי את תוצאות השליפה.
כיוון שפונקציית rank היא פונקציית חלון, היא מאפשרת לנו לעשות מניפולציות על הסדר והקיבוץ של הטבלה לפני הדירוג, ולכן ניתן לדרג את הלקוחות בתוך המדינות שלהם.
חישוב כזה ביצעתי בשדה rank_orders_by_country כלומר, הציון של כל לקוח בשדה זה נקבע לפי הדרוג שלו בתוך המדינה.
לדוגמה, הלקוח בשורה השניה קיבל 2 בדירוג הכללי, אבל בתוך המדינה שלו (UK) הוא מדורג 1 – הכי גבוה.
מציאת המקום השני בדרוג
לפעמים אנליסט נדרש למצוא את הפריט השני או השלישי בדרוג. זה יכול להיות הלקוח במקום השני, השיר השני שהכי מאזינים לו וכדומה.
גם לצורך ביצוע פעולה זו ניתן להשתמש בפקודת Rank.
למשל, כאשר נרצה למצוא את הלקוח אשר ממוקם במיקום השני מבחינת סכום הקניה בטבלת הלקוחות שראינו קודם, נריץ את השאילתה הבאה:
select
*
from
(
select
rank() over (order by sum_orders desc) as rank_orders,
customer_id,
country,
sum_orders
from
customers
where
country = 'Australia'
and customer_id is not null
) as tab
where
rank_orders=2
תוצאה:
הסבר
השתמשתי בשאילתה מהסעיף הקודם וסיננתי את הלקוחות מאוסטרליה בהוראת ה- where.
את השאילתה שמתי בתוך תת שאילתה ולאחר מכן סיננתי את הלקוח שדורג במקום השני. באותה מידה יכולתי לבחור דרוג בכל מקום אחר.
שימו לב, ישנם מנועי דאטה-בייס שמאפשרים להשתמש בהוראת qualify.
בעזרת הוראת qualify ניתן לסנן פעולות שבוצעו בפונקציית חלון. סינון כזה מייתר את הצורך להכניס את השליפה של הדירוג לתוך תת שאילתה.
select
rank() over (order by sum_orders desc) as rank_orders,
customer_id,
country,
sum_orders
from
customers
where
country = 'Australia'
qualify
rank_orders=2
איתור ההזמנות האחרונות של הלקוחות
פקודת Rank יכולה לסייע לנו גם כאשר נרצה ליצור טבלה שתכיל את השורות עם הביצוע האחרון של פעולה מסוימת. בדוגמה הבאה נראה כיצד ניתן ליצור טבלה עם הקניה האחרונה של כל לקוח.
בשביל המשימה הזאת אעבוד עם טבלת Orders.
שאילתה:
select
*
from
(
select
rank() over (partition by customer_id order by order_time_stamp desc) as order_rank,
*
from
orders
) as tab
where
order_rank=1
תוצאה:
הסבר
בפקודת ה- Rank ביקשתי מהדאטה-בייס לדרג את כל ההזמנות של כל לקוח. את הדירוג מיינתי לפי זמן הרכישה כאשר ההזמנה האחרונה תופיע במקום הראשון בדירוג (מיון לפי desc). כעת, כל שנותר הוא להשאיר את כל ההזמנות שמדורגות ראשונות (1) וכך יתקבלו ההזמנות האחרונות של כל לקוח.
למדריך בסיסי ב- שפת SQL אפשר לחצו כאן.
לתרגול שפת SQL לחצו כאן.
להערות ורעיונות נוספים לנושאים שיהיו בסדרה מוזמנים לפנות אליי למייל:
[email protected]
יובל מרנין,
דאטה אנליסט.
