קיבוץ מידע באמצעות GROUP BY, HAVING

קיבוץ נתונים באמצעות GROUP BY

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

משפט GROUP BY מארגן נתונים לקבוצות בעלות אותו ערך בעמודה אחת או יותר.

תחביר:

SELECT column, AGG_FUNCTION(other_column)
FROM table
GROUP BY column;

לפי תחביר זה, אנו מקבצים את הנתונים לפי השדה column, כלומר, לכל ערך של column ניתן ערך ייחודי יחיד. קיבוץ הנתונים נעשה באמצעות הפונקציה AGG_FUNCTION שעושה עיבוד על השדה other_column.

דוגמה:

SELECT country, COUNT(*) AS total_users
FROM users
GROUP BY country;

בדוגמה זו, אנו סורקים את כל הרשומות בטבלה users המייצגת משתמשים באתר, ומקבצים אותן לפי country. לכל ערך של השדה country אנו קובעים ערך שמבוסס על הפונקציה COUNT. למעשה, הפונקציה COUNT סופרת כמה תוצאות קיימות, ולכן, השאילתה תחזיר כמה תוצאות קיימות (יוחזר כערך בשם total_users) לכל country. תוצאה אפשרית היא: italy - 15, israel - 30, spain - 22. משמעות התוצאה היא שיש בטבלה users בסה"כ 67 רשומות: 15 רשומות עם הערך italy בשדה country, עוד 30 רשומות עם הערך israel ועוד 22 רשומות עם הערך spain.

קיבוץ יכול להתבצע על יותר משדה יחיד.

דוגמה:

SELECT user_id, status, COUNT(*) AS orders_count
FROM orders
GROUP BY user_id, status;

בדוגמה זו, קיבצנו את הנתונים מהטבלה orders המייצגת הזמנות באתר לפי user_id ולפי status, כאשר פונקציית הקיבוץ היא שוב COUNT (ספירת כמות הרשומות). השאילתה תחזיר כמה תוצאות קיימות (יוחזר כערך orders_count) לשילובים השונים של user_id ושל status.

ניתן לבצע קיבוץ גם על ביטוי שהגדרנו.

דוגמה:

SELECT YEAR(order_date) AS order_year, COUNT(*) AS total_orders
FROM orders
GROUP BY order_year;

בדוגמה זו, ביצענו קיבוץ לפי הערך order_year שהגדרנו להיות שם השדה שמחזיק את ערך הביטוי YEAR(order_date), כלומר, את השנה מהתאריך.

ניתן להוסיף תנאים בתוך ביטוי הקיבוץ, באמצעות שימוש ב CASE WHEN.

דוגמה:

SELECT
  country,
  COUNT(*) AS total,
  SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) AS active_users
FROM users
GROUP BY country;

בדוגמה זו, אנו מקבצים לפי country. השאילתה מחזירה 3 ערכים: ערך השדה country, שדה בשם total המחזיק את כמות הרשומות ושדה בשם active_users שערכו הוא הסכום (הפונקציה SUM) של הביטוי CASE WHEN. הביטוי CASE WHEN בודק האם is_active שווה ל-1: אם כן, מוחזר הערך 1, ואם לא, מוחזר הערך 0. למעשה, הפונקציה SUM סוכמת כמה רשומות כוללות את הערך 1 בשדה is_active (כמות המשתמשים המוגדרים כפעילים).

פונקציות קיבוץ נפוצות

להלן רשימה של פונקציות קיבוץ נפוצות:

• COUNT - מחזירה את מספר הרשומות.
• SUM - מחזירה את סכום הערכים.
• AVG - מחזירה את ממוצע הערכים.
• STDDEV - מחזירה את סטיית התקן (המרחק מהממוצע) של הערכים.
• MAX - מחזירה את הערך המקסימלי מהרשומות.
• MIN - מחזירה את הערך המינימלי מהרשומות.

דוגמה:

נניח שקיימת לנו טבלה בשם orders (רשימת הזמנות) במבנה הבא:

orders (
    id INT,
    user_id INT,
    product_name VARCHAR(255),
    quantity INT,
    price DECIMAL(10, 2),
    order_date DATE
)

