Coding Interview Prep · บทเรียน

ลบแถวซ้ำอย่างปลอดภัย

ลบแถวที่ซ้ำกันทุกประการหรือเกือบซ้ำ โดยเก็บระเบียนหลักไว้หนึ่งรายการ

ลบแถวซ้ำอย่างปลอดภัย เป็นบทเรียน Coding Interview Prep ฟรีบน CoddyKit นี่คือบทเรียนที่ 3 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน Coding Interview Prep และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส Coding Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

ปัญหาการกำจัดข้อมูลซ้ำ

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

เราจะครอบคลุมการตรวจหาข้อมูลซ้ำ การเลือกสำเนาที่จะเก็บ การกำจัดข้อมูลซ้ำใน SELECT และการลบข้อมูลซ้ำออกจากตารางจริง

กำหนดความหมายของข้อมูลซ้ำก่อน

คำถามแรกที่ควรถามผู้สัมภาษณ์คือ “อะไรทำให้สองแถวเป็นข้อมูลซ้ำกัน” ตัวเลือกมีดังนี้:

ข้อมูลซ้ำแบบตรงกันทุกประการ: ทุกคอลัมน์มีค่าเหมือนกัน
ข้อมูลซ้ำตามคีย์: มีคีย์ทางธุรกิจเดียวกัน (เช่น มี email เดียวกัน) แต่คอลัมน์อื่นอาจมีค่าต่างกัน

เทคนิคที่ใช้จะแตกต่างกันในแต่ละกรณี อย่าคาดเดาเอง การขอคำชี้แจงเกี่ยวกับนิยามของข้อมูลซ้ำเป็นขั้นตอนที่สำคัญที่สุด และผู้สัมภาษณ์คาดหวังให้คุณถามเรื่องนี้

การตรวจหาข้อมูลซ้ำ

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

การเรียกใช้คำสั่งค้นหาข้อมูลซ้ำก่อนเป็นแนวปฏิบัติที่ดีและควรกล่าวถึง เพราะช่วยให้คุณตรวจสอบขนาดของปัญหาก่อนลบข้อมูล

SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

ข้อมูลซ้ำแบบตรงกันทุกประการ: DISTINCT

หากข้อมูลซ้ำเหมือนกันทุกคอลัมน์จริง ๆ มุมมองที่กำจัดข้อมูลซ้ำแล้วและอ่านได้อย่างเดียวก็สร้างได้ง่าย ๆ ด้วย SELECT DISTINCT * นอกจากนี้ UNION (ที่ไม่มี ALL) ก็ลบแถวซ้ำเช่นกัน

แต่ DISTINCT มีประโยชน์เฉพาะเมื่อคุณต้องการกำจัดข้อมูลซ้ำทั้งแถวและไม่จำเป็นต้องเลือกว่าจะเก็บสำเนาใด สำหรับข้อมูลซ้ำตามคีย์ที่คอลัมน์มีค่าต่างกัน คุณต้องใช้การจัดอันดับ

-- Read-only dedup of exact-duplicate rows
SELECT DISTINCT customer_id, name, signup_date
FROM customers;

ข้อมูลซ้ำตามคีย์: ROW_NUMBER

เมื่อแถวต่าง ๆ มีคีย์เดียวกันแต่คอลัมน์อื่นมีค่าต่างกัน ให้แบ่งกลุ่มตามคีย์และกำหนดหมายเลขให้แต่ละสำเนา rn = 1 จะระบุแถวที่คุณเก็บไว้ ส่วน rn > 1 จะระบุสำเนาส่วนเกินที่ต้องทิ้ง

ORDER BY ภายในฟังก์ชันหน้าต่างเป็นตัวตัดสินว่าเก็บสำเนา<อstrong>ใดเป็นแถวหลัก ให้เลือกอย่างมีเหตุผล เช่น เลือกเก็บแถวที่อัปเดตล่าสุด

SELECT *,
  ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY updated_at DESC
  ) AS rn
FROM users;

การเลือกสำเนาหลัก

