(25-07-2009)

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

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

สมมุติว่าเรามีโต๊ะด้วย คีย์หลักคอลัมน์ id และชื่อ ซึ่งต้องมีค่าไม่ซ้ำกันตามข้อจำกัดโดเมน อย่างไรก็ตามหากคุณกำหนดโครงสร้างตารางดังนี้

สร้างตาราง T_pk (id INT IDENTITY PRIMARY KEY ชื่อ VARCHAR (50 ));

ไม่มีอะไรป้องกันการปรากฏตัวของรายการที่ซ้ำกัน ควรจะถูกนำมาใช้ โครงสร้างต่อไปนี้ตาราง:

สร้างตาราง T_pk (รหัสหลัก ID INT IDENTITY ชื่อ VARCHAR (50) UNIQUE);

ทุกคนรู้ดีว่าสิ่งที่ถูกต้องควรทำ แต่บ่อยครั้งที่คุณต้องจัดการกับโครงสร้างและข้อมูลที่ "ดั้งเดิม" ที่ละเมิดข้อจำกัดของโดเมน นี่คือตัวอย่าง:

ชื่อรหัส 1 ยอห์น 2 สมิธ 3 ยอห์น 4 สมิธ 5 สมิธ 6 ทอม

คุณอาจถามว่า:“ ปัญหานี้แตกต่างจากปัญหาก่อนหน้าอย่างไรท้ายที่สุดมีวิธีแก้ไขที่ง่ายกว่านี้ - เพียงลบแถวทั้งหมดออกจากแต่ละกลุ่มที่มีค่าเหมือนกันในคอลัมน์ชื่อเหลือเพียงแถวที่มี ค่ารหัสขั้นต่ำ/สูงสุด ตัวอย่างเช่น:"

ลบจาก T_pk โดยที่ id > (เลือก MIN (id) จาก T_pk X โดยที่ X.name = T_pk.name);

ถูกต้อง แต่ฉันยังไม่ได้บอกคุณทุกอย่างเลย :-) ลองนึกภาพว่าเรามีตารางลูก T_details เชื่อมโยงกับตาราง T_pk โดย คีย์ต่างประเทศ:

สร้างตาราง T_details (id_pk INT การอ้างอิงคีย์ต่างประเทศ T_pk ON DELETE CASCADE , VARCHAR สี (10), คีย์หลัก (id_pk, color);

ตารางนี้อาจมีข้อมูลต่อไปนี้:

สี id_pk 1 น้ำเงิน 1 แดง 2 เขียว 2 แดง 3 แดง 4 น้ำเงิน 6 แดง

เพื่อความชัดเจนยิ่งขึ้น ลองใช้แบบสอบถามกัน

เลือก id, ชื่อ, สีจาก T_pk เข้าร่วม T_details ON id= id_pk;

เพื่อดูชื่อ:

สีชื่อรหัส 1 จอห์นสีน้ำเงิน 1 จอห์นสีแดง 2 สมิธสีเขียว 2 สมิธสีแดง 3 จอห์นสีแดง 4 สมิธสีน้ำเงิน 6 ทอมสีแดง

ดังนั้นปรากฎว่าข้อมูลที่เกี่ยวข้องกับบุคคลหนึ่งจริงๆ ได้รับการจัดสรรอย่างผิดพลาดไปยังอีกบุคคลหนึ่ง บันทึกของผู้ปกครอง. นอกจากนี้ ยังมีรายการที่ซ้ำกันในตารางนี้:

1 จอห์น เรด 3 จอห์น เรด

แน่นอนว่าข้อมูลดังกล่าวจะนำไปสู่การวิเคราะห์และรายงานที่ผิดพลาด นอกจากนี้, ลบน้ำตกจะส่งผลให้ข้อมูลสูญหาย ตัวอย่างเช่น หากเราเหลือเฉพาะแถวที่มี ID ขั้นต่ำในแต่ละกลุ่มในตาราง T_pk เราก็จะสูญเสียแถวนั้นไป

4 สมิธ บลู

ในตาราง T_details ดังนั้นเราจึงต้องคำนึงถึงทั้งสองตารางเมื่อกำจัดรายการที่ซ้ำกัน

ขั้นตอน "การทำความสะอาด" ข้อมูลสามารถดำเนินการได้เป็นสองขั้นตอน:

1. อัปเดตตาราง T_details โดยกำหนดข้อมูลที่เกี่ยวข้องกับชื่อหนึ่งให้กับ id ด้วยจำนวนขั้นต่ำในกลุ่ม
2. ลบรายการที่ซ้ำกันออกจากตาราง T_pk เหลือเพียงแถวที่มีรหัสขั้นต่ำในแต่ละกลุ่มด้วย ค่าเดียวกันในคอลัมน์ชื่อ

กำลังอัปเดตตาราง T_details

SELECT id_pk, ชื่อ, สี, RANK () OVER (PARTITION BY name, color ORDER BY name, color, id_pk) dup ,(SELECT MIN (id) FROM T_pk WHERE T_pk.name = X.name) min_id จาก T_pk X JOIN T_details บน id=id_pk;

ตรวจจับการมีอยู่ของรายการที่ซ้ำกัน (ค่าซ้ำ > 1) และ ค่าต่ำสุด id ในกลุ่มที่มีชื่อเหมือนกัน (min_id) นี่คือผลลัพธ์ของการเรียกใช้แบบสอบถามนี้:

id_pk ชื่อสีซ้ำ min_id 1 จอห์น น้ำเงิน 1 1 1 จอห์น แดง 1 1 3 จอห์น แดง 2 1 4 น้ำเงินสมิธ 1 2 2 เขียวสมิธ 1 2 2 แดงสมิธ 1 2 6 ทอม แดง 1 6

ตอนนี้เราจำเป็นต้องแทนที่ค่า id_pk ด้วยค่า min_pk สำหรับทุกแถวยกเว้นแถวที่สาม เนื่องจาก บรรทัดนี้ซ้ำกับบรรทัดที่สอง ตามที่ระบุด้วยค่า dup=2 คำขออัปเดตสามารถเขียนได้ดังนี้:

อัปเดต T_details SET id_pk=min_id จาก T_details T_d JOIN (SELECT id_pk, name, color , RANK () OVER (PARTITION BY name, color ORDER BY name, color, id_pk) dup ,(SELECT MIN (id) FROM T_pk WHERE T_pk.name) = X.name) min_id จาก T_pk X เข้าร่วม T_details ON id=id_pk) Y ON Y.id_pk=T_d.id_pk โดยที่ dup =1 ;

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

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

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

