​GROUP BY – SQL מדריך

במדריך SQL הקודם, למדנו המון דברים חדשים:

SELECT, FROM, DISTINCT, ORDER BY, WHERE.

אם פספסתם את הבלוג הראשון לחצו על הלינק חלק ראשון

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

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

SUM, MAX, MIN, AVG, COUNT, GROUP BY.


SUM – סוכם לנו את כל הערכים בעמודה

COUNT – סופר לנו את כמות הערכים בעמודה

MAX – מחזיר לנו את הערך הכי גבוה בעמודה

MIN – מחזיר לנו את הערך הכי נמוך בעמודה

AVG – מחזיר את המספר הממוצע מסך העמודה

GROUP BY –מקבץ לנו את הערכים על פי מימד מסוים (למשל, כל ההכנסות לפי מוצר מסוים שנבחר)

בואו נתרגל את הפונקציות עם כמה דוגמאות:

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

טבלת תרגול פונקציות SQL: בעלת שש עמודות: first name, last name, data role, domain, email, salary

תשובה:

SELECT

SUM(amount) as salary

FROM Data_salaries

כמה שורות יחזרו? 1

כמה עמודות יחזרו? 1

מספר = ?

שאלה: כמה עובדים יש?

טבלת תרגול פונקציות SQL: בעלת שש עמודות: first name, last name, data role, domain, email, salary

תשובה:

SELECT

COUNT(first_name) as employees

FROM Data_salaries

כמה שורות יחזרו? 1

כמה עמודות יחזרו? 1

מספר = 8

שאלה: מהו השכר הממוצע?

טבלת תרגול פונקציות SQL: בעלת שש עמודות: first name, last name, data role, domain, email, salary

תשובה:

SELECT

AVG(amount) as avg_salary

FROM Data_salaries

כמה שורות יחזרו? 1

כמה עמודות יחזרו? 1

מספר = ?

שאלה: מהו השכר הכי גבוה?

טבלת תרגול פונקציות SQL: בעלת שש עמודות: first name, last name, data role, domain, email, salary

תשובה:

SELECT

MAX(amount) as max_salary

FROM Data_salaries

כמה שורות יחזרו? 1

כמה עמודות יחזרו? 1

מספר = $50,197

שאלה: מהו השכר הכי נמוך?

טבלת תרגול פונקציות SQL: בעלת שש עמודות: first name, last name, data role, domain, email, salary

תשובה:

SELECT

MIN(amount) as max_salary

FROM Data_salaries

כמה שורות יחזרו? 1

כמה עמודות יחזרו? 1

מספר = $27,863

עד כאן, מעולה שאתם יודעים כיצד לבצע את הפעולות האלו.

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

להלן מספר שאלות לדוגמא:

  1. מהו השכר הממוצע לפי תפקיד?
  2. כמה אנשים נמצאים ברשימה לכל תפקיד?
  3. מהו השכר המקסימלי לפי תפקיד?
  4. מהו השכר המינימלי לפי תפקיד?

בשביל לענות על השאלות הנ״ל, נשתמש בפקודת SQL GROUP BY:

הדבר הבא, יהווה אבן דרך רצינית בלמידה שלנו עד כה:

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

להלן מספר דוגמאות:

  1. כיצד משתמשי אייפון נבדלים ממשתמשי אנדרואיד?
  2. אילו סכומי כסף ישלם לקוח אמריקאי באמזון, לעומת לקוח ישראלי?
  3. מהו אחוז חולי הקרונה בכל מדינה?

את כל השאלות הללו, ניתן לחשב באמצעות כל מה שלמדנו עד כה.

די מסעיר, לא?

כן, קצת התרגשנו לרגע ואנחנו בטוחים שגם אתם.

אז, בואו נצלול לעומק את ה-Syntax של GROUP BY:

SELECT

[column_name],

SUM([column_name1]),

FROM Data_salaries

GROUP BY [column_name]

כל מה שנרצה בתור קבוצה, יופיע ב-GROUP BY אחרי FROM.

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

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

אז קדימה, בואו נמשיך לתרגל.

שאלות:

  1. מהו השכר הממוצע לפי תפקיד?
  2. כמה אנשים נמצאים ברשימה לכל תפקיד?
  3. מהו השכר המקסימלי לפי תפקיד?
  4. מהו השכר המינימלי לפי תפקיד?
  5. כמה אנשים עובדים בכל חברה (domain)?
  6. באיזו חברה עובדים הכי הרבה עובדים?
  7. הצג את שלושת התפקידים עם השכר הממוצע הכי גבוה

טבלת תרגול GROUP BY: בעלת שש עמודות: first name, last name, data role, domain, email, salary

תשובות:

1.

SELECT

data_role,

AVG([salary]),

FROM Data_salaries

GROUP BY data_role

SELECT

data_role,

count([first_name]),

FROM Data_salaries

GROUP BY data_role

SELECT

data_role,

MAX([salary]),

FROM Data_salaries

GROUP BY data_role

SELECT

data_role,

MIN([salary]),

FROM Data_salaries

GROUP BY data_role

SELECT

domain,

count([first_name]),

FROM Data_salaries

GROUP BY domain

SELECT

domain,

count([first_name]),

FROM Data_salaries

GROUP BY domain

order by 2 desc

limit 1

SELECT

data_role,

AVG([salary]),

FROM Data_salaries

GROUP BY data_role

order by 2 desc

HAVING

פקודת SQL HAVING, היא חלק מ-GROUP BY. מתי נשתמש בה?

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

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

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

האם תסיקו שכל מי שיתחסן, בהכרח יתעלף?

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

SELECT

data_role,

AVG([salary]),

FROM Data_salaries

GROUP BY data_role

HAVING COUNT(data_role)>30

במידה ונרצה לסנן את הנתונים, התנאי יגיע תמיד אחרי GROUP BY.

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

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

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

אבל, מה קורה אם הנתונים נמצאים בכמה טבלאות? איך אנחנו מחברים בין טבלאות? ואיך VLOOKUP המוכר של אקסל יצליח להציל את המצב?

מה נלמד במדריך SQL הבא?

בפרק הבא, נענה על השאלות הנ״ל ועוד על המון שאלות SQL אחרות שבוערות בכם, לדוגמא: איך בכלל SQL עובד מאחורי הקלעים.

לאחר מכן, נכיר לכם נושאים מתקדמים ומורכבים יותר:

JOIN, UNION, WITH, CASE, WHEN.