هر آنچه باید درباره بیانیه SQL GROUP BY بدانید

GROUP BY یک عنصر کلیدی پرس و جوهای SQL است. در اینجا همه چیزهایی است که باید در مورد استفاده از عبارت GROUP BY بدانید.

بیشتر قدرت پایگاه داده های رابطه ای از فیلتر کردن داده ها و اتصال جداول به یکدیگر ناشی می شود. به همین دلیل است که ما در وهله اول آن روابط را نمایندگی می کنیم. اما سیستم های پایگاه داده مدرن تکنیک ارزشمند دیگری را ارائه می دهند: گروه بندی.

گروه بندی به شما امکان می دهد اطلاعات خلاصه ای را از یک پایگاه داده استخراج کنید. به شما امکان می‌دهد نتایج را برای ایجاد داده‌های آماری مفید ترکیب کنید. گروه‌بندی شما را از نوشتن کد برای موارد رایج مانند میانگین‌گیری لیست ارقام نجات می‌دهد. و می‌تواند سیستم‌های کارآمدتری را ایجاد کند.

GROUP BY Clause چه کاری انجام می دهد؟

GROUP BY، همانطور که از نام پیداست، گروه ها به مجموعه کوچکتری تبدیل می شوند. نتایج شامل یک ردیف برای هر مقدار متمایز از ستون گروه بندی شده است. می‌توانیم استفاده از آن را با مشاهده برخی از داده‌های نمونه با ردیف‌هایی که مقادیر مشترکی دارند نشان دهیم.

در زیر یک پایگاه داده بسیار ساده با دو جدول نشان دهنده آلبوم های رکورد است. می‌توانید با نوشتن یک طرح اولیه برای سیستم پایگاه داده انتخابی خود، چنین پایگاه‌داده‌ای را راه‌اندازی کنید. جدول آلبوم ها دارای 9 ردیف با ستون شناسه کلید اصلی و ستون هایی برای نام، هنرمند، سال انتشار و فروش است:

+----+---------------------------+-----------+--------------+-------+
| 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 را فقط با یک مجموعه داده ساده مانند این درک کنید. البته، یک مجموعه داده واقعی دارای ردیف های بسیار بسیار بیشتری خواهد بود، اما اصول یکسان باقی می مانند.

گروه بندی بر اساس یک ستون

فرض کنید می خواهیم بفهمیم برای هر هنرمند چند آلبوم داریم. با یک کوئری SELECT معمولی برای واکشی ستون 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 منحصر به فرد دارای یک ردیف است. در نهایت، برای دریافت تعداد واقعی، COUNT(*) را به ستون های انتخاب شده اضافه کنید:

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 برای مبتدیان

نحوه دسترسی به داده های گروه بندی شده با یک تابع جمع

ممکن است قبلاً از تابع COUNT استفاده کرده باشید، به ویژه در فرم COUNT(*) همانطور که در بالا مشاهده می شود. تعداد نتایج را در یک مجموعه واکشی می کند. می توانید از آن برای بدست آوردن تعداد کل رکوردها در یک جدول استفاده کنید:

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 است. می‌تواند اطلاعات خلاصه‌ای از داده‌ها، برای مثال، برای یک صفحه محتوا ارائه دهد. این یک جایگزین عالی برای واکشی مقادیر زیاد داده است. پایگاه داده این حجم کاری اضافی را به خوبی مدیریت می‌کند، زیرا طراحی آن آن را برای کار بهینه می‌کند.

هنگامی که گروه بندی و نحوه پیوستن به چندین جدول را درک کردید، می توانید از بیشتر قدرت یک پایگاه داده رابطه ای استفاده کنید.

منبع مقاله