ตัวอย่างของตารางที่ซ้ำซ้อนอย่างชัดเจน:

ตอนนี้เรามาดูกันว่าเราจะแก้ไขปัญหานี้ได้อย่างไร สามารถใช้วิธีการได้หลายวิธีที่นี่

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

2. วิธีแก้ไขปัญหาอื่นคือการสร้างคิวรีแบบใช้เลือกข้อมูลซึ่งจัดกลุ่มข้อมูลเพื่อให้ส่งกลับเฉพาะแถวที่ไม่ซ้ำเท่านั้น:

เลือกประเทศ_id เมือง_ชื่อ
จาก มายเทเบิล
จัดกลุ่มตามประเทศ_id,เมือง_ชื่อ

เราได้รับตัวอย่างดังต่อไปนี้:

จากนั้นเราจะเขียนชุดข้อมูลผลลัพธ์ลงในตารางอื่น

3. บี การตัดสินใจข้างต้นใช้เพิ่มเติม รหัสโปรแกรมหรือตารางเพิ่มเติม อย่างไรก็ตาม มันจะสะดวกกว่าถ้าทำทุกอย่างโดยใช้แค่อย่างเดียว แบบสอบถาม SQLปราศจาก ตารางเพิ่มเติม. และนี่คือตัวอย่างวิธีแก้ปัญหาดังกล่าว:

ลบ a.* จาก mytable a
(เลือก

จาก mytable ข

)ค
ที่ไหน
ก.country_id = ค.country_id
และ a.city_name = c.city_name
และ a.id > c.mid

หลังจากดำเนินการค้นหาดังกล่าวแล้ว เฉพาะระเบียนที่ไม่ซ้ำเท่านั้นที่จะยังคงอยู่ในตาราง:

ตอนนี้เรามาดูกันดีกว่าว่ามันทำงานอย่างไร เมื่อขอลบคุณต้องกำหนดเงื่อนไขที่จะระบุว่าข้อมูลใดควรลบและควรเหลือข้อมูลใด เราจำเป็นต้องลบรายการที่ไม่ซ้ำทั้งหมดออก เหล่านั้น. หากมีบันทึกที่เหมือนกันหลายรายการ (เหมือนกันหากมีค่า Country_id และ city_name เท่ากัน) คุณจะต้องใช้บรรทัดใดบรรทัดหนึ่งจำรหัสและลบบันทึกทั้งหมดที่มีค่า Country_id และ city_name เดียวกัน แต่มีรหัสอื่น (รหัส)

สตริงการสืบค้น SQL:

ลบ a.* จาก mytable a

บ่งชี้ว่าการลบจะดำเนินการออกจากตาราง mytable

จากนั้นแบบสอบถามแบบใช้เลือกข้อมูลจะสร้างตารางเสริมที่เราจัดกลุ่มระเบียนเพื่อให้ระเบียนทั้งหมดไม่ซ้ำกัน:

(เลือก
b.country_id, b.city_name, MIN(b.id) กลาง
จาก mytable ข
จัดกลุ่มตาม b.country_id, b.city_name
)ค

MIN(b.id) mid – สร้างคอลัมน์ mid (ตัวย่อ min id) ซึ่งมีค่า id ขั้นต่ำในแต่ละกลุ่มย่อย

ผลลัพธ์ที่ได้คือตารางที่มีบันทึกที่ไม่ซ้ำและรหัสแถวแรกสำหรับกลุ่มบันทึกที่ซ้ำกันแต่ละกลุ่ม