נבצע את השאילתה להלן:

SELECT
    COUNT(*) AS total_orders,
    SUM(quantity) AS total_items_sold,
    AVG(price) AS average_price,
    MAX(price) AS highest_price,
    MIN(price) AS lowest_price,
    STDDEV(price) AS price_std_dev
FROM orders;

בדוגמה זו, השאילתה מחזירה את כמות הרשומות בשדה total_orders, את כמות המוצרים שהוזמנו בשדה total_items_sold (סוכמת את הערך quantity לכל הזמנה), את המחיר הממוצע בשדה avarage_price, את המחיר המקסימלי בשדה highest_price, את המחיר המינימלי בשדה lowest_price ואת סטיית התקן בשדה price_std_dev.

סינון תוצאות באמצעות HAVING

ניתן לבצע סינון של התוצאות באמצעות הוספת החלק HAVING. נבדיל בין WHERE לבין HAVING: החלק WHERE מסנן את התוצאות לפני קיבוץ הנתונים, בעוד החלק HAVING מסנן את התוצאות אחרי קיבוץ הנתונים.

דוגמה:

SELECT country, COUNT(*) AS total
FROM users
GROUP BY country
HAVING total > 20;

בדוגמה זו, כמו קודם, אנו מקבצים את הנתונים לפי country, ולכל ערך של country קובעים כמה רשומות קיימות (בשדה total). עם זאת, השינוי הפעם הוא שאנו מציגים רק תוצאות בהן total גדול ממש מ-20. אם נניח שמדובר באותה טבלת users כמו בדוגמאות הקודמות (בהן הוחזר italy - 15, israel - 30, spain - 22), הפעם לא תוחזר רשומה עבור המדינה italy, מכיוון שעבורה יש רק 15 תוצאות (פחות מ-20).

ניתן לשלב HAVING עם WHERE.

דוגמה:

SELECT country, COUNT(*) AS active_users
FROM users
WHERE is_active = 1
GROUP BY country
HAVING active_users > 5;

בדוגמה זו, השאילתה מחזירה לכל מדינה (country) כמה משתמשים פעילים בה (רשומות בהן השדה is_active הוא עם הערך 1). יוחזרו רק תוצאות בהן כמות הרשומות גדולה ממש מ-5. נשים לב: הסינון is_active = 1 נקבע בחלק ה-WHERE, מכיוון שהוא מחושב על כלל הרשומות בטבלה, עוד בטרם הקיבוץ. הסינון active_users > 5 נקבע בחלק ה-HAVING, מכיוון שהוא מחושב רק לאחר הקיבוץ של הנתונים לפי country.

פונקציית הקיבוץ GROUP_CONCAT

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

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

תחביר:

SELECT GROUP_CONCAT(column_name)
FROM table_name
GROUP BY another_column;

דוגמה:

נניח שלפנינו הטבלה orders להלן.

נפעיל את השאילתה להלן.

SELECT user_id, GROUP_CONCAT(product_name) AS products
FROM orders
GROUP BY user_id;

בדוגמה זו, השאילתה מקבצת את הנתונים לפי user_id ומחזירה את ערך השדה user_id (מזהה המשתמש), ושדה בשם products שמחזיק רשימת מוצרים (רצף של ערכי product_name מופרדים בפסיקים).

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

ההפרדה בין הערכים במחרוזות המוחזרות נעשית כברירת מחדל באמצעות פסיק (,). ניתן לקבוע מפריד אחר (במקום הפסיק) שיפריד בין הערכים במחרוזות, באמצעות שימוש ב-SEPARATOR.

דוגמה:

SELECT user_id, GROUP_CONCAT(product_name SEPARATOR ' | ') AS products
FROM orders
GROUP BY user_id;

בדוגמה זו, הערכים של product_name יופרדו זה מזה באמצעות תו רווח, הסימן | ושוב תו רווח.

השאילתה תחזיר הפעם את הרשומות הבאות:

ניתן למיין את סדר הערכים המוחזרים ברשימה, באמצעות שימוש ב-ORDER BY.

דוגמה:

GROUP_CONCAT(product_name ORDER BY product_name ASC)

בדוגמה זו, הערכים של product_name יוחזרו בסדר א'-ב' עולה.

