הפוסט הבא הוא תחילה של סדרה שבה אתאר פעולות נפוצות ש- דאטה אנליסט מבצע ביומיום בעזרת שפת SQL.
בפרק הנוכחי אתמקד באנליזות שאפשר לבצע בעזרת הוראת האגרגציה – Group by.
(את הדוגמאות בפוסט כתבתי על מנוע של Postgre SQL).
ניתוח מגמות (טרנדים) בעזרת Group by
לוג או רשימה של נתונים עם תאריך של ביצוע פעולה מסוימת מאפשר לנו לראות את המגמות (טרנדים) של הפעולות שבוצעו. כדי לחשב את המגמה בעזרת שפת SQL יש לקבץ את הנתונים לפי התאריך ולחשב את המטריקות שיעניינו אותנו.
חשוב לשים לב לרמת האגרגציה שלפיה רוצים לבנות את המגמה. האם רוצים לעבוד לפי רמת אגרגציה של יום, חודש, שבוע או אולי שעה.
דוגמה
להלן טבלת Orders שעליה נרצה לחשב טרנד ברמה היומית.
השאילתה תהיה
select
order_time_stamp::date,
count(order_id) as order_count,
avg(sum_order) as orders_avg,
avg(case when country='United Kingdom' then sum_order end) as orders_avg_UK,
avg(case when country='France' then sum_order end) as orders_avg_France
from
orders
group by
1
order by
1
התוצאה שנקבל
הערות:
- השדה order_time_stamp הינו שדה ברמת שעה ולכן המרתי את השדה מסוג שעה לסוג תאריך בעזרת אופרטור ::date
- בדוגמה הזאת חישבתי מספר מטריקות, אך אפשר להוסיף כמה מטריקות שרוצים.
השטחה של טבלה
בטבלאות גרנולריות, כלומר טבלאות בהן הנתונים מגיעים מהמערכות השונות באופן לא מקובץ (כמו לוג של פעולות) יהיה לנו קל יותר לעבוד באופן מקובץ לפני שנוכל לבצע עליהן אנליזות. פעולה זאת מכונה לעיתים השטחה של טבלה.
למשל, הטבלה Item orders מכילה את כל הפריטים שנרכשו בכל ההזמנות בחנות E-commerce, ובכדי שנוכל לנתח את ההזמנות בקלות נצטרך לבצע השטחה לרמה של הזמנות. בהשטחה נעשה את פעולות האגרגציה לפי מספר הזמנה (לפי InvoiceNo) ונחשב את השדות שאנחנו רוצים על כל הזמנה.
בנוסף על כך, יצירת הטבלה המשוטחת מאפשרת גם לסדר את השמות של השדות ולנקות את הדאטה מתקלות.
בשביל ההשטחה נריץ את השאילתה הבאה:
select
InvoiceNo as order_id,
customerid,
country,
sum(UnitPrice*Quantity) as sum_order,
count(StockCode) as item_orders,
min(date(InvoiceDate)) as order_date,
avg(UnitPrice) as avg_unit_price
from
e_commerce.items_order
where
InvoiceDate<>'12/1/2010 8:26'
group by
1,2,3
התוצאה:
הסבר
בשאילתה הנ"ל יצרתי אגרגציה לרמת הזמנות וכדי להוסיף מידע על ההזמנות הוספתי עוד רמות קיבוץ (customer_id,country). יכולתי להוסיף את השדות האלה לאגרגציה שלכל הזמנה יש מספר לקוח אחד ומדינה אחת.
לאחר שקבעתי את השדות לפיהן אני אבצע את האגרגציה, יצרתי מטריקות מעניינות שלפיהן ארצה לנתח אח"כ את טבלת ההזמנות.
הערות
- בהוראת ה-where סיננתי שעה בעייתית שבה הייתה תקלה הנתונים.
- את הטבלה המושטחת אפשר ליצור בתוך תת שאילתה, אבל מומלץ יותר ליצור טבלה חדשה או view כדי להקל עלינו להיעזר בה בשאילתות נוספות.
איתור ערכים כפולים
לפעמים דאטה אנליסט צריך לאתר ערכים כפולים בדאטה. בפעולה זאת משתמשים פעמים רבות כדי לבדוק האם יש לקוח שרכש יותר מפעם אחת או לפני חיבור בין טבלאות כדי לוודא שהמפתח הוא חד ערכי.
בטבלת ה- Orders שהזכרנו בסעיף הקודם יש שדה בשם customer_id שמתייחס למספר לקוח. והשאילתה הבאה תעזור לנו לבדוק האם לקוח הזמין יותר מפעם אחת.
select
count(customer_id) as cnt,
count(distinct customer_id) as cnt_d
from
orders
תוצאה:
בשאילתה אני סופר את כמות הלקוחות שיש בשורות הטבלה ואת כמות הלקוחות היחודיים בטבלה.
אם כמות הלקוחות היחודיים קטנה מכמות הלקוחות בטבלה אזי שיש לקוחות שהזמינו יותר מפעם אחת.
אם המספר היה שווה זה אומר שכל לקוח הזמין פעם אחת בלבד.
כדי לראות את הלקוחות עצמם שהזמינו יותר מפעם אחת, נריץ את השאילתה הבאה:
select
customer_id,
count(order_id) as number_of_orders
from
orders
group by
customer_id
having
count(order_id)>1
order by
number_of_orders desc
הסבר
בשאילתה ספרתי את מספר ההזמנות שכל לקוח עושה וסיננתי את הלקוחות שהזמינו רק פעם אחת כדי לאתר את הלקוחות הכפולים. את המיון ביצעתי כדי לראות בשורות הראשונות את הלקוחות שהזמינו הכי הרבה.
בתשובה לשאילתה אנחנו יכולים לראות שלקוח מספר 14911 הזמין 248 פעמים ולקוח מספר 12748 הזמין 224 פעמים.
השורה הראשונה אומרת שהיו 3,710 הזמנות שלהן לא היה מספר לקוח. מקרה זה מצביע על תקלה שיש בטבלאות כי לא יתכן הזמנות ללא מספר לקוח.
למדריך בסיסי בשפת SQL לחצו כאן.
לתרגול SQL לחצו כאן.
להערות ורעיונות נוספים לנושאים שיהיו בסדרה מוזמנים לפנות אלי למייל:
[email protected]
יובל מרנין,
דאטה אנליסט.
