article

توابع تجمعی (Aggregate Functions) در MySQL

mysql-aggregate-functions

اگر آموزش های مقدماتی بخش MySQL را دنبال کرده باشید، حتما به خاطر دارید در مبحث گذشته که در مورد استفاده از GROUP BY صحبت کردیم، گفتیم که توابع مربوط به GROUP BY را در اصطلاح توابع تجمعی یا aggregate functions می گویند (شامل COUNT، MAX، MIN، SUM، AVG و...) و قرار شد که در آموزش های بعد به آن بپردازیم، از این رو طبق روال، این مطلب را اختصاص داده ایم به مبحث مذکور تا با فراگیری آن، بیش از پیش و با تسلط بیشتر بتوانیم به نحو دلخواه با php، اطلاعات موجود در دیتابیس MySQL را مدیریت کنیم، ذکر این نکته نیز لازم است که استفاده از این توابع همیشه هم ضروری نیست و بستگی به نوع نیاز شما دارد، اما در کل یک برنامه نویس خوب کسی است که از همه ظرفیت های موجود آگاه باشد، حتی اگر از آنها هرگز استفاده نکند.

توابع تجمعی (Aggregate Functions) به چه معنی است؟


شاید این سوال به ذهن شما رسیده باشد که در کل توابع تجمعی (Aggregate Functions) به چه معنی است؟ پاسخ این سوال را اینگونه می توان داد که در PHP و MySQL برای گروه بندی در هنگام کار با اطلاعات عددی و آماری و بدست آوردن نتایج، بعد از یک سری محاسبات ریاضی (در حد معمولی یا پیشرفته)، توابعی از پیش تعریف شده که با تنظیم آنها در هنگام پرس و جو از پایگاه داده، طبق نیاز ما عملیات مورد نظر انجام شده و نتایج برگردانده می شوند، به فرض در مثالی که در ادامه خواهیم دید، اطلاعات خودروهایی با قیمت ها و رنگ های مختلف در دیتابیس موجود است، حال اگر بخواهیم خودرو ها را بر اساس رنگ گروه بندی کرده و در عین حال، ارزان ترین قیمت آن رنگ ها را انتخاب کنیم، ناچاریم از تابع MIN که جزء Aggregate Functions است استفاده نمائیم (در ادامه خواهیم دید).

ساخت جدول و وارد کردن اطلاعات نمونه در MySQL


اجازه بدهید برای یادآوری هم که شده، ابتدا با استفاده از کد زیر، اطلاعات نمونه ای را در دیتابیس فرضی test ایجاد کنیم، این اطلاعات شامل جدول tb_cars و شش ردیف پنج ستونه از اسامی، مدل، رنگ و قیمت خودروها است (به اضافه ستون id که با خاصیت AUTO_INCREMENT است و به صورت خودکار ایجاد شده و افزایش می یابد).
<?php
$con = mysql_connect("localhost","root","")
or die(mysql_error());

mysql_select_db("test")
or die(mysql_error());

mysql_query("CREATE TABLE tb_cars(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(255),
model VARCHAR(255),
color VARCHAR(255),
price INT(11)) ENGINE=MyISAM "
)
or die(mysql_error());

mysql_query("ALTER TABLE tb_cars
DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci;"
)
or die(mysql_error());

$cars = array(
"carname" => array("Peugeot","Pride","Nissan","L90","Tiba","Mazda"),
"carmodel" => array("1388","1390","1390","1388","1390","1388"),
"carcolor" => array("white","black","blue","black","white","blue"),
"carprice" => array("14","8","10","12","9","18")
);

$loop = count($cars['carname']);
for ($row=0; $row < $loop; $row++){
        $carname = $cars['carname'][$row];
        $carmodel = $cars['carmodel'][$row];
        $carcolor = $cars['carcolor'][$row];
        $carprice = $cars['carprice'][$row];
        
        mysql_query("INSERT INTO tb_cars (name, model, color, price) VALUES('$carname', '$carmodel', '$carcolor', '$carprice') ")
        or die(mysql_error());
}

