Home-theater-designers
חלק גדול מהעוצמה של מאגרי מידע יחסיים נובעת מסינון נתונים ומחיבור טבלאות יחד. זו הסיבה שאנו מייצגים את היחסים האלה מלכתחילה. אבל מערכות מסדי נתונים מודרניות מספקות טכניקה בעלת ערך נוסף: קיבוץ.
קיבוץ מאפשר לך לחלץ מידע סיכום ממאגר נתונים. הוא מאפשר לך לשלב תוצאות ליצירת נתונים סטטיסטיים שימושיים. קיבוץ חוסך ממך כתיבת קוד למקרים נפוצים כגון רשימות ממוצעות של דמויות. וזה יכול לייצר מערכות יעילות יותר.
מה עושה סעיף GROUP BY?
GROUP BY, כפי שהשם מרמז, מקבץ את התוצאות לקבוצה קטנה יותר. התוצאות מורכבות משורה אחת לכל ערך מובהק של העמודה המקובצת. אנו יכולים להציג את השימוש בו על ידי הסתכלות על כמה דוגמאות לנתונים עם שורות החולקות כמה ערכים נפוצים.
האם מצב צריכת חשמל נמוכה טוען את הטלפון שלך מהר יותר
להלן מסד נתונים פשוט מאוד עם שתי טבלאות המייצגות אלבומי תקליטים. אתה יכול להקים מסד נתונים כזה על ידי כתיבת סכמה בסיסית עבור מערכת מסד הנתונים שבחרת. ה אלבומים הטבלה כוללת תשע שורות עם מפתח ראשי תְעוּדַת זֶהוּת טור ועמודות עבור שם, אמן, שנת יציאה ומכירות:
+----+---------------------------+-----------+--------------+-------+
| id | name | artist_id | release_year | sales |
+----+---------------------------+-----------+--------------+-------+
| 1 | Abbey Road | 1 | 1969 | 14 |
| 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
| 3 | Rumours | 3 | 1977 | 28 |
| 4 | Nevermind | 4 | 1991 | 17 |
| 5 | Animals | 2 | 1977 | 6 |
| 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
| 7 | 21 | 6 | 2011 | 25 |
| 8 | 25 | 6 | 2015 | 22 |
| 9 | Bat Out of Hell | 7 | 1977 | 28 |
+----+---------------------------+-----------+--------------+-------+ה אמנים השולחן פשוט עוד יותר. יש לה שבע שורות עם עמודות מזהה ושמות:
+----+---------------+
| id | name |
+----+---------------+
| 1 | The Beatles |
| 2 | Pink Floyd |
| 3 | Fleetwood Mac |
| 4 | Nirvana |
| 5 | Elton John |
| 6 | Adele |
| 7 | Meat Loaf |
+----+---------------+אתה יכול להבין היבטים שונים של GROUP BY בעזרת מערכת נתונים פשוטה כמו זו. כמובן שלמערכת נתונים אמיתיים יהיו הרבה יותר שורות, אבל העקרונות נשארים זהים.
קיבוץ לפי טור יחיד
נניח שאנחנו רוצים לברר כמה אלבומים יש לנו לכל אמן. התחל עם טיפוסי בחר שאילתה לאחזור העמודה artist_id:
SELECT artist_id FROM albumsזה מחזיר את כל תשע השורות, כצפוי:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 5 |
| 6 |
| 6 |
| 7 |
+-----------+כדי לקבץ את התוצאות הללו על ידי האמן, צרף את הביטוי GROUP BY artist_id :
SELECT artist_id FROM albums GROUP BY artist_idמה שנותן את התוצאות הבאות:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+-----------+במערך התוצאות יש שבע שורות, מופחתות מסך התשע ב אלבומים שולחן. כל אחד ייחודי artist_id בעל שורה אחת. לבסוף, כדי לקבל את הספירות בפועל, הוסף לספור(*) לעמודות שנבחרו:
SELECT artist_id, COUNT(*)
FROM albums
GROUP BY artist_id
+-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
| 7 | 1 |
+-----------+----------+התוצאות מקבצות שני זוגות שורות לאמנים עם מזהים 2 ו 6 . לכל אחד מהם שני אלבומים במאגר המידע שלנו.
קָשׁוּר: גיליונות הרמאות החיוניים של פקודות SQL למתחילים
כיצד לגשת לנתונים מקובצים באמצעות פונקציה מצטברת
יכול להיות שהשתמשת ב- לספור לתפקד לפני, במיוחד ב- לספור(*) צורה כפי שניתן לראות למעלה. זה מביא את מספר התוצאות בקבוצה. אתה יכול להשתמש בו כדי לקבל את המספר הכולל של הרשומות בטבלה:
SELECT COUNT(*) FROM albums
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+COUNT היא פונקציה מצטברת. מונח זה מתייחס לפונקציות המתרגמות ערכים משורות מרובות לערך יחיד. הם משמשים לעתים קרובות יחד עם הצהרת GROUP BY.
במקום לספור את מספר השורות, נוכל להחיל פונקציה מצטברת לערכים מקובצים:
SELECT artist_id, SUM(sales)
FROM albums
GROUP BY artist_id
+-----------+------------+
| artist_id | SUM(sales) |
+-----------+------------+
| 1 | 14 |
| 2 | 30 |
| 3 | 28 |
| 4 | 17 |
| 5 | 8 |
| 6 | 47 |
| 7 | 28 |
+-----------+------------+סך המכירות המוצגות לעיל עבור אמנים 2 ו -6 הן מכירות האלבומים המרובות שלהן יחד:
SELECT artist_id, sales
FROM albums
WHERE artist_id IN (2, 6)
+-----------+-------+
| artist_id | sales |
+-----------+-------+
| 2 | 24 |
| 2 | 6 |
| 6 | 25 |
| 6 | 22 |
+-----------+-------+קיבוץ לפי עמודות מרובות
תוכל לקבץ יותר מעמודה אחת. כלול רק עמודות או ביטויים מרובים, המופרדים באמצעות פסיקים. התוצאות יתקבצו בהתאם לשילוב הטורים הללו.
SELECT release_year, sales, count(*)
FROM albums
GROUP BY release_year, salesבדרך כלל זה יניב יותר תוצאות מאשר קיבוץ לפי עמודה אחת:
+--------------+-------+----------+
| release_year | sales | count(*) |
+--------------+-------+----------+
| 1969 | 14 | 1 |
| 1973 | 24 | 1 |
| 1977 | 28 | 2 |
| 1991 | 17 | 1 |
| 1977 | 6 | 1 |
| 1973 | 8 | 1 |
| 2011 | 25 | 1 |
| 2015 | 22 | 1 |
+--------------+-------+----------+שימו לב, בדוגמה הקטנה שלנו, רק לשני אלבומים יש אותה שנת יציאה וספירת מכירות (28 בשנת 1977).
פונקציות צבירה שימושיות
מלבד COUNT, מספר פונקציות עובדות היטב עם GROUP. כל פונקציה מחזירה ערך המבוסס על הרשומות השייכות לכל קבוצת תוצאות.
- COUNT () מחזירה את המספר הכולל של רשומות תואמות.
- SUM () מחזיר את סך כל הערכים בעמודה הנתונה שנוספו.
- MIN () מחזיר את הערך הקטן ביותר בעמודה נתונה.
- MAX () מחזיר את הערך הגדול ביותר בעמודה נתונה.
- AVG () מחזיר את הממוצע הממוצע. זה שווה ערך ל- SUM () / COUNT ().
תוכל גם להשתמש בפונקציות אלה ללא סעיף GROUP:
כיצד לבטל את נעילת הטלפון הנעול של הספק
SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+שימוש ב- GROUP BY עם סעיף WHERE
בדיוק כמו עם SELECT רגיל, אתה עדיין יכול להשתמש ב- WHERE כדי לסנן את קבוצת התוצאות:
SELECT artist_id, COUNT(*)
FROM albums
WHERE release_year > 1990
GROUP BY artist_id +-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 6 | 2 |
+-----------+----------+עכשיו יש לך רק את האלבומים האלה שיצאו לאחר 1990, מקובצים לפי אמן. תוכל גם להשתמש ב- join עם סעיף WHERE, ללא תלות ב- GROUP BY:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id +---------+--------+
| name | albums |
+---------+--------+
| Nirvana | 1 |
| Adele | 2 |
+---------+--------+עם זאת, שים לב שאם אתה מנסה לסנן על סמך עמודה מצטברת:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND albums > 2
GROUP BY artist_id;תקבל שגיאה:
ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'עמודות המבוססות על נתונים מצטברים אינן זמינות לפסקה WHERE.
שימוש בסעיף HAVING
אז איך מסננים את קבוצת התוצאות לאחר שהתקיימה קיבוץ? ה יש סעיף עוסק בצורך זה:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
GROUP BY artist_id
HAVING albums > 1;שים לב שסעיף HAVING מגיע אחרי GROUP BY. אחרת, זה בעצם החלפה פשוטה של ה- WHERE ל- HAVING. התוצאות הן:
+------------+--------+
| name | albums |
+------------+--------+
| Pink Floyd | 2 |
| Adele | 2 |
+------------+--------+אתה עדיין יכול להשתמש בתנאי WHERE כדי לסנן את התוצאות לפני הקיבוץ. זה יעבוד יחד עם סעיף HAVING לסינון לאחר הקיבוץ:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id
HAVING albums > 1;רק אמן אחד במאגר המידע שלנו הוציא יותר מאלבום אחד לאחר 1990:
+-------+--------+
| name | albums |
+-------+--------+
| Adele | 2 |
+-------+--------+שילוב תוצאות עם GROUP BY
הצהרת GROUP BY היא חלק שימושי להפליא משפת ה- SQL. הוא יכול לספק מידע סיכום של נתונים, למשל עבור דף תוכן. זוהי אלטרנטיבה מצוינת לאחזור כמויות גדולות של נתונים. מסד הנתונים מטפל היטב בעומס העבודה הנוסף הזה מכיוון שהעיצוב שלו הופך אותו לאופטימלי לתפקיד.
לאחר שתבין קיבוץ וכיצד להצטרף למספר טבלאות, תוכל לנצל את מרבית העוצמה של מסד נתונים יחסי.
לַחֲלוֹק לַחֲלוֹק צִיוּץ אימייל כיצד לשאול טבלאות מרובות מסדי נתונים בבת אחת עם חיבורי SQLלמד כיצד להשתמש ב- Joins SQL כדי לייעל שאילתות, לחסוך זמן ולגרום לך להרגיש כמו משתמש כוח SQL.
חפש בגוגל את עצמך בצע חיפוש מעמיקקרא הבא נושאים קשורים
- תִכנוּת
- SQL
בובי הוא חובב טכנולוגיה שעבד כמפתח תוכנה במשך רוב שני העשורים. הוא נלהב משחקים, עובד כעורך ביקורות במגזין Switch Player, והוא שקוע בכל ההיבטים של פרסום מקוון ופיתוח אתרים.
עוד מאת בובי ג'קהירשם לניוזלטר שלנו
הצטרף לניוזלטר שלנו לקבלת טיפים, סקירות, ספרים אלקטרוניים בחינם ומבצעים בלעדיים!
לחצו כאן להרשמה