(25-07-2009)

Dalam artikel sebelumnya, kami melihat menyelesaikan masalah pendua yang disebabkan oleh kunci utama yang hilang. Sekarang mari kita pertimbangkan kes yang lebih sukar, apabila kunci nampaknya ada di sana, tetapi ia adalah sintetik, yang, jika direka dengan tidak betul, juga boleh membawa kepada pendua dari sudut pandangan bidang subjek.

Memang pelik, tetapi apabila saya bercakap di kuliah tentang kelemahan kunci sintetik, saya tetap mendapati fakta bahawa pelajar sentiasa menggunakannya dalam projek pangkalan data pertama mereka. Nampaknya, seseorang mempunyai keperluan genetik untuk menomborkan semula segala-galanya, dan hanya ahli psikoterapi boleh membantu di sini. :-)

Jadi, katakan kita mempunyai meja dengan kunci utama id dan lajur nama, yang, menurut sekatan domain, mesti mengandungi nilai unik. Walau bagaimanapun, jika anda mentakrifkan struktur jadual seperti berikut

BUAT JADUAL T_pk (id INT IDENTITI KUNCI UTAMA , nama VARCHAR (50 ));

maka tiada apa yang menghalang kemunculan pendua. Sepatutnya telah digunakan struktur berikut jadual:

BUAT JADUAL T_pk (id KUNCI UTAMA IDENTITI INT, nama VARCHAR (50) UNIK);

Semua orang tahu perkara yang betul untuk dilakukan, tetapi selalunya anda perlu berurusan dengan struktur "warisan" dan data yang melanggar kekangan domain. Berikut ialah contoh:

nama id 1 John 2 Smith 3 John 4 Smith 5 Smith 6 Tom

Anda mungkin bertanya: "Bagaimana masalah ini berbeza daripada yang sebelumnya? Lagipun, terdapat penyelesaian yang lebih mudah di sini - hanya keluarkan semua baris daripada setiap kumpulan dengan nilai yang sama dalam lajur nama, hanya tinggalkan baris dengan nilai id minimum/maksimum. Contohnya, seperti ini:"

PADAM DARI T_pk WHERE id > (PILIH MIN (id) DARI T_pk X WHERE X.name = T_pk.name);

Betul, tetapi saya belum memberitahu anda semua lagi. :-) Bayangkan bahawa kita mempunyai jadual kanak-kanak T_details dipautkan ke jadual T_pk oleh kunci asing:

