EP08: การสร้างรายงานด้วย JOIN และ Aggregate Functions

ส่วนที่ 1:

หัวข้อ: “จากข้อมูลดิบสู่ข้อมูลเชิงลึก”: การสร้างรายงานด้วย JOIN และ Aggregate Functions

คำถามที่ตารางเดียวตอบไม่ได้

• เรารู้ว่าใบแจ้งซ่อมรหัส 202401 เป็นของเครื่องจักรรหัส 1 และพนักงานรหัส 102… แต่ถ้าผู้จัดการโรงงานเดินมาถามว่า ‘แล้วเครื่องจักรเบอร์ 1 คือเครื่องอะไร? พนักงานเบอร์ 102 ชื่ออะไร?’ เราจะตอบได้อย่างไร?”
• ชี้ให้เห็นปัญหา: ข้อมูลที่ต้องการอยู่กระจัดกระจายในหลายตาราง (machines, employees)
• แนะนำทางออก: เราต้องใช้คำสั่ง JOIN เพื่อ “สร้างสะพาน” เชื่อมตารางเหล่านี้เข้าด้วยกัน โดยใช้ Foreign Key ที่เราออกแบบไว้

การเชื่อมตาราง (JOINing Tables)

• 1. INNER JOIN (การเชื่อมแบบภายใน):
  • แนวคิด: เหมือนส่วนที่ซ้อนกันของแผนภาพเวนน์ (Venn Diagram) จะดึงมาเฉพาะแถวข้อมูลที่มี “คู่” อยู่ในทั้งสองตารางเท่านั้น
  • รูปแบบคำสั่ง:SQL

SELECT ... FROM ตาราง1 INNER JOIN ตาราง2 ON ตาราง1.คอลัมน์ที่เชื่อม = ตาราง2.คอลัมน์ที่เชื่อม;

• เน้นย้ำ: ON คือ “กฎ” ที่บอกว่าจะใช้คอลัมน์ไหนเป็นสะพานเชื่อม (โดยมากคือ PK เชื่อมกับ FK)
• 2. LEFT JOIN (การเชื่อมแบบซ้าย):
  • แนวคิด: เอาข้อมูล “ทุกแถว” จากตารางด้านซ้าย (ตารางแรก) มาก่อน แล้วค่อยพยายามหาคู่จากตารางด้านขวา ถ้าหาไม่เจอก็จะแสดงค่าเป็น NULL
  • ใช้เมื่อไหร่?: “ถ้าผู้จัดการอยากเห็นรายชื่อเครื่องจักร ทั้งหมด ในโรงงาน พร้อมทั้งข้อมูลการซ่อม (ถ้ามี) เราจะใช้ INNER JOIN ไม่ได้ เพราะมันจะไม่แสดงเครื่องที่ไม่เคยเสียเลย” LEFT JOIN คือคำตอบ
• เทคนิค: Table Aliases (การตั้งชื่อเล่นให้ตาราง):
  • แนะนำการใช้ AS เพื่อย่อชื่อตารางให้สั้นลง (เช่น maintenance_tickets AS t)
  • ประโยชน์: ทำให้โค้ดสั้นลงมาก อ่านง่ายขึ้น โดยเฉพาะเมื่อต้อง JOIN หลายๆ ตาราง

ฟังก์ชันการรวมกลุ่ม (Aggregate Functions) และ GROUP BY

• แนวคิด: ผู้จัดการไม่ได้อยากเห็นข้อมูลเป็นพันๆ แถว แต่อยากเห็น “บทสรุป”
• ฟังก์ชันพื้นฐาน 5 อย่าง:
  • COUNT(): นับจำนวนแถว/รายการ (เช่น จำนวนใบแจ้งซ่อมทั้งหมด)
  • SUM(): หาผลรวมของคอลัมน์ที่เป็นตัวเลข (เช่น ผลรวมจำนวนชิ้นงานที่ผลิตได้)
  • AVG(): หาค่าเฉลี่ย (เช่น อุณหภูมิเฉลี่ยของเครื่องจักร)
  • MAX(): หาค่าสูงสุด
  • MIN(): หาค่าต่ำสุด

• GROUP BY (การจัดกลุ่ม):
  • แนวคิด: เป็นคำสั่งที่ใช้คู่กับ Aggregate Functions เพื่อจัดกลุ่มข้อมูลที่มีค่าเหมือนกันให้เป็นแถวเดียว แล้วค่อยคำนวณค่าสรุปของกลุ่มนั้นๆ
  • คำถามกระตุ้น: “เราใช้ COUNT(*) เพื่อนับใบแจ้งซ่อมทั้งหมดได้ แต่ถ้าอยากรู้ว่า ‘เครื่องจักรแต่ละเครื่องมีใบแจ้งซ่อมกี่ใบ’ เราจะทำอย่างไร?”
  • รูปแบบคำสั่ง:

SELECT คอลัมน์ที่ใช้จัดกลุ่ม, ฟังก์ชันรวมกลุ่ม(...) FROM ชื่อตาราง GROUP BY คอลัมน์ที่ใช้จัดกลุ่ม;

• กฎทองของ GROUP BY: คอลัมน์ใดๆ ใน SELECT ที่ไม่ได้อยู่ในฟังก์ชันรวมกลุ่ม จะต้องปรากฏอยู่ใน GROUP BY เสมอ

ส่วนที่ 2: การฝึกปฏิบัติ

หัวข้อ: Workshop: ตอบคำถามผู้จัดการด้วย SQL JOIN และ GROUP BY

เครื่องมือ: เชื่อมต่อกับฐานข้อมูลเดิม ที่มีข้อมูลแล้ว

ทบทวนข้อมูลและโครงสร้าง

