3 שאילתות מורכבות לצרכים אנליטיים וחישוב יחס המרה ב-SQL – חלק ב'


בחלקו הראשון של המאמר הוצגה בעיה של חישוב יחס המרה עבור מוצר אינטרנט המוכר מנויים חודשיים.בחלקו השני של המאמר, נדון בפתרונות אפשריים של בעיית השליפה וחישוב יחס המרה ב-SQL עבור מנויים בתשלום באמצעות 3 שיטות.
הדוגמאות שתוצגנה במאמר הן בסביבת SQL Server 2012 Express.
תחילה, נמפה את שלבי השליפה ועיבוד הנתונים.
השליפה אינה טריוויאלית, וכוללת 8 שלבים:

  1. שליפת כלל המנויים בעלי מנוי 0 ותאריך התחלה בנוב' 2011 מטבלת המנויים.
  2. שליפת כלל המנויים בתשלום מטבלת המנויים.
  3. שליפת מועד המנוי הראשון בתשלום עבור כל משתמש מטבלת המנויים (למקרה שיש משתמשים שחידשו מנוי בתשלום).
  4. קבלת נתוני המנוי הראשון בתשלום עבור כל משתמש [הצלבת שלב 2 עם שלב 3].
  5. קבלת המנויים בתשלום, מתוך כל המנויים שהתחילו תקופת התנסות בנוב' 2011 [הצלבת שלב 4 לתוך שלב 1], וחישוב יחס ההמרה.
  6. הצלבת טבלת המנויים בעלי מנוי מסוג 0 [טבלה 1] לטבלת הביקורים בכלי, מציאת ביקורים במהלך תקופת ההתנסות (14 ימים), והחזרת אינדיקציה ברמת משתמש האם היו ביקורים כאלה.
  7. הצלבת האינדיקציה לפעילות בכלי במהלך תקופת ההתנסות [טבלה 6] לתוך טבלת ההמרות [טבלה 5].
  8. אגרגציה של הטבלה המסכמת [טבלה 7] לפי האם היתה המרה כן/לא, האם היתה פעילות בתקופת ההתנסות כן/לא, וספירת המשתמשים בכל קומבינציה.

 

שימוש בתתי שאילות (Sub-Queries)

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

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

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

חישוב יחס המרה ב-SQL - תוצאות

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

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

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

הקוד בקובץ.

 

שימוש ב-CTE

מאפיין ה-CTE (או בשמו המלא – Common Table Expressions) מציג יכולת מעניינת מאוד להרצת שאילתות, והוא משלב את ה'טוב משני העולמות' משתי השיטות הקודמות. השיטה מאפשרת שמירה זמנית של תוצאות ביניים – וזאת ללא שמירתן בטבלה.

השיטה מתבקשת כאשר תוצאה של שלב ביניים נדרש עבור יותר משלב אחד בהמשך הריצה – כמו שלב 1 בדוגמה שלנו (כלל המנויים בעלי מנוי 0 ותאריך התחלה בנוב' 2011 מטבלת המנויים), המשמש גם את חישוב יחס ההמרה, וגם את חישוב הפעילות בתקופת ההתנסות.

עוד שימוש מפורסם לשיטה זו הוא שאילתות רקורסיביות, אשר בהן נדון במאמר נפרד.

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

הקוד בקובץ.

 

סיכום

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

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

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

 

השארת תגובה