GROUP BY + Aggregations

สรุปข้อมูลด้วย COUNT/SUM/AVG/MIN/MAX แบ่งกลุ่มด้วย GROUP BY และความต่างของ WHERE (ก่อน group) กับ HAVING (หลัง group)

~8 นาที

ผู้บริหาร Vibe Mart ส่งคำถามมา 3 ข้อ ได้แก่ "เดือนนี้ขายได้กี่ออเดอร์", "หมวดสินค้าไหนทำเงินสูงสุด", และ "ลูกค้าคนไหนซื้อบ่อยสุด" 3 คำถามนี้คำตอบไม่ใช่ row เดียว แต่ต้องเอา row หลายๆ อันมารวมกันแล้วสรุปยอด คำสั่งที่ทำหน้าที่นี้คือ GROUP BY ใช้คู่กับฟังก์ชันสรุป (aggregate function)

โครงสร้างพื้นฐาน

SELECT category_id, COUNT(*) AS total
FROM products
GROUP BY category_id;

สาระสำคัญมีอยู่ 2 อย่าง

GROUP BY หมายถึง "แบ่ง row ลง bucket ตามค่า column นี้" สินค้าที่อยู่ใน category 1 รวมกองหนึ่ง category 2 รวมอีกกอง ไล่ไปเรื่อยๆ
Aggregate function อย่าง COUNT(*) ใช้นับจำนวน row ในแต่ละ bucket หนึ่ง bucket จะออกมาเป็นหนึ่งบรรทัดของผลลัพธ์

5 ฟังก์ชันสรุปที่ใช้บ่อย

COUNT(*) นับ row ทั้งหมด ส่วน COUNT(col) นับเฉพาะที่ไม่ใช่ NULL
SUM(col) บวกรวม ใช้กับเลขเท่านั้น
AVG(col) หาค่าเฉลี่ย
MIN(col) และ MAX(col) หาค่าน้อยสุดและมากสุดตามลำดับ

WHERE กับ HAVING ต่างกันยังไง

คำถามที่ junior dev ถามบ่อยที่สุดคือ "ทำไมตอนนี้ใช้ HAVING ไม่ใช้ WHERE" คำตอบอยู่ที่ เวลาที่ filter ทำงาน

WHERE ตัด row ก่อน ที่จะตกลง bucket ใช้กับ column ปกติ
HAVING ตัด bucket หลังจาก aggregate เสร็จแล้ว ใช้กับผลลัพธ์ที่ออกมาจาก aggregate

ลองเล่น widget ด้านล่างได้ เปลี่ยน WHERE ดูว่า row จะหายก่อน ตกลง bucket ส่วนเปลี่ยน HAVING ก็จะเห็น bucket ทั้งก้อนหายไปจากผลลัพธ์

Aggregate

WHERE (ก่อน group)

HAVING (หลัง group)

SELECT c.name, SUM(oi.qty * oi.unit_price) AS value
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
GROUP BY c.id, c.name
ORDER BY value DESC;

1. Source: order_items + product + category41/41

#1ปากกาเจล2×35฿70

#2น้ำผลไม้1×35฿35

#3มาม่ารสต้มยำ2×8฿16

#4หูฟัง bluetooth1×890฿890

#5น้ำหอม1×1290฿1,290

#6มาส์กหน้า1×149฿149

#7ขนมปังกรอบ3×45฿135

#8สมุดโน้ต A51×89฿89

#9สายชาร์จ USB-C1×159฿159

#10ช็อคโกแลต1×89฿89

#11เครื่องคิดเลข1×450฿450

#12น้ำหอม1×1290฿1,290

#13มาม่ารสต้มยำ6×8฿48

#14ปากกาเจล1×35฿35

#15น้ำหอม2×1290฿2,580

#16มาส์กหน้า1×149฿149

#17หูฟัง bluetooth1×890฿890

#18ลิปสติก6×290฿1,740

#19การ์ดเกม Magic1×450฿450

#20ช็อคโกแลต1×89฿89

#21ครีมกันแดด3×390฿1,170

#22ปากกาเจล7×35฿245

#23หูฟัง bluetooth1×890฿890

#24เกมมือถือ premium1×199฿199

#25น้ำหอม1×1290฿1,290

#26ปากกาเจล1×35฿35

#27มาม่ารสต้มยำ3×8฿24

#28ลิปสติก2×290฿580

#29สมุดโน้ต A51×89฿89

