SELECT : שימוש בפונקציות צבירה

SELECT: שימוש בפונקציות צבירה Using Aggregate Functions in SQL Queries

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

רשימה של פונקציות צבירה • בכל DBMS קיימות הרבה פונקציות צבירה. • כאן תמצאו פונקציות צבירה הקיימות ב-ORACLE • דוגמאות של פונקציות צבירה: • MAX = הערך הגבוה ביותר • MIN = הערך הנמוך ביותר • SUM = סכום הערכים • AVG = ממוצע הערכים • COUNT = מספר הערכים (אפילו לא שונים) • נלמד להשתמש בפונקציות צבירה דרך דוגמאות

דוגמה 1: לכתוב שאילתה המציגה את ממוצע הציונים של תלמיד 105 • כדי להתחיל, ניראה מה הם הציונים של תלמיד 105: SELECT grade FROM Grades WHERE student_id=105; • השאילתה הנדרשת היא: SELECT AVG(grade) FROM Grades WHERE student_id=105;

הבנה יותר עמוקה בדוגמה 1: לכתוב שאילתה המציגה את ממוצע הציונים של תלמיד 105 • השאילתה הנדרשת היא: SELECT AVG(grade) FROM Grades WHERE student_id=105; • השאילתה מתמצתת את הטבלה לרשומה אחת. פונקציית צבירה • נותנת הוראה לתמצת • אומרת איך לתמצת אטריבוט מסוים לערך אחד • ננסה להריץ שאילתה הבאה: SELECT student_id, AVG(grade) FROM Grades WHERE student_id=105; • סיבת השגיאה: DBMS לא יודעת איזה מספר סטודנט להציג

דוגמה 2: לכתוב שאילתה המציגה את הציון המינימאלי והמקסימאלי שהושגו SELECT MIN(grade), MAX(grade) FROM Grades;

דוגמה 3: לכתוב שאילתה המציגה את מספר הציונים שתלמידים קיבלו בקורסים SELECTCOUNT(student_id) FROM Grades; • בעצם אנחנו רוצים לספור רשומות. אפשר לעשות את זה גם בדרך הבאה: SELECTCOUNT(*) FROM Grades;

דוגמה 4: לכתוב שאילתה המציגה את מספר התלמידים שקיבלו ציונים בקורסים • האם הפתרון לשאלה הקודמת מתאימה? SELECTCOUNT(student_id) FROM Grades; • לא! כי אנחנו יכולים לספור אותו סטודנט כמה פעמים! SELECTCOUNT(DISTINCTstudent_id) FROM Grades; • הערה חשובה: זה לא אותו דבר כמו השימוש ב-DISTINCT הידוע לנו: SELECTDISTINCTCOUNT(student_id) FROM Grades;

דוגמה 5: לכתוב שאילתה המציגה, עבור כל סטודנט, את המספר שלו ביחד עם ממוצע הציונים • כדי להתחיל, ניראה ציוני סטודנטים ממוינים לפי מס' סטודנט: • SELECT student_id, grade • FROM Grades • ORDER BY student_id • אנחנו צריכים לחשב ממוצע של ציונים בכל קבוצה של רשומות עם אותו מספר סטודנט • SELECT student_id, AVG(grade) • FROM Grades • GROUP BY student_id;

SELECTמבנה בסיסי של 4 סכמה של טבלת פלט SELECT AVG)A1(, MIN)A7(, A3, A4 FROM T1, T2, T3 WHERE תנאים GROUPBYA2, A3, A4 טבלאות קלט 1 לשלוף נתונים לפי תנאים אלו 2 לצבור על אטריבוטים אלו 3 • האם שאלתה הבאה מתאימה למבנה זה? SELECT student_id, course_name, AVG(grade) FROM Grades GROUP BY student_id; • הבנת השגיאה: • עבור student_id מסוים יכולים להיות כמה רשומות עם שמות שונים של קורסים • DBMS לא יודעת מאיזו רשומה לקחת שם קורס!

דוגמה 6: לכתוב שאילתה המציגה, עבור כל סטודנט, את השם שלו ביחד עם ממוצע הציונים • נצטרך לקחת בחשבון שייתכנו שני סטודנטים עם אותו שם SELECT S.name, ROUND(AVG(G.grade), 2) FROM Grades G, Students S WHERE G.student_id=S.student_id GROUP BY S.student_id, S.name

דוגמה 7: לכתוב שאילתה המציגה, עבור כל סטודנט עם ממוצע הציונים פחות מ-75, מס' שלו והציון הכי גבוה SELECT student_id, MAX(grade) FROM Grades GROUP BY student_id HAVING AVG(grade)<75; • הגענו למבנה הבאה של SELECT: SELECT AVG)A1(, MIN)A7(, A3, A4 FROM T1, T2, T3 WHERE תנאים GROUPBYA2,A3, A4 HAVINGMAX(A2)>5 OR COUNT(A7)<100 5 סכמה של טבלת פלט 1 טבלאות קלט 2 לשלוף נתונים לפי תנאים אלו 3 לצבור על אטריבוטים אלו לשלוף נתונים לפי תנאים אלו המשתמשים בפונקציות צבירה 4

SELECT : שימוש בפונקציות צבירה