איך להשתמש ב-GROUP BY ו-ORDER BY ב-SQL

הקדמה

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

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

במדריך זה, תמיין את תוצאות השאילתות שלך ב SQL באמצעות ההצהרות GROUP BY ו־ORDER BY. תתאמן גם ביישום פונקציות כוללות והעצה WHERE בשאילות שלך כדי למיין את התוצאות עוד יותר.

דרישות מוקדמות

כדי לעקוב אחרי המדריך הזה, תצטרך מחשב הרץ מערכת לניהול מסדי נתונים רציונליים (RDBMS) שמשתמשת ב־SQL. ההוראות והדוגמאות במדריך זה אומתו באמצעות הסביבה הבאה:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL מותקן ומאובטח בשרת. עקבו אחרי המדריך שלנו איך להתקין MySQL על Ubuntu 20.04 כדי להגדיר את זה. מדריך זה מניח שגם הגדרתם משתמש MySQL לא מנהל, כפי שמופיע ב־שלב 3 של מדריך זה.

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

מתחברים ל־MySQL ומגדירים מסד נתונים דוגמתי

אם מסד הנתונים שלך מריץ על שרת מרוחק, התחבר ב־SSH לשרת מהמכונה המקומית שלך:

ssh sammy@your_server_ip

אחר כך, פתח את מסד הנתונים של MySQL, והחלף את sammy בפרטי המשתמש שלך במסד הנתונים של MySQL:

mysql -u sammy -p

צור מסד נתונים בשם movieDB:

CREATE DATABASE movieDB;

אם מסד הנתונים נוצר בהצלחה, תקבל את הפלט הבא:

Output
Query OK, 1 row affected (0.01 sec)

כדי לבחור במסד הנתונים movieDB, הרץ את ההצהרה הבאה של USE:

USE movieDB;

Output
Database changed

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

• theater_id: שומר ערכים של סוג הנתונים int לכל חדר הצגה בתיאטרון, וישמש כמפתח ראשי של הטבלה, המשמעות של כל ערך בעמודה זו תהיה זיהוי ייחודי לשורתה המקבילה.
• date: משתמש בסוג הנתונים DATE לשמירת התאריך המדויק לפי שנה, חודש ויום בו הוצג הסרט. סוג הנתונים זה עומד בפרמטרים הבאים: ארבע ספרות לשנה, ומקסימום שני ספרות לחודש וליום (YYYY-MM-DD).
• time: מייצג את הצגת הסרט המתוזמנת עם סוג הנתונים TIME לפי שעות, דקות ושניות (HH:MM:SS).
• movie_name: שומר את שם הסרט באמצעות סוג הנתונים varchar עם מקסימום 40 תווים.
• movie_genre: משתמש בסוג הנתונים varchar עם מקסימום של 30 תווים, כדי להכיל מידע על הסוג של כל סרט.
• guest_total: מציג את מספר האורחים הכולל שהשתתפו בהצגת סרט עם סוג הנתונים int.
• ticket_cost: משתמש בסוג הנתונים decimal, עם דיוק של ארבעה וסולם של אחד, המשמעות היא שערכים בעמודה זו יכולים להיות בעלי ארבעה ספרות, ושתי ספרות לימין לנקודה העשרונית. עמודה זו מייצגת את עלות הכרטיס להצגת הסרט הספציפית.

צור טבלה בשם movie_theater המכילה כל אחת מהעמודות הללו על ידי הרצת הפקודה CREATE TABLE הבאה:

CREATE TABLE movie_theater (
theater_id int, 
date DATE,
time TIME, 
movie_name varchar(40),
movie_genre varchar(30),
guest_total int,
ticket_cost decimal(4,2),
PRIMARY KEY (theater_id)
); 

לאחר מכן, הכנס נתוני דוגמה לטבלה הריקה:

INSERT INTO movie_theater
(theater_id, date, time, movie_name, movie_genre, guest_total, ticket_cost)
VALUES
(1, '2022-05-27', '10:00:00', 'Top Gun Maverick', 'Action', 131, 18.00),
(2, '2022-05-27', '10:00:00', 'Downton Abbey A New Era', 'Drama', 90, 18.00),
(3, '2022-05-27', '10:00:00', 'Men', 'Horror', 100, 18.00),
(4, '2022-05-27', '10:00:00', 'The Bad Guys', 'Animation', 83, 18.00),
(5, '2022-05-28', '09:00:00', 'Top Gun Maverick', 'Action', 112, 8.00),
(6, '2022-05-28', '09:00:00', 'Downton Abbey A New Era', 'Drama', 137, 8.00),
(7, '2022-05-28', '09:00:00', 'Men', 'Horror', 25, 8.00),
(8, '2022-05-28', '09:00:00', 'The Bad Guys', 'Animation', 142, 8.00),
(9, '2022-05-28', '05:00:00', 'Top Gun Maverick', 'Action', 150, 13.00),
(10, '2022-05-28', '05:00:00', 'Downton Abbey A New Era', 'Drama', 118, 13.00),
(11, '2022-05-28', '05:00:00', 'Men', 'Horror', 88, 13.00),
(12, '2022-05-28', '05:00:00', 'The Bad Guys', 'Animation', 130, 13.00);

