טעויות SQL נפוצות שעושים ג'וניורים (ב') – Joins

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

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

כשאתה חדש בתחום הדאטה, קל מאוד לטעות. הלוגיקה שמאחורי הטבלאות לא מוכרת לך, אין לך Benchmark להשוות אליו, והכי חשוב – עוד לא עשית מספיק טעויות שאפשר ללמוד מהן. זה פוסט המשך לפוסט הקודם שנגע בטעויות שקשורות לNULLs. בואו ננסה ללמוד איך להימנע מטעויות הקשורות לJOINS שיגרמו לאיזה בלוגר רנדומלי לכתוב עליכן סיפור בהמשך הדרך 😉 .

טעות #1 – ביצוע JOIN על עמודה המכילה Null

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

זוכרים שאמרנו שאי אפשר להשוות Null לNull? זה בא לידי ביטוי בצורה הטובה ביותר בפעולת JOIN, מכיוון שהיא כוללת בהכרח השוואה בין שני ערכים.

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

קווארי אפשרי יראה כך:

SELECT *
FROM MANAGERS as A
LEFT JOIN CUSTOMERS as B ON a.address = b.store_address

התוצאה תיראה כך:

מה קרה לנו בעצם?
יתכן וציפינו לראות שמנהל בעל ID 123, אשר לא אחראי על חנות ספציפית, אך עדיין עובד כמנהל בחברה, יהיה matched עם לקוחות שלא הוזנה להם כתובת (ולכן זכו בערך Null). מכיוון שזהו Left Join הערך של מנהל 123 נשאר, אבל הלקוחות דפנה ואיתי אינם מופיעים בטבלה המאוחדת. SQL לא יכול להשוות Null לNull והערכים יסוננו החוצה.
חשוב להכיר את הניואנס הזה כי אתם עלולים למצוא את עצמכם מבזבזים זמן רב בלהבין לאן נעלמו ערכים בטבלה, או יותר גרוע, שבכלל לא תשימו לב שהם לא שם.

פתרון אפשרי אחד יכול להיות:

SELECT *
FROM MANAGERS as A
LEFT JOIN CUSTOMERS as B ON a.address = b.store_address 
OR a.address is null and b.store_address is null

בעבודה מול מחשב כמו בעבודה מול בני אדם חשוב לשמור על הכלל הבא:

Explicit is better than implicit

Zen Of Python

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

טעות #2 – שימוש יתר בINNER JOIN

המון אנליסטים (צעירים וותיקים כאחד) נוהגים להשתמש בINNER JOIN יתר על המידה. אני מניח שזה נובע מחוסר רצון להתעסק עם NULL Values שנוצרים בעזרת LEFT JOIN. זאת לא 'טעות' במובן הקלאסי של המילה אלא יותר אי שמירה על Best Practice. כולנו מכירים את דיאגרמת וון המפורסמת שמראה מה עושה INNER JOIN:

INNER JOIN הוא מקרה מאוד ספציפי בו אנחנו רוצים לראות את החיתוך בין שתי טבלאות. אני לא אומר שלעולם לא נרצה להשתמש בו, פשוט שברצף העבודה, נורא קל לשכוח מה הוא עושה ולהתעלם מהסיכונים שטמונים בו. מה הסיכונים אתם שואלים?
א. יתכן ששתי הטבלאות אותן אנו מחברים לא מכילות ערכים מתאימים מבלי שהתכוונו לכך.
ב. יתכן שהעמודה עליה אנחנו עושים JOIN בטבלה ימין או שמאל, מכילה ערך NULL ולא שמנו לכך לב.

אתן דוגמא פשוטה מהעולם האמיתי –

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

יתכן ונרצה לכתוב קווארי כזה:

SELECT ID, Client_name, COUNT([case_id])
FROM customers
INNER JOIN customer_support_cases
ON customers.ID = customer_support_cases.customer_id
GROUP BY ID, Client_name

על פניו, קווארי פשוט. בפועל, שכחנו שלא כל לקוח פונה לשירות הלקוחות בכלל. הINNER JOIN הפיל לנו את הערך של הלקוח DR Martin Van Nostrand. אם היינו משתמשים בLEFT JOIN, היינו פשוט מקבלים את התוצאה הבאה, שיכלה לקלוע למה שחיפשנו.

SELECT ID, Client_name, COUNT([case_id])
FROM customers
LEFT JOIN customer_support_cases
ON customers.ID = customer_support_cases.customer_id
GROUP BY ID, Client_name

