گروه بندی داده ها با GROUP BY

گروه بندی (Grouping) داده ها به فرایندی گفته می شود که چندین ستون بر اساس مقادیر تکراری (Duplicate) با یکدیگر ادغام می شوند. به طور مثال در جدول hr.employees اطلاعات کارمندان ذخیره شده اند و قاعدتا این کارمندان در شهرهای مختلف زندگی می کنند ولی کارمندانی هستند که همشهری یکدیگر هستند.

بنابراین زمانی که لازم داریم تا اطلاعات کارمندانی همشهری (نام شهر تکراری) را نمایش دهیم باید سطرهایی که اطلاعات کارمندان را نگه می دارند را بر اساس نام شهر گروه بندی کنیم و یک گزارش خلاصه شده (summarized report) را بدست آوریم و نمایش دهیم.

مثال دیگر این است که می خواهیم میزان میانگین حقوق کارمندانی که در یک شهر کار می کنند را بدست آوریم، برای این کار باید ابتدا از تابع ()AVG استفاده کنیم تا میانگین حقوق را بدست آوریم و سپس با استفاده از عبارت GROUP BY باید خروجی را بر اساس ستون نام شهر گروه بندی کنیم.

مثالتعداد کارمندان درون هر دپارتمان را بدست آورید.

برای حل این مثال ابتدا باید از تابع COUNT(*) استفاده کنیم تا تعداد را بدست آوریم ولی می خواهیم خروجی را بر اساس دپارتمان گروه بندی کنیم پس باید ستون department_id را در جلوی GROUP BY استفاده کنیم.

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

در واقع میان ستون department_id در جدول hr.employees کلید خارجی به جدول hr.departments است زیرا میان دو جدول hr.employees و hr.departments رابطه یک به چند وجود دارد به این معنی که هر کارمند عضو یک دپارتمان است ولی هر دپارتمان دارای چندین و چند کارمند است.

اما چگونه می توانیم نام دپارتمان ها و بسته به جدول مروبط دپارتمان ها، دیگر اطلاعات در مورد دپارتمان ها را در خروجی نمایش دهیم. چون اطلاعات در دو جدول یا چندین جدول مجزا هستند، پس باید از ترکیب یاد ادغام (Join) کردن جدول ها استفاده کنیم که در مطالب بعدی توضیح داده شده اند.

مثالتعداد کارمندان دو دپارتمان با شناسه ۹۰ و ۸۰ را پیدا کنید.

توجه کنید که عبارت GROUP BY حتما و حتما باید پس از عبارت WHERE و قبل از عبارت ORDER BY قرار بگیرد. بنابراین دو دستور زیر منجر به بروز خطای ORA-00933: SQL command not properly ended می شود.

در دستور بالا چون ORDER BY پس از عبارت GROUP BY آمده است، پس خطای ORA-00933 نشان داده می شود. شکل زیر خروجی کوئری بالا و خطای ORA-00933: SQL command not properly ended را نشان می دهد.

در دستور بالا نیز چون عبارت ORDERY BY پیش از عبارت GROUP BY آمده است، خطای ORA-00933 نشان داده می شود. قطعه کدهای زیر باز نوسی شده و قطعه کد بالا است که هر عبارت در محل مناسب خودش قرار گرفته است. هدف از کوئری های زیر پیدا کردن تعداد کارمندانی است که دپارتمان آنها یکی از شناسه های عددی ۹۰ یا ۸۰ است.

مثال – تعداد کارمندان هر واحد شغلی را نمایش دهید که نام شغل با SA آغاز می شود.

مثالمجموع حقوق تمامی کارمندان را پیدا کنید.

مثالمجموع حقوق تمامی کارمندان در هر دپارتمان را پیدا کنید.

تفاوت دو تا آخرین کوئری های بالا در این است که در اولی می خواهیم مجموع حقوق تمامی کارمندان را یکجا پیدا کنیم ولی در دومی مجموع حقوق کارمندان هر یک از دپارتمان ها را مجزا از دیگری بدست آوریم، پس باید از عبارت GROUP BY استفاده کنیم تا خروجی را بر اساس ستون department_id گروه بندی کنیم. همچنین از تابع ()SUM برای محاسبه مجموع حقوق استفاده شده است.

نکته دیگری که باید به آن توجه کنید اینکه اگر از توابع تجمیعی در دستور SELECT استفاده نکرده اید، پس نمی توان از عبارت GROUP BY استفاده کرد و در صورتی که دستور GROUP BY را بدون استفاده از توابع تجیعی به کار ببرید، منجر به خطای ORA-00979: not a GROUP BY expression می شود. بنابراین دستور زیر اشتباه است و باعث بروز خطای ORA-00979 می شود.

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

در دستور زیر چون ستون first_name در جلوی عبارت GROUP BY نوشته نشده است، پس چون آنرا جلوی دستور SELECT نوشته ایم، این باعث خطا می شود ولی اگر نام ستون first_name در در دستور زیر به department_id  تغییر دهیم، مشکل رفع می شود. همچنین می توانیم فقط خود تابع ()SUM را به تنهایی استفاده کنیم مانند کوئری که پیش از این توضیح داده بودیم.

مثالمجموع و میانگین حقوق تمامی کارمندان دپارتمان ۸۰ را پیدا کنید.

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

مثالبیشترین میزان حقوق  را برای تمامی کارمندان بر اساس شغل آنها پیدا کنید که شغل آنها IT_PROG نباشد.

پیش از هر چیز باید شرطی را در جلوی عبارت WHERE استفاده کنیم که سطرهایی را که ستون job_id آنها برابر با IT_PROG است را شامل نشود، پس از عبارت WHERE job_id NOT ‘IT_PROG’ استفاده کرده ایم.

چون می خواهیم بیشترین میزان حقوق را پیدا کنیم، پس باشد از تابع ()MAX استفاده کنیم و همینطور باید خروجی را بر اساس شغل گروه بندی کنیم، پس نام ستون job_id را در عبارت GROUP BY استفادخ کنیمدر کوئری بالا از عبارت NOT IN استفاده کرده ایم که می توانیم NOT IN را معادل دو عملگر <> و یا != بدانیم، پس می توانیم کوئری بالا را با یکی از عملگرهای فوق باز نویسی کنیم.