#30ช็อคโกแลต1×89฿89

#31ครีมกันแดด1×390฿390

#32ลิปสติก1×290฿290

#33ขนมปังกรอบ1×45฿45

#34การ์ดเกม Magic1×450฿450

#35ปากกาเจล4×35฿140

#36น้ำผลไม้1×35฿35

#37มาม่ารสต้มยำ7×8฿56

#38หูฟัง bluetooth1×890฿890

#39ช็อคโกแลต1×89฿89

#40สายชาร์จ USB-C2×159฿318

#41น้ำผลไม้6×35฿210

2. Buckets ตาม category_id (SUM)5/5

เครื่องเขียน8 items

฿1,153

SUM(qty * unit_price)

อาหารแห้ง13 items

฿960

SUM(qty * unit_price)

เครื่องสำอาง11 items

฿10,918

SUM(qty * unit_price)

เกม3 items

฿1,099

SUM(qty * unit_price)

อุปกรณ์มือถือ6 items

฿4,037

SUM(qty * unit_price)

ผลลัพธ์สุดท้าย 5 rows

category_name	SUM(amount)
เครื่องสำอาง	฿10,918
อุปกรณ์มือถือ	฿4,037
เครื่องเขียน	฿1,153
เกม	฿1,099
อาหารแห้ง	฿960

ตัวอย่างจริงของ Vibe Mart

รายได้ของแต่ละหมวด ใช้ GROUP BY category_id คู่กับ SUM(qty * unit_price)
Top buyer ใช้ GROUP BY user_id คู่กับ SUM(total) และเรียงด้วย ORDER BY SUM(total) DESC LIMIT 5
จำนวนออเดอร์ต่อสถานะ ใช้ GROUP BY status คู่กับ COUNT(*)
ลูกค้าที่ซื้อมากกว่า 3 ครั้ง ใช้ GROUP BY user_id คู่กับ HAVING COUNT(*) > 3

Playground ลอง GROUP BY จริง

ตัวอย่าง:

SELECT status, COUNT(*) AS n
FROM orders
GROUP BY status
ORDER BY n DESC;

กด Cmd / Ctrl + Enter เพื่อรัน

สรุปบทนี้

GROUP BY col ใช้แบ่ง row ลง bucket ตาม col หนึ่ง bucket จะออกมาเป็นหนึ่งบรรทัดผลลัพธ์
Aggregate function ที่ใช้บ่อยคือ COUNT, SUM, AVG, MIN, MAX
WHERE กรอง row ก่อน group ส่วน HAVING กรอง bucket หลัง group
Column ที่ไม่ใช่ aggregate ใน SELECT ต้องไปอยู่ใน GROUP BY ด้วยเสมอ
บทถัดไปจะเปลี่ยนข้อมูลกัน ทั้ง INSERT, UPDATE, DELETE และเรื่อง transaction

ก่อนหน้าJOIN ต่อตาราง ถัดไปINSERT / UPDATE / DELETE + Transaction

โครงสร้างพื้นฐาน

SELECT category_id, COUNT(*) AS total
FROM products
GROUP BY category_id;

สาระสำคัญมีอยู่ 2 อย่าง

GROUP BY หมายถึง "แบ่ง row ลง bucket ตามค่า column นี้" สินค้าที่อยู่ใน category 1 รวมกองหนึ่ง category 2 รวมอีกกอง ไล่ไปเรื่อยๆ

Aggregate function อย่าง COUNT(*) ใช้นับจำนวน row ในแต่ละ bucket หนึ่ง bucket จะออกมาเป็นหนึ่งบรรทัดของผลลัพธ์

5 ฟังก์ชันสรุปที่ใช้บ่อย

COUNT(*) นับ row ทั้งหมด ส่วน COUNT(col) นับเฉพาะที่ไม่ใช่ NULL

SUM(col) บวกรวม ใช้กับเลขเท่านั้น

AVG(col) หาค่าเฉลี่ย

MIN(col) และ MAX(col) หาค่าน้อยสุดและมากสุดตามลำดับ

WHERE กับ HAVING ต่างกันยังไง

WHERE ตัด row ก่อน ที่จะตกลง bucket ใช้กับ column ปกติ

HAVING ตัด bucket หลังจาก aggregate เสร็จแล้ว ใช้กับผลลัพธ์ที่ออกมาจาก aggregate

Aggregate

WHERE (ก่อน group)

HAVING (หลัง group)

