סימולציה ב-SQL עבור אנליסט נתונים

סימולציה ב-SQL - מערכתכאשר אנליסט נתונים בא למדל בעיה עסקית מורכבת ולפתור אותה באופן כמותי, עומדים בפניו מספר כלים אפשריים.
הכלי הראשון הוא פתרון מתמטי – מידול המערכת בעזרת נוסחאות. לאחר המידול – יש לפתור את מערכת הנוסחאות ולקבל תשובה.

מערכת כזו לרוב כוללת פרמטרים בעלי ערכים ממוצעים (לדוגמה: יחס המרה) לצורך החישוב.

אבל מה יקרה כאשר נרצה להכניס גם אלמנט של שונות – ולא רק ממוצע? הלא כולנו מכירים את המקרה של הסטטיסטיקאי שטבע בבריכה שהעומק הממוצע שלה הוא 30 ס"מ….
כאן החישובים מתחילים להיות מורכבים יותר.

המאמר ידון בשיטה חלופית לחישובים מתמטיים במערכות מורכבות – סימולציות, ויכלול דוגמאות למימוש סימולציות כאלו באמצעות SQL.

מדוע נדרשת סימולציה

על מנת להמחיש את הצורך בסימולציות, ניקח דוגמה מייצגת של בעיית המרה באתרי אינטרנט. להלן מספר נוסחאות/מדדים אפשריים בתחום:

  • אחוז ההמרה (Conversion) מחושב כיחס שבין מספר ההמרות לתנועה הנכנסת לאתר.
  • שימור (Retention) מוגדר כאחוז המשתמשים הממשיכים להשתמש בכלי לאחר שבוע/ חודש; נטישה (Churn) היא כמובן מי שלא המשיך להשתמש בכלי.
  • צמיחה נטו בכמות המשתמשים (Growth) שווה לכמות המשתמשים החדשים, פחות הנוטשים בשבוע/חודש האחרון, לחלק לכמות המשתמשים בתחילת התקופה.

המכנה המשותף לכל המדדים האמורים הוא שהם משקפים נוסחאות דטרמיניסטיות – קבועות. האחוזים מתבססים על ממוצעים והם משמשים לחישובים בסיסיים.

עכשיו נניח שבתחילת החודש היו לנו 1,000 משתמשים, ובממוצע נוטשים את המוצר שלנו 20% מהמשתמשים בכל חודש. אם נרצה לצמוח בכמות המשתמשים בחודש הבא – נצטרך להיערך לגיוס משתמשים חדשים של מעל ל-20% הנוטשים.
אבל 20% הוא רק ממוצע, מה יקרה בתרחיש שבו ינטשו אותנו במשך 3 חודשים מסוימים 30% מהלקוחות בכל חודש במקום 20%? יכול להיות שזה ייצור בעיות מהותיות בתפעול החברה ובתזרים המזומנים, ולכן נרצה להיערך לתרחיש כזה מבעוד מועד.
היערכות כזו יכולה להתבצע ע"י שמירת יתרת מזומנים מספיקה – לדוג' ב -99% מהמקרים, כך שגם אם יהיו מספר חודשים עם נטישה גבוהה – עדיין העסק יוכל להמשיך להתקיים.
למה הכוונה ב-99% מהמקרים? הכוונה היא שמתוך 100 חודשים עם סיכוי נטישה ממוצע של 20%, רק באחד מהם סיכוי הנטישה יסכן את תזרים המזומנים של החברה.

כמובן שאפשר להיערך גם ל-99.9% מהמקרים, או אפילו יותר מכך – אך המשמעות עשויה להיות פגישה בביצועים העסקיים, לאור הקפאת סכום גבוה מאוד אשר יכול לשמש לצמיחת החברה.

 

כיצד מתכננים את המערכת להיות יציבה?

נוסחה מתמטיתבעוד שנוסחאות מתמטיות הן פשוטות מאוד עבור ממוצעים, כאשר אנו דנים בבעיות שאמורות לתת מענה ב-X% מהמקרים אנחנו חייבים להיכנס לתוך נוסחאות ההתפלגות עצמן, מה שהופך את החישובים למורכבים.

וכאן באה לעזרתנו הסימולציה.

אחת מהמתודולוגיות המוכרות ביותר היא מונטה קרלו. לפי המתודולוגיה, מוגרלים מספרים אקראיים, מוכנסים לפרמטרים מסוימים במערכת, ומתבצע חישוב של התוצרים הצפויים שלה.
מאחר והגרלה אחת חשופה לעיוותים, סימולציה כוללת מספר רב מאוד של ריצות – לרוב מעל 100. ככל שתהיינה יותר ריצות – כך תתכנס המערכת לתוצאות תקפות יותר, ותכלול מקרי קיצון רבים יותר.
לאחר סיום הריצות, נוצרת התפלגות לתוצאת המערכת מכל אחת מהריצות, ואפשר לחשב מהו הערך הקריטי תחת ערך הסף (לדוגמה: 99% מהמקרים).
סימולציה תכלול מספר שלבים עיקריים:

  • מידול המערכת. לרוב יכלול 4 מרכיבים:
    • הפרמטר/ים שיוגרל/ו אקראית.
    • הישויות שעבורן הפרמטר הוא רלוונטי (לדוגמה: חישוב נפרד עבור כל משתמש/ לקוח)
    • חישובים בתוך המערכת (לדוגמה: הכנסה חודשית ממוצעת ללקוח כפול אחוז הנטישה).
    • תוצאת החישובים (פלט הריצה).
  • לולאה שמריצה את המערכת X פעמים.
  • ניתוח התוצאות על פני כלל הריצות.

