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

הושלם

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

עבודה עם פונקציות צבירה

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

פונקציות צבירה רבות מסופקות ב- SQL Server. בסעיף זה, נבחן את הפונקציות הנפוצות ביותר, כגון SUM, MIN, MAX, AVG ו- COUNT.

בעת עבודה עם פונקציות צבירה, עליך לשקול את הנקודות הבאות:

• פונקציות צבירה מחזירות ערך יחיד (סקארי) ובאפשרותך להשתמש בהן במשפטי SELECT כמעט בכל מקום שבו ניתן להשתמש בערך בודד. לדוגמה, ניתן להשתמש בפונקציות אלה במשפטי SELECT, HAVING ו- ORDER BY. עם זאת, לא ניתן להשתמש בהן במשפט WHERE.
• פונקציות צבירה מתעלמות מ- NULLs, למעט בעת שימוש ב- COUNT(*).
• פונקציות צבירה ברשימת SELECT אינן כוללות כותרת עמודה אלא אם תספק כינוי באמצעות AS.
• פונקציות צבירה ברשימת SELECT פועלות בכל השורות שהועברו לפעולת SELECT. אם אין משפט GROUP BY, כל השורות הממלאות כל מסנן במשפט WHERE יסוכם. נושא זה יסייע לך לקבל מידע נוסף על GROUP BY בנושא הבא.
• אלא אם כן אתה משתמש ב- GROUP BY, אין לשלב פונקציות צבירה עם עמודות שלא נכללו בפונקציות באותה רשימת SELECT.

כדי להרחיב מעבר לפונקציות המוכללות, SQL Server מספק מנגנון עבור פונקציות צבירה המוגדרות על-ידי המשתמש באמצעות זמן הריצה של השפה הנפוצה של .NET (CLR). נושא זה אינו נכלל במודול זה.

פונקציות צבירה מוכללות

כפי שצוין, Transact-SQL פונקציות צבירה מוכללות רבות. פונקציות נפוצות כוללות:

שם פונקציה

תחביר

תיאור

סכום

SUM(expression)

סיכום כל הערכים המספריים שאינם NULL בעמודה.

ממוצע

AVG(expression)

חישוב ממוצע של כל הערכים המספריים שאינם NULL בעמודה (סכום/ספירה).

דקות

MIN(expression)

החזרת המספר הקטן ביותר, התאריך/השעה המוקדמים ביותר או המחרוזת הראשונה (בהתאם לכללי המיון של איסוף).

מקס

MAX(expression)

החזרת המספר, התאריך/השעה העדכניים ביותר או המחרוזת האחרונה שמתרחשת (בהתאם לכללי מיון האיסוף).

COUNT או COUNT_BIG

COUNT(*) או COUNT(expression)

באמצעות (*), ספירת כל השורות, כולל שורות עם ערכי NULL. כאשר עמודה מוגדרת כביטוי, החזרת הספירה של שורות שאינן NULL עבור עמודה זו. הפונקציה COUNT מחזירה ערך Int; COUNT_BIG מחזירה big_int.

כדי להשתמש צבירה מוכללת במשפט SELECT, שקול את הדוגמה הבאה במסד הנתונים לדוגמה של MyStore :

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

התוצאות של שאילתה זו ייראו בערך כך:

מחיר ממוצע

מחיר מינימום

מחיר מרבי

744.5952

2.2900

3578.2700

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

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;

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

שקול את השאילתה הבאה, שינסה לכלול את השדה ProductCategoryID בתוצאות המצטברות:

SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

הפעלת שאילתה זו התוצאה היא השגיאה הבאה

Msg 8120, רמה 16, מצב 1, שורה 1

העמודה 'Production.ProductCategoryID' אינה חוקית ברשימת הבחירה מאחר שהיא אינה כלול בפונקציית צבירה או במשפט GROUP BY.

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

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

