זהו החלק השלישי בסדרה של יישומים אנליטים בשפת SQL, חלק זה יעסוק ביישומים אנליטים של נרמול נתונים בשפת SQL.
החלק הראשון עסק ביישומים אנליטים של אגרגציות והחלק השני עסק ביישומים אנליטים של פונקציית Rank.
הקדמה
במחקרים אנליטים יש צורך להשוות בין שני משתנים אשר אינם מדברים באותה שפה עסקית. למשל, השוואה בין סכום הרכישה של לקוח לתדירות הקניה שלו, או גובה ההלוואה שהלקוח לקח לכושר ההחזר שלו.
על פניו, לא ניתן להשוות בין שני שדות מעולם תוכן שונה, אך שימוש בשיטות נרמול נתונים מאפשר לנו לתרגם את השדות לאותה שפה מתמטית וכך להצליח להשוות ביניהם. בשיטות נרמול אפשר גם להיעזר בתהליך של יצירת ציון הוליסטי ללקוח.
ישנן שתי שיטות פופולריות לביצוע נרמול של דאטה. האחת היא לפי חלוקה בסכום המקסימלי של הטבלה והשניה היא לפי ציוני תקן. בפוסט זה אדון בשתי השיטות ואראה כיצד ניתן ליישם אותם בעזרת שפת SQL תוך שימוש ב- פונקציות חלון.
הערה: הדוגמאות בפוסט נכתבו על מנוע של Postrgre SQL.
שיטת נרמול לפי הסכום המקסימלי.
שיטת נרמול זו היא שיטה קלה ופשוטה להבנה. מחשבים את הסכום הגבוה בשדה שרוצים לנרמל ואז מחלקים את כל הערכים בשדה במספר המקסימלי שחישבנו. לאחר החישוב כל הערכים יהיו מנורמלים בין 0 ל 1. (הערה: ישנה גרסה לשיטה בה מנרמלים את השדה לערכים שבין מינוס 1 ל 1).
בשאילתה הבאה אקח את טבלת הלקוחות Customers ואנרמל את שדה סכום ההזמנות של הלקוח sum_orders.
select
customer_id,
sum_orders,
max(sum_orders) over() as max_value,
sum_orders/max(sum_orders) over() as sum_orders_norm
from
customers
תוצאה:
הסבר
שדה max_value מכיל את הערך של הלקוח עם סכום הרכישות הגבוה ביותר, והשדה sum_orders_norm הוא השדה המנורמל – כלומר חלוקה של max_value בסכום ההזמנות של הלקוח.
החיסרון הגדול של שיטה זו הוא בהתמודדות עם ערכים קיצוניים. אומנם ההתפלגות לא משתנה כי עשינו טרנספורמציה ליניארית אבל הסקאלה מושפעת מערכים קיצוניים, וכאשר נרצה להשוות בין שני שדות מנורמלים, תהיה לכך השפעה.
למשל אם סכום הרכישות החציוני של הלקוחות הוא 200 דולר אבל יש רכישה אחת בסכום גבוה של 10,000 דולר, החלוקה של כל הערכים בשדה תהיה ב- 10,000 דולר, וכאשר נשווה את השדה המנורמל עם שדות אחרים, יהיה עיוות בערכים של המשתנה הזה כיוון שכל הערכים יהיו נמוכים מאד.
שיטת נרמול לפי ציוני תקן (z-scores)
שיטת ציוני התקן (z-scores) היא שיטה מאד ותיקה בעולם הסטטיסטיקה ומשתמשים בה החל משנת 1968 כדי לנרמל ערכים. בשיטה זו ממירים את ההתפלגות של השדה שאותו מעוניינים לנרמל לשדה שבו הממוצע הוא 0 וסטיית התקן היא 1. היתרון בשיטה הוא בהמרה של הערכים להתפלגות סטנדרטית המאפשרת להשוות בין אוכלוסיות ללא העיוות שראינו בשיטה הקודמת.
עוד על ציוני תקן אפשר לראות בסרטון הנהדר של מר סטטיסטיקה.
נסתכל שוב על טבלת הלקוחות -Customer שראינו בסעיף הקודם, ונריץ את השאילתה הבאה:
select
customer_id,
sum_orders,
avg(sum_orders) over() as avg_sum_orders,
stddev(sum_orders) over () as std_sum_orders,
(sum_orders-avg(sum_orders) over())/stddev(sum_orders) over () as sum_orders_z_score
from
customers
תוצאה:
הסבר
בשדות avg_sum_orders ו- std_sum_orders חישבתי את הממוצע וסטיית התקן של סכום הקניות של כל הלקוחות בעזרת פונקציות חלון. בשדה sum_orders_z_score חישבתי את ציון התקן עצמו.
השדות avg_sum_orders ו- std_sum_orders אינם הכרחיים בשביל חישוב ציון התקן, אך הם עוזרים לי להבין כיצד מתבצע החישוב.
לסיכום
בפוסט זה סקרתי שתי שיטות נרמול פופולריות והדגמתי כיצד אפשר לממש אותן בשפת SQL. שיטת ציוני התקן היא השיטה מועדפת לנרמול כי היא פחות מושפעת מערכים קיצוניים. ישנן שיטות נרמול נוספות שמשמשות למקרים אחרים שלא סקרתי בפוסט הזה.
בפוסט הזה נעשה שימוש נרחב בפונקציות חלון בשפת SQL. להסבר על פונקציות חלון לחצו כאן.
המאמר נכתב על ידי יובל מרנין.
לעזרה בניתוח נתונים ניתן לפנות אליי ב- לינקדאין, פייסבוק או במייל: [email protected]
English version of the article – Normalizing data in SQL
