פקודת Group By מאפשרת לנו לקבץ את תוצאות השאילתה שלנו לפי קבוצות מוגדרות. במדריך SQL זה נתמקד בפונקציות הקבוצה ופקודת ה Group By. להבדיל מפונקציות השורה אשר פועלות על כל שורה ושורה, פונקציות הקבוצה פועלות על קבוצות, לדוגמא :

פקודת Group By - מדריך SQL

* הקבוצה יכולה להיות כל הטבלה או תתי קבוצות מהטבלה.

פונקציות הקבוצה והערך NULL

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

! פונקציות קבוצה אינן מתייחסת לערך NULL. לדוגמא, אם שכר אחד מהעובדים היה NULL והיינו רוצים להפעיל על עמודת השכר את פונקציית הממוצע, היא היתה מחברת 3 ערכים ומחלקת ב- 3.

פונקציות קבוצה נפוצות

שם הפונקציה

תפקיד

עובדת על סוגי הערכים

דוגמא (בהתאם לערכי הקבוצה מהדוגמא הקודמת)

AVG

מחזירה ממוצע

מספרים

SELECT AVG(sal) > 4000

SUM

מחזירה סכום

מספרים

SELECT SUM(sal) > 16000

MAX

מחזירה את הערך הגבוה ביותר

מספרים

מחרוזות

תאריכים

SELECT MAX(sal) > 7000

MIN

מחזירה את הערך הנמוך ביותר

מספרים

מחרוזות

תאריכים

SELECT MIN(sal) > 2000

שם הפונקציה

תפקיד

עובדת על סוגי הערכים

דוגמא (בהתאם לערכי הקבוצה מהדוגמא הקודמת)

COUNT

מחזירה את כמות הערכים בעמודה מסויימת

מספרים

מחרוזות

תאריכים

SELECT COUNT(name) > 3

SELECT COUNT(sal) > 4

COUNT(*)

מחזירה את כמות השורת בטבלה

מספרים

מחרוזות

תאריכים

SELECT COUNT(*) > 4

COUNT DISTINCT

סופרת את הערכים היחודיים בטבלה

מספרים

מחרוזות

תאריכים

COUNT(name) > 3

COUNT(DISTINCT name) > 2

פעולות על תתי קבוצות – משפט GROUP BY

SELECT      שם_עמודה   , פונקציית_קבוצה(שם_עמודה)

FROM          שם_טבלה

WHERE         תנאי

GROUP BY     שם_עמודה

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

SELECT      department_id , AVG(salary)    

FROM        employees              

GROUP BY    department_id  

קווים מנחים לעבודה עם GROUP BY

כל עמודה המופיעה ב- SELECT חייבת להופיע במשפט GROUP BY.

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

פקודת Group By - מדריך SQL

כל עמודה הכתובה במשפט GROUP BY לא חייבת להופיע ב- SELECT.

אין מניעה מלבצע הקבצות לפי עמודות שונות אך לא להציגן במשפט SELECT (על אף שהתוצאה לא תצא קריאה).

SELECT      AVG(salary)    

FROM        employees 

WHERE       department_id IN (50, 80, 90)             

GROUP BY    department_id  

משפט WHERE מאפשר לנו לסנן תוצאות לפני ההקבצה.

לדוגמא, יש להציג את הממוצעים עבור מחלקות 50, 80, 90 בלבד :

SELECT      department_id , AVG(salary)    

FROM        employees 

WHERE       department_id IN (50, 80, 90)             

GROUP BY    department_id   

! משפט WHERE למעשה מתבצע לפני פעולת GROUP BY, נראה זאת בהמשך – סדר פעולות מול סדר ביצועים.

ניתן לכתוב במשפט GROUP BY יותר מעמודה אחת.

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

SELECT      department_id , job_id , AVG(salary)    

 FROM        employees 

 WHERE       department_id IN (50, 80, 90)             

 GROUP BY    department_id , job_id 

לא ניתן לכתוב במשפט WHERE פונקציות קבוצה.

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

המחלקות אשר הממוצע שלהם גבוה מ- 5000 :

 SELECT      department_id , AVG(salary)    

 FROM        employees 

 WHERE       AVG(salary) > 5000 

 GROUP BY    department_id    

(שגיאה)  

משפט ה HAVING

כדי לסנן תוצאות על סמך פונקציות קבוצה אנו נשתמש במשפט HAVING.

SELECT       שם_עמודה   , פונקציית_קבוצה(שם_עמודה)

FROM           שם_טבלה

WHERE          תנאי

GROUP BY      שם_עמודה

HAVING         תנאי_על_פונקציות_קבוצה

 

המחלקות אשר הממוצע שלהן גבוה מ- 5000 :

SELECT      department_id , AVG(salary)    

 FROM        employees 

 GROUP BY    department_id    

 HAVING      AVG(salary) > 5000 

ניתן לשלב בין משפט HAVING למשפט WHERE.

מתוך המחלקות 80, 50, 90, המחלקות אשר הממוצע שלהן גבוה מ- 5000 :

SELECT      department_id , AVG(salary)    

 FROM        employees 

 WHERE       department_id IN (50, 80, 90) 

 GROUP BY    department_id    

 HAVING      AVG(salary) > 5000 

ניתן לבצע סינון על סמך פונקציית קבוצה אחרת מזו אשר מופיעה במשפט SELECT:

 SELECT      department_id , AVG(salary)    

 FROM        employees 

 WHERE       department_id IN (50, 80, 90) 

 GROUP BY    department_id    

 HAVING      MAX(salary) > 5000 

קינון פונקציות קבוצה (Nesting)

פונקציות קבוצה ניתנות לקינון עד שתי רמות.

מבין ממוצעי השכר במחלקות השונות, ממוצע השכר הגבוה ביותר :

 SELECT     MAX(AVG(salary))   

 FROM       employees 

 GROUP BY   department_id