ใช้ฟังก์ชันการรวม

เสร็จสมบูรณ์เมื่อ

T-SQL มีฟังก์ชันการรวม เช่น SUM, MAX และ AVG เพื่อดําเนินการคํานวณที่ใช้ค่าหลายค่าและส่งกลับผลลัพธ์เดียว

การทํางานกับฟังก์ชันการรวม

คิวรีส่วนใหญ่ที่เราดูที่ ดําเนินการบนแถวในแต่ละครั้ง โดยใช้ส่วนคําสั่ง WHERE เพื่อกรองแถว แต่ละแถวที่ส่งกลับจะสอดคล้องกับหนึ่งแถวในชุดข้อมูลต้นฉบับ

มีฟังก์ชันการรวมหลายรายการใน SQL Server ในส่วนนี้ เราจะดูที่ฟังก์ชันที่ใช้บ่อยที่สุด เช่น SUM, MIN, MAX, AVG และ COUNT

เมื่อทํางานกับฟังก์ชันการรวม คุณต้องพิจารณาประเด็นต่อไปนี้:

• ฟังก์ชันการรวมส่งกลับค่าเดียว (สเกลา) และสามารถใช้ได้ในคําสั่ง SELECT เกือบทุกที่ที่สามารถใช้ค่าเดียวได้ ตัวอย่างเช่น ฟังก์ชันเหล่านี้สามารถใช้ในส่วนคําสั่ง SELECT, HAVING และ ORDER BY ได้ อย่างไรก็ตาม ไม่สามารถใช้ตัวแปรในคําสั่ง WHERE ได้
• ฟังก์ชันการรวมจะละเว้นค่า NULL ยกเว้นเมื่อใช้ COUNT(*)
• ฟังก์ชันการรวมในรายการ SELECT ไม่มีส่วนหัวของคอลัมน์ เว้นแต่ว่าคุณกําหนดนามแฝงโดยใช้ AS
• ฟังก์ชันการรวมในรายการ SELECT ดําเนินการกับแถวทั้งหมดที่ส่งผ่านไปยังการดําเนินการ SELECT ถ้าไม่มีส่วนคําสั่ง GROUP BY แถวทั้งหมดที่ตรงตามตัวกรองใด ๆ ในส่วนคําสั่ง WHERE จะถูกสรุป คุณจะได้เรียนรู้เพิ่มเติมเกี่ยวกับ GROUP BY ในหัวข้อถัดไป
• เว้นแต่ว่าคุณกําลังใช้ GROUP BY คุณไม่ควรรวมฟังก์ชันการรวมกับคอลัมน์ที่ไม่ได้รวมอยู่ในฟังก์ชันในรายการ SELECT เดียวกัน

เมื่อต้องการขยายนอกเหนือจากฟังก์ชันที่มีอยู่แล้วภายใน SQL Server มีกลไกสําหรับฟังก์ชันการรวมที่ผู้ใช้กําหนดเองผ่านทาง .NET Common Language Runtime (CLR) หัวข้อนั้นอยู่นอกเหนือขอบเขตของโมดูลนี้

ฟังก์ชันการรวมที่มีอยู่ภายใน

ตามที่กล่าวถึง Transact-SQL มีฟังก์ชันการรวมภายในมากมาย ฟังก์ชันที่ใช้กันทั่วไปได้แก่:

ชื่อฟังก์ชัน

ไวยากรณ์

คำอธิบาย

SUM

SUM(expression)

รวมค่าตัวเลขที่ไม่ใช่ NULL ทั้งหมดในคอลัมน์

ค่าเฉลี่ย

AVG(expression)

หาค่าเฉลี่ยค่าตัวเลขที่ไม่ใช่ NULL ทั้งหมดในคอลัมน์ (ผลรวม/จํานวน)

นาที

MIN(expression)

ส่งกลับจํานวนที่น้อยที่สุด วันที่/เวลาแรกสุด หรือสตริงที่เกิดขึ้นครั้งแรก (ตามกฎการเรียงลําดับ)

สูง สุด

MAX(expression)

ส่งกลับจํานวนที่มากที่สุด วันที่/เวลาล่าสุด หรือสตริงที่เกิดขึ้นล่าสุด (ตามกฎการเรียงลําดับ)

COUNT หรือ COUNT_BIG

COUNT(*) หรือ COUNT(นิพจน์)