SELECT c.name, SUM(oi.qty * oi.unit_price) AS value
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
GROUP BY c.id, c.name
ORDER BY value DESC;

1. Source: order_items + product + category41/41

#1ปากกาเจล2×35฿70

#2น้ำผลไม้1×35฿35

#3มาม่ารสต้มยำ2×8฿16

#4หูฟัง bluetooth1×890฿890

#5น้ำหอม1×1290฿1,290

#6มาส์กหน้า1×149฿149

#7ขนมปังกรอบ3×45฿135

#8สมุดโน้ต A51×89฿89

#9สายชาร์จ USB-C1×159฿159

#10ช็อคโกแลต1×89฿89

#11เครื่องคิดเลข1×450฿450

#12น้ำหอม1×1290฿1,290

#13มาม่ารสต้มยำ6×8฿48

#14ปากกาเจล1×35฿35

#15น้ำหอม2×1290฿2,580

#16มาส์กหน้า1×149฿149

#17หูฟัง bluetooth1×890฿890

#18ลิปสติก6×290฿1,740

#19การ์ดเกม Magic1×450฿450

#20ช็อคโกแลต1×89฿89

#21ครีมกันแดด3×390฿1,170

#22ปากกาเจล7×35฿245

#23หูฟัง bluetooth1×890฿890

#24เกมมือถือ premium1×199฿199

#25น้ำหอม1×1290฿1,290

#26ปากกาเจล1×35฿35

#27มาม่ารสต้มยำ3×8฿24

#28ลิปสติก2×290฿580

#29สมุดโน้ต A51×89฿89

#30ช็อคโกแลต1×89฿89

#31ครีมกันแดด1×390฿390

#32ลิปสติก1×290฿290

#33ขนมปังกรอบ1×45฿45

#34การ์ดเกม Magic1×450฿450

#35ปากกาเจล4×35฿140

#36น้ำผลไม้1×35฿35

#37มาม่ารสต้มยำ7×8฿56

#38หูฟัง bluetooth1×890฿890

#39ช็อคโกแลต1×89฿89

#40สายชาร์จ USB-C2×159฿318

#41น้ำผลไม้6×35฿210

2. Buckets ตาม category_id (SUM)5/5

เครื่องเขียน8 items

฿1,153

SUM(qty * unit_price)

อาหารแห้ง13 items

฿960

SUM(qty * unit_price)

เครื่องสำอาง11 items

฿10,918

SUM(qty * unit_price)

เกม3 items

฿1,099

SUM(qty * unit_price)

อุปกรณ์มือถือ6 items

฿4,037

SUM(qty * unit_price)

ผลลัพธ์สุดท้าย 5 rows

category_name	SUM(amount)
เครื่องสำอาง	฿10,918
อุปกรณ์มือถือ	฿4,037
เครื่องเขียน	฿1,153
เกม	฿1,099
อาหารแห้ง	฿960

ตัวอย่างจริงของ Vibe Mart

รายได้ของแต่ละหมวด ใช้ GROUP BY category_id คู่กับ SUM(qty * unit_price)

Top buyer ใช้ GROUP BY user_id คู่กับ SUM(total) และเรียงด้วย ORDER BY SUM(total) DESC LIMIT 5

จำนวนออเดอร์ต่อสถานะ ใช้ GROUP BY status คู่กับ COUNT(*)

ลูกค้าที่ซื้อมากกว่า 3 ครั้ง ใช้ GROUP BY user_id คู่กับ HAVING COUNT(*) > 3

Playground ลอง GROUP BY จริง

ตัวอย่าง:

SELECT status, COUNT(*) AS n
FROM orders
GROUP BY status
ORDER BY n DESC;

กด Cmd / Ctrl + Enter เพื่อรัน

สรุปบทนี้

GROUP BY col ใช้แบ่ง row ลง bucket ตาม col หนึ่ง bucket จะออกมาเป็นหนึ่งบรรทัดผลลัพธ์

Aggregate function ที่ใช้บ่อยคือ COUNT, SUM, AVG, MIN, MAX

WHERE กรอง row ก่อน group ส่วน HAVING กรอง bucket หลัง group

Column ที่ไม่ใช่ aggregate ใน SELECT ต้องไปอยู่ใน GROUP BY ด้วยเสมอ

บทถัดไปจะเปลี่ยนข้อมูลกัน ทั้ง INSERT, UPDATE, DELETE และเรื่อง transaction