mysql_close($con);
?>
توضیح:
- برای ذخیره این اطلاعات نمونه در پایگاه داده، کافی است یک دیتابیس با نام test، ترجیحا در لوکال هاست بسازید و کد بالا را در یک فایل php ذخیره کرده و اجرا کنید.
- در کد بالا، ابتدا با دستور CREATE TABLE جدول فرضی tb_cars را ساخته ایم که درون خود ستون هایی با نام id (از نوع INT) و name، model، color و price (همگی از نوع VARCHAR) را دارد.
- در قسمت ALTER TABLE یک دستور اضافه برای کار با حروف فارسی قرار داده ایم تا یونیکد جداول به صورت استاندار utf8 تبدیل شود که برای کار با داده های زبان فارسی در MySQL مناسب است (البته در این مثال تنها از حروف انگلیسی استفاده شده است).
- در بخش بعد از کد بالا، اطلاعات خودروها در یک آرایه چند بعدی یا (Multidimensional) ذخیره شده است تا با یک حلقه for مقادیر با دستور INSERT INTO وارد دیتابیس شوند (برای درک صحیح این بخش به آموزش های php در مبحث حلقه و آرایه مراجعه کنید).

استفاده از تابع MIN


اولین تابعی که قصد بررسی آن را داریم، تابع MIN در MySQL است، به کاربردن این تابع باعث می شود که کم ترین مقادیر از موارد مورد نظر انتخاب شوند، به طور مثال در کد زیر، از خودروهای با رنگ همسان، ارزان ترین (رنگ ها) را انتخاب کرده ایم.
<?php
$result = mysql_query("SELECT color,MIN(price) FROM tb_cars GROUP BY color")
or die(mysql_error());
while($row = mysql_fetch_array($result)){
    $carcolor = $row['color'];
    $carprice = $row['MIN(price)'];
    
    echo $carcolor.' - '.$carprice.'<br />';
}
?>
خروجی پرس و جوی بالا از پایگاه داده به صورت زیر خواهد بود:
black - 8
blue - 10
white - 9
همانطور که ملاحظه می کنید، رسم الخط یا همان syntax این تابع به گونه ای است که باید هم در query و هم در تابع mysql_fetch_array از آن در ستون مورد نظر استفاده شود، نکته دیگری که باید به آن توجه شود این است، ستونی که به عنوان گروه بندی از آن استفاده می شود به عنوان مبنای کار و ستونی که پائین ترین مقادیر از آن انتخاب می شود به عنوان پارامتر دوم دارای اهمیت است.

استفاده از تابع MAX


تابع دیگری که جزء خانواده توابع تجمعی یا Aggregate Functions است، تابع MAX است که دقیقا کارکردی شبیه MIN دارد، با این تفاوت که در اینجا بزرگ ترین مقادیر انتخاب می  شوند.
<?php
$result = mysql_query("SELECT color,MAX(price) FROM tb_cars GROUP BY color")
or die(mysql_error());
while($row = mysql_fetch_array($result)){
    $carcolor = $row['color'];
    $carprice = $row['MAX(price)'];
    
    echo $carcolor.' - '.$carprice.'<br />';
}
?>
خروجی پرس و جوی بالا از پایگاه داده به صورت زیر خواهد بود:
black - 12
blue - 18
white - 14

استفاده از تابع AVG


بعد از آشنایی با تابع MIN و MAX نوبت به بررسی تابعی دیگر از این خانواده است، این تابع AVG نام دارد که در واقع مخفف عبارت average یا میانگین است، با استفاده از تابع AVG می توانیم حد متوسط مقادیر مورد نظر را از ستون های گروهی، محاسبه کرده و نشان دهیم.
<?php
$result = mysql_query("SELECT color,AVG(price) FROM tb_cars GROUP BY color")
or die(mysql_error());
while($row = mysql_fetch_array($result)){
    $carcolor = $row['color'];
    $carprice = $row['AVG(price)'];
    
    echo $carcolor.' - '.$carprice.'<br />';
}
?>
خروجی پرس و جوی بالا از پایگاه داده به صورت زیر خواهد بود:
black - 10.0000
blue - 14.0000
white - 11.5000

