MySQL ฟังก์ชันการรวม: ผลรวม, จำนวนนับ AVG และแม็กซ์

ฟังก์ชันรวมคืออะไรใน MySQL?

An ฟังก์ชั่นรวม ฟังก์ชันการรวมข้อมูลจะอ่านข้อมูลหลายแถวจากคอลัมน์เดียว แล้วรวมเข้าเป็นค่าเดียว ฟังก์ชันการรวมข้อมูลนั้นเกี่ยวข้องกับสิ่งต่อไปนี้:

• การคำนวณบนหลายแถว
• ของคอลัมน์เดียวของตาราง
• และส่งคืนค่าเดียว

มาตรฐาน ISO กำหนดฟังก์ชันรวมห้า (5) ฟังก์ชัน ได้แก่:

1. COUNT
2. SUM
3. AVG
4. นาที
5. MAX

มีกฎข้อหนึ่งที่ใช้ได้กับทั้งห้าข้อ: ฟังก์ชันรวมจะไม่สนใจค่า NULLCOUNT(*) เป็นข้อยกเว้นเพียงอย่างเดียว และเราจะมาดูเหตุผลกันด้านล่าง

เหตุใดจึงต้องใช้ฟังก์ชันรวม

แต่ละระดับขององค์กรมีความต้องการข้อมูลที่แตกต่างกัน ผู้บริหารระดับสูงมักสนใจตัวเลขโดยรวมมากกว่ารายละเอียดปลีกย่อย

ฟังก์ชันการรวมช่วยให้เราสร้างข้อมูลสรุปจากฐานข้อมูลได้อย่างง่ายดาย

ตัวอย่างเช่น จากฐานข้อมูล myflix ของเรา ฝ่ายบริหารอาจต้องการรายงานต่อไปนี้:

• ภาพยนตร์เช่าน้อยที่สุด
• ภาพยนตร์ที่ถูกเช่ามากที่สุด
• จำนวนครั้งเฉลี่ยที่ภาพยนตร์แต่ละเรื่องถูกเช่าในหนึ่งเดือน

รายงานทั้งหมดข้างต้นได้มาจากฟังก์ชันการรวมข้อมูล เรามาดูรายละเอียดของแต่ละรายงานกัน

ฟังก์ชัน COUNT

ฟังก์ชัน COUNT จะส่งคืนจำนวนรวมของค่าในฟิลด์ที่ระบุ ทั้งสำหรับชนิดข้อมูลตัวเลขและไม่ใช่ตัวเลข เช่นเดียวกับฟังก์ชันการรวมข้อมูลทุกประเภท COUNT(column) จะไม่รวมค่า NULL

COUNT(*) เป็นรูปแบบพิเศษที่ใช้สำหรับนับจำนวนแถวทั้งหมดในตาราง นอกจากนี้ยังนับจำนวนแถวด้วย ค่าว่าง และจะเกิดการซ้ำซ้อน เนื่องจากนับจำนวนแถวแทนที่จะนับค่า

ตาราง movierentals มีข้อมูลดังต่อไปนี้:

สมมติว่าเราต้องการทราบจำนวนครั้งที่ภาพยนตร์ที่มีรหัส 2 ถูกเช่าออกไป

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

ดำเนินการสิ่งนี้ใน MySQL ม้านั่งทำงานของช่างเครื่อง เมื่อตรวจสอบกับ myflixdb จะได้ผลลัพธ์เป็น 3 เนื่องจากมีสามแถวที่มี movie_id เป็น 2

DISTINCT คำสำคัญ

COUNT ตอบว่า “จำนวนเท่าไหร่” คำถามต่อไปมักจะเป็น “จำนวนเท่าไหร่” ต่าง “หนึ่งเดียว” และนั่นคือสิ่งที่ DISTINCT มีไว้เพื่อ

