מדריך חלק שני
GROUP BY - SQL
במדריך SQL הקודם, למדנו המון דברים חדשים:
- SELECT
- FROM
- DISTINCT
- ORDER BY
- WHERE
אם פספסתם את הבלוג הראשון ואתם מעוניינים לתרגל בbigquery של גוגל את השאלות שמופיעות בבלוג בחינם לחצו על הלינק חלק ראשון
עכשיו, כשאנחנו כבר יודעים איך לקבל את הנתונים הרלוונטים לנו, נמשיך להסקת מסקנות מנתונים ממשיים.
רגע זה השלב שאנחנו מזכירים שאם דלגתם על החלק הראשון במדריכים אתם מפספסים את האופציה לתרגל עם Bigquery – דאטה בייס של גוגל, את השאלות שמופיעות כאן ממש ,בחינם ללא צורך בכרטיס אשראי ותוך חמש דקות
במדריך זה, אנו נתקדם צעד נוסף קדימה בלימוד שלנו על שפת התכנות SQL, על ידי הוספת פונקציות חדשות ללקסיקון:
SUM, MAX, MIN, AVG, COUNT, GROUP BY.
SUM – סוכם לנו את כל הערכים בעמודה
COUNT – סופר לנו את כמות הערכים בעמודה
MAX – מחזיר לנו את הערך הכי גבוה בעמודה
MIN – מחזיר לנו את הערך הכי נמוך בעמודה
AVG – מחזיר את המספר הממוצע מסך העמודה
GROUP BY –מקבץ לנו את הערכים על פי מימד מסוים (למשל, כל ההכנסות לפי מוצר מסוים שנבחר)
שאלה: מה השכר ששולם לכל העובדים?
SELECT
SUM(amount) as salary
FROM Data_salaries
כמה שורות יחזרו? 1
כמה עמודות יחזרו? 1
מספר = בקרוב תוכלו להשוות לתשובות בקורס החדש….
שאלה: כמה עובדים יש?
SELECT
COUNT(first_name) as employees
FROM Data_salaries
כמה שורות יחזרו? 1
כמה עמודות יחזרו? 1
מספר = ?
שאלה: מהו השכר הממוצע?
SELECT
AVG(amount) as avg_salary
FROM Data_salaries
כמה שורות יחזרו? 1
כמה עמודות יחזרו? 1
מספר = ?
שאלה: מהו השכר הכי גבוה?
SELECT
MAX(amount) as max_salary
FROM Data_salaries
כמה שורות יחזרו? 1
כמה עמודות יחזרו? 1
מספר = ?
שאלה: מהו השכר הכי נמוך?
SELECT
MIN(amount) as min_salary
FROM Data_salaries
כמה שורות יחזרו? 1
כמה עמודות יחזרו? 1
מספר = ?
עד כאן, מעולה שאתם יודעים כיצד לבצע את הפעולות האלו.
אבל, מה שאנחנו יותר מעוניינים בו, הוא לקבל חיתוכים של נתונים.
להלן מספר שאלות לדוגמא:
- מהו השכר הממוצע לפי תפקיד?
- כמה אנשים נמצאים ברשימה לכל תפקיד?
- מהו השכר המקסימלי לפי תפקיד?
- מהו השכר המינימלי לפי תפקיד?
בשביל לענות על השאלות הנ״ל, נשתמש בפקודת SQL GROUP BY
הדבר הבא, יהווה אבן דרך רצינית בלמידה שלנו עד כה:
כשאנחנו מסיקים מסקנות, אנחנו בעצם בודקים איך קבוצות שונות מתנהגות לעומת קבוצות אחרות.
להלן מספר דוגמאות:
- כיצד משתמשי אייפון נבדלים ממשתמשי אנדרואיד?
- אילו סכומי כסף ישלם לקוח אמריקאי באמזון, לעומת לקוח ישראלי?
- מהו אחוז חולי הקרונה בכל מדינה?
את כל השאלות הללו, ניתן לחשב באמצעות כל מה שלמדנו עד כה.
די מסעיר, לא?
כן, קצת התרגשנו לרגע ואנחנו בטוחים שגם אתם.
אז, בואו נצלול לעומק את ה-Syntax של GROUP BY
SELECT
[column_name],
SUM([column_name1]),
FROM Data_salaries
GROUP BY [column_name]
כל מה שנרצה בתור קבוצה, יופיע ב-GROUP BY אחרי FROM.
אם עד כה יש לכם עדיין אינסוף שאלות על SQL, אנחנו מציעים פשוט לתרגל, לתרגל ולתרגל כדי להגיע לשליטה אמיתית בשפה.
לנוחיותכם, בבלוג קיים לרשותכם קורס SQL חינם המורכב מהמון תרגילים שיעזרו לכם להפנים ולעשות שימוש ממשי בידע שצברתם.
אז קדימה, בואו נמשיך לתרגל.
שאלות:
- מהו השכר הממוצע לפי תפקיד?
- כמה אנשים נמצאים ברשימה לכל תפקיד?
- מהו השכר המקסימלי לפי תפקיד?
- מהו השכר המינימלי לפי תפקיד?
- כמה אנשים עובדים בכל חברה (domain)?
- באיזו חברה עובדים הכי הרבה עובדים?
- הצג את שלושת התפקידים עם השכר הממוצע הכי גבוה
--QUESTION 1
SELECT
data_role,
AVG([salary]),
FROM Data_salaries
GROUP BY data_role
--QUESTION 2
SELECT
data_role,
count([first_name]),
FROM Data_salaries
GROUP BY data_role
--QUESTION 3
SELECT
data_role,
MAX([salary]),
FROM Data_salaries
GROUP BY data_role
--QUESTION 4
SELECT
data_role,
MIN([salary]),
FROM Data_salaries
GROUP BY data_role
--QUESTION 5
SELECT
domain,
count([first_name]),
FROM Data_salaries
GROUP BY domain
--QUESTION 6
SELECT
domain,
count([first_name]),
FROM Data_salaries
GROUP BY domain
order by 2 desc
limit 1
--QUESTION 7
SELECT
data_role,
AVG([salary]),
FROM Data_salaries
GROUP BY data_role
order by 2 desc
HAVING
פקודת SQL HAVING, היא חלק מ-GROUP BY.
מתי נשתמש בה?
דוגמא: נגיד ואנחנו מעוניינים לבדוק מהו השכר הממוצע לתפקיד, אבל בתנאי שיש לפחות עשרה עובדים באותו תפקיד.
מדוע השאלה הזאת מעניינת אותנו? כי לפעמים כשאנחנו מסתכלים על משתמש אחד בלבד, המידע שנפיק יכול להיות מוטעה.
אפרופו, בימים אלו ממש נגיד והלכתם להתחסן נגד הקורונה וכשנכנסתם למקום, ראיתם אדם שהתעלף אחרי החיסון.
האם תסיקו שכל מי שיתחסן, בהכרח יתעלף?
ברור שלא.
זה העניין עם HAVING, נשתמש בה כדי להסיק מסקנות תוך כדי סינון של הטייה בנתונים. כיצד זה ייראה?במידה ונרצה לסנן את הנתונים, התנאי יגיע תמיד אחרי GROUP BY.
SELECT
data_role,
AVG([salary]),
FROM Data_salaries
GROUP BY data_role
HAVING COUNT(data_role)>30
בדוגמא הנ״ל, נרצה לקבל את השכר הממוצע לפי תפקיד, בתנאי שיש לפחות 30 אנשים באותו תפקיד. כל זאת, על מנת שנוכל להסיק מסקנות נכונות מתוך הנתונים.
זהו רגע מכריע בהבנה שלנו – אנחנו נוכל להבין סטטיסטיקות של קבוצות ולהשוות קבוצות מסוימות לפי מדדים שקבענו:
קבוצה א׳ מול קבוצה ב׳,
לפי מחיר ממוצע, האם יש לנו חריגים בנתונים, או כל דבר אחר שתרצו לבדוק.
- אבל, מה קורה אם הנתונים נמצאים בכמה טבלאות?
- איך אנחנו מחברים בין טבלאות?
- איך VLOOKUP המוכר של אקסל יצליח להציל את המצב?
בפרק הבא, נענה על השאלות הנ״ל ועוד על המון שאלות SQL אחרות שבוערות בכם, לדוגמא: איך בכלל SQL עובד מאחורי הקלעים.
לאחר מכן, נכיר לכם נושאים מתקדמים ומורכבים יותר:
JOIN, UNION, WITH, CASE, WHEN