ניתן להמנע מכפילויות בערכים המוחזרים ברשימה, באמצעות שימוש ב-DISTINCT.

דוגמה:

GROUP_CONCAT(DISTINCT product_name)

בדוגמה זו, הערכים של product_name לא יכללו כפילויות (כל ערך יוצג פעם אחת בלבד).

ההרחבה ROLLUP

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

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

תחביר:

SELECT column1, column2, AGGREGATE_FUNCTION(column3)
FROM table
GROUP BY column1, column2 WITH ROLLUP;

דוגמה:

נניח שלפנינו הטבלה orders להלן.

נפעיל את השאילתה להלן.

SELECT country, city, SUM(total_amount) AS total_sales
FROM orders
GROUP BY country, city WITH ROLLUP;

בדוגמה זו, השאילתה מחזירה לכל country ולכל city את סכום הערכים total_amount. כלומר, לכל מדינה ועיר היא מחזירה כמה מכירות היו.

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

ההרחבה ROLLUP למעשה הוסיפה את שורת הביניים השלישית (שמסכמת את כמות המכירות במדינה Canada), שורת הביניים השישית (שמסכמת את כמות המכירות במדינה USA) ושורת הביניים השביעית (שמסכמת את כלל המכירות בכלל המדינות). נשים לב לערכי ה-NULL (ערך שאינו מוגדר) שהתווספו בשדה ה-city (בשורות שסוכמות כל מדינה) ובשדה ה-country (בשורת הסיכום הסופית).

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

נשפר את השאילתה האחרונה שהפעלנו באופן הבא:

SELECT
  IFNULL(country, 'ALL COUNTRIES') AS country,
  IFNULL(city, 'TOTAL') AS city,
  SUM(total_amount) AS total_sales
FROM orders
GROUP BY country, city WITH ROLLUP;

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

משפט PARTITION BY וההבדל בינו לבין GROUP BY

PARTITION BY הוא משפט המשמש "פונקציות חלון" (window functions) ב-SQL. פונקציות חלון מבצעות חישובים על פני קבוצת שורות הקשורות לשורה הנוכחית, מבלי לקבץ את קבוצת השורות כמו שעושה GROUP BY.

משפט PARTITION BY מחלק את קבוצת התוצאות למחיצות (partitions), ולאחר מכן מחיל את פונקציית החלון באופן עצמאי על כל מחיצה.

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

תחביר:

SELECT column1, column2,
       WINDOW_FUNCTION(...) OVER (PARTITION BY column3)
FROM table;

להלן רשימה של פונקציות חלון נפוצות:

• ROW_NUMBER - קובעת מספר שורה ייחודי לכל שורה במחיצה.
• RANK - מדרגת את השורות עם קפיצות.
• DENSE_RANK - מדרגת את השורות בצפיפות (ללא קפיצות).
• SUM - מחשבת את הסכום המצטבר במחיצה.
• AVG - מחשבת את הממוצע המצטבר במחיצה.
• LEAD - משווה את השורה לשורה הבאה.
• LAG - משווה את השורה לשורה הקודמת.
• FIRST_VALUE - מחזירה את הערך הראשון במחיצה.
• LAST_VALUE - מחזירה את הערך האחרון במחיצה.
• NTILE(n) - מחלקת את המחיצה ל-n קבוצות.

דוגמה:

נניח שלפנינו הטבלה sales להלן.

הטבלה מכילה רשימת מכירות שנעשו, ולכל מכירה מזהה ייחודי (id), אזור (region), איש מכירות (salesperson), תאריך מכירה (sale_date) וסכום מכירה (amount). נבחן הפעלת שאילתות שונות על הטבלה הזו.

נפעיל את השאילתה הבאה:

SELECT region, salesperson, sale_date, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_date) AS row_num
FROM sales;

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

תשובת השאילתה:

נשים לב שהמספרים בשדה row_num הם בכל מחיצה (כל region) בנפרד. כלומר, בשורות בהן region עם הערך North, ערך השדה row_num כולל מספור מ-1 עד 3, ובשורות בהן region עם הערך South, ערך השדה row_num כולל מספור נוסף מ-1 עד 3 (ולא המשך מיספור מ-4 עד 6).