คำหลัก DISTINCT จะตัดรายการที่ซ้ำกันออกจากผลลัพธ์ของเราโดยแบ่งตามกลุ่มping ค่าที่เหมือนกันทุกประการอยู่ด้วยกัน ดังที่ภาพประกอบด้านบนแสดงให้เห็น

ขั้นแรก เรามาลองรันคำสั่งค้นหาแบบง่ายๆ กันก่อน

SELECT `movie_id` FROM `movierentals`;

ทีนี้ลองใช้คำสั่งค้นหาเดียวกันโดยใช้คำว่า DISTINCT ดู:

SELECT DISTINCT `movie_id` FROM `movierentals`;

คำสั่ง DISTINCT จะตัดข้อมูลที่ซ้ำกันออก:

COUNT เทียบกับ COUNT(*) เทียบกับ COUNT(DISTINCT): คุณควรใช้ตัวไหน?

DISTINCT สามารถวางได้เช่นกัน ภายใน ฟังก์ชันรวม และนี่คือจุดที่ผู้เริ่มต้นส่วนใหญ่พลาด track ซึ่งจำนวนแถวที่ถูกนับจริง ๆ นั้น แบบฟอร์มทั้งสี่ด้านล่างนี้ทำงานกับตารางข้อมูลการเช่าภาพยนตร์ห้าแถวเดียวกันที่แสดงไว้ก่อนหน้านี้ แต่ผลลัพธ์ที่ได้ไม่เหมือนกันทั้งหมด ความแตกต่างเกิดจากสองคำถาม: แบบฟอร์มนั้นนับจำนวนแถวหรือจำนวนค่า และมันเก็บข้อมูลซ้ำหรือไม่?

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

💡 เคล็ดลับ: ใช้ COUNT(*) สำหรับนับจำนวนแถว, COUNT(column) เมื่อค่า NULL หมายถึง “ไม่เกี่ยวข้อง” และ COUNT(DISTINCT column) สำหรับค่าที่ไม่ซ้ำกัน คำสั่งตรงข้ามกับ DISTINCT คือ ALL ซึ่งเป็นค่าเริ่มต้น และจึงไม่ค่อยได้เขียนออกมา

ฟังก์ชัน MIN

ฟังก์ชัน MIN ส่งกลับค่าที่น้อยที่สุดในเขตข้อมูลตารางที่ระบุ.

สมมติว่าเราต้องการทราบปีที่ภาพยนตร์เรื่องเก่าที่สุดในคลังของเราออกฉาย MySQLฟังก์ชัน MIN ของ 's ช่วยให้เราได้สิ่งนั้น

SELECT MIN(`year_released`) FROM `movies`;

ผลลัพธ์:

ฟังก์ชั่น MAX

เช่นเดียวกับชื่อที่แนะนำ ฟังก์ชัน MAX จะตรงกันข้ามกับฟังก์ชัน MIN มัน ส่งคืนค่าที่ใหญ่ที่สุดจากฟิลด์ตารางที่ระบุ.

สมมติว่าเราต้องการทราบปีที่ภาพยนตร์เรื่องล่าสุดในฐานข้อมูลของเราออกฉาย ตัวอย่างต่อไปนี้จะแสดงผลลัพธ์นั้น

SELECT MAX(`year_released`) FROM `movies`;

ผลลัพธ์:

ฟังก์ชัน SUM

MIN และ MAX จะเลือกค่าที่มีอยู่แล้วจากคอลัมน์ SUM และ AVG คำนวณหาตัวเลขใหม่จากทั้งคอลัมน์

สมมติว่าเราต้องการทราบยอดรวมของการชำระเงินทั้งหมดจนถึงปัจจุบัน MySQL SUM ฟังก์ชัน คืนค่าผลรวมของค่าทั้งหมดในคอลัมน์ที่ระบุ. SUM ใช้ได้กับช่องตัวเลขเท่านั้นและ ค่าว่าง (NULL) จะไม่ถูกนำมาแสดงในผลลัพธ์.

ตารางต่อไปนี้แสดงข้อมูลในตารางการชำระเงิน