استفاده از تابع SUM


تابع دیگری که در خانواده توابع تجمعی (Aggregate Functions) دسته بندی می شود، تابع SUM یا حاصل جمع است، این تابع در واقع مقادیر مجموع ستون هایی که با هم گروه بندی می شوند را نشان می دهد.
<?php
$result = mysql_query("SELECT color,SUM(price) FROM tb_cars GROUP BY color")
or die(mysql_error());
while($row = mysql_fetch_array($result)){
    $carcolor = $row['color'];
    $carprice = $row['SUM(price)'];
    
    echo $carcolor.' - '.$carprice.'<br />';
}
?>
خروجی پرس و جوی بالا از پایگاه داده به صورت زیر خواهد بود:
black - 20
blue - 28
white - 23

استفاده از تابع COUNT


آخرین تابعی که در این آموزش قصد معرفی آن را داریم، تابع COUNT است که در واقع نمایانگر تعداد آیتم های هر گروه است، به فرض اگر 5 خودرو مشکی رنگ داشته باشیم، در هنگامی که از ستون color برای GROUP BY استفاده کنیم، تابع COUNT مقادیر 5 را برای این ستون برمی گرداند.
<?php
$result = mysql_query("SELECT color,COUNT(price) FROM tb_cars GROUP BY color")
or die(mysql_error());
while($row = mysql_fetch_array($result)){
    $carcolor = $row['color'];
    $carprice = $row['COUNT(price)'];
    
    echo $carcolor.' - '.$carprice.'<br />';
}
?>
خروجی پرس و جوی بالا از پایگاه داده به صورت زیر خواهد بود:
black - 2
blue - 2
white - 2
لیست توابع تجمعی به MIN MAX AVG SUM و COUNT محدود نمی شود، MySQL توابع دیگری نیز برای محاسبات آماری پیچیده تر در نظر گرفته است که البته معمولا در برنامه های عادی، کم تر مورد استفاده قرار می گیرند، برای کسب اطلاعات بیشتر در این خصوص، می توانید به سایت رسمی MySQL در آدرس زیر مراجعه کنید:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
sectionدسته بندی: آموزش مقدماتی » MySQL
related مطالب بیشتر:
» اتصال PHP به MySQL با اکستنشن MySQLi
» استفاده از JOIN و ساخت پرس و جوی ترکیبی در MySQL
» به روز رسانی ردیف ها در MySQL با UPDATE
» حذف مقادیر از MySQL با دستور DELETE
» انتخاب ردیف ها با دستور SELECT در MySQL
commentنظرات (۲۱ یادداشت برای این مطلب ارسال شده است)
more یادداشت های جدید بر اساس تاریخ ارسال در انتهای یادداشت های موجود نمایش داده می شوند.
نویسنده: علی
۲۱:۵۰ ۱۳۹۵/۰۵/۲۳
باید اعتراف کنم که با این اموزش های که به کاربران به صورت رایگان میدهید سایت وبگو یکی از بهترین وب سایت های ایرانی ایست.
نویسنده: اسماعیل
۲۳:۲۱ ۱۳۹۵/۰۸/۲۰
سلام من میخوام وقتی مقادیر ستون در گروه ها جمع بسته شده در نتیجه فقط مقدار یک گروه رو نشون بده . مثلا در مثالی که فرمودید فقط جمع مقدار ماشین آبی رو بخوایم .
مرسی از سایت بسیار خوب و عالیتون .
پاسخ: 
می توانید پارامتر WHERE را به پرس و جوی خود اضافه کنید یا اینکه در حلقه از شرط if استفاده کنید، مثال:
SELECT color,COUNT(price) FROM tb_cars WHERE color = 'blue' GROUP BY color
نویسنده: احسان
۰۳:۴۰ ۱۳۹۶/۱۱/۲۵
با سلام
لطفا راهنمایی کنید
گه ما بخایم در mysql مقادیر خونه های یک ستون رو با یک عدد خاص جمع بزنیم و جمع هرخونه با اون عدد خاص رو داخل همون خونه بریزیم ، باید چیکار کنیم ؟؟؟؟
باتشکر
پاسخ: 
می توانید از دستور UPDATE به نحو زیر استفاده کنید:
UPDATE tbl SET col = col + 1 WHERE id = 1
نکته: در این حالت نوع داده ستون مورد نظر باید از نوع عددی مانند INT و... باشد.
نویسنده: احسان
۱۲:۰۹ ۱۳۹۶/۱۱/۲۵
با دستور بالا فکر کنم برای هر خونه از یک ستون ، باید یکی یکی این دستور را اجرا کنیم ، آیا راهی هست که بشه تمام خونه های اون ستون تک به تک با این عدد خاص جمع بشه و مقدار هرخونه توی اون خونه خودش ریخته بشه ؟؟؟؟
با تشکر
پاسخ: 
اگر شماره id ها ترتیبی باشند می توانید قسمت WHERE را به صورت نمونه زیر ویرایش کتید:
 UPDATE tbl SET col = col + 1 WHERE id > 1