CIPTA JADUAL T_perincian (id_pk INT RUJUKAN KUNCI ASING T_pk PADA DELETE CASCADE , warna VARCHAR (10), KUNCI UTAMA (id_pk, warna);

Jadual ini mungkin mengandungi data berikut:

warna id_pk 1 biru 1 merah 2 hijau 2 merah 3 merah 4 biru 6 merah

Untuk lebih jelas, mari gunakan pertanyaan

PILIH id, nama, warna DARI T_pk JOIN T_details ON id= id_pk;

untuk melihat nama:

warna nama id 1 John biru 1 John merah 2 Smith hijau 2 Smith merah 3 John merah 4 Smith biru 6 Tom merah

Oleh itu, ternyata data yang sebenarnya berkaitan dengan satu orang telah tersilap diperuntukkan kepada yang berbeza rekod ibu bapa. Di samping itu, terdapat pendua dalam jadual ini:

1 John merah 3 John merah

Jelas sekali, data sedemikian akan membawa kepada analisis dan laporan yang salah. Lebih-lebih lagi, lata padam akan mengakibatkan kehilangan data. Sebagai contoh, jika kita meninggalkan hanya baris dengan ID minimum dalam setiap kumpulan dalam jadual T_pk, kita akan kehilangan baris

4 Smith biru

dalam jadual T_details. Oleh itu, kita mesti mengambil kira kedua-dua jadual apabila menghapuskan pendua.

Prosedur "pembersihan" data boleh dijalankan dalam dua peringkat:

1. Kemas kini jadual T_details dengan memberikan data yang berkaitan dengan satu nama kepada id dengan nombor minimum dalam kumpulan.
2. Alih keluar pendua daripada jadual T_pk, hanya tinggalkan baris dengan id minimum dalam setiap kumpulan dengan nilai yang sama dalam lajur nama.

Mengemas kini jadual T_details

PILIH id_pk, nama, warna , PANGKAT () 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 FROM T_pk X JOIN T_details HIDUP id=id_pk;

mengesan kehadiran pendua (nilai duplikat > 1) dan nilai minimum id dalam kumpulan nama yang sama (min_id). Berikut ialah hasil menjalankan pertanyaan ini:

id_pk nama warna dup min_id 1 John biru 1 1 1 John merah 1 1 3 John merah 2 1 4 Smith biru 1 2 2 Smith hijau 1 2 2 Smith merah 1 2 6 Tom merah 1 6

Sekarang kita perlu menggantikan nilai id_pk dengan nilai min_pk untuk semua baris kecuali yang ketiga, kerana baris ini ialah pendua baris kedua, seperti yang ditunjukkan oleh nilai dup=2. Permintaan kemas kini boleh ditulis seperti ini:

KEMASKINI T_details SET id_pk=min_id DARIPADA T_details T_d JOIN (PILIH id_pk, nama, warna , PANGKAT () OVER (PARTITION BY nama, color ORDER BY name, color, id_pk) dup ,(SELECT MIN (id) FROM T_pk WHERE T_pk.name = X.nama) id_min DARI T_pk X SERTAI T_butiran ON id=id_pk) Y ON Y.id_pk=T_d.id_pk WHERE dup =1 ;

Apabila tugas mengoptimumkan pangkalan data timbul atau strukturnya berubah, kadangkala tugas berkaitan timbul untuk mengatur data yang telah terkumpul. Adalah baik jika jadual sudah dalam bentuk biasa semasa pembangunan, dan keseluruhan sistem disusun sedemikian rupa sehingga ia tidak mengumpul maklumat pendua yang tidak perlu. Jika ini tidak berlaku, maka apabila memuktamadkan sistem sedemikian, anda ingin menyingkirkan semua data berlebihan dan melakukan segala-galanya dengan kualiti tertinggi.

Dalam artikel ini kita akan mempertimbangkan tugas mengalih keluar baris pendua dalam jadual pangkalan data. Saya ingin menunjukkan dengan segera bahawa kita bercakap tentang tentang keperluan untuk mengalih keluar baris pendua. Sebagai contoh, rekod dalam jadual pesanan dengan medan "kod pesanan", "kod produk", "kod pelanggan", "tarikh pesanan" boleh berbeza hanya dalam kod pesanan, kerana seorang pelanggan boleh memesan produk yang sama beberapa kali pada hari yang sama sekali. Dan penunjuk utama di sini bahawa semuanya betul ialah kehadiran medan utama.

Jika kita melihat jadual penuh dengan medan pendua, tanpa keperluan yang jelas untuk setiap entri, maka inilah yang perlu diperbaiki.

Contoh jadual yang jelas berlebihan:

Sekarang mari kita lihat bagaimana kita boleh menyelesaikan masalah ini. Beberapa kaedah boleh digunakan di sini.

1. Anda boleh menulis fungsi untuk membandingkan dan mengulangi semua data. Ia mengambil masa yang lama, dan anda tidak selalu mahu menulis kod untuk kegunaan sekali sahaja.

2. Penyelesaian lain ialah membuat pertanyaan terpilih yang mengumpulkan data supaya hanya baris unik dikembalikan:

PILIH country_id, city_name
DARI mytable
KUMPULAN MENGIKUT country_id, city_name

Kami mendapat sampel berikut:

Kemudian kami menulis set data yang terhasil ke dalam jadual lain.

3. B keputusan di atas tambahan dikenakan kod program atau jadual tambahan. Walau bagaimanapun, adalah lebih mudah untuk melakukan segala-galanya menggunakan sahaja pertanyaan SQL tanpa jadual tambahan. Dan berikut adalah contoh penyelesaian sedemikian:

PADAM a.* DARI mytable a,
(PILIH

DARIPADA mytable b

)c
DI MANA
a.country_id = c.country_id
DAN a.nama_bandar = c.nama_bandar
DAN a.id > c.pertengahan

Selepas melaksanakan pertanyaan sedemikian, hanya rekod unik akan kekal dalam jadual:

Sekarang mari kita lihat dengan lebih dekat cara semuanya berfungsi. Apabila meminta pemadaman, anda mesti menetapkan syarat yang akan menunjukkan data yang harus dipadamkan dan yang harus ditinggalkan. Kita perlu mengalih keluar semua entri bukan unik. Itu. jika terdapat beberapa rekod yang sama (ia adalah sama jika mereka mempunyai nilai country_id dan city_name yang sama), maka anda perlu mengambil salah satu baris, ingat kodnya dan padam semua rekod dengan nilai country_id dan city_name yang sama, tetapi kod yang berbeza (ID).

Rentetan pertanyaan SQL:

PADAM a.* DARI mytable a,

menunjukkan bahawa pemadaman akan dilakukan daripada jadual mytable.

Pertanyaan pilih kemudian menjana jadual tambahan di mana kami mengumpulkan rekod supaya semua rekod adalah unik:

(PILIH
b.country_id, b.city_name, MIN(b.id) pertengahan
DARIPADA mytable b
KUMPULAN MENGIKUT b.country_id, b.city_name
)c

MIN(b.id) pertengahan – membentuk pertengahan lajur (singkatan id min), yang mengandungi nilai id minimum dalam setiap subkumpulan.

Hasilnya ialah jadual yang mengandungi rekod unik dan id baris pertama untuk setiap kumpulan rekod pendua.

Sekarang kita ada dua meja. Satu am yang mengandungi semua rekod. Garisan tambahan akan dialih keluar daripadanya. Yang kedua mengandungi maklumat tentang baris yang perlu disimpan.

Yang tinggal hanyalah membuat syarat yang menyatakan: anda perlu memadamkan semua baris yang sepadan dengan medan country_id dan city_name, tetapi id tidak akan sepadan. DALAM dalam kes ini nilai id minimum dipilih, jadi semua rekod yang idnya lebih besar daripada yang dipilih dalam jadual sementara dipadamkan.

Ia juga perlu diperhatikan bahawa operasi yang diterangkan boleh dilakukan jika terdapat medan utama dalam jadual. Jika anda tiba-tiba terjumpa jadual tanpa pengecam unik, maka tambahkan sahaja:

ALTER JADUAL ` mytable` TAMBAH `id` INT(11) BUKAN NULL AUTO_INCREMENT , TAMBAH KUNCI UTAMA (`id`)

Setelah melaksanakan pertanyaan sedemikian, kami mendapat lajur tambahan yang diisi dengan unik nilai berangka bagi setiap baris meja.

Kami melakukan segala-galanya tindakan yang perlu. Selepas operasi untuk mengosongkan jadual rekod pendua selesai, medan ini juga boleh dipadamkan.

Mengeluarkan ulangan

Sumber pangkalan data

Keperluan untuk menyahduplikasi data adalah perkara biasa, terutamanya apabila menangani isu kualiti data dalam persekitaran di mana pertindihan telah timbul kerana kekurangan kekangan untuk memastikan keunikan data. Untuk menunjukkan, mari kita gunakan kod berikut untuk menyediakan contoh data dengan pesanan pendua dalam jadual bernama MyOrders:

JIKA OBJECT_ID("Sales.MyOrders") BUKAN NULL DROP TABLE Jualan.MyOrders; GO SELECT * INTO Sales.MyOrders FROM Sales.Orders UNION ALL SELECT * FROM Sales.Orders UNION ALL SELECT * FROM Sales.Orders;

Bayangkan anda perlu menghapuskan data pendua, hanya meninggalkan satu contoh setiap satu dengan nilai tertib yang unik. Nombor pendua ditanda menggunakan fungsi ROW_NUMBER, membahagikan dengan nilai yang kononnya unik (orderid dalam kes kami) dan menggunakan susunan rawak jika anda tidak peduli baris mana yang hendak disimpan dan mana yang hendak dialih keluar. Berikut ialah kod yang mana fungsi ROW_NUMBER menandakan pendua:

PILIH orderid, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n DARIPADA Sales.MyOrders;

Kemudian anda perlu mempertimbangkan varian yang berbeza bergantung pada bilangan baris yang perlu dipadamkan, peratusan saiz jadual, bilangan itu, aktiviti persekitaran pengeluaran dan keadaan lain. Pada sebilangan kecil Untuk baris yang dipadamkan, biasanya memadai untuk menggunakan operasi pemadaman pengelogan penuh, yang memadamkan semua kejadian dengan nombor baris yang lebih besar daripada satu:

Tetapi jika bilangan baris yang dipadamkan adalah besar - terutamanya apabila ia membentuk sebahagian besar baris jadual - memadam dengan rakaman penuh operasi log akan menjadi terlalu perlahan. Dalam kes ini, anda mungkin ingin mempertimbangkan untuk menggunakan operasi pengelogan pukal seperti SELECT INTO untuk menyalin baris unik (bernombor 1) ke jadual lain. Selepas ini, jadual asal dipadamkan, kemudian meja baru nama jadual jauh diberikan, kekangan, indeks dan pencetus dicipta semula. Berikut ialah kod untuk penyelesaian yang lengkap:

DENGAN C AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders) PILIH orderid, custid, empid, orderdate, requireddate, shippeddate, shipperid, freight, shipname, shipaddress, shipcity, shipregion, shippostalcode, shipcountry INTO Sales.OrdersTmp FROM C WHERE n = 1; DROP TABLE Jualan.Pesanan Saya; EXEC sp_rename "Sales.OrdersTmp", "MyOrders"; -- mencipta semula indeks, kekangan dan pencetus

