Group Function And Grouping Sets

פונקציות מספריות:

• Avg – מחזירה את ממוצע כל הערכים בקבוצה שהם לא NULL .
• Sum – סוכמת את מספר הפריטים בקבוצה.

פונקציות מספריות, תאריכיות ומחרוזתיות:

• Count – מחזירה את מספר הפריטים בקבוצה.
• Max – מחזירה את הערך הגבוה ביותר מבין הקבוצה.
• Min – מחזירה את הערך הנמוך ביותר מבין הקבוצה.

דוגמאות

ממוצע מחירים של מוצרים

SELECT avg(UnitPrice) 
FROM Products

מספר העובדים שעונים לתנאי מסויים

SELECT count(*) 
FROM Employees 
WHERE city='London'

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

SELECT count(Distinct CustomerId) as "num of customers" 
FROM Orders

Group By

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

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

SELECT count(*) as "num of employees", city 
FROM Employees 
GROUP BY City

Having

לא ניתן להפעיל תנאי במשפט ה WHERE על ערך המוחזר כתוצאה מ- Group Function ולכן, כאשר רוצים לבצע התניה על הערך שמחזירה הפונקציה הקבוצתית יש לציין את התנאי במשפט ה HAVING.

לדוגמא: הצגת שמות הערים שגרים בהן לפחות שני עובדים + ציון מספר העובדים ע"פ הנתונים מטבלת Employee

SELECT count(*) AS "num of employee", city 
FROM Employees 
GROUP BY City 
HAVING count(*)>=2

דוגמא מסכמת:

הצגת ה- id של כל הלקוחות שביצעו יותר מ- 5 הזמנות משנת 1998 ואילך + ציון מספר ההזמנות. הנתונים המוחזרים יוצגו במיון אלפא-ביתי.

SELECT customerid,count(*) as "num of order" 
FROM orders 
WHERE OrderDate>='01/01/1998' 
GROUP BY CustomerID 
HAVING count(*)>=5 
ORDER BY customerid

GROUPING SETS

ל- GROUP BY מספר הרחבות אשר מאפשרות ליצור סיכומי ביניים בנוסף לחישובים של פונקציות הקבוצה.

Grouping set

סיכומי ביניים עפ"י קבוצות שונות.

מבנה

SELECT <column list with aggregate(s)>
FROM <source>
GROUP BY
GROUPING SETS(
(<column_name>),--one or more columns
(<column_name>),--one or more columns
() -- empty parentheses if aggregating all rows
);

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

SELECT EmployeeID, customerid, year(orderdate), count(*) 
FROM orders 
WHERE customerid like 'b%' 
GROUP BY GROUPING SETS (EmployeeID, customerid, year(orderdate)) 
ORDER BY employeeid

SELECT EmployeeID, customerid, year(orderdate), count(*) 
FROM orders 
WHERE customerid like 'b%' 
GROUP BY GROUPING SETS ((customerid, year(orderdate)), (employeeid, year(orderdate)),()) 
order by employeeid

CUBE AND ROLLUP

כמו פקודת ה- Grouping sets , גם פקודות ה- Rollup & cube מאפשרות ליצור סיכומי ביניים לקבוצות מרובות בשילוב עם פונקציות קבוצה. בשונה מ- Grouping sets אין צורך בפקודות אלה לקבוע את סדר הקבוצות.

Rollup – יוצר קבוצות של סיכומי ביניים עפ"י סדר הנחת העמודות בשורת ה- Group by .

דוגמא:

SELECT categoryid, supplierid, count(*) 
FROM products 
GROUP BY ROLLUP (categoryid, supplierid)

Cube – יוצרת שורת סיכומי ביניים לכל שילוב של קבוצות המוגדרות בשורת ה- Group by . דוגמא:

SELECT categoryid, supplierid, count(*) 
FROM products 
GROUP BY CUBE (categoryid, supplierid)

תרגול Group Function – שאלות