Output
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

כאשר הכנסת את הנתונים, אתה מוכן להתחיל למיין תוצאות שאילתה ב- SQL.

שימוש ב- GROUP BY

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

GROUP BY מועיל להחזרת תוצאות מרובות שממוינות לפי הקבוצה(ות) שציינת, במקום סתם עמודה אחת. בנוסף, GROUP BY חייבת להופיע תמיד אחרי ההצהרה FROM וסעיף WHERE, אם בחרת להשתמש באחד. הנה דוגמה לאיך שאילתה עם GROUP BY ופונקצית כוח מובנית מאורגנת:

SELECT column_1, AGGREGATE_FUNCTION(column_2) FROM table GROUP BY column_1;

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

SELECT * FROM movie_theater;

Output
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
| theater_id | date       | time     | movie_name              | movie_genre | guest_total | ticket_cost |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
|          1 | 2022-05-27 | 10:00:00 | Top Gun Maverick        | Action      |         131 |       18.00 |
|          2 | 2022-05-27 | 10:00:00 | Downton Abbey A New Era | Drama       |          90 |       18.00 |
|          3 | 2022-05-27 | 10:00:00 | Men                     | Horror      |         100 |       18.00 |
|          4 | 2022-05-27 | 10:00:00 | The Bad Guys            | Animation   |          83 |       18.00 |
|          5 | 2022-05-28 | 09:00:00 | Top Gun Maverick        | Action      |         112 |        8.00 |
|          6 | 2022-05-28 | 09:00:00 | Downton Abbey A New Era | Drama       |         137 |        8.00 |
|          7 | 2022-05-28 | 09:00:00 | Men                     | Horror      |          25 |        8.00 |
|          8 | 2022-05-28 | 09:00:00 | The Bad Guys            | Animation   |         142 |        8.00 |
|          9 | 2022-05-28 | 05:00:00 | Top Gun Maverick        | Action      |         150 |       13.00 |
|         10 | 2022-05-28 | 05:00:00 | Downton Abbey A New Era | Drama       |         118 |       13.00 |
|         11 | 2022-05-28 | 05:00:00 | Men                     | Horror      |          88 |       13.00 |
|         12 | 2022-05-28 | 05:00:00 | The Bad Guys            | Animation   |         130 |       13.00 |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
12 rows in set (0.00 sec)

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

מאחר שעבדת על סרטים בכמה ז'אנרים שונים, אתה מעוניין לדעת כמה הם היו מקובלים על ידי צופים. במיוחד, אתה רוצה לדעת את הכמות הממוצעת של אנשים שצפו בכל ז'אנר סרט. השתמש ב־SELECT כדי לאחזר את הסוגים השונים של סרטים מהעמודה movie_genre. לאחר מכן החל את הפונקציה המובנית AVG על עמודת guest_total, השתמש ב־AS כדי ליצור כינוי עבור עמודה הנקראת average, וכלול את ההצהרה GROUP BY כדי לקבץ את התוצאות לפי movie_genre. קיבוץ כזה יספק לך את התוצאות הממוצעות לכל ז'אנר סרט:

SELECT movie_genre, AVG(guest_total) AS average
FROM movie_theater 
GROUP BY movie_genre;

Output
+-------------+----------+
| movie_genre | average  |
+-------------+----------+
| Action      | 131.0000 |
| Drama       | 115.0000 |
| Horror      |  71.0000 |
| Animation   | 118.3333 |
+-------------+----------+
4 rows in set (0.00 sec)

הפלט הזה מספק את ארבעת הממוצעים עבור כל סוג תתי הז'אנרים בתוך קבוצת movie_genre. לפי המידע הזה, סרטי Action מושכים את ממוצע האורחים הגבוה ביותר לכל הצגה.

