เรื่องเก่า เล่าอีกครั้งกับ Grouping Set

ผู้เขียนได้รับคำถาม ว่า เราสามารถแสดงหลายกลุ่มผลรวมพร้อมกันในคิวรี่เดียวได้หรือไม่
อาทิ ต้องการให้แสดงยอดขายในหลายมุมมองดังนี้

1. ยอดขายทั้งหมด โดยเป็นผลรวมยอดขายทุกรายการ
2. ยอดขายในแต่ละปี โดยเป็นผลรวมที่จัดกลุ่มยอดขายในแต่ละปีเข้าด้วยกัน
3. ยอดขายของแต่ละเดือน ในแต่ละปี โดยเป็นการหาผลรวมที่จัดกลุ่มยอดขายแต่ละเดือนในแต่ละปีเข้าด้วยกัน

ผู้เขียนตอบทันทีว่าทำได้ ก็ใช้ประโยคย่อยของ GROUP BY ไง ก็บรรดา GROUPING SETS, CUBE หรือ ROLLUP ไงล่ะครับ ก็จะได้รับคำถามกลับมาอีกว่า แล้วทำอย่างไร

อันที่จริงแล้ว GROUP BY….WITH CUBE หรือ GROUP BY ….WITH ROLLUP มีมานานแสนนาน ตั้งแต่สมัย SQL Server 2000  จนมาเปลี่ยน เป็น GROUP BY CUBE () หรือ GROUP BY ROLLUP () ในปัจจุบัน อีกทั้งเพิ่ม GROUP BY GROUPING SETS ในสมัย SQL Server 2008

(นอกจากนั้น RDBMS ค่ายอื่น ๆ อาทิ ORACLE, DB2, MySQL, PostgreSQL และอีกมากมายหลายค่ายก็มีใช้งานเหมือน ๆ กัน)

แต่ก็ยังมีคนที่หาคำตอบเรื่องนี้อยู่เสมอ ผู้เขียนจึงอยาากจะเล่าเรื่องนี้อีกสักรอบ

ก่อนอื่นเพราะความสะดวกในการหาฐานข้อมูลตัวอย่างมาทดลองเล่นกัน ผู้เขียนแนะนำฐานข้อมูล AdventureWorks ซึ่งเป็นฐานข้อมูลตัวอย่างยอดนิยมบน Microsoft SQL Server
โดยสามารถดาวน์โหลดได้จากลิงก์นี้ https://msftdbprodsamples.codeplex.com/releases/view/125550  

เมื่อทำการ Restore เสร็จแล้วให้ทำการสร้างวิว Sales.OrderValues สำหรับใช้ทดสอบดังนี้

USE AdventureWorks;
 
CREATE VIEW Sales.OrderValues as
 
SELECT
       O.SalesOrderID
,      O.OrderDate
,      P1.FirstName+' '+P1.LastName as CustomerName
,      P2.FirstName+' '+P2.LastName as SaleName
,      SUM(D.OrderQty) as OrderQty
,      SUM(D.UnitPrice*D.OrderQty*(1-D.UnitPriceDiscount)) as SaleAmount
FROM [Sales].[SalesOrderHeader] as O
INNER JOIN [Sales].[SalesOrderDetail] as D
ON O.SalesOrderID=D.SalesOrderID
INNER JOIN [Sales].[Customer] as C
ON O.CustomerID=C.CustomerID
INNER JOIN [Person].[Person] as P1
ON C.PersonID=P1.BusinessEntityID
INNER JOIN Person.Person as P2
ON O.SalesPersonID=P2.BusinessEntityID
WHERE O.OrderDate>='20060101' AND O.OrderDate<'20080101'
GROUP BY      O.SalesOrderID,O.OrderDate
              ,      P1.FirstName+' '+P1.LastName
              ,      P2.FirstName+' '+P2.LastName
;

ซึ่งวิว Sales.OrderValues นี้สร้างขึ้น
เพื่อให้แสดงคอลัมน์ SalesOrderID, OrderDate, CustomerName, SaleName, OrderQty และ SaleAmount

ตัวอย่างข้อมูลภายใน View เป็นดังนี้

จะเห็นว่า ข้อมูลที่ได้นั้น แสดงยอดขาย (Sale Amount) แต่ละเลขที่ใบสั่งซื้อ (SalesOrderID)
ซึ่งยังไม่มีการสรุปยอดขายประจำเดือน ,ประจำปี และยอดทั้งหมด ครับ

ย้อนอดีตก่อนมี GROUP BY GROUPING SETS