שאילתה זו מחזירה את החברה הראשונה ואת החברה האחרונה לפי שם, באמצעות MIN ו- MAX:

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

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

MinCustomer

MaxCustomer

חנות אופניים

חברת אופניים צהובה

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

לדוגמה, הפונקציה הסרגלית YEAR משמשת בדוגמה הבאה כדי להחזיר רק את חלק השנה של תאריך ההזמנה, לפני הערכה של MIN ו- MAX:

SELECT MIN(YEAR(OrderDate)) AS Earliest,
       MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;

המוקדם

האחרונה

2008

2021

ניתן להשתמש בפונקציות MIN ו- MAX גם עם נתוני תאריך, כדי להחזיר את הערכים הכרונולוגיים המוקדמים ביותר והתאריך המאוחר ביותר. עם זאת, הפונקציות AVG ו- SUM יכולות לשמש רק עבור נתונים מספריים, הכוללים מספרים שלמים, כסף, ציפה ונתונים עשרוניים.

שימוש ב- DISTINCT עם פונקציות צבירה

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

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

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;

הפונקציה COUNT(<some_column>) סופרת רק כמה שורות מכילות ערך בעמודה. אם אין ערכי NULL, COUNT(<some_column>) יהיה זהה ל- COUNT(*). הפונקציה COUNT (DISTINCT <some_column>) סופרת כמה ערכים שונים קיימים בעמודה.

שימוש בפונקציות צבירה עם NULL

חשוב להיות מודע לנוכחות האפשרית של כתובות NUL בנתונים שלך, ולאופן בו NULL מקיים אינטראקציה עם רכיבי שאילתה של T-SQL, כולל פונקציית צבירה. עליך להיות מודע לכמה שיקולים:

• למעט COUNT בשימוש עם האפשרות (*), פונקציות הצבירה של T-SQL מתעלמות מ- NULLs. לדוגמה, הפונקציה SUM תוסיף ערכים שאינם NULL בלבד. כתובות NUL אינן מוערכים כאפס. הפונקציה COUNT(*) סופרת את כל השורות, ללא קשר לערך או לערך שאינו ערך בעמודה כלשהי.
• הנוכחות של כתובות NUL בעמודה עשויה להוביל לחישובים לא מדויקים עבור AVG, אשר יתווכו רק שורות מאוכלסות ויחלקו סכום זה במספר שורות שאינן NULL. ייתכן שיש הבדל בתוצאות בין AVG(<column>) לבין (SUM(<column>)/COUNT(*)).

לדוגמה, שקול את הטבלה הבאה בשם t1:

C1

מס' C2

1

אֶפֶס

2

10

3

20

4

30

5

40

6

50

שאילתה זו מדגימה את ההבדל בין האופן שבו AVG מטפל ב- NULL לבין אופן החישוב של ממוצע באמצעות עמודה מחושבת SUM/COUNT(*):

SELECT SUM(c2) AS sum_nonnulls, 
    COUNT(*) AS count_all_rows, 
    COUNT(c2) AS count_nonnulls, 
    AVG(c2) AS average, 
    (SUM(c2)/COUNT(*)) AS arith_average
FROM t1;

התוצאה תהיה:

sum_nonnulls

count_all_rows

count_nonnulls

ממוצע

arith_average

150

6

5

30

25

בערכת תוצאות זו, העמודה בעלת השם average היא הצבירה שמקבלת באופן פנימי את הסכום של 150 ומחלקת בספירת ערכים שאינם Null בעמודה c2. החישוב יהיה 150/5, או 30. העמודה הנקראת arith_average מחלקת באופן מפורש את הסכום בספירת כל השורות, כך שה בחישוב הוא 150/6, או 25.

אם עליך לסכם את כל השורות, בין אם הן NULL או לא, שקול להחליף את כתובות ה- NUL בערך אחר שפונקציית הצבירה לא תתעלם ממנו. באפשרותך להשתמש בפונקציה COALESCE למטרה זו.