คำสั่ง SQL ด้านล่างนี้จะดึงข้อมูลการชำระเงินทั้งหมดและรวมผลลัพธ์ทั้งหมดไว้ในผลลัพธ์เดียว: 2500 + 2000 + 6000 = 10500

SELECT SUM(`amount_paid`) FROM `payments`;

ผลลัพธ์:

AVG ฟังก์ชัน

การขอ MySQL AVG ฟังก์ชัน ส่งกลับค่าเฉลี่ยของค่าในคอลัมน์ที่ระบุ- เช่นเดียวกับฟังก์ชัน SUM ใช้งานได้กับประเภทข้อมูลตัวเลขเท่านั้น.

สมมติว่าเราต้องการหาค่าเฉลี่ยของจำนวนเงินที่จ่ายไป เราสามารถใช้คำสั่งต่อไปนี้ ซึ่งจะนำผลรวม 10500 มาหารด้วยจำนวนแถวการชำระเงินที่ไม่ใช่ค่าว่าง (non-NULL) ทั้งสามแถว

SELECT AVG(`amount_paid`) FROM `payments`;

ผลลัพธ์:

⚠️คำเตือน: AVG หารด้วยจำนวนแถวที่ไม่ใช่ค่าว่าง ไม่ใช่จำนวนแถวทั้งหมดของตาราง ค่าว่างจะถูกข้ามไปแทนที่จะนับเป็นศูนย์ ซึ่งจะทำให้ค่าเฉลี่ยสูงขึ้นโดยไม่รู้ตัว ใช้งาน AVG(IFNULL(`amount_paid`, 0)) เมื่อค่าที่หายไปหมายถึงศูนย์

ตัวอย่างการใช้งานจริง: การรวมฟังก์ชันการรวมเข้ากับคำสั่ง GROUP BY

แต่ละฟังก์ชันข้างต้นส่งคืนตัวเลขหนึ่งตัวสำหรับตารางทั้งหมด การเพิ่ม จัดกลุ่มตาม เงื่อนไขส่งคืนตัวเลขหนึ่งตัว ต่อกลุ่ม แต่ในทางกลับกัน นั่นคือวิธีการสร้างรายงานที่แท้จริง

ตัวอย่างต่อไปนี้จะจัดกลุ่มสมาชิกตามชื่อ จากนั้นจะนับจำนวนการชำระเงินทั้งหมด จำนวนเงินเฉลี่ยต่อการชำระเงิน และยอดรวมทั้งหมดของการชำระเงินสำหรับสมาชิกแต่ละคน

SELECT m.`full_names`,
       COUNT(p.`payment_id`) AS `paymentscount`,
       AVG(p.`amount_paid`) AS `averagepaymentamount`,
       SUM(p.`amount_paid`) AS `totalpayments`
FROM members m, payments p
WHERE m.`membership_number` = p.`membership_number`
GROUP BY m.`full_names`;

ดำเนินการตัวอย่างข้างต้นใน MySQL โปรแกรม Workbench ให้ผลลัพธ์ดังต่อไปนี้

คำสั่ง SQL นี้เชื่อมตารางสองตารางเข้าด้วยกันในส่วน WHERE ซึ่งเป็นรูปแบบการเชื่อมตารางแบบเก่าโดยใช้เครื่องหมายจุลภาค แต่โค้ดสมัยใหม่จะเขียนตรรกะเดียวกันนี้โดยใช้การเชื่อมตารางโดยตรง การเชื่อมต่อภายใน … บนโปรดทราบด้วยว่าคอลัมน์ที่ไม่ใช่คอลัมน์รวมทั้งหมดในรายการ SELECT จะต้องปรากฏใน GROUP BY หรือ MySQL 5.7 และเวอร์ชันต่อมาปฏิเสธการค้นหาภายใต้ ONLY_FULL_GROUP_BY โปรดดูที่ เป็นทางการ MySQL การอ้างอิงฟังก์ชันรวม.

คำถามที่พบบ่อย