ด้วย (*) นับจํานวนแถวทั้งหมด รวมถึงแถวที่มีค่า NULL เมื่อมีการระบุคอลัมน์เป็น นิพจน์ ระบบจะแสดงจํานวนแถวที่ไม่ใช่ NULL สําหรับคอลัมน์นั้น COUNT จะส่งกลับ int แต่จะส่งกลับค่า int COUNT_BIG ส่งกลับ big_int

เพื่อใช้การรวมที่มีอยู่แล้วภายในในส่วนคําสั่ง SELECT ให้พิจารณาตัวอย่างต่อไปนี้ในฐานข้อมูลตัวอย่าง MyStore :

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

ผลลัพธ์ของคิวรีนี้จะมีลักษณะดังนี้:

AveragePrice

MinimumPrice

MaximumPrice

744.5952

2.2900

3578.2700

โปรดทราบว่าตัวอย่างข้างต้นจะสรุปแถวทั้งหมดจากตาราง Production.Product เราสามารถปรับเปลี่ยนคิวรีได้อย่างง่ายดายเพื่อส่งกลับราคาเฉลี่ย ต่ําสุด และสูงสุดสําหรับผลิตภัณฑ์ในประเภทเฉพาะโดยการเพิ่มส่วนคําสั่ง WHERE ดังนี้:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;

เมื่อใช้การรวมในส่วนคําสั่ง SELECT คอลัมน์ทั้งหมดที่อ้างอิงในรายการ SELECT จะต้องใช้เป็นข้อมูลป้อนเข้าสําหรับฟังก์ชันการรวม หรืออ้างอิงในส่วนคําสั่ง GROUP BY

พิจารณาคิวรีต่อไปนี้ ซึ่งจะพยายามรวมเขตข้อมูล ProductCategoryID ในผลลัพธ์รวม:

SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

การเรียกใช้คิวรีนี้ส่งผลให้เกิดข้อผิดพลาดต่อไปนี้

ข่าวสาร 8120, ระดับ 16, รัฐ 1, บรรทัดที่ 1

คอลัมน์ 'Production.ProductCategoryID' ไม่ถูกต้องในรายการที่เลือก เนื่องจากไม่มีอยู่ในฟังก์ชันการรวมหรือส่วนคําสั่ง GROUP BY

คิวรีจะถือว่าแถวทั้งหมดเป็นกลุ่มรวมเดียว ดังนั้น ต้องใช้คอลัมน์ทั้งหมดเป็นข้อมูลป้อนเข้าเพื่อรวมฟังก์ชัน

ในตัวอย่างก่อนหน้านี้ เราได้รวบรวมข้อมูลตัวเลข เช่น ราคาและปริมาณในตัวอย่างก่อนหน้านี้ ฟังก์ชันการรวมบางอย่างยังสามารถใช้เพื่อสรุปข้อมูลวันที่ เวลา และอักขระได้ ตัวอย่างต่อไปนี้แสดงการใช้การรวมที่มีวันที่และอักขระ:

คิวรีนี้ส่งกลับบริษัทแรกและบริษัทสุดท้ายตามชื่อ โดยใช้ MIN และ MAX:

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

คิวรีนี้จะส่งกลับค่าแรกและค่าสุดท้ายสําหรับ CompanyName ในลําดับการจัดเรียงของฐานข้อมูล ซึ่งในกรณีนี้เป็นการเรียงลําดับตามตัวอักษร:

MinCustomer

MaxCustomer

ร้านจักรยาน

บริษัทจักรยานสีเหลือง

ฟังก์ชันอื่น ๆ อาจซ้อนกันกับฟังก์ชันการรวม

ตัวอย่างเช่น ฟังก์ชัน YEAR scalar ถูกใช้ในตัวอย่างต่อไปนี้เพื่อแสดงเฉพาะส่วนปีของวันที่สั่งซื้อ ก่อนการประเมิน MIN และ MAX:

SELECT MIN(YEAR(OrderDate)) AS Earliest,
       MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;

แรก

ล่าสุด

2008

2021

นอกจากนี้ คุณยังสามารถใช้ฟังก์ชัน MIN และ MAX กับข้อมูลวันที่เพื่อแสดงค่าแรกเริ่มและล่าสุด อย่างไรก็ตาม AVG และ SUM สามารถใช้สําหรับข้อมูลตัวเลขเท่านั้น ซึ่งรวมถึงจํานวนเต็ม เงิน ประเภทข้อมูลเลขทศนิยมและเลขทศนิยม

