การใช้ฟังก์ชันกลุ่ม
การรายงานข้อมูลรวมโดยใช้ฟังก์ชันกลุ่ม
SQL มีฟังก์ชันการรวมที่กำหนดไว้ล่วงหน้าจำนวนมากซึ่งสามารถใช้ในการเขียนคิวรีเพื่อสร้างข้อมูลประเภทนี้ได้คำสั่ง GROUP BY ระบุวิธีการจัดกลุ่มแถวจากตารางข้อมูลเมื่อรวบรวมข้อมูลในขณะที่คำสั่ง HAVING จะกรองแถวที่ไม่ได้อยู่ใน กลุ่มที่ระบุ
ฟังก์ชันรวมดำเนินการต่างๆเช่นการนับแถวทั้งหมดในตารางการหาค่าเฉลี่ยข้อมูลของคอลัมน์และการสรุปข้อมูลตัวเลข ผลรวมยังสามารถค้นหาตารางเพื่อค้นหาค่า "สูงสุด" สูงสุดหรือต่ำสุด "MIN" ในคอลัมน์ เช่นเดียวกับการสืบค้นประเภทอื่น ๆ คุณสามารถ จำกัด หรือกรองแถวที่ฟังก์ชันเหล่านี้ดำเนินการกับคำสั่ง WHERE ได้ ตัวอย่างเช่นหากผู้จัดการต้องการทราบจำนวนพนักงานที่ทำงานในองค์กรสามารถใช้ฟังก์ชันรวมชื่อ COUNT (*) เพื่อสร้างข้อมูลนี้ได้ฟังก์ชัน COUNT (*) ที่แสดงในคำสั่ง SELECT ด้านล่างจะนับแถวทั้งหมดใน a ตาราง.
SELECT COUNT(*)
FROM employees;
COUNT(*)
----------
24
ตารางผลลัพธ์สำหรับฟังก์ชัน COUNT (*) คือคอลัมน์เดียวจากแถวเดียวที่เรียกว่าผลลัพธ์หรือค่าสเกลาร์ สังเกตว่าตารางผลลัพธ์มีส่วนหัวของคอลัมน์ที่ตรงกับชื่อของฟังก์ชันการรวมที่ระบุในส่วนคำสั่ง SELECT
ฟังก์ชันการรวมที่ใช้กันทั่วไปมีดังต่อไปนี้ -
SUM( [ALL | DISTINCT] expression )
AVG( [ALL | DISTINCT] expression )
COUNT( [ALL | DISTINCT] expression )
COUNT(*)
MAX(expression)
MIN(expression)
คีย์เวิร์ด ALL และ DISTINCT เป็นทางเลือกและดำเนินการเช่นเดียวกับคำสั่ง SELECT ที่คุณเรียนรู้ที่จะเขียนคีย์เวิร์ด ALL เป็นค่าดีฟอลต์ที่อนุญาตให้ใช้อ็อพชันนิพจน์ที่แสดงในไวยากรณ์อาจเป็นค่าคงที่ฟังก์ชัน หรือการรวมกันของชื่อคอลัมน์ค่าคงที่และฟังก์ชันที่เชื่อมต่อโดยตัวดำเนินการทางคณิตศาสตร์อย่างไรก็ตามฟังก์ชันการรวมมักจะใช้กับชื่อคอลัมน์ ยกเว้นฟังก์ชัน COUNT ฟังก์ชันการรวมทั้งหมดจะไม่พิจารณาค่า NULL
มีกฎสองข้อที่คุณต้องเข้าใจและปฏิบัติตามเมื่อใช้มวลรวม:
ฟังก์ชันรวมสามารถใช้ได้ทั้งในส่วนคำสั่ง SELECT และ HAVING (ส่วนคำสั่ง HAVING จะกล่าวถึงในบทนี้ในภายหลัง)
ไม่สามารถใช้ฟังก์ชันการรวมในคำสั่ง WHERE การละเมิดจะทำให้เกิดฟังก์ชันกลุ่ม Oracle ORA-00934 ที่นี่ข้อความแสดงข้อผิดพลาดไม่ได้รับอนุญาต
ภาพประกอบ
คำค้นหา SELECT ด้านล่างจะนับจำนวนพนักงานในองค์กร
SELECT COUNT(*) Count
FROM employees;
COUNT
-----
24
แบบสอบถาม SELECT ด้านล่างจะแสดงค่าเฉลี่ยของเงินเดือนของพนักงานในองค์กร
SELECT AVG(Salary) average_sal
FROM employees;
AVERAGE_SAL
-----------
15694
แบบสอบถาม SELECT ด้านล่างจะแสดงผลรวมของเงินเดือนของพนักงานในองค์กร
SELECT SUM(Salary) total_sal
FROM employees;
TOTAL_SAL
---------
87472
ข้อความค้นหา SELECT ด้านล่างจะแสดงวันที่จ้างที่เก่าแก่ที่สุดและล่าสุดของพนักงานในองค์กร
SELECT MIN (hire_date) oldest, MAX (hire_date) latest
FROM employees;
OLDEST LATEST
--------- -----------
16-JAN-83 01-JUL-2012
GROUP BY
โดยปกติฟังก์ชันการรวมจะใช้ร่วมกับคำสั่ง GROUP BY GROUP BY clause ช่วยให้คุณสามารถใช้ฟังก์ชันรวมเพื่อตอบคำถามด้านการจัดการที่ซับซ้อนยิ่งขึ้นเช่น:
เงินเดือนเฉลี่ยของพนักงานในแต่ละแผนกคืออะไร?
ในแต่ละแผนกมีพนักงานกี่คน?
มีพนักงานกี่คนที่ทำงานในโครงการหนึ่ง ๆ
จัดกลุ่มตามฟังก์ชันสร้างกลุ่มข้อมูลตามคอลัมน์และรวบรวมข้อมูลภายในกลุ่มเท่านั้น เกณฑ์การจัดกลุ่มถูกกำหนดโดยคอลัมน์ที่ระบุใน GROUP BY clause ตามลำดับชั้นนี้ข้อมูลจะถูกจัดระเบียบในกลุ่มก่อนจากนั้นคำสั่ง WHERE จะ จำกัด แถวในแต่ละกลุ่ม
แนวทางการใช้ GROUP BY clause
(1) คอลัมน์ที่ขึ้นต่อกันทั้งหมดที่ใช้ในฟังก์ชัน GROUP BY ต้องเป็นพื้นฐานของการจัดกลุ่มดังนั้นจึงต้องรวมอยู่ใน GROUP BY clause ด้วย
SELECT DEPARTMENT_ID, SUM(SALARY)
FROM employees;
DEPARTMENT_ID,
*
ERROR at line 2:
ORA-00937: not a single-group group function
(2) GROUP BY clause ไม่สนับสนุนการใช้นามแฝงคอลัมน์ แต่เป็นชื่อจริง
(3) GROUP BY clause สามารถใช้ได้กับฟังก์ชันรวมเช่น SUM, AVG, COUNT, MAX และ MIN เท่านั้นหากใช้กับฟังก์ชันแถวเดียว Oracle จะแสดงข้อยกเว้นเป็น "ORA-00979: ไม่ใช่ GROUP BY expression" .
(4) ไม่สามารถใช้ฟังก์ชันการรวมในคำสั่ง GROUP BY Oracle จะส่งคืนข้อความแสดงข้อผิดพลาด "ORA-00934: group function not allowed" ที่นี่
แบบสอบถามด้านล่างแสดงจำนวนพนักงานที่ทำงานในแต่ละแผนก
SELECT DEPARTMENT_ID, COUNT (*)
FROM employees
GROUP BY DEPARTMENT_ID;
ในทำนองเดียวกันแบบสอบถามด้านล่างเพื่อค้นหาผลรวมของเงินเดือนสำหรับรหัสงานที่เกี่ยวข้องในแต่ละแผนก โปรดทราบว่ากลุ่มถูกสร้างขึ้นตามแผนกและรหัสงาน ดังนั้นจึงปรากฏใน GROUP BY clause
SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;
ข้อความค้นหาด้านล่างยังให้ผลลัพธ์เดียวกัน โปรดทราบว่าการจัดกลุ่มจะขึ้นอยู่กับรหัสแผนกและคอลัมน์รหัสงาน แต่ไม่ได้ใช้เพื่อการแสดงผล
SELECT SUM (SALARY)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;
การใช้ DISTINCT คำสำคัญทั้งหมดที่มีฟังก์ชัน Aggregate
โดยการระบุคีย์เวิร์ด DISTINCT พร้อมกับพารามิเตอร์อินพุตการจัดกลุ่มตามฟังก์ชันจะพิจารณาเฉพาะค่าเฉพาะของคอลัมน์สำหรับการรวม โดยการระบุคีย์เวิร์ด ALL ด้วยพารามิเตอร์อินพุตกลุ่มตามฟังก์ชันจะพิจารณาค่าทั้งหมดของคอลัมน์สำหรับการรวมรวมทั้ง nulls และรายการที่ซ้ำกัน ALL เป็นข้อกำหนดเริ่มต้น
HAVING อนุประโยค
ส่วนคำสั่ง HAVING ใช้สำหรับฟังก์ชันการรวมในลักษณะเดียวกับที่ใช้คำสั่ง WHERE สำหรับชื่อคอลัมน์และนิพจน์โดยพื้นฐานแล้วส่วนคำสั่ง HAVING และ WHERE จะทำสิ่งเดียวกันนั่นคือกรองแถวจากการรวมไว้ในตารางผลลัพธ์ตามเงื่อนไข . แม้ว่า HAVING clause จะกรองกลุ่มออก แต่ก็ไม่ได้ แต่ HAVING clause จะกรองแถว
เมื่อแถวทั้งหมดของกลุ่มถูกกำจัดออกไปดังนั้นกลุ่มเพื่อสรุปความแตกต่างที่สำคัญระหว่างคำสั่ง WHERE และ HAVING คือ:
คำสั่ง WHERE ใช้เพื่อกรองแถวก่อนการดำเนินการ GROUPING (กล่าวคือก่อนการคำนวณฟังก์ชันการรวม)
HAVING clause จะกรองแถวหลังจากการดำเนินการ GROUPING (กล่าวคือหลังจากการคำนวณฟังก์ชันการรวม)
SELECT JOB_ID, SUM (SALARY)
FROM employees
GROUP BY JOB_ID
HAVING SUM (SALARY) > 10000;
HAVING clause เป็นอ็อพชันเงื่อนไขที่เกี่ยวข้องโดยตรงกับอ็อพชัน GROUP BY clause เนื่องจาก HAVING clause จะกำจัดแถวออกจากตารางผลลัพธ์โดยยึดตามผลลัพธ์ของ GROUP BY clause
SELECT department_id, AVG(Salary)
FROM employees
HAVING AVG(Salary) > 33000;
ERROR at line 1: ORA-00937: not a single-group group function