1. ให้นักศึกษา SELECT * FROM ... ทั้ง 3 ตารางอีกครั้ง เพื่อทบทวนข้อมูล
2. เปิด E-R Diagram ขึ้นมาดูอีกครั้ง เพื่อย้ำเตือนถึงความสัมพันธ์และคอลัมน์ที่จะใช้เป็น “สะพาน” ในการ JOIN

ส่วนที่ 1: ฝึกฝนการเชื่อมข้อมูล (JOIN Practice)

• คำถามจากผู้จัดการข้อที่ 1: “ผมเห็นใบแจ้งซ่อมรหัส 202401 ช่วยบอกหน่อยว่ามันเป็นของเครื่องจักรชื่ออะไร และตั้งอยู่ที่ไหน?”
  • ให้นักศึกษาเขียน (แนะนำให้ใช้ชื่อเล่นตาราง):

SELECT t.ticket_id, m.machine_name, m.location, t.problem_description FROM maintenance_tickets AS t INNER JOIN machines AS m ON t.machine_id_fk = m.machine_id WHERE t.ticket_id = 202401;

• คำถามจากผู้จัดการข้อที่ 2: “ช่วยทำรายงานสรุปใบแจ้งซ่อมทั้งหมดมาหน่อย แต่ผมไม่อยากเห็นรหัสพนักงานกับรหัสเครื่องจักรนะ ขอเป็นชื่อคนกับชื่อเครื่องจักรเลย”
  • ภารกิจ: เชื่อม 3 ตารางเข้าด้วยกัน
  • ให้นักศึกษาเขียน:

SELECT t.ticket_id, t.report_date, t.status, m.machine_name, e.full_name FROM maintenance_tickets AS t INNER JOIN machines AS m ON t.machine_id_fk = m.machine_id INNER JOIN employees AS e ON t.employee_id_fk = e.employee_id;

• คำถามจากผู้จัดการข้อที่ 3: “ผมอยากได้รายชื่อเครื่องจักร ‘ทั้งหมด’ ในโรงงาน พร้อมกับสถานะการซ่อมล่าสุด (ถ้ามี) เครื่องไหนไม่เคยเสียเลยก็ต้องแสดงขึ้นมาด้วย”
  • ภารกิจ: ใช้ LEFT JOIN เพื่อให้แน่ใจว่าเครื่องจักรทั้งหมดจะถูกแสดง
  • ให้นักศึกษาเขียน:

SELECT m.machine_name, m.location, t.status, t.report_date FROM machines AS m LEFT JOIN maintenance_tickets AS t ON m.machine_id = t.machine_id_fk;

ส่วนที่ 2: ฝึกฝนการสรุปข้อมูล (GROUP BY Practice)

• คำถามจากผู้จัดการข้อที่ 4: “สรุปให้หน่อย ตอนนี้เรามีใบแจ้งซ่อมทั้งหมดกี่ใบแล้ว?”
  • ให้นักศึกษาเขียน:

SELECT COUNT(*) AS total_tickets FROM maintenance_tickets;

อธิบาย: การใช้ AS เพื่อตั้งชื่อคอลัมน์ผลลัพธ์ให้อ่านง่าย

• คำถามจากผู้จัดการข้อที่ 5 (คำถามสำคัญ): “ยอดเยี่ยม แต่ผมอยากรู้ว่า ‘เครื่องจักรแต่ละเครื่อง’ ถูกแจ้งซ่อมไปกี่ครั้ง? ขอชื่อเครื่องจักรกับจำนวนครั้งที่เสีย”
  • ภารกิจ: JOIN เพื่อเอาชื่อเครื่องจักร แล้ว GROUP BY เพื่อนับจำนวน
  • ให้นักศึกษาเขียน:

SELECT m.machine_name, COUNT(t.ticket_id) AS number_of_breakdowns FROM machines AS m JOIN maintenance_tickets AS t ON m.machine_id = t.machine_id_fk GROUP BY m.machine_name;

• คำถามจากผู้จัดการข้อที่ 6 (ท้าทาย): “แล้วพนักงานคนไหนแจ้งซ่อมบ่อยที่สุด? จัดอันดับมาให้ดูหน่อย”
  • ภารกิจ: JOIN, GROUP BY และเพิ่มการเรียงลำดับ ORDER BY
  • ให้นักศึกษาเขียน:

SELECT e.full_name, COUNT(t.ticket_id) AS tickets_reported FROM employees AS e JOIN maintenance_tickets AS t ON e.employee_id = t.employee_id_fk GROUP BY e.full_name ORDER BY tickets_reported DESC;

อธิบาย: ORDER BY ... DESC ใช้สำหรับเรียงข้อมูลจากมากไปน้อย

งานมอบหมาย

“จงสร้างตาราง products ตามโครงสร้างที่กำหนดให้” (ทดสอบ CREATE TABLE)

“เพิ่มข้อมูลสินค้าใหม่ลงในตาราง products” (ทดสอบ INSERT)

“สินค้า A ถูกเปลี่ยนชื่อเป็น B จงแก้ไขข้อมูลในฐานข้อมูลให้ถูกต้อง” (ทดสอบ UPDATE + WHERE)

“ค้นหาสินค้าทั้งหมดที่ผลิตโดยบริษัท ‘XYZ'” (ทดสอบ SELECT + WHERE)

“จัดทำรายงานแสดงชื่อซัพพลายเออร์และชื่อสินค้าที่ซัพพลายเออร์นั้นๆ จัดส่ง” (ทดสอบ INNER JOIN)

“จัดทำรายงานสรุปว่าสินค้าแต่ละประเภท (Category) มีจำนวนรวมในสต็อกเท่าไร” (ทดสอบ SUM + GROUP BY)