לקריאה נוספת: סימולציית מונטה-קרלו.

 

מימוש סימולציה ב-SQL

לטובת המחשת סימולציה ב-SQL, ניקח תרחיש המבוסס על הדוגמה הקודמת של אתר האינטרנט: 1,000 משתמשים, סיכוי נטישה של 20%. בנוסף נניח הכנסה חודשית ממוצעת של 10 דולר ל-25% מהמשתמשים והיתר ללא הכנסה.

כעת נרצה למצוא מהו התרחיש הגרוע ביותר עבור אובדן הכנסה בחודש מסוים.

הפרמטרים בסימולציה יהיו האם בוצעה נטישה (משתנה בינומי עם 20% סיכוי), והאם הלקוח משלם (משתנה בינומי עם 25% סיכוי).

לאחר מכן נממש שתי לולאות:

  • יצירת נתוני המשתמשים (יצירת 1000 משתמשים, כולל אינדיקציה מי משתמש משלם ומי לא).
  • יצירת נתוני הריצה, כולל אינדיקציה מי נטש, ומה הפסד ההכנסה (הסכום שמשלם במידה ונטש).

לפני שנריץ את הסימולציה, חשוב לקבל מושג על המשמעות של השונות. אם נחשב את תוחלת ההפסד החודשי לפי המספרים, נקבל 1,000*25%*20%*10$ = 500$.

בנוסף, לפני הרצת הסימולציה, חשוב לוודא שמבינים היטב את ההתפלגויות הצפויות בכל פרמטר, על מנת שהסימולציה תגריל ערכים נכונים. כך, חשוב להבין אם מדובר בהתפלגות אחידה, נורמלית, מעריכית, בעלת זנב ימני או כל התפלגות אחרת. בדוגמה שלנו, ההתפלגות של הערכים הוגדרה כבינומית.

סימולציה ב-SQL – קוד לדוגמה.

כאשר נריץ את הסימולציה, נראה שבאופן לא מפתיע, בכ-50% מהמקרים הפסד ההכנסה גבוה יותר מאשר הממוצע. ב-1% מהמקרים ההפסד עמד על 680 דולר, וב-0.1% מהמקרים – על 730 דולר (46% מעל הממוצע). היישום של התוצאות יחסית מיידי – אם נשמור כרית בטחון של 730 דולר נהיה מכוסים על הפסד של חודש אחד – ב-99.9% מהמקרים.

חסרונות הסימולציה

כמו כל שיטה אחרת, גם לסימולציה קיימים מספר חסרונות.

החיסרון העיקרי הוא רגישות לטעויות במידול המערכת. מאחר וסימולציה עוסקת במידול מערכות מורכבות, קיים פוטנציאל לא מועט לטעויות בתהליך הממודל, ויש לוודא שהוא אכן מאופיין נכון.
חיסרון שני, אשר כבר הוצג קודם לכן, הוא הגרלת משתנים בהתפלגות לא נכונה.
חיסרון שלישי ומהותי הוא זמן ריצה מסיבי שהסימולציה דורשת – מאחר ומספר הריצות מכפיל את כמות העיבוד ביחס לחישוב פשוט. לאור זאת, מומלץ תמיד להריץ את הסימולציה על מספר ריצות נמוך ולהעריך את המשך – לפני הפעלת 1000 ריצות ומעלה
.

חיסרון נוסף נוגע שוב במורכבות המערכת, בהיבט הבנת התוצאות שהסימולציה יוצרת, דרך המנגנון ודרך הפעולה שלה. חשוב לבצע הרבה מאוד הרצות של הסימולציה, ולוודא כל העת שהתוצאות אכן הגיוניות ביחס לתהליך העסקי (בדוגמה שהוצגה לקחנו את החישוב הממוצע כ-Benchmark והשווינו מולו את הפתרון של 99%/99.9%).

 

סיכום

במאמר הוצגה התפיסה של מימוש סימולציה ב-SQL. ניתן כמובן גם להריץ סימולציות דומות בכל כלי אנליטי אחר – דוגמת Excel, SAS או כל שפת קוד אחרת.

הסימולציה היא עוד כלי בארגז הכלים של אנליסט נתונים / מדען נתונים, והיא יכולה לעיתים קרובות להביא לתוצאות מהירות ועדיפות על גישה מתמטית. שימוש נכון בכלי הזה יכול להוביל לפתרון מהיר יחסית, להבנה טובה יותר של אופן פעולת המערכת ונקודות החולשה שלה, ולזיהוי והתמודדות עם תרחישים קיצוניים אליהם עשויה המערכת (או הארגון) להגיע.

 

השארת תגובה