Untuk memudahkan, saya tidak menambah sebarang kawalan transaksi di sini, tetapi anda mesti sentiasa ingat bahawa berbilang pengguna boleh bekerja dengan data pada masa yang sama. Apabila melaksanakan kaedah ini dalam persekitaran pengeluaran, anda mesti mengikut urutan berikut:

Buka transaksi.

Dapatkan kunci meja.

Laksanakan SELECT kenyataan KE DALAM.

Padam dan namakan semula objek.

Cipta semula indeks, kekangan dan pencetus.

Lakukan transaksi.

Terdapat satu lagi pilihan - untuk menapis hanya baris unik atau hanya bukan unik. ROW_NUMBER dan RANK dikira berdasarkan orderid, seperti ini:

PILIH orderid, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum, RANK() OVER(ORDER BY orderid) AS rnk FROM Sales.MyOrders;

Perhatikan bahawa dalam keputusan, hanya satu baris untuk setiap nilai unik dalam orderid sepadan dengan nombor baris dan pangkat baris. Sebagai contoh, jika anda perlu mengalih keluar sebahagian kecil data, anda boleh merangkum pertanyaan sebelumnya dalam definisi CTE, dan dalam pertanyaan luar, mengeluarkan arahan untuk memadamkan baris yang mempunyai nombor yang berbeza baris dan pangkat.