ห่อการกำหนดหมายเลขไว้ใน CTE แล้วเก็บเฉพาะ rn = 1 วิธีนี้จะคืนหนึ่งแถวต่อคีย์ โดยเป็นแถวที่ ORDER BY ของคุณจัดให้อยู่ในอันดับแรกโดยเฉพาะ

รูปแบบ SELECT นี้ไม่ทำลายข้อมูล เหมาะอย่างยิ่งสำหรับสร้างมุมมองที่สะอาด หรือส่งข้อมูลผ่าน INSERT ... SELECT ไปยังตารางปลายทางที่กำจัดข้อมูลซ้ำแล้ว โดยไม่แตะต้องตารางต้นทาง

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY updated_at DESC
    ) AS rn
  FROM users
)
SELECT user_id, email, name, updated_at
FROM ranked
WHERE rn = 1;

การเลือกลำดับมีความสำคัญ

ORDER BY ภายในกลุ่มเป็นการตัดสินใจทางธุรกิจ ไม่ใช่เพียงรูปแบบไวยากรณ์:

ORDER BY updated_at DESC จะเก็บระเบียนที่ใหม่ที่สุด
ORDER BY created_at ASC จะเก็บระเบียนแรกที่สร้าง
ORDER BY id ASC จะเก็บคีย์ทดแทนที่มีค่าต่ำที่สุด ซึ่งมีประโยชน์เมื่อจำเป็นต้องเลือกแบบตามอำเภอใจแต่คงที่

เพิ่มตัวตัดสินกรณีเสมอที่ไม่ซ้ำกัน เพื่อให้แถวที่เลือกมีผลลัพธ์แน่นอนเมื่อคอลัมน์หลักที่ใช้จัดลำดับมีค่าเท่ากันด้วย

ROW_NUMBER() OVER (
  PARTITION BY email
  ORDER BY updated_at DESC, id ASC
) AS rn

การลบข้อมูลซ้ำจริงออกจากตาราง

หากต้องการลบข้อมูลซ้ำออกจากตารางจริง ให้ระบุแถวส่วนเกิน (rn > 1) แล้วลบแถวนั้น ในโพสต์เกรสและเอสคิวแอล เซิร์ฟเวอร์ คุณสามารถลบโดยใช้ CTE ได้ ส่วนใน MySQL มักใช้การเชื่อมตารางกับตัวเองหรือแบบสอบถามย่อย

เรียกใช้ SELECT ที่ตรงกันก่อนเสมอ เพื่อดูตัวอย่างว่าแถวใดจะหายไปบ้างอย่างชัดเจน การลบโดยไม่ตรวจสอบก่อนคือสาเหตุที่ผู้สมัครมักตอบคำถามนี้ไม่ผ่าน

WITH ranked AS (
  SELECT ctid,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY updated_at DESC, id ASC
    ) AS rn
  FROM users
)
DELETE FROM users
WHERE ctid IN (SELECT ctid FROM ranked WHERE rn > 1);

รูปแบบการลบด้วยการเชื่อมตารางกับตัวเอง

แนวทางคลาสสิกที่ใช้ได้กับหลายระบบคือเก็บแถวที่มีค่า id ต่ำที่สุดต่อคีย์ที่ซ้ำกัน แล้วลบแถวที่เหลือด้วยการเชื่อมตารางกับตัวเอง วิธีนี้ไม่ต้องใช้ฟังก์ชันหน้าต่าง ซึ่งมีความสำคัญเมื่อใช้ระบบฐานข้อมูลรุ่นเก่า

เงื่อนไขการเชื่อมตารางจะจับคู่แต่ละแถวกับอีกแถวหนึ่งที่มีคีย์เดียวกันแต่มี id ต่ำกว่า ดังนั้นแถวใดก็ตามที่มีแถวคู่ซึ่งมี id ต่ำกว่าจะถือเป็นข้อมูลซ้ำที่ต้องลบ

DELETE u1
FROM users u1
JOIN users u2
  ON u1.email = u2.email
 AND u1.id > u2.id;

รายการตรวจสอบความปลอดภัย

