📞 02166056460

دستور Group By در sql

7 / 10
از 11 کاربر
بازگشت به لیست مقالات » | شنبه 2 آبان 1405 در ساعت 17 و 4 دقیقه | نظرات کاربران ( 1 )

آموزش SQL

دستور GROUP BY در SQL؛ آموزش گروه بندی و مثال کاربردی

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

دستور GROUP BY در SQL برای دسته بندی رکوردهایی استفاده می شود که مقدار یکسانی در یک یا چند ستون دارند. این دستور زمانی کاربرد دارد که بخواهید تابعی مانند مجموع، میانگین یا تعداد را برای هر دسته جداگانه محاسبه کنید؛ برای نمونه، مجموع نمرات هر دانش آموز یا تعداد سفارش های هر مشتری.

توابع تجمیعی مانند SUM و AVG به تنهایی می توانند محاسبه را روی کل مجموعه داده انجام دهند. اما وقتی نتیجه را به تفکیک یک ستون لازم دارید، گروه بندی به کمک شما می آید. در این راهنما، ساختار دستور، ترتیب اجرای بخش ها، کاربرد HAVING و اشتباهات متداول را با مثال های روشن بررسی می کنیم.

۰۱

دستور GROUP BY در SQL چیست و چه کاری انجام می دهد؟

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

برای مثال، فرض کنید جدولی از نمرات دانش آموزان دارید که نام دانش آموز و نمره هر درس در آن ثبت شده است. با گروه بندی بر اساس نام، می توانید مجموع نمره های هر دانش آموز را محاسبه کنید. بدون گروه بندی، تابع مجموع معمولاً یک مقدار کلی برای تمام ردیف های انتخاب شده برمی گرداند.

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

ساختار اصلی دستور GROUP BY

۰۲

ساختار اصلی دستور GROUP BY

در شکل ساده، پرس و جو ابتدا ستون های موردنیاز را با SELECT مشخص می کند، سپس جدول را با FROM معرفی می کند و در پایان، ستون یا ستون های گروه بندی را در GROUP BY می نویسد. اگر بخواهید پیش از گروه بندی رکوردهایی را کنار بگذارید، می توانید از WHERE استفاده کنید.

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;

در این الگو، aggregate_function نام تابع تجمیعی است؛ برای مثال، SUM برای جمع یا AVG برای میانگین. عبارت column_name بعد از GROUP BY مشخص می کند رکوردها با توجه به کدام مقدار مشترک در یک گروه قرار بگیرند.

در نمونه بالا، WHERE اختیاری است. اگر شرطی برای محدود کردن ردیف ها ندارید، آن خط را حذف کنید. همچنین می توانید بیش از یک ستون را در دستور گروه بندی قرار دهید؛ در این حالت، هر ترکیب یکتای مقدارهای آن ستون ها یک گروه مستقل خواهد بود.

۴ بخش
اجزای الگوی نمونه: SELECT، FROM، WHERE و GROUP BY
۱ تابع
نمونه تابع تجمیعی برای محاسبه خروجی هر گروه
۱ یا چند ستون
ستون های قابل استفاده برای تشکیل گروه

۵ نکته مهم درباره GROUP BY در SQL

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

۰۳

۱. ستون های SELECT را با GROUP BY هماهنگ کنید

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

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

SELECT Name, SUM(Grade) AS TotalGrade
FROM Class
GROUP BY Name;

در این مثال، نام هر دانش آموز یک بار در خروجی دیده می شود و مجموع نمرات ثبت شده برای او در ستون TotalGrade قرار می گیرد. اگر بخواهید مجموع را به تفکیک دانش آموز و درس داشته باشید، باید هر دو ستون را در گروه بندی لحاظ کنید.

۰۴

۲. تفاوت WHERE و HAVING را بشناسید

WHERE ردیف ها را پیش از گروه بندی فیلتر می کند، اما HAVING گروه های ساخته شده را پس از محاسبه تابع های تجمیعی محدود می کند. این تفاوت زمانی اهمیت دارد که شرط شما به نتیجه ای مانند مجموع یا تعداد وابسته باشد.

برای نمونه، اگر می خواهید فقط سفارش های ثبت شده در یک وضعیت خاص وارد محاسبه شوند، شرط مربوط به وضعیت را در WHERE قرار می دهید. اما اگر قصد دارید تنها مشتریانی را ببینید که مجموع خریدشان از یک مقدار بیشتر است، شرط را با HAVING می نویسید؛ چون مقدار مجموع پس از گروه بندی محاسبه می شود.

SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Orders
WHERE Status = 'Paid'
GROUP BY CustomerID
HAVING SUM(Amount) > 1000;

در این مثال، ابتدا سفارش های پرداخت شده انتخاب می شوند، سپس مبلغ سفارش ها برای هر مشتری جمع می شود و در نهایت فقط گروه هایی باقی می مانند که مجموع خریدشان از شرط تعیین شده عبور می کند. مقدار شرط در مثال صرفاً نمونه است و باید متناسب با داده های واقعی تنظیم شود.

🎯

WHERE

ردیف ها را پیش از تشکیل گروه ها فیلتر می کند.

📊

GROUP BY

ردیف های دارای مقدار مشترک را در گروه قرار می دهد.

✅

HAVING

پس از گروه بندی، گروه ها را بر اساس شرط محدود می کند.

۰۵

۳. تابع تجمیعی مناسب را انتخاب کنید

انتخاب تابع تجمیعی به پرسشی بستگی دارد که می خواهید با داده ها پاسخ دهید. SUM مجموع مقادیر را محاسبه می کند، AVG میانگین را به دست می آورد، COUNT تعداد ردیف ها را می شمارد و MAX و MIN به ترتیب بزرگ ترین و کوچک ترین مقدار را نمایش می دهند.

برای مثال، یک فروشگاه می تواند مجموع مبلغ فروش را برای هر دسته محصول محاسبه کند، میانگین مبلغ سفارش های هر مشتری را بسنجد یا تعداد سفارش های ثبت شده در هر وضعیت را به دست آورد. تابع باید با نوع داده و هدف گزارش هماهنگ باشد؛ میانگین گرفتن از شناسه ها، برای نمونه، معمولاً معنای تحلیلی مفیدی ندارد.

💰

SUM

محاسبه مجموع مقادیر عددی.

📈

AVG

محاسبه میانگین مقادیر.

📊

COUNT

شمارش ردیف ها یا مقدارهای غیرتهی.

🔎

MIN و MAX

یافتن کوچک ترین و بزرگ ترین مقدار.

۰۶

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

گاهی گروه بندی بر اساس یک ستون برای پاسخ به پرسش کافی نیست. اگر گزارش را به تفکیک چند ویژگی می خواهید، نام ستون ها را با ویرگول در GROUP BY بنویسید. در این حالت، گروه ها بر اساس ترکیب مقدارها ساخته می شوند، نه بر اساس هر ستون به تنهایی.

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

SELECT ProductID, City, SUM(Amount) AS TotalSales
FROM Orders
GROUP BY ProductID, City;

هرچه تعداد ستون های گروه بندی بیشتر شود، معمولاً تعداد گروه ها نیز بیشتر می شود و خروجی جزئیات بیشتری پیدا می کند. بنابراین ستون ها را بر اساس نیاز گزارش انتخاب کنید؛ افزودن ستون های نامرتبط ممکن است خروجی را پراکنده و خواندن آن را دشوار کند.

۰۷

۵. نتیجه را مرتب و قابل خواندن کنید

GROUP BY داده ها را دسته بندی می کند، اما نباید برای مرتب کردن خروجی به آن تکیه کنید. اگر ترتیب مشخصی لازم دارید، از ORDER BY استفاده کنید. می توانید خروجی را بر اساس ستون گروه بندی، مجموع محاسبه شده یا معیار دیگری که در پرس و جو در دسترس است مرتب کنید.

برای نمونه، اگر می خواهید مشتریانی که بیشترین خرید را داشته اند در ابتدای گزارش باشند، نتیجه را بر اساس مجموع خرید به صورت نزولی مرتب کنید. این کار بررسی گزارش را آسان تر می کند؛ به ویژه زمانی که تعداد گروه ها زیاد است و لازم دارید گروه های مهم را سریع پیدا کنید.

SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
ORDER BY TotalAmount DESC;
ادامه آموزش

انتخاب ستون ها را دقیق تر یاد بگیرید

برای شناخت نقش انتخاب ستون ها در پرس و جوهای SQL، آموزش دستور select را هم ببینید.

مطالعه دستور SELECT ←

مثال کاربردی: مجموع نمرات هر دانش آموز

۰۸

مثال کاربردی: مجموع نمرات هر دانش آموز

فرض کنید جدولی به نام Class دارید که اطلاعات نام دانش آموز و نمره های ثبت شده در آن نگهداری می شود. برای نمایش مجموع نمرات هر دانش آموز، باید نام را به عنوان معیار گروه بندی انتخاب کنید و تابع SUM را روی ستون نمره اجرا کنید.

