MariaDB ROW_NUMBER() ช่วยได้

วันนี้มาแชร์ปัญหาที่เจอตอนทำงานและวิธีแก้ไข เรื่องมีอยู่ว่า เรามีข้อมูลที่ต้องแก้ไข ซึ่งข้อมูลชุดนี้มีการจัดเรียง priority ตามลำดับแบ่งตาม group id ต่างๆ และดาต้าเบสที่เราใช้คือ MariaDB

ความเป็นมา

ปัญหาที่เจอเป็นเรื่องของข้อมูลที่ผิดเพี้ยนไป เนื่องจากตัวระบบมีการอัพเดตข้อมูลผิดพลาด ตัวอย่างชุดข้อมูลจะเป็น

Table property_informations

id	name	group_id	priority
1	Condo floor 1	1	1
2	Condo floor 2	1	2
3	Condo floor 3	1	2
4	House room 1	2	1
5	House room 2	2	1
6	House room 3	2	1
7	House room 4	2	1

ตอนนี้ปัญหาคือ ข้อมูลมีการเรียง priority ผิดพลาด ซึ่งถ้าชุดข้อมูลที่มีปัญหามีน้อย เราก็สามารถที่จะแก้ไขข้อมูลแบบ manual ได้ไม่เสียเวลานัก แต่เหตุการณ์จริงคือ ชุดข้อมูลที่ต้องการแก้ไขมีเยอะๆๆๆ มาก

โจทย์คือ จากชุดข้อมูลที่เรามี เราต้องการเรียง priority ใหม่ทั้งหมดให้ถูกต้อง โดยอิงจาก group_id และเรียง order ตาม id แบบ asc ได้เลย

น้อง บูมสุดหล่อ ที่ทำงานด้วยกันแนะนำ ROW_NUMBER() เพื่อนำมาใช้แก้ไขปัญหา ซึ่งก็ได้ผลดีและเร็วมากทั้งกับการเช็คข้อมูล และ การแก้ไข

SELECT QUERY เริ่มต้นการสร้าง Query เพื่อตรวจสอบ

SELECT 
    property_informations.id,
    property_informations.name,
    property_informations.group_id,
    property_informations.priority AS current_priority,
    sub_property_informations.new_priority
FROM property_informations
INNER JOIN (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY id asc) new_priority,
        id
    FROM property_informations
) sub_property_informations ON sub_property_informations.id = property_informations.id

เมื่อเรา run คำสั่ง sql เราจะผลลัพธ์เพื่อตรวจเหมือนในตาราง

id	name	group_id	current_priority	new_priority
1	Condo floor 1	1	1	1
2	Condo floor 2	1	2	2
3	Condo floor 3	1	2	3
4	House room 1	2	1	1
5	House room 2	2	2	2
6	House room 3	2	2	3
7	House room 4	2	2	4

เมื่อดูที่ column current_priority และ new_priority จะเห็นได้ว่า ส่ิงที่เราต้องการที่จะอัพเดตหน้าตาจะเป็นอย่างไร

UPDATE QUERY จัดการอัพเดตรวดเดียวไปเลย

UPDATE
  property_informations
  JOIN 
    ( 
      SELECT id, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY id asc) AS new_priority 
      FROM property_informations
    ) AS sub_property_informations
  ON sub_property_informations.id = property_informations.id
SET 
  property_informations.priority = sub_property_informations.new_priority;

เมื่อรัน update query ด้านบน ข้อมูลจะถูกอัพเดตเป็น

id	name	group_id	priority
1	Condo floor 1	1	1
2	Condo floor 2	1	2
3	Condo floor 3	1	3
4	House room 1	2	1
5	House room 2	2	2
6	House room 3	2	3
7	House room 4	2	4

เท่านี้เราก็สามารถแก้ไขข้อมูลปริมาณเยอะๆ ได้ในคราวเดียว แต่เวลาใช้ก็อย่าลืม Backup ข้อมูลไว้ก่อนด้วยนะครับ