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.คอลัมน์ที่เชื่อม;

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 (การจัดกลุ่ม):

GROUP BY เป็นคำสั่ง SQL ที่ใช้ในการจัดกลุ่มแถวที่มีค่าเดียวกันในคอลัมน์ที่ระบุ โดยปกติแล้วจะใช้ร่วมกับฟังก์ชันการรวม (aggregate functions) เช่น COUNT(), SUM(), AVG(), MIN(), MAX() เพื่อคำนวณค่าสำหรับแต่ละกลุ่ม

วัตถุประสงค์หลักของ GROUP BY คือ:

• รวมข้อมูล: ช่วยให้คุณสามารถรวมข้อมูลจากหลายๆ แถวให้เป็นแถวเดียวตามเกณฑ์ที่กำหนด
• สร้างสรุป: ใช้เพื่อสร้างรายงานสรุปหรือข้อมูลเชิงสถิติจากชุดข้อมูล

รูปแบบคำสั่ง:

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

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

ตัวอย่าง: หากคุณต้องการนับจำนวนตั๋วสำหรับแต่ละเครื่อง:

SELECT
    machine_id_fk,
    COUNT(ticket_id) AS total_tickets
FROM
    maintenance_tickets
GROUP BY
    machine_id_fk;

SELECT
    machine_id_fk,
    COUNT(ticket_id) AS total_tickets
FROM
    maintenance_tickets
GROUP BY
    machine_id_fk;

ส่วนที่ 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;

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;

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;

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;

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;

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;

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 ใช้สำหรับเรียงข้อมูลจากมากไปน้อย

งานมอบหมาย

ส่วนที่ 1: การเตรียมฐานข้อมูล (Setup)

คำชี้แจง: ให้สร้างตารางและเพิ่มข้อมูลตั้งต้น

1.1 สร้างตาราง

1.2 เพิ่มข้อมูลตั้งต้น

ตาราง products

ตาราง production_lines

ตาราง production_logs

ตาราง qc_checks

ส่วนที่ 2: สถานการณ์จัดการข้อมูล

คำชี้แจง: เกิดเหตุการณ์ต่างๆ ในโรงงาน ให้นักเรียนเขียนคำสั่ง SQL เพื่อจัดการข้อมูลให้เป็นปัจจุบัน

ภารกิจ 2.1 (INSERT): โรงงานได้อนุมัติให้ผลิตสินค้าใหม่คือ ‘ชิ้นส่วน D-04’ (รหัส 104) ซึ่งมีน้ำหนักเป้าหมาย 75.50 กรัม จงเขียนคำสั่ง SQL เพื่อเพิ่มข้อมูลสินค้าใหม่นี้ลงในตาราง products

ภารกิจ 2.2 (UPDATE): ฝ่ายผลิตแจ้งว่าบันทึกการผลิตรหัส 202 (log_id = 202) จริงๆ แล้วผลิตสินค้าได้ 380 ชิ้น ไม่ใช่ 350 จงเขียนคำสั่ง SQL เพื่อแก้ไขจำนวนที่ผลิต (quantity_produced) ให้ถูกต้อง

ภารกิจ 2.3 (DELETE): มีการบันทึกผล QC ซ้ำซ้อนเข้ามาโดยมีรหัส 404 (check_id = 404) ซึ่งเป็นข้อมูลที่ผิดพลาด จงเขียนคำสั่ง SQL เพื่อลบแถวข้อมูลนี้ออกจากตาราง qc_checks (ให้นักเรียน INSERT ข้อมูลผิดพลาดนี้เข้าไปก่อน แล้วค่อยลบ)

• INSERT INTO qc_checks VALUES (404, 201, 150.00, 'ERROR');

ส่วนที่ 3: การวิเคราะห์และจัดทำรายงาน

คำชี้แจง: ผู้จัดการโรงงานต้องการรายงานสรุปผลต่างๆ ให้นักเรียนเขียนคำสั่ง SQL เพื่อดึงข้อมูลตามที่ผู้จัดการต้องการ

ภารกิจ 3.1 (INNER JOIN): ผู้จัดการต้องการรายงานที่แสดง “ชื่อสินค้า”, “จำนวนที่ผลิต”, และ “ชื่อสายการผลิต” ของทุกรอบการผลิต จงเขียน SQL เพื่อสร้างรายงานดังกล่าว

ภารกิจ 3.2 (LEFT JOIN): ผู้จัดการต้องการดูรายชื่อ “สินค้าทั้งหมด” ที่มีในระบบ พร้อมทั้ง “รหัสรอบการผลิต” (log_id) ที่เคยผลิตสินค้านั้นๆ (หากสินค้าใดไม่เคยถูกผลิตเลย ให้แสดงค่าเป็น NULL) จงเขียน SQL เพื่อสร้างรายงานดังกล่าว

ภารกิจ 3.3 (GROUP BY และ COUNT): ผู้จัดการอยากทราบว่า “สายการผลิตแต่ละสาย” ทำงานไปกี่รอบการผลิตแล้ว จงเขียน SQL เพื่อแสดง “ชื่อสายการผลิต” และ “จำนวนรอบการผลิตทั้งหมด” ของแต่ละสาย

ภารกิจ 3.4 (ท้าทาย: JOIN, GROUP BY และ AVG): เพื่อควบคุมคุณภาพ ผู้จัดการต้องการทราบ “น้ำหนักเฉลี่ยที่วัดได้จริง” จากการสุ่มตรวจ QC ของ “สินค้าแต่ละชนิด” จงเขียน SQL เพื่อแสดง “ชื่อสินค้า” และ “น้ำหนักเฉลี่ยจากการตรวจ QC”