פקודת Group By מאפשרת לנו לקבץ את תוצאות השאילתה שלנו לפי קבוצות מוגדרות. במדריך SQL זה נתמקד בפונקציות הקבוצה ופקודת ה Group By. להבדיל מפונקציות השורה אשר פועלות על כל שורה ושורה, פונקציות הקבוצה פועלות על קבוצות, לדוגמא :
* הקבוצה יכולה להיות כל הטבלה או תתי קבוצות מהטבלה.
פונקציות הקבוצה והערך 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 לא חייבת להופיע ב- 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.
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