באשר למדידת הרווחים של התיאטרון למשך שני ימים נפרדים, השאילתה הבאה מחזירה ערכים מתוך עמודת date, וגם ערכים המוחזרים על ידי פונקציית האיגוד SUM. במיוחד, פונקציית האיגוד SUM תכלול משוואה מתמטית בסוגריים לכפל (באמצעות האופרטור *) את מספר האורחים הכולל על ידי עלות הכרטיס, המיוצגת כ: SUM(guest_total * ticket_cost). בשאילתה זו יש בתוכה פסקת AS כדי לספק את הכינוי total_revenue עבור העמודה שמוחזרת על ידי פונקציית האיגוד. לאחר מכן נשלים את השאילתה עם הצהרת GROUP BY כדי לקבוע את תוצאות השאילתה לפי עמודת date:

SELECT date, SUM(guest_total * ticket_cost) 
AS total_revenue 
FROM movie_theater 
GROUP BY date;

Output
+------------+---------------+
| date       | total_revenue |
+------------+---------------+
| 2022-05-27 |       7272.00 |
| 2022-05-28 |       9646.00 |
+------------+---------------+
2 rows in set (0.00 sec)

מאחר שהשתמשת ב־GROUP BY כדי לקבוע את עמודת ה־date, הפלט שלך מספק את תוצאות הרווחים הכוללים במכירות כרטיסים לכל יום, במקרה זה, $7,272 עבור יום שישי, 27 במאי, ו־$9,646 עבור יום שבת, 28 במאי.

עכשיו תפקידך להתמקד ולנתח סרט בודד: "The Bad Guys". בתרחיש הזה, ברצונך להבין איך הזמן ונקודות המחיר משפיעות על בחירת המשפחה לצפות בסרט מצויר. למענה על דרישה זו, השתמש בפונקציית הכולל MAX כדי לאחזר את העלות המרבית של הכרטיס, ווודא שאתה כולל AS כדי ליצור את הכינוי עבור עמודת המחירים price_data. לאחר מכן, השתמש במשפט התנאים WHERE כדי להקטין את התוצאות לפי שם הסרט לבדו "The Bad Guys", והשתמש ב־AND כדי לקבוע גם את הזמנים הפופולריים ביותר של הסרטים על סמך מספר האורחים guest_total שהיה יותר מ־100 עם אופרטור ההשוואה >. לבסוף, השלם את השאילתא עם ההצהרה GROUP BY וקבוצה אותה לפי time:

SELECT time, MAX(ticket_cost) AS price_data 
FROM movie_theater
WHERE movie_name = "The Bad Guys" 
AND guest_total > 100
GROUP BY time;

Output
+----------+------------+
| time     | price_data |
+----------+------------+
| 09:00:00 |       8.00 |
| 05:00:00 |      13.00 |
+----------+------------+
2 rows in set (0.00 sec)

לפי הפלט הזה, יש יותר אורחים שהגיעו לצפות בסרט "The Bad Guys" בזמן המטינה המוקדם של 9:00 בבוקר, שהיה במחיר נמוך יותר של 8.00 דולר לכרטיס. אך, התוצאות מראות גם שהאורחים לימדו מחיר כרטיס גבוה יותר של 13.00 דולר בשעה 5:00 אחר הצהריים, והם מציינים שמשפחות מעדיפות הצגות שאינן מאוחרות מדי ביום ומוכנות לשלם קצת יותר על כרטיס. נראה שהערכה זו נכונה בהשוואה לשעה 10:00 בערב, כאשר סרט "The Bad Guys" ספק רק 83 אורחים והמחיר לכרטיס היה 18.00 דולר. מידע זה יכול להיות מועיל למנהל הקולנוע ולספק לו ראיות שפתיחת עוד פרקי זמן במטינה ובערב המוקדם יכולה להגביר את הנוכחות של המשפחות שמבצעות בחירה על פי זמן ונקודת מחיר מועדפת.

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

SELECT DISTINCT movie_name FROM movie_theater;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| Downton Abbey A New Era |
| Men                     |
| The Bad Guys            |
+-------------------------+
4 rows in set (0.00 sec)

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

SELECT movie_name FROM movie_theater GROUP BY movie_name;

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

שימוש ב- ORDER BY

הפונקציה של ההצהרה ORDER BY היא למיין את התוצאות בסדר עולה או יורד בהתאם לעמודה/עמודות שאתה מציין בשאילתה. בהתבסס על סוג הנתונים שמאוחסנים בעמודה שאתה מציין אחריה, ORDER BY יארגן אותם בסדר אלפביתי או מספרי. כברירת מחדל, ORDER BY ימיין את התוצאות בסדר עולה; אם ברצונך בסדר יורד, עליך לכלול את המילה המפתחית DESC בשאילתתך. ניתן גם להשתמש בהצהרת ORDER BY עם GROUP BY, אך היא חייבת לבוא אחרי כדי שתעבוד כראוי. בדומה לGROUP BY, ORDER BY חייבת גם לבוא אחרי ההצהרה FROM והתנאי WHERE. תחביר השימוש הכללי בORDER BY הוא כדלקמן:

