การใช้ฟังก์ชันกลุ่ม

การรายงานข้อมูลรวมโดยใช้ฟังก์ชันกลุ่ม

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