סיכום נתונים באמצעות GROUP BY

הושלם

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

שימוש במשפט GROUP BY

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

כדי לקבץ שורות, ציין רכיבים אחד או יותר במשפט GROUP BY:

GROUP BY <value1> [, <value2>, …]

GROUP BY יוצר קבוצות וממקם שורות בכל קבוצה כפי שנקבע על-ידי הרכיבים שצוינו במשפט.

לדוגמה, השאילתה הבאה תגרום לקבוצה של שורות מקובצות, שורה אחת לכל CustomerID בטבלה Sales.SalesOrderHeader . דרך נוספת לתסתכל בתהליך GROUP BY היא שכל השורות בעלות אותו ערך עבור CustomerID יוחזרו יחד בשורת תוצאה בודדת.

SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

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

SELECT DISTINCT CustomerID
FROM Sales.SalesOrderHeader

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

אז מה ההבדל בין כתיבת השאילתה באמצעות GROUP BY או DISTINCT? אם כל מה שברצונך לדעת הוא הערכים הייחודיים עבור CustomerID, אין הבדל. עם זאת, באמצעות GROUP BY, נוכל להוסיף רכיבים אחרים לרשימה SELECT שנצברים לאחר מכן עבור כל קבוצה.

פונקציית הצבירה הפשוטה ביותר היא COUNT(*). השאילתה הבאה לוקחת את 830 שורות המקור המקוריות מ- CustomerID ומקבבץ אותן ל- 89 קבוצות, בהתבסס על ערכי CustomerID . כל ערך CustomerID ייחודי יוצר שורת פלט אחת בשאילתת GROUP BY

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

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

מזהה לקוח

מספר הזמנה

1234

3

1005

1

שים לב כי GROUP BY אינה מבטיחה את סדר התוצאות. לעתים קרובות, כתוצאה מאופן ביצוע פעולת הקיבוץ על-ידי מעבד השאילתות, התוצאות מוחזרות בסדר של ערכי הקבוצה. עם זאת, אין להשתמש באופן פעולה זה. אם אתה זקוק למיון התוצאות, עליך לכלול פסוקית ORDER באופן מפורש:

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY CustomerID;

הפעם, התוצאות מוחזרות בסדר שצוין:

מזהה לקוח

מספר הזמנה

1005

1

1234

3

המשפטים במשפט SELECT מוחלים בסדר הבא:

1. מ
2. איפה
3. קבץ לפי
4. לאחר
5. בחירה
6. סדר לפי

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

SELECT CustomerID AS Customer,
       COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY Customer
ORDER BY Customer;

עם זאת, השאילתה הבאה תצליח, קיבוץ ומיון של התוצאות לפי מזהה הלקוח.

SELECT CustomerID AS Customer,
       COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY Customer;

פתרון בעיות של שגיאות GROUP BY

מכשול נפוץ להתקלות בשימוש ב- GROUP BY במשפטי SELECT הוא להבין מדוע מופיע סוג הודעת השגיאה הבא:

Msg 8120, רמה 16, מצב 1, שורה 2 <עמודה column_name> אינה חוקית ברשימת בחירה מאחר שהיא אינה כלול בפונקציית צבירה או במשפט GROUP BY.

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

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

השאילתה הבאה תחזיר שגיאה מכיוון ש- PurchaseOrderNumber אינו חלק מה- GROUP BY, ולא נעשה בו שימוש עם פונקציית צבירה.

SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

שאילתה זו מחזירה את השגיאה:

Msg 8120, Level 16, State 1, Line 1
Column 'Sales.SalesOrderHeader.PurchaseOrderNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

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

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

SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, PurchaseOrderNumber;

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