ตอนนี้เรามีโต๊ะสองโต๊ะ หนึ่งรายการทั่วไปที่มีบันทึกทั้งหมด เส้นพิเศษจะถูกลบออกจากมัน ส่วนที่สองประกอบด้วยข้อมูลเกี่ยวกับแถวที่ต้องบันทึก

สิ่งที่เหลืออยู่คือการสร้างเงื่อนไขที่ระบุว่า: คุณต้องลบบรรทัดทั้งหมดที่ช่อง country_id และ city_name ตรงกัน แต่ id จะไม่ตรงกัน ใน ในกรณีนี้เลือกค่า ID ขั้นต่ำ ดังนั้นบันทึกทั้งหมดที่มี ID มากกว่าที่เลือกในตารางชั่วคราวจะถูกลบ

นอกจากนี้ยังเป็นที่น่าสังเกตว่าการดำเนินการที่อธิบายไว้สามารถทำได้หากมีฟิลด์คีย์ในตาราง หากคุณบังเอิญเจอตารางที่ไม่มีตัวระบุเฉพาะ ให้เพิ่มเข้าไป:

แก้ไขตาราง ` mytable` เพิ่ม `id` INT(11) ไม่เป็นโมฆะ AUTO_INCREMENT เพิ่มคีย์หลัก (`id`)

หลังจากดำเนินการค้นหาดังกล่าวแล้ว เราได้รับคอลัมน์เพิ่มเติมที่เต็มไปด้วยข้อมูลเฉพาะ ค่าตัวเลขสำหรับแต่ละแถวของตาราง

เราทำทุกอย่าง การดำเนินการที่จำเป็น. หลังจากดำเนินการล้างตารางบันทึกที่ซ้ำกันเสร็จสิ้นแล้ว ฟิลด์นี้ก็สามารถลบออกได้เช่นกัน

การลบการซ้ำซ้อน

แหล่งที่มาของฐานข้อมูล

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

ถ้า OBJECT_ID("Sales.MyOrders") ไม่ใช่ NULL DROP TABLE Sales.MyOrders; ไป SELECT * เข้าสู่ Sales.MyOrders จาก Sales.Orders UNION ALL SELECT * จาก Sales.Orders UNION ALL SELECT * จาก Sales.Orders;

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

SELECT orderid, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n จาก Sales.MyOrders;

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

แต่ถ้าจำนวนแถวที่ถูกลบมีขนาดใหญ่ - โดยเฉพาะอย่างยิ่งเมื่อประกอบด้วยแถวในตารางเป็นสัดส่วนมาก - การลบด้วย บันทึกเต็มรูปแบบการดำเนินการบันทึกจะช้าเกินไป ในกรณีนี้ คุณอาจต้องการพิจารณาใช้การดำเนินการบันทึกจำนวนมาก เช่น SELECT INTO เพื่อคัดลอกแถวที่ไม่ซ้ำ (หมายเลข 1) ไปยังตารางอื่น หลังจากนี้ตารางเดิมจะถูกลบออกไปแล้ว ตารางใหม่ชื่อของตารางระยะไกลได้รับการกำหนด ข้อจำกัด ดัชนี และทริกเกอร์จะถูกสร้างขึ้นใหม่ นี่คือรหัสสำหรับโซลูชันที่สมบูรณ์:

ด้วย C AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n จาก Sales.MyOrders) SELECT orderid, custid, empid, orderdate, requireddate, shippingdate, shipperid, ค่าขนส่ง, ชื่อเรือ, ที่อยู่จัดส่ง, เมืองทางเรือ, พื้นที่ทางเรือ, รหัสไปรษณีย์, ประเทศทางเรือเข้าสู่การขาย คำสั่งซื้อTmp จาก C โดยที่ n = 1; วางตารางการขาย MyOrders; EXEC sp_rename "Sales.OrdersTmp", "MyOrders"; -- การสร้างดัชนี ข้อจำกัด และทริกเกอร์ขึ้นใหม่

เพื่อความง่าย ฉันไม่ได้เพิ่มการควบคุมธุรกรรมใดๆ ที่นี่ แต่คุณต้องจำไว้เสมอว่าผู้ใช้หลายคนสามารถทำงานกับข้อมูลได้ในเวลาเดียวกัน เมื่อนำวิธีนี้ไปใช้ในสภาพแวดล้อมการผลิต คุณต้องปฏิบัติตามลำดับต่อไปนี้:

เปิดธุรกรรม

รับล็อคโต๊ะ

ดำเนินการ คำสั่งเลือกเข้าไปข้างใน.

ลบและเปลี่ยนชื่อวัตถุ

สร้างดัชนี ข้อจำกัด และทริกเกอร์ขึ้นมาใหม่

ยืนยันการทำธุรกรรม

มีตัวเลือกอื่น - เพื่อกรองเฉพาะแถวที่ไม่ซ้ำหรือเฉพาะแถวที่ไม่ซ้ำเท่านั้น ทั้ง ROW_NUMBER และ RANK คำนวณตามรหัสลำดับ มีลักษณะดังนี้:

SELECT orderid, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum, RANK() OVER(ORDER BY orderid) AS rnk จาก Sales.MyOrders;

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