در غیر اینصورت راه حل دیگری وجود ندارد و باید دستور UPDATE روی ستون های هدف اجرا شود، البته با برنامه نویسی می توانید در حلقه ها اجرای دستور را به صورت داینامیک انجام دهید.
نویسنده: اسماعیل
۲۱:۴۸ ۱۳۹۷/۰۸/۱۴
دوستان سلام
من یه سوال دیتابیستی داشتم لطفا کمکم کنید با تشکر
یه جدول بانک اطلاعاتی دارم میخوام یه شخصی را به قید قرعه انتخاب کنم مثلا قبلا همه افراد ثبت نام کردند امتیاز هریک هم در ابتدا صفر است وقتی قرعه کشی می کنم مثلا از ده نفر 4 نفر را انتخاب می کنم به صورت رندم و هریک امتیاز می گیرند و از لیست قرعه کنار میرن و در بار دوم برای یه پرونده دیگر میخوام قرعه کشی کنم میام ابتدا چک می کنم در کل جدول افرادی که قرار است انتخاب شوند مثلا هریک یک امتیاز دارند بعضی ها هم دو امتیاز میخوام سیستم عدالت توشون برقرار باشه
مثلا
اقای علی محمدی با امتیاز 1 و رضا حمیدی با امتیاز 1 و مجید مجیدی با امتیاز 2 و رحیمی رحیمی با امتیاز 2
کوئری میخوام بنویسم که بیاد ابتدا اونایی که امتیاز شون از همه کمتر را از لیست انتخاب کند و بین اونا قرعه کشی کند و از قرعه کنار برن
ارتباط جداول و نحوه کوئریش چجوری میشه تشکر
پاسخ: 
سوالتان کلی است و پاسخ کوتاهی ندارد، با توجه به توضیحات می توانید به چند روش این کار را انجام دهید، به طور مثال در گام اول یک جدول امتیازات داشته باشید که ستون های نام کاربر، امتیاز، وضعیت شرکت در قرعه کشی و... داشته باشد، قاعدتا باید یک جدول مختص اطلاعات کاربران نیز داشته باشید، در کدهای برنامه کلاس یا تابعی بنویسید که بحث امتیازدهی اولیه را انجام دهد (اجرای پرس و جوی UPDATE در MySQL)، یک کلاس یا تابع نیز بنویسید و یک متغیر با مقدار اولیه 1 تعریف کنید تا در حلقه while در پرس و جو به صورت داینامیک در قسمت WHERE جایگزین شده و در هر دور کاربرانی که امتیاز مشترکی دارند SELECT شوند و متد قرعه کشی روی آنها اعمال و نتیجه به صورت آرایه نگهداری شود، در انتهای حلقه مقدار متغیر را یک واحد افزایش دهید و در دور بعدی مقدار جدید را در پرس و جو جایگزین کرده و آرایه نتایج را با اطلاعات جدید به روز کنید و همین روال تا دور آخر، ملاحظه می کنید که هر قسمت باید به صورت جداگانه کدنویسی و متد یا تابع تعریف شود.
نویسنده: کیوان
۱۷:۰۷ ۱۳۹۹/۰۱/۲۶
چطور میشه مبلغ هزینه ها تا هر ردیف در ردیف جدید نشان دهد بطور مثال
ردیف هزینه       ردیف جمع 
500 500
200 700
100 800
دیتابیس sqlite
ممنون
پاسخ: 
برای محاسبه و دریافت مجموع مقادیر ردیف های یک ستون در ستون جدید به صورت توالی باید یک متغیر با مقدار صفر تعریف و به شکل نمونه زیر پرس و جو را بنویسید:
SET @CumulativeSum := 0;
SELECT col_1, (@CumulativeSum := @CumulativeSum + col_1) AS total FROM `tbl` WHERE 1
البته این پرس و جو در MySQL تست شده اما برای SQLite هم باید به همین روال باشد.
more لطفا پیش از ارسال یادداشت نکات زیر را مد نظر داشته باشید:
- مواردی که به کلی خارج از موضوع این مطلب هستند را در فرم منوی "تماس با ما" مطرح و پاسخ را از طریق ایمیل دریافت کنید.
- به سوالات کلی، مبهم، غیرضروری و مشکلاتی که تلاشی برای رفع آن نکرده باشید پاسخ کوتاه و مختصر داده خواهد شد!
- کدها و اسکریپت های طولانی را ترجیحا در یک صفحه وب آنلاین قرار دهید تا امکان تست و بررسی وجود داشته باشد.
- تمام یادداشت ها بررسی و برای هر کاربر زمان مشخصی جهت پاسخگویی در نظر گرفته می شود، لطفا از طرح سوالات متعدد در بازه زمانی کوتاه خودداری کنید.