ก่อนหน้าที่จะมี GROUP BY GROUPING SETS เกิดขึ้นใน Microsoft SQL Server 2008 นั้น
หากต้องการรวมกลุ่มของ Result Set ที่เกิดจากการหาผลรวมเข้าด้วยกัน และปรับแต่งได้ตามต้องการนั้น
คงต้องนำ Result Set ต่าง ๆ มา UNION ALL กัน ดังแสดง  

โดย Query 1 , Query 2 และ Query 3  นั้น ดึงข้อมูลมาจาก View Sales.OrderValues ครับ

Query1 (ยอดรวมทั้งหมด)

SELECT
       NULL as OrderYear
,      NULL as OrderMonth
,      REPLICATE(N' ', 15-LEN(FORMAT(SUM(O.SaleAmount),'N','en-US')))+
       FORMAT(SUM(O.SaleAmount), 'N', 'en-us') as SaleAmount
FROM Sales.OrderValues as O;

Query2 (ยอดรวมในแต่ละปี)

SELECT
       YEAR(O.OrderDate) as OrderYear
,      NULL as OrderMonth
,      REPLICATE(N' ', 15-LEN(FORMAT(SUM(O.SaleAmount),'N','en-US')))+
       FORMAT(SUM(O.SaleAmount), 'N', 'en-us') as SaleAmount
FROM Sales.OrderValues as O
GROUP BY YEAR(O.OrderDate)
ORDER BY OrderYear;

ผลลัพธ์ Result Set 2

Query 3 (ยอดรวมแต่ละเดือนของปี) 

SELECT
       YEAR(O.OrderDate) as OrderYear
,      MONTH(O.OrderDate) as OrderMonth
,      REPLICATE(N' ', 15-LEN(FORMAT(SUM(O.SaleAmount),'N','en-US')))+
       FORMAT(SUM(O.SaleAmount), 'N', 'en-us') as SaleAmount
FROM Sales.OrderValues as O
GROUP BY YEAR(O.OrderDate),MONTH(O.OrderDate)
ORDER BY OrderYear,OrderMonth;

ผลลัพธ์ Result Set 3

เมื่อมี GROUP BY GROUPING SETS

เพียงแค่นำเอาคอลัมน์ที่ประกาศใน SELECT List แต่ไม่ได้อยู่ใน Aggregate Function มาจัด SET ตามต้องการ
เช่น

SELECT
       YEAR(O.OrderDate) as OrderYear
,      MONTH(O.OrderDate) as OrderMonth
,      REPLICATE(N' ', 15-LEN(FORMAT(SUM(O.SaleAmount),'N','en-US')))+
       FORMAT(SUM(O.SaleAmount), 'N', 'en-us') as SaleAmount
FROM Sales.OrderValues as O
GROUP BY GROUPING SETS
                           (
                                  (YEAR(O.OrderDate),MONTH(O.OrderDate))
                           ,      (YEAR(O.OrderDate))
                           ,      ()
                           )
ORDER BY OrderYear,OrderMonth;

ผลลัพธ์ที่ได้จะเหมือนกันกับที่เอาสาม Result Sets มา UNION ALL เลย

เพราะผู้เขียนเลือกให้คำนวณยอดรวมทั้งหมด ก็คือ

• เซต ( ) , คำนวณยอดรวมในแต่ละปี ก็คือเซต (YEAR(O.OrderDate)) และ
• คำนวณยอดรวมแต่ละเดือนของปี ซึ่งก็คือเซต (YEAR(O.OrderDate),MONTH(O.OrderDate))

ที่ประกาศในประโยคย่อย GROUPING SETS นั่นเอง

ผลลัพธ์ของ Grouping Set 1

แต่อย่างที่เกรินเอาไว้ว่า ประโยคย่อย GROUPING SETS นั้นเปิดโอกาสให้เราจัดกลุ่มของ SET ได้เอง

จากตัวอย่างคอลัมน์ที่ไม่ได้อยู่ใน Aggregate Function มีด้วยกันสองคอลัมน์ นั่นก็คือ OrderYear และ OrderMonth ซึ่งหากนำมาเรียงสับเปลี่ยนก็ควรที่จะได้ 4 วิธี

แต่ผู้เขียนเลือกที่จะไม่คำนวณ เซต ของ OrderMonth เพียงอย่างเดียว เพราะไม่น่าจะมีความหมายทางธุรกิจ แต่ผู้เขียนจะลองทำให้ดู ดังนี้

SELECT
       YEAR(O.OrderDate) as OrderYear
,      MONTH(O.OrderDate) as OrderMonth
,      REPLICATE(N' ', 15-LEN(FORMAT(SUM(O.SaleAmount),'N','en-US')))+
       FORMAT(SUM(O.SaleAmount), 'N', 'en-us') as SaleAmount