בדוגמא זו השתמשתי באגרגציה כדי להמחיש את הנקודה. אבל האמת היא ששימוש בLEFT JOIN פשוט מאפשר יותר שליטה בנתונים. במקרה והיינו משלבים בין שתי הטבלאות ללא אגרגציה, היינו מוצאים Nulls והיינו יכולים להחליט אם אנחנו צריכים אותם לאחר מכן או לא. אם נבחר בכך, תמיד אפשר לשנות את הJOIN ל – INNER אחר כך.

טעות #3 – הכפלת שורות ע"י JOIN

תפיסה שגויה לגבי JOINS שרווחת בקרב ג'וניורים היא שכמות השורות בטבלה המאוחדת, אמורה להתאים לכמות השורות בטבלה עליה עשינו JOIN. כלומר, אם עשינו LEFT JOIN נצפה למספר שורות כמו בטבלה השמאלית, אם עשינו RIGHT JOIN נצפה למספר השורות בטבלה ימין. אם עשינו INNER JOIN נצפה אך ורק למספר השורות שנמצאות בשמאל וגם בימין.
(גילוי נאות – בחיים לא עשיתי RIGHT JOIN 😉 )

אמחיש שוב עם דוגמא בעזרת הטבלאות שהצגתי לפני כן – טבלת הלקוחות וטבלת הפניות למוקד שירות הלקוחות. נניח וארצה לחבר כל לקוח עם הCustomer representative ID שטיפל בו על מנת לראות מי איש השירות שטיפל במספר הלקוחות הגדול ביותר. אבחר בINNER JOIN כי לא מעניין אותי לקוחות שלא פנו לשירות הלקוחות (וכבר הבנתי שלא נמצאים בטבלה אחרי הטעות בסעיף הקודם).

אני מצפה לתוצאה כזאת:

כדי להגיע לתוצאה אכתוב קווארי כזה:

SELECT ID, Client_name, [service rep id]
FROM customers
INNER JOIN customer_support_cases
ON customers.ID = customer_support_cases.customer_id

בפועל מה שקיבלתי זו הטבלה הזאת:

מה קרה כאן? הINNER JOIN הפיל את הערך של Dr. Martin Van Nostrand כפי שרצינו. אבל הערך של הלקוח Vandalay Industries הוכפל. SQL מחפש התאמה על בסיס הכלל הזה כפי שהגדרנו אותו:
ON customers.ID = customer_support_cases.customer_id
בכל התאמה תיווצר שורה חדשה בטבלה הסופית. כשאנחנו עושים JOIN בין טבלאות, חשוב שנכיר את האפשרות להכפלת ערכים. פתרון קל יכול להיות שימוש בDISTINCT.

פתרונות

כמו בפוסט הקודם, הפתרון הטוב ביותר הוא מודעות. אבל במקרה של JOINS יש לי פתרון עזר להציע לכם שהוא לא רק "תהיו מודעים למה שאתם עושים". אם מסתכלים על שלושת הבעיות שהצגתי למעלה, המכנה המשותף לכולן הוא העובדה שמספר הערכים בטבלה הסופית אינו כפי שצפינו שיהיה. טריק מוכר של אנליסטים עם קצת ניסיון הוא להשתמש בCOUNT לפני ואחרי כל JOIN. לדוגמא:

SELECT COUNT(*)
FROM customers
--INNER JOIN customer_support_cases
--ON customers.ID = customer_support_cases.customer_id

SELECT COUNT(*)
FROM customers
INNER JOIN customer_support_cases
ON customers.ID = customer_support_cases.customer_id

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

סיכום

טעויות JOINS הן הטעויות הנפוצות ביותר שעושים דאטה אנליסטים שחדשים לSQL. חשוב להכיר את הניואנסים בפעולת JOIN ולהבין שזה לא פשוט כמו שזה מוצג בקורסי SQL מסודרים. שימוש בCOUNT לפני ואחרי כל JOIN, אמור למנוע טעויות מטופשות. אני ממליץ להשתמש בשיטה זו כמה שיותר, ובמיוחד בעת JOIN בין טבלאות שחדשות לכם. אין סיכוי שתימנעו לחלוטין מטעויות אבל לפחות זה יעזור לצמצם את המיילים המביכים שתאלצו לשלוח על מנת להתנצל.