3 × 4
 refresh
آخرین دیدگاه ها
more برای دسترسی سریع به یادداشت مربوطه می توانید از لینک مطلب در کادر زیر استفاده کنید.
form نبی
در:
‏asc و desc رو جابجا نوشتی
۱۳۹۹/۰۷/۰۴

form ساناز محمدی
در:
سلام مرسی از کدی که گذاشتید ♥
۱۳۹۹/۰۷/۰۳

form mahtab
در:
سلام خسته نباشین ببخشید میخواستم بپرسم که چجوری میتونیم یه کلیپ رو از کامپیوتر از انیستا دانلود کنیم ؟؟ اها اینم بگم...
۱۳۹۹/۰۷/۰۲

form سعید
در:
سلام دستتون درد نکنه از پروژه شما استفاده کردم فقط یه مشکل اگه در یک صفحه دو تا لیست کشویی داشته باشیم چطوری...
۱۳۹۹/۰۷/۰۱

form Iman Mafakheri
در:
سلام من یه قالب خارجی اوردم راست چینش کردم حتی فونتشم تغییر دادم اما متاسفانه وقتی متن فارسی مینویسم حروف رو جدا جدا مینویسه نمیدونم...
۱۳۹۹/۰۶/۳۰

form میثم صدیق
در:
سلام برای اضافه کردن یک المان با (append) من یک کلید گذاشتم اضافه میشه ولی با هر بار کلیک کردن اضافه میشود اگر بخواییم یک...
۱۳۹۹/۰۶/۲۹

form amin
در:
سلام و خسته نباشید می خواستم Slash ( / ) رو به صورت اتوماتیک از تمامی URL ها حذف کنم البته با کمک htaccess...
۱۳۹۹/۰۶/۲۵

form محمد
در:
سلام وقت بخیر من یه همچین کدی دارم میشه لطفا راهنمایی کنید مشکلش چیه؟ سپاسگزارم
۱۳۹۹/۰۶/۲۴

