parsgreen.com
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 مطالب بیشتر:
» ساخت دیتابیس، جدول و ردیف با دستور CREATE در MySQL
» استفاده از JOIN و ساخت پرس و جوی ترکیبی در MySQL
» حذف مقادیر از MySQL با دستور DELETE
» آشنایی با mysql_query در php و دیتابیس
» نحوه استفاده از WHERE در MySQL
commentنظرات (۱۷ یادداشت برای این مطلب ارسال شده است)
نویسنده: ایمان
زمان: ۲۱:۲۹:۳۲ - تاریخ: ۱۳۹۱/۱۱/۱۸
سلام
من میخوام مثلآ بعد از اینکه کاربر یک فرم رو پر کرد بعد یه گزارش از اطلاعاتی که وارد کرده رو بش بدم:
$CurrentID = "SELECT count(*) FROM hotel";
$query = "SELECT * FROM hotel WHERE id=$CurrentID";
... پیغام خطا داد. بعد اینو نوشتم:
$query = "SELECT * FROM hotel where id=MAX(id)";
... پیغام خطا داد.
لطف کنید بگید اشکال کارم چیست.
وقتی بطورت استتیک مینویسم درست کار میکنه:
$query = "SELECT * FROM hotel where id=66";
پاسخ: 
سلام
شیوه استفاده از count و max در کدهایی که نوشتید درست نیست، به مثال هایی که در آموزش ارائه شده توجه کنید، یک بار از توابع در query استفاده شده و یک بار هم در هنگام گرفتن خروجی از آرایه موجود در mysql_fetch_array و حلقه while.
یک روش دیگر استفاده از SELECT، ORDER BY و DESC است، به طور مثال:
$sql = mysql_query("SELECT id FROM table ORDER BY id DESC LIMIT 1") 
or die(mysql_error());
while($row = mysql_fetch_array($sql)){
$id = $row['id'];
}
نویسنده: ایمان
زمان: ۲۱:۴۴:۰۳ - تاریخ: ۱۳۹۱/۱۱/۱۸
اگه متوجه منظورم نشدید بهتر بگم من میخوام برای اینکار آخرین سطر جدول رو چاپ کنم و برای این کار از ستون id که کلید اصلیم هست و تابع MAX کمک گرفتم که ناموفق بودم، لطفآ ایراد کارم رو بگید یا اگه روش بهتری سراغ دارید ...
خیلی ممنون
نویسنده: ایمان
زمان: ۰۲:۱۷:۴۲ - تاریخ: ۱۳۹۱/۱۱/۱۹
بسیــــار ممنون
نویسنده: آرش
زمان: ۱۰:۳۰:۳۹ - تاریخ: ۱۳۹۲/۰۸/۱۰
سلام من یه سوال داشتم یه مثال با having میخواستم در سامانه دانشجو ممنون میشم کمکم منید.
پاسخ: 
سلام
HAVING نقش WHERE را در حالتی که از توابع تجمعی در پرس و جو استفاده می کنیم بازی می کند، مثال:
SELECT user FROM tbl GROUP BY age HAVING COUNT(*) > 1;
مثالی دیگر در حالت معمولی:
SELECT id FROM tbl HAVING  id > 10;
نویسنده: جواد
زمان: ۱۷:۰۳:۴۰ - تاریخ: ۱۳۹۲/۰۸/۱۳
من یه فیلد عددی در پایگاه داده دارم مقدار اولیه اش 100 هست چطور میشه کاری کرد که این عدد در هر ثانیه افزایش پیدا کنه
پاسخ: 
افزایش مقادیر موجود در دیتابیس به صورت خودکار با استفاده از قابلیت event scheduler در خود MySQL ممکن است، مثال:
CREATE EVENT e_update ON SCHEDULE EVERY 1 SECOND DO UPDATE tbl_name SET col_name = col_name + 1;
نکته: نوع داده ستون مورد نظر باید از نوع INT بوده یا اینکه یک مقدار عددی در آن ست شده باشد.
نویسنده: جواد
زمان: ۱۶:۱۱:۵۰ - تاریخ: ۱۳۹۲/۰۸/۱۴
واقعا تشکر کارم راه افتاد کاری نمی تونم براتون انجام بدم بجز دعا
نویسنده: جواد
زمان: ۰۰:۰۹:۴۷ - تاریخ: ۱۳۹۲/۰۸/۱۵
من با راهنمایی شما تونستم یه فیلد افزایشی در پایگاه داده ایجاد کنم و مقدارش رو از طریق یه رویداد اژاکسی وارد صفحه کردم ابتدا مشکلم این بود که فقط مقدار فعلی فیلد در صفحه نمایش داده می شد و روند افزایشی نداشت اما با تابع setinterval که رویداد آژاکسی رو درون اون قرار دادم مشکل حل شد و روند افزایشی بوجود اومد اما بعد از 32 بار افزایش روندش متوقف میشه و هر کاری می کنم ادامه پیدا نمیکنه ایا راهی هست متوقف نشه؟ ضمن اینکه من با متد get این کار رو میکنم
پاسخ: 
به نظر IP شما به دلیل ارسال درخواست های مکرر در یک بازه زمانی کوتاه توسط فایروال سرور بلاک می شود، باید بین درخواست های پی در پی وقفه بیشتری ایجاد کنید.
نویسنده: جواد
زمان: ۱۴:۵۳:۰۲ - تاریخ: ۱۳۹۲/۰۸/۱۵
ببخشید سوالم طولانی شد وقتی که تاخیر در اجرای setinterval رو روی 1000 میلی ثانیه میزارم روند افزایشی فقط 5 بار تکرار و وقتی مثلا روی 0 میزارمش 32 بار اجرا میشه ایا مرورگر در ساخت شی xmlhttp محدودیت داره چون بار هر بار اجرای تابع احتمالا یک نمونه از این شی ساخته میشه ضمن اینکه من با مرورگر ie تست می کنم چون رویداد اژاکسی روی فایرفاکسم انجام نمیشه راهی داره فعالش کنم شاید روی فایرفاکس اجرا بشه؟ بازم ممنون
پاسخ: 
به نظر کدنویسی شما دارای اشکلاتی است، Ajax در اکثر مرورگرهای استاندارد پشتیبانی می شود و مرورگر نیز در حالت عادی محدودیتی در ساخت شی xmlhttp ندارد!
نویسنده: جواد
زمان: ۱۵:۲۳:۳۱ - تاریخ: ۱۳۹۲/۰۸/۱۶
من کد رو از سایت شما کپی گرفتم و اگه کدنویسی ایراد داره چرا روی IE اجرا میشه این کدشه اگه ایرادی داره خواهشا بگید ممنون میشم
حذف شد
پاسخ: 
به جای حالت زیر:
http://127.0.0.1:8888
از آدرس دهی به این صورت استفاده کنید:
http://localhost
نویسنده: جواد
زمان: ۱۶:۵۹:۴۶ - تاریخ: ۱۳۹۲/۰۸/۱۶
اقا دستون درد نکنه فک می کنم همون که اول گفتید درست بود مشکل از سرور بود چون mysql رو ارتقا دادم درست شد و روند افزایشی ادامه پیدا کرد بازم ممنون اگه تونستید یه مقاله درباره ی تنظیمات mysql ارائه بدید چون اموزش این قسمت تقریبا در اینترنت موجود نیست و خیلی هم مهمه. با تشکر از سایت خوبتون
نویسنده: shahin
زمان: ۰۰:۱۹:۵۴ - تاریخ: ۱۳۹۲/۱۰/۰۳
سلام دوستان من میخوام مقادیر یک ستون با تابع sum جمع بزنم
مقادیر از 2 تا جدول واکشی می کنم
حالا میخوام مقدار فیلد nomre جمع کنم تابع sum کجاش بزارم؟
$result = mysql_query("SELECT karname.nomre,user.nam, FROM karname,user where 
user.id_user=karname.id_user");
پاسخ: 
سلام
به نظر شیوه زیر برای هدف شما کاربرد دارد (تست نشده!):
$result = mysql_query("SELECT SUM(karname.nomre),user.nam, FROM karname,user where 
user.id_user=karname.id_user");
نویسنده: reza
زمان: ۱۳:۰۴:۱۷ - تاریخ: ۱۳۹۳/۰۳/۱۲
سلام دوست گرامی من می خوام یه شمارنده توی جدولم درست کنم که یکی یکی اضافه بشه و در هنگام حذف رکورد یه دونه کم شه
پاسخ: 
سلام
برای این کار باید در جدولی یک ستون از نوع INT ایجاد کنید و در هنگام اجرای پرس و جوها، متناسب با هدفتان، یک پرس و جو هم برای افزایش یا کاهش مقدار ستون مورد نظر اجرا کنید، این کار به صورت یک خطی (نمونه زیر) قابل انجام است:
UPDATE tbl SET stats = stats + 1 WHERE id = 1
به این ترتیب مقدار موجود با عدد 1 جمع شده و برای تفریق از علامت - استفاده کنید.
نویسنده: یارمحمدی
زمان: ۱۰:۵۳:۵۴ - تاریخ: ۱۳۹۳/۰۸/۲۵
سلام بر دوستان. ببخشید من می خوام میانگین یک ستون را در اکسس حساب کنم. اما اطلاعات آن ستون از نوع time می باشد. خواهشمند است اگه راهی داره بفرمایید.
پاسخ: 
سلام
لطفا به سایت های مرتبط با ASP.NET و پایگاه داده ACCESS مراجعه کنید.
نویسنده: امین ولی زاده
زمان: ۱۸:۳۵:۵۱ - تاریخ: ۱۳۹۴/۰۲/۱۱
سلام من با استفاده از رکورد ست ها و insert توانستم مقدار را در ستونی از جدولی ذخیره کنم الان میخوام هنگام درج داده جدید در جدول جدید مقداری را نیز کسر کنم تا به یک حداقل مقداری برسه پیغام موجودی کم است بدهم
مثلا سایت خرید شارژ که اول یه مقداری را به عنوان مبلغ شارژ بگیرم بعد با هر خرید یا رزرو شارژ مقدار را از آن کسر کنم
پاسخ: 
سلام
برای این کار دو راه وجود دارد، می توانید چند برس و جو (به فرض برای دریافت اطلاعات اولیه از دیتابیس جهت بررسی، افزایش یا کاهش مقدار در کدهای PHP و سپس پرس و جو برای آپدیت اطلاعات و...) را جداگانه بنویسید، یا اینکه اگر ستون مورد نظر از نوع INT باشد، در یک پرس و جو بدون دریافت قبلی اطلاعات، مقداری را کاهش یا افزایش دهید، مثال برای این حالت:
UPDATE tbl SET col = col - 1 WHERE id = 1
البته برای بررسی دقیق قاعدتا باید بر موارد دیگری نیز مسلط باشید که جای طرح آنها در آموزش های مقدماتی است (باید فرآیند آموزش های مقدماتی را طی کرده باشید، در غیر اینصورت درک روال کار پیچیده به نظر خواهد رسید!).
نویسنده: دانیال
زمان: ۲۰:۴۵:۱۸ - تاریخ: ۱۳۹۵/۰۱/۱۸
با سلام و خسته نباشید.
ببخشید اگه ما یک خونه تو دیتابیس داشته باشیم و بخواهیم مقدار اون را با یک مقدار جدید جمع کنیم و دوباره توی همون خونه ذخیره کنیم باید چیکار کنیم.
البته نمیخوام یه بار با کوئری سلکت مقدار رو بخونم و بعد با آپدیت اون رو ذخیره کنم.
ممنون میشم اگه جواب بدید.
پاسخ: 
می توانید از دستور UPDATE به نحو زیر استفاده کنید:
UPDATE tbl SET col = col + 1 WHERE id = 1
نکته: در این حالت نوع داده ستون مورد نظر باید از نوع عددی مانند INT و... باشد.
paged صفحه 1 از 2




more لطفا دقت کنید که یادداشت های زیر منتشر نشده و حذف خواهند شد:
- یادداشت های خارج از موضوع این مطلب.
- سوالات کلی، غیر ضروری و مشکلاتی که هیچ تلاشی برای رفع آن نکرده باشید.
- نظرات حاوی کدها و اسکریپت های خیلی طولانی (به طور مثال کد کامل قالب وبلاگ).

8 × 9
 refresh
آگهی
seonab.com
طراحی نرم افزار اندروید
رنگین کمان عکس
Ranginkamaan.com

آرشیو عکس های باکیفیت با موضوعات متنوع...