1. הצג מטבלת Employees את שם המשפחה הקטן ביותר מבחינה אלפאבתית
2. הצג מטבלת Employees את השם הפרטי הגדול ביותר מבחינה אלפאבתית
3. הצג מטבלת Employees את מספר הרשומות שיש בטבלה
4. הצג מטבלת Employees את מספר הרשומות שיש בעמודת Region (לא כולל NULL)
5. הצג מטבלת Products את ממוצע המחיר ליחידה
6. הצג מטבלת Products את המחיר, UnitPrice, הגבוה ביותר, ואת המחיר הממוצע. תן שמות מתאימים לעמודות
7. הצג מטבלת Employees את תאריך יום ההולדת הנמוך ביותר ותאריך יום ההולדת הגבוה ביותר. יש להציג את התאריכים בפורמט 113 תן שמות מתאימים לעמודות.
8. הצג את מספר הלקוחות השונים הקיימים בטבלת Customers, תן שם מתאים לעמודה
9. הצג את מספר הלקוחות השונים הקיימים בטבלת Orders, תן שם מתאים לעמודה. שים לב, הפעם יתכן שלקוח מסויים ביצע יותר מהזמנה אחת.
10. הצג מטבלת Products את המחיר UnitPrice המקסימאלי, המינימאלי, והממוצע למוצר עבור כל קטגוריה CategoryID. תן שמות מתאימים לעמודות.
11. הצג מטבלת Products את המחיר הגבוה ביותר של המוצר לפי כל מספר ספק, מיין לפי מספר הספק.
12. הצג מטבלת Products את ממוצע היחידות במלאי לפי כל מספר ספק מיין לפי ממוצע היחידות. סדר יורד.
13. הצג מטבלת Customers את מספר הלקוחות לפי המדינה , ולפי העיר.
14. הצג מטבלת Products את המחיר הממוצע של המוצרים לפי כל קטגוריה , עבור המוצרים שהמחיר שלהם גדול מ 4.
15. הצג מטבלת Customers את מספר הלקוחות לפי עיר , עבור הלקוחות אשר גרים ב London, Berlin, Rio de Janeiro או Paris.
16. הצג מתוך טבלת Products את המחיר הגבוה ביותר, המחיר הנמוך ביותר, המחיר הממוצע, וכמות המוצרים, לפי כל מספר קטגוריה ומספר ספק.
17. הצג מטבלת Products את המחיר המקסימלי לפי כל קטגוריה, עבור המוצרים אשר המחיר המקסימלי שלהם גדול מ 4.
18. הצג מטבלת Products את המחיר הממוצע לפי כל ספק, עבור המוצרים אשר המחיר הממוצע שלהם גדול מ 4.
19. הצג מטבלת Products את סך כל הפריטים שהוזמנו, UnitsOnOrder, ואת סך כל היחידות במלאי, UnitsInStock, עבור כל קטגוריה. בנוסף, הצג את שם הקטגוריה מטבלת Categories. יש לכלול בתוצאות רק קטגוריות שבשמן מופיעה האות C ורק מוצרים שסך כל היחידות המוזמנות מהם גדול מ 10. יש למיין את התוצאות ע"פ שם הקטגוריה.
20. הצג מטבלת Customers את האזור, העיר ומספר הלקוחות השייכים לאותו האזור עבור אותן הערים שקיימות בשמן האותיות NULL וגם האזור אינו 'L' או 'M'. יש לכלול בתוצאות רק אזורים שמספר הלקוחות בהם גדול או שווה ל 2.
21. הצג את העמודות הבאות: שם עובד LastName מטבלת Employees, סך כל ההזמנות שהעובד ביצע מטבלת Orders, התאריך האחרון שהעובד ביצע הזמנה OrderDate( מטבלת Orders. תן שמות מתאימים לעמודות. יש לכלול בתוצאות רק עובדים שביצעו מעל 100 הזמנות.

תרגול Group Function – תשובות

-- ex1
select
    min(lastname)
from Employees

-- ex2
select
    max(firstName)
from Employees

-- ex3
select count(*) as CountRows
FROM Employees 

-- ex4
select count(Region) as CountRegion
FROM Employees 
where Region is not null

-- ex5
SELECT avg(UnitPrice) 
FROM Products

-- ex6
SELECT 
    avg(UnitPrice) as Average,
    max(UnitPrice) as Maximum
FROM Products

-- ex7
select 
    convert(varchar, min(BirthDate), 113) as minBirthDate,
    convert(varchar, max(BirthDate), 113) as maxBirthDate
from Employees

-- ex8
select count(Distinct CustomerId) as countCustomers
from Customers

-- ex9
select count(Distinct CustomerId) as countCustomers
from Orders

-- ex10
select
    max(UnitPrice) as [max price],
    min(UnitPrice) as [min price],
    avg(UnitPrice) as [avg price],
    CategoryID
from products
GROUP BY CategoryID

-- ex11
select
    SupplierID,
    max(UnitPrice) as [max price],
    min(UnitPrice) as [min price]
from products
GROUP BY SupplierID
order by SupplierID desc

-- ex12
select
    SupplierID,
    avg(UnitsInStock) as [UnitsInStock avg]
from products
GROUP BY SupplierID
order by [UnitsInStock avg] desc

-- ex13
select
    count(Distinct CustomerId),
    Country,
    City
from Customers
GROUP BY Country, City

-- ex14
select 
    CategoryID,
    avg(UnitPrice)
from Products
where UnitPrice > 40
GROUP BY CategoryID

-- ex15
select
    count(Distinct CustomerId),
    City
from Customers
where city = 'Berlin' or city = 'London' or 
    city = 'Paris' or city = 'Rio de Janeiro'
GROUP BY City

-- ex 16
select 
    CategoryID,
    SupplierID,
    max(UnitPrice) as [max price],
    min(UnitPrice) as [min price],
    avg(UnitPrice) as [avg price],
    count(UnitPrice) as [count items]
from Products
GROUP BY CategoryID, SupplierID

-- ex17
select
    CategoryID,
    max(UnitPrice) as maxprice
from Products
GROUP BY CategoryID
having max(UnitPrice) > 40

-- ex18
select
    SupplierID,
    avg(UnitPrice) as avgprice
from Products
GROUP BY SupplierID
having avg(UnitPrice) > 40

-- ex19
select
    c.CategoryName,
    sum(p.UnitsOnOrder) as [UnitsOnOrder sum],
    sum(p.UnitsInStock) as [UnitsInStock sum]
FROM products p JOIN categories c
ON c.CategoryID = p.CategoryID
WHERE c. categoryname LIKE '%c%'
GROUP BY c.CategoryName
HAVING sum(p.UnitsOnOrder) > 100
ORDER BY c.categoryName

-- ex20
select
    Region,
    City,
    count(Distinct CustomerId) as [count customers]
from Customers
WHERE (City LIKE '%m%' or City LIKE '%l%') and Region is not null
group by Region, City
having count(Distinct CustomerId) >= 2

-- ex21
select
    e.LastName,
    count(o.EmployeeID) as [orders for emp],
    max(o.OrderDate) as [last date]
from Employees e inner join Orders o
on e.EmployeeID = o.EmployeeID
group by e.LastName
having count(o.EmployeeID) > 100