form سروش
در:
سلام . من اطلاعات را از دیتابیس دریافت میکنم و در جدول میبینم . میخوام مثلا 6 مورد آخر را در یک ردیف ببینم و...
۱۳۹۹/۰۶/۲۳

form Behdad kanani
در:
سلام اصلا نمی شه اینکارو انجام داد
۱۳۹۹/۰۶/۲۲

form جعفری
در:
سلام قبل از تبدیل تاریخ نوشتید مثلا خب این مقدار تاریخ برای من در دیتابیس در جدولی بنام startedtm بصورت یونیکس...
۱۳۹۹/۰۶/۲۰

form احمد
در:
با عرض سلام و خسته نباشید ببخشید برای فایل دانلودی باید از چه دستوری استفاده کنم فایل با پسوند pdf رو میخوام بزارم اگر امکانش...
۱۳۹۹/۰۶/۲۰

form شیما
در:
سلام آیا وقتی در قسمت وبلاگ دوستان وبلاگی رو ثبت میکنیم صاحب وبلاگ با استفاده از برنامه های خاصی میتونه متوجه بشه ؟
۱۳۹۹/۰۶/۱۹

form mahtab
در:
سلام خسته نباشین من نمیدونم چرا مدیریت وبم برام باز میشه اما وقتی میزنم مشاهده وب رو میزنم نمیاره واسم فقط امیدوارم هک...
۱۳۹۹/۰۶/۱۸

form ساناز محمدی
در:
سلام دوباره این ساب دامین بنده هست اگر یکی از مطالب را مشاهده کنید هر محصولی که ارسال شده داخل از طریق مدیریت یک...
۱۳۹۹/۰۶/۱۷
form محسن
در:
سلام و عرض ادب مجدد کد زیر هم کار نمیکنه مشکل چی میتونه باشه ؟!
۱۳۹۹/۰۶/۱۶
form ساناز محمدي
در:
سلام بنده یه ساب دامین دارم با دامنه شخصی حالا داخل این ساب دامین طرف آدرس سایت خودش رو داخل ساب دامین بنده میذاره مثلآ...
۱۳۹۹/۰۶/۱۶
form محسن
در:
سلام و عرض ادب لطفا راهنمایی کنید مشکل کد زیر چیه
۱۳۹۹/۰۶/۱۶
form احمد
در:
با عرض سلام ببخشید من با دستور append میخوام یه سطری رو به سبدم اضافه کنم ولی چطوری باید داخل append تگ ها رو بنویسم...
۱۳۹۹/۰۶/۱۵
form احمد
در:
با عرض سلام مجدد و خسته نباشید ببخشید طبق فرمایش شما من قبل از دستور else دستور if را نوشته بودم تا جایی که اطلاع...
۱۳۹۹/۰۶/۱۴
form احمد
در:
با سلام ببخشید طریقه استفاده از دستور else در ایجکس به چه صورت هست ایا باید دوباره فانکشن دان رو در هنگام شرط گذاشتن بزارم....
۱۳۹۹/۰۶/۱۳
form مجتهد
در:
سلام ابتدائا از سایت مفیدتون تشکر می کنم. من خیلی از آموزه هام رو از سایت شما یاد گرفتم. یه سوال دارم...
۱۳۹۹/۰۶/۱۳
form محمد حسین
در:
سلام لینک های من وقتی کلیک شوند اررور 404 تولید میکنند. علت چیست؟
۱۳۹۹/۰۶/۱۱
form حسین
در:
سلام بر شما و با تشکر از سایت خوبتون. من ابتدا با تابع mail پیش رفتم و یک if ایجاد کردم که اگر ایمیل...
۱۳۹۹/۰۶/۱۰
form mehdi
در:
خطای -22 مربوط به چیه؟
۱۳۹۹/۰۶/۱۰
  در انتظار بررسی: ۰
 پاسخگویی به سوالات ممکن است تا 24 ساعت زمان ببرد.