SELECT column_1, column_2 FROM table ORDER BY column_1;

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

SELECT guest_total FROM movie_theater 
ORDER BY guest_total;

Output
+-------------+
| guest_total |
+-------------+
|          25 |
|          83 |
|          88 |
|          90 |
|         100 |
|         112 |
|         118 |
|         130 |
|         131 |
|         137 |
|         142 |
|         150 |
+-------------+
12 rows in set (0.00 sec)

מכיוון שהשאילתה שלך ציינה עמודה עם ערכים מספריים, ההצהרה ORDER BY אירגנה את התוצאות לפי סדר מספרי ועולה, התחלת הסדר היתה עם המספר 25 תחת עמודת guest_total.

אם בחרת לסדר את העמודה בסדר יורד, תוסיף את המילה המפתח DESC בסוף השאילתה. בנוסף, אם ברצונך לסדר את הנתונים לפי ערכי התווים בתוך movie_name, תפרט זאת בשאילתתך. בואו נבצע סוג כזה של שאילתה באמצעות ORDER BY כדי לסדר את העמודה movie_name עם ערכי תווים בסדר יורד. סדר את התוצאות עוד יותר בכלל, על ידי כלול שאילתת WHERE כדי לאחזר את הנתונים על סרטים המוצגים בשעה 10:00 בערב מתוך העמודה time:

SELECT movie_name FROM movie_theater
WHERE time = '10:00:00' 
ORDER BY movie_name DESC;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| The Bad Guys            |
| Men                     |
| Downton Abbey A New Era |
+-------------------------+
4 rows in set (0.01 sec)

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

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

בשאילתה זו השתמש ב־SUM וכלול את האורחים הנוספים של 12 בכל הצגת סרט על ידי הפעלת אופרטור החיבור + ואז להוסיף 12 לשדה guest_total. ודא שתסגור את זה בסוגריים. לאחר מכן, הכפל את הסכום הזה ב־ticket_cost באמצעות האופרטור *, והשלם את המשוואה המתמטית על ידי סגירת הסוגריים בסופה. הוסף את המילה השמורה AS כדי ליצור את הכינוי לעמודה החדשה בשם total_revenue. לאחר מכן, השתמש ב־GROUP BY כדי לקבץ את תוצאות total_revenue עבור כל סרט על פי הנתונים שנאספו מעמודת movie_name. לבסוף, השתמש ב־ORDER BY כדי לארגן את התוצאות תחת העמודה החדשה total_revenue בסדר עולה:

SELECT movie_name, SUM((guest_total + 12) * ticket_cost) 
AS total_revenue
FROM movie_theater 
GROUP BY movie_name 
ORDER BY total_revenue;

Output
+-------------------------+---------------+
| movie_name              | total_revenue |
+-------------------------+---------------+
| Men                     |       3612.00 |
| Downton Abbey A New Era |       4718.00 |
| The Bad Guys            |       4788.00 |
| Top Gun Maverick        |       5672.00 |
+-------------------------+---------------+
4 rows in set (0.00 sec)

סט התוצאות הזה מציין את ההכנסה הכוללת עבור כל סרט עם מכירות הכרטיסים הנוספות של 12 אורחים ומארגן את מכירות הכרטיסים הכוללות בסדר עולה מהנמוך לגבוה. מכך אנו למדים שסרט המשגר משקל הקליעים הגבוה ביותר קיבל את רוב המכירות בכרטיסים, בעוד שסרט האנשים קיבל את המכירות הנמוכות ביותר. בינתיים, סרטי The Bad Guys ו־Downton Abbey A New Era היו קרובים מאוד במכירות הכוללות של הכרטיסים.

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

סיכום

הבנת כיצד להשתמש בהצהרות GROUP BY ו־ORDER BY חשובה למיון התוצאות והנתונים שלך. בין אם ברצונך לארגן תוצאות מרובות תחת קבוצה אחת, לארגן את אחד מהעמודות שלך לפי סדר אלפבתי או יורד, או לעשות את שניהם בו זמנית; זה תלוי בך ובתוצאה(ות) הרצויה שלך. למדת גם על אופציות נוספות למיון התוצאות שלך עוד יותר עם הצהרת ה־WHERE. אם ברצונך ללמוד עוד, צפה בהדרכה שלנו על איך להשתמש בתוויות גליונות ב־SQL כדי לתרגל סינון של התוצאות עם הצהרת ה־LIKE.