آگهی
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
» آموزش MySQL، سیستم مدیریت پایگاه داده
» استفاده از WHERE در پرس و جوی MySQL
» به روز رسانی ردیف ها در MySQL با UPDATE
» ساخت دیتابیس، جدول و ستون با دستور CREATE در 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 شوند و متد قرعه کشی روی آنها اعمال و نتیجه به صورت آرایه نگهداری شود، در انتهای حلقه مقدار متغیر را یک واحد افزایش دهید و در دور بعدی مقدار جدید را در پرس و جو جایگزین کرده و آرایه نتایج را با اطلاعات جدید به روز کنید و همین روال تا دور آخر، ملاحظه می کنید که هر قسمت باید به صورت جداگانه کدنویسی و متد یا تابع تعریف شود.
more لطفا پیش از ارسال یادداشت نکات زیر را مد نظر داشته باشید:
- مواردی که به کلی خارج از موضوع این مطلب هستند را در فرم منوی "تماس با ما" مطرح و پاسخ را از طریق ایمیل دریافت کنید.
- به سوالات کلی، مبهم، غیرضروری و مشکلاتی که تلاشی برای رفع آن نکرده باشید پاسخ خاصی داده نخواهد شد.
- کدها و اسکریپت های طولانی را ترجیحا در یک صفحه وب آنلاین قرار دهید تا امکان تست و بررسی وجود داشته باشد.
- تمام یادداشت ها بررسی و برای هر کاربر زمان مشخصی جهت پاسخگویی در نظر گرفته می شود، لذا از طرح سوالات متعدد در بازه زمانی کوتاه خودداری کنید.




5 × 8
 refresh
نکته:
با توجه به تاریخ نگارش آموزش های سایت و پیشرفت تکنولوژی های مرتبط با وب در سالیان اخیر، محتوای برخی از مطالب قدیمی ممکن است نیاز به ویرایش و به روزرسانی داشته باشد که این کار هم زمان با تهیه نسخه جدید «وبگو» به مرور در حال انجام است، لطفا در استفاده از مطالب سایت به این نکته دقت داشته و حتی المقدور از چند منبع مختلف استفاده نمائید.
آخرین دیدگاه ها
form حامد
در:
عالی بود
۲۱:۳۸:۳۱ ۱۳۹۸/۱۱/۰۷

form ناشناس
در:
سلام خیلی ممنون بابت پاسخ سریعتون خیلی دنبال همین دو خط کد بودم برای یادگیری سریع ریجکس ولی با سرچ پیدا نکردم و...
۱۸:۰۳:۴۳ ۱۳۹۸/۱۱/۰۷

form فرید
در:
درود بشما، میخواستم بدونم مثل کلمه @، کلمه ایی ساین هم داریم؟ با تشکر
۱۲:۱۹:۰۷ ۱۳۹۸/۱۱/۰۷

form ناشناس
در:
سلام میشه لطفا به سوالم جواب بدید من می خوام با استفاده از عبارات با قاعده تمام تگ های html رو تشخیص بدم...
۱۰:۱۳:۰۵ ۱۳۹۸/۱۱/۰۷

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

form رسول
در:
سلام میخوام بجای نمایش متن، متن موجود در یه لینکو بهم نشون بده ممنون میشم کمکم کنید
۱۳:۳۸:۱۸ ۱۳۹۸/۱۱/۰۶

form حسن
در:
من مطابق کد نوشته شده شما میخواستم آدرس
۱۴:۱۰:۱۳ ۱۳۹۸/۱۱/۰۵

form saemrezaei
در:
سلام واقعا عالییه خیلی استفاده کردم به وبلاگم سر بزن لطفا.
۱۳:۳۷:۳۴ ۱۳۹۸/۱۱/۰۵

form نصراله رضایی
در:
سلام در ادمین های گذشته که باز می کنم تصاویر مشاهده نمی شود و من را به آدرس Huge domains.com ارجاع می...
۲۲:۳۹:۵۳ ۱۳۹۸/۱۱/۰۴

form نقدی زاده
در:
واقعا مرسی یه overflow-y:hidden به body دادم اسکرل افقی برای صفحه نمایش تبلت از بین رفت
۱۴:۴۴:۱۳ ۱۳۹۸/۱۱/۰۴

form وحید پوربشیری
در:
سلام لطفا یکی که خوب جاوا اسکریپت بلده بیاد به این آیدی ت.ل.گ.ر.ا.م Alpha2020@ یه کد برام بنویسه پول هم میدم
۱۱:۱۲:۳۲ ۱۳۹۸/۱۱/۰۲

form امین
در:
سلام تشکر بابت راهنماییتون. منظور من این بود که: الان با ثبت نام کاربر a در سایت اطلاعاتش در دیتابیس ثبت میشه و یک پوشه...
۰۳:۱۲:۴۰ ۱۳۹۸/۱۱/۰۲

form شریفی
در:
سلام معنی متن زیر چیست؟
۱۸:۲۸:۰۹ ۱۳۹۸/۱۱/۰۱

form امین
در:
سلام خسته نباشید یه اسکریپت دارم که وقتی کاربر ثبت نام میکنه یه پوشه و محتواش ایجاد میشه. میخوام یه صفحه با هر ثبت نام...
۱۴:۱۸:۵۱ ۱۳۹۸/۱۰/۳۰

form Admin
در:
سلام دوست عزیز. میشه بگویید چگونه میتوان در وبلاگ کادری را باز کرد که بازدید کنندگان بتوانند در آن چیزی بنویسند. ممنون میشم...
۱۱:۲۸:۵۰ ۱۳۹۸/۱۰/۳۰
آگهی