ก่อนลบข้อมูล ให้ป้องกันความผิดพลาดไว้ก่อน:

ครอบการลบด้วยธุรกรรม เพื่อให้คุณใช้ ROLLBACK ได้หากจำนวนแถวไม่ถูกต้อง
เรียกใช้ SELECT COUNT(*) เพื่อนับแถวที่จะลบก่อน แล้วตรวจสอบว่าจำนวนสมเหตุสมผล
พิจารณาสร้างตารางสำรอง: CREATE TABLE users_bak AS SELECT * FROM users
ยืนยันว่าคอลัมน์ใน PARTITION BY กำหนดข้อมูลซ้ำได้จริง มิฉะนั้นคุณอาจลบระเบียนที่แตกต่างกัน

BEGIN;
-- run the DELETE, inspect row count
-- COMMIT; if correct, otherwise ROLLBACK;

ข้อมูลเกือบซ้ำและการทำให้เป็นมาตรฐาน

บางครั้งแถวไม่ได้เหมือนกันทุกประการ แต่ในเชิงตรรกะถือว่าเป็นข้อมูลเดียวกัน เช่น 'Ann@X.com' กับ 'ann@x.com' หรือกรณีมีช่องว่างต่อท้าย ให้แบ่งกลุ่มตามนิพจน์ที่ทำให้เป็นมาตรฐานแทนที่จะใช้คอลัมน์ดิบ

การกล่าวถึงการทำให้เป็นมาตรฐานแสดงให้เห็นถึงความเข้าใจในระดับดี เพราะข้อมูลซ้ำในโลกจริงมักซ่อนอยู่หลังความแตกต่างของตัวพิมพ์ ช่องว่าง หรือรูปแบบ ซึ่งการเปรียบเทียบคีย์แบบง่าย ๆ ตรวจไม่พบ

ROW_NUMBER() OVER (
  PARTITION BY LOWER(TRIM(email))
  ORDER BY updated_at DESC, id ASC
) AS rn

ตรวจสอบอย่างรวดเร็ว

เลือกแนวทางกำจัดข้อมูลซ้ำที่ปลอดภัย

สรุป: การกำจัดข้อมูลซ้ำอย่างปลอดภัย

กำจัดข้อมูลซ้ำอย่างเป็นขั้นตอน:

กำหนดก่อนว่าข้อมูลซ้ำคืออะไร จากนั้นตรวจหาด้วย GROUP BY / HAVING COUNT(*) > 1
ข้อมูลซ้ำแบบตรงกันทุกประการ → DISTINCT ข้อมูลซ้ำตามคีย์ → ROW_NUMBER โดยแบ่งกลุ่มตามคีย์ แล้วเก็บ rn = 1
ORDER BY ของฟังก์ชันหน้าต่างจะเลือกสำเนาหลัก ให้เพิ่มตัวตัดสินกรณีเสมอที่ไม่ซ้ำกัน
ลบแถวที่มี rn > 1 ภายในธุรกรรม หลังจากตรวจสอบจำนวนแถวแล้ว
ทำคีย์ให้เป็นมาตรฐานเพื่อจับข้อมูลเกือบซ้ำ

คำถามที่พบบ่อย

บทเรียน “ลบแถวซ้ำอย่างปลอดภัย” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “ลบแถวซ้ำอย่างปลอดภัย” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส Coding Interview Prep ให้อัปเกรดเป็น CoddyKit PRO คอร์ส Coding Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “ลบแถวซ้ำอย่างปลอดภัย”

ลบแถวที่ซ้ำกันทุกประการหรือเกือบซ้ำ โดยเก็บระเบียนหลักไว้หนึ่งรายการ คุณปฏิบัติ Coding Interview Prep ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน Coding Interview Prep หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน Coding Interview Prep บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 3 จากทั้งหมด 4 บทเรียน

บทเรียน “ลบแถวซ้ำอย่างปลอดภัย” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน Coding Interview Prep นี้ได้ไหม

ได้ บทเรียน Coding Interview Prep ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

← กลับไปที่ Coding Interview Prep