נפעיל את השאילתה הבאה:

SELECT region, salesperson, amount,
       RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank
FROM sales;

השאילתה מבצעת חלוקה למחיצות לפי region ומיון לפי amount, ומפעילה את פונקציית החלון RANK שמדרגת את השורות עם קפיצות.

תשובת השאילתה:

למעשה, במקרה הזה, הפעלה של RANK ושל DENSE_RANK מחזירה בדיוק אותה התשובה. בשני המקרים אין קפיצות, מכיוון שסכומי המכירה שונים זה מזה. אם לדוגמה סכום המכירה של Bob היה 150 במקום 200 (כלומר סכום זהה ל-Bob ול-Alice בעסקה הראשונה שלה), אז הפעלה של RANK היתה מחזירה דירוג 1 עבור Bob וגם 1 עבור העסקה הראשונה של Alice ודירוג 3 עבור העסקה השניה של Alice (יש קפיצה: אין דירוג 2). לעומת זאת, במקרה שסכום המכירה של Bob היה 150 במקום 200, הפעלה של DENSE_RANK היתה מחזירה 1 עבור Bob וגם 1 עבור העסקה הראשונה של Alice (כמו הפעלה של RANK), אבל העסקה השניה של Alice היתה מקבלת דירוג 2 ולא 3 (בניגוד ל-RANK, ב-DENSE_RANK ערכי הדירוג רציפים ללא קפיצות).

נפעיל את השאילה הבאה:

SELECT region, salesperson, sale_date, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total
FROM sales;

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

תשובת השאילתה:

ניתן לראות, כי בכל עסקה הערך running_total מציג את סכום העסקאות המצטבר עד כה באותו האזור.

נפעיל את השאילתה הבאה:

SELECT region, salesperson, sale_date, amount,
       AVG(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_avg
FROM sales;

השאילתה מבצעת חלוקה למחיצות לפי region ומיון לפי sale_date, ומפעילה את פונקציית החלון AVG שמחשבת את הממוצע המצטבר.

תשובת השאילתה:

ניתן לראות, כי בכל עסקה הערך running_avg מציג את ממוצע העסקאות המצטבר עד כה באותו האזור.

נפעיל את השאילתה הבאה:

SELECT region, salesperson, sale_date, amount,
       LEAD(amount) OVER (PARTITION BY region ORDER BY sale_date) AS next_amount
FROM sales;

השאילתה מבצעת חלוקה למחיצות לפי region ומיון לפי sale_date, ומפעילה את פונקציית החלון LEAD שמחזירה את ערך סכום המכירה הבא.

תשובת השאילתה:

ניתן לראות, כי הערך next_amount כולל בכל רשומה את ערך ה-amount הבא באותו ה-region. הערך האחרון של next_amount הוא NULL, מכיוון שאין רשומות לאחר מכן, ולכן ערך ה-amount הבא אינו מוגדר.

נפעיל את השאילתה הבאה:

SELECT region, salesperson, sale_date, amount,
       LAG(amount) OVER (PARTITION BY region ORDER BY sale_date) AS prev_amount
FROM sales;

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

תשובת השאילתה:

ניתן לראות, כי הערך prev_amount כולל בכל רשומה את ערך ה-amount הקודם באותו ה-region. הערך הראשון של prev_amount הוא NULL, מכיוון שאין רשומות לפני הרשומה הראשונה, ולכן ערך ה-amount הקודם אינו מוגדר.

נפעיל את השאילתה הבאה:

SELECT region, salesperson, sale_date, amount,
       FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY sale_date) AS first_sale
FROM sales;

השאילתה מבצעת חלוקה למחיצות לפי region ומיון לפי sale_date, ומפעילה את פונקציית החלון FIRST_VALUE שמחזירה את הערך הראשון של סכום המכירה באותו ה-region.

תשובת השאילתה:

נשים לב, כי הערך first_sale הוא אחיד בכל region וזהה תמיד לערך ה-amount הראשון. אם היינו מחליפים את פונקציית החלון ב-LAST_VALUE, הערך שהיה מוחזר היה ערך ה-amount האחרון בכל region.