FROM Sales.OrderValues as O
GROUP BY GROUPING SETS
                           (
                                  (YEAR(O.OrderDate),MONTH(O.OrderDate))
                           ,      (YEAR(O.OrderDate))
                           ,      (MONTH(O.OrderDate))
                           ,      ()
                           )
ORDER BY OrderYear,OrderMonth;

ผลลัพธ์ของ Grouping Set 2

จะเห็นว่ามีการยอดรวมของแต่ละเดือนออกมา แต่เราไม่สามารถรู้ได้ว่าเป็นของปีอะไร
ซึ่งข้อมูลลักษณะนี้ควรเป็น Hierarchy หรือลำดับชั้นใต้ ปี มากกว่า ไม่ควรนำมาคำนวณแยกต่างหาก

GROUP BY CUBE ( ) และ GROUP BY ROLLUP ( )

ประโยคย่อยนี้มีมานานกว่า GROUP BY GROUPING SETS จะทำงานตายตัว
ไม่ยืดหยุ่นเหมือนกับ GROUP BY GROUPING SETS กล่าวคือ
 

• GROUP BY CUBE ( ) จะเรียงสับเปลี่ยนทุกวิธีกับคอลัมน์ที่ประกาศ ตัวอย่างเช่น
  • GROUP BY CUBE ( OrderYear, EmpID, CustID) จะมีทั้งหมด 8 Sets ดังนี้
    • ( )
    • ( OrderYear )
    • ( OrderYear, EmpID )
    • ( OrderYear, CustID)
    • ( OrderYear, EmpID, CustID )
    • ( EmpID )
    • ( EmpID, CustID )
    • ( CustID )
• GROUP BY ROLLUP ( ) จะสร้างลำดับชั้น ตามลำดับคอลัมน์ที่ประกาศ ตัวอย่างเช่น
  • GROUP BY ROLLUP ( OrderYear, OrderMonth, OrderDay ) จะมีทั้งหมด 4 Sets ดังนี้
    • ( )
    • ( OrderYear )
    • ( OrderYear, OrderMonth )
    • ( OrderYear, OrderMonth, OrderDay )

ตัวอย่าง คิวรี่ของ GROUP BY CUBE ( )

SELECT
       YEAR(O.OrderDate) as OrderYear
,      O.SaleName
,      O.CustomerName
,      REPLICATE(N' ', 15-LEN(FORMAT(SUM(O.SaleAmount),'N','en-US')))+
       FORMAT(SUM(O.SaleAmount), 'N', 'en-us') as SaleAmount
FROM Sales.OrderValues as O
GROUP BY
       CUBE (YEAR(O.OrderDate),SaleName,O.CustomerName)
ORDER BY OrderYear,SaleName,CustomerName;

ผู้เขียนขอไม่แสดงผลลัพธ์ของคิวรี่ข้างบนนี้ เพราะ Result Set มีขนาดใหญ่และหากตัดต่อผลลัพธ์บางส่วน ก็จะยิ่งทำให้สับสน
ผู้เขียนจึงอยากชวนให้ คุณผู้อ่านลองนำไปทดลองดูว่าผลลัพธ์จะออกมาแบบใด

ตัวอย่าง คิวรี่ของ GROUP BY ROLLUP ( )

ผลลัพธ์ที่ได้ออกมาเหมือนกันกับ ของ Grouping Set 1 เลย

SELECT
       YEAR(O.OrderDate) as OrderYear
,      MONTH(O.OrderDate) as OrderMonth
,      REPLICATE(N' ', 15-LEN(FORMAT(SUM(O.SaleAmount),'N','en-US')))+
       FORMAT(SUM(O.SaleAmount), 'N', 'en-us') as SaleAmount
FROM Sales.OrderValues as O
GROUP BY
       ROLLUP (YEAR(O.OrderDate),MONTH(O.OrderDate))
ORDER BY OrderYear,OrderMonth;

สรุป

หากต้องการกำหนด Result Sets ได้เองตามต้องการ ผู้เขียนขอแนะนำให้ใช้ประโยคย่อย GROUPING SETS

แต่หากต้องการ Result Sets ที่เกิดจากการเรียงสับเปลี่ยนคอลัมน์ครบทุกวิธี ก็ให้เลือกใช้ประโยคย่อย CUBE ทั้งนี้ควรระมัดระวังเรื่องความหมายทางธุรกิจเป็นสำคัญ

แต่หากคอลัมน์ที่จะนำมาสร้าง Set นั้นมีความเป็นลำดับชั้น เช่น Year, Month , Day ก็ควรเลือกใช้ ประโยคย่อย ROLLUP นะครับ :)