การใช้ DISTINCT กับฟังก์ชันการรวม

คุณควรระวังการใช้ DISTINCT ในคําสั่ง SELECT เพื่อลบแถวที่ซ้ํากันออก เมื่อใช้กับฟังก์ชันการรวม DISTINCT จะลบค่าที่ซ้ํากันออกจากคอลัมน์อินพุตก่อนคํานวณค่าสรุป DISTINCT มีประโยชน์เมื่อทําการสรุปการปรากฏของค่าที่ไม่ซ้ํากัน เช่น ลูกค้าในตารางคําสั่งซื้อ

ตัวอย่างต่อไปนี้แสดงจํานวนลูกค้าที่มีการสั่งซื้อสินค้า ไม่ว่าจะสั่งซื้อกี่รายการก็ตาม:

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;

COUNT(<some_column>) นับจํานวนแถวที่มีค่าบางอย่างในคอลัมน์เท่านั้น ถ้าไม่มีค่า NULL COUNT(<some_column>) จะเหมือนกับ COUNT(*) COUNT (DISTINCT <some_column>) นับจํานวนค่าที่แตกต่างกันในคอลัมน์

การใช้ฟังก์ชันการรวมที่มี NULL

สิ่งสําคัญคือต้องระวังการมีอยู่ของค่า NULL ที่เป็นไปได้ในข้อมูลของคุณ และวิธีการที่ NULL โต้ตอบกับคอมโพเนนต์คิวรี T-SQL รวมถึงฟังก์ชันการรวม มีข้อควรพิจารณาสองสามข้อที่ควรระวัง:

• ยกเว้น COUNT ที่ใช้กับตัวเลือก (*) ฟังก์ชันการรวม T-SQL จะละเว้นค่า NULL ตัวอย่างเช่น ฟังก์ชัน SUM จะเพิ่มเฉพาะค่าที่ไม่ใช่ NULL เท่านั้น ค่า NULL ไม่ประเมินเป็นศูนย์ COUNT(*) นับจํานวนแถวทั้งหมด โดยไม่คํานึงถึงค่าหรือไม่เป็นค่าในคอลัมน์ใดก็ตาม
• การปรากฏของค่า NULL ในคอลัมน์อาจนําไปสู่การคํานวณที่ไม่ถูกต้องสําหรับ AVG ซึ่งจะรวมเฉพาะแถวที่มีการเติมและหารผลรวมด้วยจํานวนแถวที่ไม่ใช่ NULL อาจมีความแตกต่างในผลลัพธ์ระหว่าง AVG(<คอลัมน์>) และ (SUM(<คอลัมน์>)/COUNT(*))

ตัวอย่างเช่น พิจารณาตารางต่อไปนี้ที่มีชื่อ t1:

C1

C2

1

โมฆะ

2

10

3

20

4

30

5

40

6

50

คิวรีนี้แสดงความแตกต่างระหว่างวิธีที่ AVG จัดการกับ NULL และวิธีที่คุณอาจคํานวณค่าเฉลี่ยด้วยคอลัมน์จากการคํานวณ SUM/COUNT(*):

SELECT SUM(c2) AS sum_nonnulls, 
    COUNT(*) AS count_all_rows, 
    COUNT(c2) AS count_nonnulls, 
    AVG(c2) AS average, 
    (SUM(c2)/COUNT(*)) AS arith_average
FROM t1;

ผลลัพธ์จะเป็น:

sum_nonnulls

count_all_rows

count_nonnulls

เฉลี่ย

arith_average

150

6

5

30

25

ในชุดผลลัพธ์นี้ คอลัมน์ที่ชื่อว่า average คือการรวมที่รับผลรวมของ 150 ภายในและหารด้วยจํานวนของค่าที่ไม่ใช่ null ในคอลัมน์ c2 การคํานวณจะเป็น 150/5 หรือ 30 คอลัมน์ที่เรียกว่า arith_average หารผลรวมอย่างชัดเจนด้วยจํานวนแถวทั้งหมด ดังนั้นการคํานวณคือ 150/6 หรือ 25

หากคุณต้องการสรุปแถวทั้งหมดไม่ว่าจะเป็น NULL หรือไม่พิจารณาเปลี่ยนค่า NULL ด้วยค่าอื่นที่จะไม่ละเว้นโดยฟังก์ชันการรวมของคุณ คุณสามารถใช้ฟังก์ชัน COALESCE สําหรับวัตถุประสงค์นี้ได้