GROUP BY vs. OVER

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

select od.OrderID, SUM(od.Quantity) as 'Sum of quantity' from [Order Details] as od
group by(od.OrderID)

כמובן שבטבלה orderDetails יש הרבה יותר שורות, אבל group by מצמצם את השורות ומותיר שורה אחת בשביל כל הזמנה  (orderID). במקרה הזה זה מצוין כי כל מה שרצינו זה לדעת זה את הכמות של הפריטים בכל הזמנה.

אבל מה אם נרצה גם את הפרטים של כל מוצר שנקנה בהזמנה זו וגם את המידע המחושב של כל ההזמנה? בדיוק בשביל זה יש לנו את OVER. הביטוי OVER מתווסף לכל מידע חישובי כמו SUM, AVG וכדומה ללא צורך ב- group by, אך מכיוון שללא group by אין לנו קבוצה (בכדי לעשות חישוב אנו זקוקים להגדיר קבוצה כי אם לא נעשה זאת החישוב ייעשה על כל השורות יחד. SUM יחזיר לנו סה"כ כל כל ההזמנות יחד, ואנחנו רוצים סה"כ של פריטים של כל הזמנה בנפרד…), נצטרך להגדיר לחישוב (במקרה זה SUM) לאיזו קבוצה לבצע את החישוב.

נקח את הדוגמא שלמעלה: השדה שעליו נעשה החישוב הוא Quantity, התוצאה הרצויה היא סה"כ הפריטים של הזמנה בודדת, אם כן השדה שלפיו נעשה את החישוב הוא… OrderID.

הנה התחביר:

SUM(od.Quantity) OVER(PARTITION BY od.OrderID)

והנה דוגמא מלאה לשימוש ב- OVER:

select od.OrderID , od.ProductID , od.UnitPrice ,od.Quantity, od.Discount,
SUM(od.Quantity) OVER(PARTITION BY od.OrderID) as 'Sum of quantity' ,
AVG(od.Discount) OVER(PARTITION BY od.OrderID) as 'Avg of Discount'
from [Order Details] as od

Read more: יוסלה גולדברג

Posted via email from Jasper-net