SELECT Name, SUM(Grade) AS TotalGrades
FROM Class
GROUP BY Name;

در این پرس و جو، پایگاه داده ردیف های دارای نام یکسان را در یک گروه قرار می دهد. سپس نمره های هر گروه را جمع می کند و نتیجه را با نام مستعار TotalGrades نمایش می دهد. نام مستعار باعث می شود عنوان ستون خروجی در گزارش روشن تر باشد.

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

می توانید همین الگو را برای محاسبه میانگین نمره ها به کار ببرید و SUM را با AVG جایگزین کنید. همچنین برای شمارش تعداد نمره های ثبت شده برای هر دانش آموز، از COUNT استفاده کنید. انتخاب تابع به نوع گزارشی بستگی دارد که می خواهید تهیه کنید.

راهنمای استفاده و خطاهای رایج

۰۹

راهنمای استفاده و خطاهای رایج

وقتی خروجی گروه بندی با انتظار شما تفاوت دارد، ابتدا بررسی کنید ستون های انتخاب شده با ستون های گروه بندی هماهنگ باشند. سپس مطمئن شوید شرط های مربوط به ردیف ها در WHERE و شرط های مربوط به نتیجه تجمیع در HAVING قرار گرفته اند. این دو بررسی ساده بسیاری از خطاهای معمول را آشکار می کنند.

۱۰

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

یکی از خطاهای رایج، انتخاب ستونی در SELECT است که نه در GROUP BY آمده و نه با تابع تجمیعی استفاده شده است. برای رفع آن، مشخص کنید آیا آن ستون باید بخشی از تعریف گروه باشد یا باید با تابعی مانند MIN یا MAX خلاصه شود. تابع را فقط زمانی انتخاب کنید که معنای نتیجه برای مسئله شما درست باشد.

۱۱

استفاده از WHERE برای شرط تجمیعی

شرطی که به نتیجه SUM یا COUNT وابسته است، معمولاً باید در HAVING قرار بگیرد؛ زیرا مقدار تابع تجمیعی پس از تشکیل گروه محاسبه می شود. در مقابل، شرطی مانند تاریخ یا وضعیت یک ردیف را می توان پیش از گروه بندی با WHERE اعمال کرد.

۱۲

گروه بندی بیش از اندازه

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

ترتیب مفهومی اجرای بخش ها

۱۳

ترتیب مفهومی اجرای بخش ها

ترتیب نوشتن اجزای پرس و جو با ترتیب مفهومی پردازش آن ها یکی نیست. در یک پرس و جوی رایج، ابتدا جدول منبع مشخص می شود، سپس ردیف ها با WHERE محدود می شوند، گروه ها با GROUP BY ساخته می شوند و شرط های تجمیعی با HAVING اعمال می شوند. در پایان، ستون های خروجی انتخاب و نتیجه مرتب می شود.

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

مزایای GROUP BY

گزارش های دقیق تر و خواناتر

خلاصه سازی داده ها برای تحلیل بهتر
۰۱
خلاصه سازی

کاهش جزئیات تکراری

نمایش خلاصه به جای ردیف های هم گروه

۰۲
تحلیل

محاسبه برای هر دسته

جمع و میانگین جداگانه برای هر گروه

۰۳
گزارش

مقایسه گروه ها

بررسی تفاوت مشتریان یا دسته های محصول

۰۴
انعطاف

ترکیب با فیلترها

محدودسازی ردیف ها و گروه ها با شرط مناسب

۰۵
کاربرد

پشتیبانی از گزارش های متنوع

استفاده در آمار فروش، نمره و سفارش

جدول خلاصه اجزای مرتبط با گروه بندی

بخش یا تابع کاربرد نمونه استفاده
GROUP BY ساخت گروه بر اساس مقدار یک یا چند ستون گروه بندی سفارش ها بر اساس شناسه مشتری
WHERE فیلتر ردیف ها پیش از تشکیل گروه انتخاب سفارش های پرداخت شده
HAVING فیلتر گروه ها پس از محاسبه نمایش گروه هایی با مجموع خرید بالاتر از شرط
SUM محاسبه مجموع مقادیر عددی هر گروه جمع مبلغ سفارش های هر مشتری
COUNT شمارش ردیف ها یا مقدارهای غیرتهی شمارش سفارش های هر مشتری
ORDER BY مرتب سازی نتیجه نهایی نمایش بیشترین مجموع فروش در ابتدا
۱۴

نتیجه گیری: چه زمانی از دستور GROUP BY در SQL استفاده کنیم؟

