วิธีค้นหา 5 อันดับปัญหาที่พบบ่อยที่สุดในแต่ละโครงการ (MySQL)
ในโลกของการวิเคราะห์ข้อมูล การมองเห็นแค่ "ภาพรวม" อาจไม่เพียงพอที่จะนำไปสู่การตัดสินใจที่เฉียบคมได้เสมอไป เราอาจจะรู้ว่าปัญหา "งานประปา" คือปัญหาอันดับหนึ่งเมื่อมองจากข้อมูลทั้งหมด แต่ถ้าเราเจาะลึกลงไปในแต่ละโครงการ เราอาจพบว่า "โครงการ A" มีปัญหา "งานไฟฟ้า" หนักที่สุด ในขณะที่ "โครงการ B" กลับเจอปัญหา "งานโครงสร้าง" เป็นหลัก
บทความนี้จะแสดงวิธีการใช้ความสามารถอันทรงพลังของ MySQL 8 โดยเฉพาะฟังก์ชันที่เรียกว่า Window Functions เพื่อเปลี่ยนข้อมูลดิบให้กลายเป็นข้อมูลเชิงลึก (Insight) ที่สามารถนำไปใช้งานได้จริง ด้วยการค้นหา 5 อันดับปัญหาที่พบบ่อยที่สุดแยกตามแต่ละโครงการ
SQL Query: หัวใจของการวิเคราะห์
นี่คือคำสั่ง SQL ที่เราจะใช้ในการวิเคราะห์ครั้งนี้:
-- ใช้ Common Table Expression (CTE) เพื่อสร้างตารางเสมือนชื่อ RankedIssues
WITH RankedIssues AS (
-- ส่วนที่ 1: คำนวณและจัดอันดับปัญหาภายในแต่ละโปรเจกต์
SELECT
U.`project_id`,
S.`main_id`,
M.`main_issue`,
COUNT(*) AS total,
ROW_NUMBER() OVER (PARTITION BY U.`project_id` ORDER BY COUNT(*) DESC) AS rank_in_project
FROM `admin_newclus`.`app_unit_issues` AS I
LEFT JOIN `admin_newclus`.`app_units` AS U ON U.`id` = I.`unit_id`
INNER JOIN `admin_newclus`.`app_issue_details` AS D ON D.`id` = I.`issue_detail_id`
INNER JOIN `admin_newclus`.`app_sub_issues` AS S ON S.`id` = D.`sub_id`
INNER JOIN `admin_newclus`.`app_main_issues` AS M ON M.`id` = S.`main_id`
GROUP BY U.`project_id`, S.`main_id`
)
-- ส่วนที่ 2: ดึงข้อมูลจากตารางเสมือนและกรองเฉพาะ 5 อันดับแรก
SELECT *
FROM RankedIssues
WHERE rank_in_project <= 5
ORDER BY project_id, rank_in_project;
การทำงานเบื้องหลัง: แบ่งการวิเคราะห์ออกเป็น 2 ขั้นตอน
โค้ดนี้ดูซับซ้อน แต่จริงๆ แล้วมันทำงานเป็น 2 ขั้นตอนหลักๆ ที่เข้าใจง่าย โดยใช้เทคนิคที่เรียกว่า Common Table Expression (CTE) หรือ WITH RankedIssues AS (...) ซึ่งช่วยให้เราสามารถแบ่งโค้ดที่ซับซ้อนออกเป็นส่วนย่อยๆ ได้
ขั้นตอนที่ 1: คำนวณและจัดอันดับ (ภายใน WITH RankedIssues AS ...)
นี่คือส่วนที่เปรียบเสมือน "ห้องครัว" ที่มีการเตรียมข้อมูลที่สำคัญที่สุด มันจะทำการคำนวณจำนวนปัญหาและจัดอันดับความสำคัญของปัญหา ภายในขอบเขตของแต่ละโปรเจกต์
มาเจาะลึกที่คำสั่งสำคัญที่สุดในส่วนนี้:
ROW_NUMBER() OVER (PARTITION BY U.project_id ORDER BY COUNT(*) DESC) AS rank_in_project
-
ROW_NUMBER(): เป็นฟังก์ชันที่ทำหน้าที่ง่ายๆ คือการสร้างเลขลำดับ (1, 2, 3, ...) ให้กับแต่ละแถว
-
OVER (...): เป็น clause ที่กำหนด "กรอบ" การทำงานของ ROW_NUMBER()
-
PARTITION BY U.project_id: นี่คือ "ไม้กายสิทธิ์" ของ MySQL 8! มันสั่งให้ฟังก์ชัน "แบ่งกลุ่มข้อมูลตาม project_id" และที่สำคัญคือ "เริ่มนับเลขลำดับที่ 1 ใหม่ทุกครั้งที่เจอ project_id ใหม่"
-
ORDER BY COUNT(*) DESC: ก่อนที่จะแจกลำดับเลข มันจะเรียงข้อมูล ภายในกลุ่มของแต่ละโปรเจกต์ ตามจำนวนปัญหา (COUNT(*)) จากมากไปน้อยเสียก่อน
-
AS rank_in_project: ตั้งชื่อคอลัมน์ใหม่ที่เก็บอันดับนี้ว่า rank_in_project
ผลลัพธ์ของขั้นตอนนี้: เราจะได้ตารางเสมือน (ตารางชั่วคราว) ที่ชื่อว่า RankedIssues ซึ่งมีข้อมูลครบถ้วนพร้อม "อันดับ" ของแต่ละปัญหาในโปรเจกต์ของตัวเองเรียบร้อยแล้ว
ตัวอย่างผลลัพธ์จากตาราง RankedIssues (ภาพในจินตนาการ):
| project_id | main_issue | total | rank_in_project |
| 101 | งานประปา | 550 | 1 |
| 101 | งานไฟฟ้า | 420 | 2 |
| 101 | งานสี | 315 | 3 |
| ... | ... | ... | ... |
| 102 | งานโครงสร้าง | 650 | 1 |
| 102 | งานแอร์ | 610 | 2 |
| 102 | งานไฟฟ้า | 300 | 3 |
| ... | ... | ... | ... |
จะเห็นว่า rank_in_project เริ่มนับ 1 ใหม่เมื่อ project_id เปลี่ยนจาก 101 เป็น 102
ขั้นตอนที่ 2: กรองและแสดงผล (ส่วน SELECT สุดท้าย)
เมื่อเราได้ข้อมูลที่ปรุงเสร็จจากขั้นตอนแรกแล้ว ขั้นตอนนี้ก็ง่ายมาก
SELECT *
FROM RankedIssues
WHERE rank_in_project <= 5
ORDER BY project_id, rank_in_project;
-
SELECT * FROM RankedIssues: เลือกข้อมูลทุกคอลัมน์จากตารางเสมือน RankedIssues ที่เราสร้างไว้
-
WHERE rank_in_project <= 5: นี่คือการกรองข้อมูล โดยเลือกมาเฉพาะแถวที่มีอันดับ 1 ถึง 5 เท่านั้น
-
ORDER BY project_id, rank_in_project: จัดเรียงผลลัพธ์สุดท้ายให้อ่านง่าย โดยเรียงตามโปรเจกต์ก่อน แล้วค่อยเรียงตามอันดับ
บทสรุป: จากข้อมูลสู่การลงมือทำ
การใช้ CTE ร่วมกับ Window Functions ใน MySQL 8 ช่วยให้เราสามารถเปลี่ยนคำถามทางธุรกิจที่ซับซ้อนให้กลายเป็นคำสั่ง SQL ที่มีโครงสร้างชัดเจนและอ่านเข้าใจง่าย ผลลัพธ์ที่ได้ไม่ใช่แค่ตัวเลขลอยๆ แต่เป็นข้อมูลเชิงลึกที่ผู้จัดการโครงการสามารถนำไปวางแผนได้ทันทีว่า "ในโครงการของฉัน ปัญหา 5 อันดับแรกที่ต้องรีบเข้าไปดูแลคืออะไร" ซึ่งนำไปสู่การแก้ไขปัญหาที่ตรงจุดและมีประสิทธิภาพสูงสุด