دستور GROUP BY در SQL زمانی انتخاب مناسبی است که بخواهید داده های دارای مقدار مشترک را دسته بندی و برای هر دسته محاسبه ای جداگانه انجام دهید. ترکیب آن با توابعی مانند SUM، AVG و COUNT به شما امکان می دهد گزارش های کاربردی از فروش، سفارش، نمره یا هر مجموعه داده دیگری تهیه کنید.

برای نتیجه درست، ستون های انتخاب شده را با گروه بندی هماهنگ کنید، فیلتر ردیف ها را در WHERE و شرط گروه ها را در HAVING قرار دهید و برای تعیین ترتیب خروجی از ORDER BY کمک بگیرید. با رعایت این نکته ها، می توانید پرس و جوهایی روشن تر بنویسید و از ادغام ناخواسته داده ها جلوگیری کنید.

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

برای گام بعدی آماده اید؟

دانش فنی خود را در کنار تجربه کاربری و حضور حرفه ای آنلاین گسترش دهید. برای آشنایی با خدمات طراحی سایت به وب سایت پارس سافت سر بزنید.

مشاهده خدمات طراحی سایت ←

سوالات متداول دستور GROUP BY در SQL

دستور GROUP BY در SQL چه کاربردی دارد؟

این دستور ردیف هایی را که در یک یا چند ستون مقدار مشترک دارند، در گروه های جداگانه قرار می دهد. معمولاً همراه تابع های تجمیعی مانند SUM، AVG یا COUNT استفاده می شود تا نتیجه برای هر گروه محاسبه شود؛ مثلاً مجموع فروش هر مشتری.

آیا می توان چند ستون را در GROUP BY نوشت؟

بله. نام ستون ها را با ویرگول از هم جدا کنید. در این حالت، هر ترکیب یکتای مقدارهای ستون ها یک گروه خواهد بود؛ برای نمونه می توانید گزارش فروش را هم زمان به تفکیک محصول و شهر تهیه کنید.

تفاوت WHERE و HAVING چیست؟

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

آیا همه ستون های SELECT باید در GROUP BY باشند؟

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

آیا GROUP BY خروجی را مرتب می کند؟

برای مرتب سازی قابل اتکا از ORDER BY استفاده کنید. وظیفه اصلی GROUP BY تشکیل گروه هاست و نباید ترتیب خروجی را به آن وابسته کنید. می توانید نتیجه را بر اساس ستون گروه بندی یا مقدار تجمیعی مرتب کنید.

ارسال نظر و نظرات ثبت شده
احسان :
3 0
ممنونم از نکات خوبتون
سایر مقالات
دستور Delete در sql دستور DELETE در SQL؛ آموزش حذف رکورد از جدول 💡آشنایی با حذف داده‌ها دستور DELETE در ...
دستور Update در sql دستور Update در SQL چیست و چه کاربردی دارد؟ 💡آشنایی با به روزرسانی داده ها دستور Update در ...
دستور Insert در sql دستور Insert در اسکیوال برای افزودن رکورد به جدول به کار می رود. در این راهنما با شیوه نوشتن دستور، ...
دستور مرتب سازی Order By در sql دستور ORDER BY در SQL چیست و چه کاربردی دارد؟ 💡مرتب سازی خروجی کوئری دستور ORDER BY در SQL ...
دستور Group By در sql آموزش SQL دستور GROUP BY در SQL؛ آموزش گروه بندی و مثال کاربردی یاد بگیرید داده ها را ...
دستور شرطی Having در sql دستور Having در SQL برای فیلتر کردن نتیجه گروه بندی و اعمال شرط روی مقادیر محاسبه شده با توابعی مانن ...
عملگر های And و Or در SQL آموزش SQL عملگرهای AND و OR در SQL؛ راهنمای ترکیب شرط ها با مثال های کاربردی، تفاوت AND و ...
دستور شرطی WHERE در sql دستور WHERE در SQL چیست و چه کاربردی دارد؟ 🔎فیلتر کردن نتیجه ها دستور WHERE در SQL برای تعی ...
دستور SELECT DISTINCT در sql دستور SELECT DISTINCT در SQL چیست و چه کاربردی دارد؟ 💡آشنایی با حذف تکرارها در پایگاه داده، ...
دستور SELECT در sql دستور SELECT در SQL چیست و چه کاربردی دارد؟ 💡آشنایی با بازیابی داده ها دستور SELECT در SQL ...
دسته بندی
جستجو
⏳ در حال پردازش...
لطفا صبر کنید