Loại bỏ các bản sao có giá trị sql lớn. Loại bỏ sự lặp lại trong T-SQL

Loại bỏ sự lặp lại

Nguồn cơ sở dữ liệu

Nhu cầu loại bỏ trùng lặp dữ liệu là phổ biến, đặc biệt là khi giải quyết các vấn đề về chất lượng dữ liệu trong các môi trường phát sinh sự trùng lặp do thiếu các ràng buộc để đảm bảo tính duy nhất của dữ liệu. Để minh họa, hãy sử dụng đoạn mã sau để chuẩn bị một ví dụ về dữ liệu có các đơn hàng trùng lặp trong bảng có tên MyOrders:

NẾU OBJECT_ID("Sales.MyOrders") KHÔNG PHẢI LÀ BẢNG DROP NULL Sales.MyOrders; CHỌN * VÀO Sales.MyOrders TỪ Sales.Orders UNION ALL CHỌN * TỪ Sales.Orders UNION ALL CHỌN * TỪ Sales.Orders;

Hãy tưởng tượng rằng bạn cần loại bỏ dữ liệu trùng lặp, chỉ để lại một phiên bản của mỗi dữ liệu có giá trị orderid duy nhất. Các số trùng lặp được đánh dấu bằng hàm ROW_NUMBER, phân vùng theo một giá trị được cho là duy nhất (orderid trong trường hợp của chúng tôi) và sử dụng thứ tự ngẫu nhiên nếu bạn không quan tâm nên giữ hàng nào và xóa hàng nào. Đây là đoạn mã trong đó hàm ROW_NUMBER đánh dấu các bản sao:

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

Sau đó bạn cần xem xét các biến thể khác nhau tùy thuộc vào số lượng hàng cần xóa, tỷ lệ phần trăm kích thước bảng, con số đó là gì, hoạt động của môi trường sản xuất và các trường hợp khác. Khi số lượng hàng bị xóa ít, thường chỉ cần sử dụng thao tác xóa được ghi nhật ký đầy đủ để xóa tất cả các phiên bản có số hàng lớn hơn một:

Nhưng nếu số lượng hàng bị xóa lớn - đặc biệt khi nó chiếm tỷ lệ lớn trong số các hàng của bảng - hãy xóa bằng ghi âm đầy đủ hoạt động đăng nhập sẽ quá chậm. Trong trường hợp này, bạn có thể cân nhắc sử dụng thao tác ghi nhật ký hàng loạt chẳng hạn như CHỌN VÀO để sao chép các hàng duy nhất (được đánh số 1) sang một bảng khác. Sau đó, bảng gốc sẽ bị xóa, sau đó bảng mới tên của bảng từ xa được chỉ định, các ràng buộc, chỉ mục và trình kích hoạt được tạo lại. Đây là mã cho giải pháp hoàn chỉnh:

VỚI C AS (CHỌN *, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders) CHỌN orderid, custid, empid, orderdate, requiredate, vận chuyển, vận chuyển, tên vận chuyển, địa chỉ vận chuyển, thành phố vận chuyển, khu vực vận chuyển, mã bưu điện, quốc gia vận chuyển VÀO Sales.OrdersTmp FROM C WHERE n = 1; BẢNG THẢ Sales.MyOrders; EXEC sp_rename "Sales.OrdersTmp", "MyOrders"; -- tạo lại chỉ mục, ràng buộc và kích hoạt

Để đơn giản, tôi chưa thêm bất kỳ kiểm soát giao dịch nào ở đây, nhưng bạn phải luôn nhớ rằng nhiều người dùng có thể làm việc với dữ liệu cùng một lúc. Khi triển khai phương pháp này trong môi trường sản xuất, bạn phải tuân theo trình tự sau:

Mở giao dịch.

Có được một khóa bảng.

Thực hiện câu lệnh CHỌN VÀO.

Xóa và đổi tên các đối tượng.

Tạo lại các chỉ mục, ràng buộc và kích hoạt.

Cam kết giao dịch.

Có một tùy chọn khác - chỉ lọc các hàng duy nhất hoặc chỉ các hàng không duy nhất. Cả ROW_NUMBER và RANK đều được tính toán dựa trên orderid, đại loại như thế này:

CHỌN đơn hàng, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum, RANK() OVER(ORDER BY orderid) AS rnk FROM Sales.MyOrders;

Lưu ý rằng trong kết quả, chỉ có một hàng cho mỗi giá trị duy nhất trong orderid khớp với số hàng và thứ hạng hàng. Ví dụ, nếu bạn cần loại bỏ Một phần nhỏ dữ liệu, bạn có thể gói gọn truy vấn trước đó trong định nghĩa CTE và trong truy vấn bên ngoài, đưa ra hướng dẫn xóa các hàng có số khác nhau dòng và thứ hạng.

(25-07-2009)

Trong bài viết trước, chúng ta đã xem xét cách giải quyết vấn đề trùng lặp do thiếu khóa chính. Bây giờ chúng ta hãy xem xét một trường hợp khó khăn hơn, khi chìa khóa dường như có ở đó, nhưng nó là tổng hợp, nếu được thiết kế không chính xác, cũng có thể dẫn đến sự trùng lặp theo quan điểm. lĩnh vực chủ đề.

Thật kỳ lạ, nhưng khi tôi nói chuyện tại các bài giảng về những thiếu sót của khóa tổng hợp, tôi vẫn liên tục nhận thấy rằng sinh viên luôn sử dụng chúng trong các dự án cơ sở dữ liệu đầu tiên của họ. Rõ ràng, một người có nhu cầu di truyền để đánh số lại mọi thứ và chỉ có nhà trị liệu tâm lý mới có thể giúp đỡ ở đây. :-)

Vì vậy, giả sử chúng ta có một bảng có id khóa chính và tên cột, theo các hạn chế về miền, phải chứa các giá trị duy nhất. Tuy nhiên, nếu bạn xác định cấu trúc bảng như sau

TẠO BẢNG T_pk (id INT IDENTITY PRIMARY KEY, tên VARCHAR (50 ));

thì không có gì ngăn cản sự xuất hiện của các bản sao. Nên sử dụng cấu trúc bảng sau:

TẠO BẢNG T_pk (id INT IDENTITY PRIMARY KEY, name VARCHAR (50) UNIQUE);

Mọi người đều biết điều đúng đắn cần làm, nhưng bạn thường phải đối mặt với cấu trúc và dữ liệu “cũ” vi phạm các ràng buộc về miền. Đây là một ví dụ:

tên id 1 John 2 Smith 3 John 4 Smith 5 Smith 6 Tom

Bạn có thể hỏi: "Vấn đề này khác với vấn đề trước như thế nào? Rốt cuộc, có một giải pháp thậm chí còn đơn giản hơn ở đây - chỉ cần xóa tất cả các hàng khỏi mỗi nhóm có cùng giá trị trong cột tên, chỉ để lại hàng có giá trị id tối thiểu/tối đa. Ví dụ: như thế này:"

XÓA TỪ T_pk WHERE id > (CHỌN MIN (id) TỪ T_pk X WHERE X.name = T_pk.name);

Đúng vậy, nhưng tôi vẫn chưa kể cho bạn mọi chuyện. :-) Hãy tưởng tượng rằng chúng ta có một bảng con T_details được liên kết với bảng T_pk bởi khóa ngoại:

TẠO BẢNG T_details (id_pk INT TÀI LIỆU THAM KHẢO KHÓA NGOẠI T_pk TRÊN XÓA CASCADE, màu VARCHAR (10), KHÓA CHÍNH (id_pk, màu);

Bảng này có thể chứa dữ liệu sau:

màu id_pk 1 xanh 1 đỏ 2 xanh 2 đỏ 3 đỏ 4 xanh 6 đỏ

Để rõ ràng hơn, hãy sử dụng truy vấn

CHỌN id, tên, màu TỪ T_pk THAM GIA T_details TRÊN id= id_pk;

để xem tên:

màu tên id 1 John xanh 1 John đỏ 2 Smith xanh 2 Smith đỏ 3 John đỏ 4 Smith xanh 6 Tom đỏ

Vì vậy, hóa ra dữ liệu thực sự liên quan đến một người đã bị phân bổ nhầm cho người khác. hồ sơ phụ huynh. Ngoài ra, còn có các bản sao trong bảng này:

1 John đỏ 3 John đỏ

Rõ ràng, những dữ liệu như vậy sẽ dẫn đến những phân tích và báo cáo sai sót. Hơn nữa, việc xóa tầng sẽ dẫn đến mất dữ liệu. Ví dụ: nếu chúng ta chỉ để lại các hàng có ID tối thiểu trong mỗi nhóm trong bảng T_pk thì chúng ta sẽ mất hàng

4 Smith màu xanh

trong bảng T_details. Vì vậy, chúng ta phải tính đến cả hai bảng khi loại bỏ các bảng trùng lặp.

Quy trình “làm sạch” dữ liệu có thể được thực hiện theo hai giai đoạn:

Cập nhật bảng T_details bằng cách gán dữ liệu liên quan đến một tên cho id có số lượng tối thiểu trong nhóm.
Xóa các bản sao khỏi bảng T_pk, chỉ để lại các hàng có id tối thiểu trong mỗi nhóm cùng một giá trịở cột tên.

Cập nhật bảng T_details

CHỌN id_pk, tên, màu, RANK () TRÊN (PHẦN THAM GIA THEO tên, màu ĐẶT HÀNG THEO tên, màu, id_pk) dup ,(CHỌN MIN (id) TỪ T_pk Ở ĐÂU T_pk.name = X.name) min_id TỪ T_pk X THAM GIA T_details TRÊN id=id_pk;

phát hiện sự hiện diện của các bản sao (giá trị trùng lặp > 1) và giá trị tối thiểu id trong một nhóm có tên giống hệt nhau (min_id). Đây là kết quả của việc chạy truy vấn này:

id_pk tên màu trùng lặp min_id 1 John xanh 1 1 1 John đỏ 1 1 3 John đỏ 2 1 4 Smith xanh 1 2 2 Smith xanh 1 2 2 Smith đỏ 1 2 6 Tom đỏ 1 6

Bây giờ chúng ta cần thay thế giá trị id_pk bằng giá trị min_pk cho tất cả các hàng ngoại trừ hàng thứ ba, bởi vì dòng này trùng lặp với dòng thứ hai, được biểu thị bằng giá trị dup=2. Một yêu cầu cập nhật có thể được viết như thế này:

CẬP NHẬT T_details SET id_pk=min_id TỪ T_details T_d THAM GIA (CHỌN id_pk, tên, màu, RANK () TRÊN (PHẦN THAM GIA THEO tên, màu ĐẶT HÀNG THEO tên, màu, id_pk) dup,(CHỌN MIN (id) TỪ T_pk Ở ĐÂU T_pk.name = X.name) min_id TỪ T_pk X THAM GIA T_details TRÊN id=id_pk) Y TRÊN Y.id_pk=T_d.id_pk WHERE dup =1 ;

Khi nhiệm vụ tối ưu hóa cơ sở dữ liệu phát sinh hoặc cấu trúc của nó thay đổi, đôi khi nảy sinh nhiệm vụ liên quan là tổ chức dữ liệu đã tích lũy. Thật tốt nếu bảng đã được đưa ra trong quá trình phát triển hình dạng bình thường và toàn bộ hệ thống được tổ chức theo cách không tích lũy thông tin trùng lặp không cần thiết. Nếu không phải như vậy, thì khi hoàn thiện một hệ thống như vậy, bạn muốn loại bỏ tất cả dữ liệu dư thừa và làm mọi thứ với chất lượng cao nhất.

Trong bài viết này, chúng ta sẽ xem xét nhiệm vụ loại bỏ các hàng trùng lặp trong bảng cơ sở dữ liệu. Tôi muốn chỉ ra ngay rằng Chúng ta đang nói về về sự cần thiết phải loại bỏ các dòng trùng lặp. Ví dụ: các bản ghi trong bảng đơn hàng với các trường “mã đơn hàng”, “mã sản phẩm”, “mã khách hàng”, “ngày đặt hàng” chỉ có thể khác nhau về mã đơn hàng vì một khách hàng có thể đặt cùng một sản phẩm nhiều lần trên cùng ngày một lần. Và dấu hiệu chính ở đây cho thấy mọi thứ đều đúng là sự hiện diện trường khóa.

Nếu chúng ta thấy một bảng chứa đầy các trường trùng lặp, không có nhu cầu rõ ràng cho từng mục nhập thì đây chính xác là điều cần phải sửa.

Một ví dụ về một bảng dư thừa rõ ràng:

Bây giờ hãy xem làm thế nào chúng ta có thể giải quyết vấn đề này. Một số phương pháp có thể được sử dụng ở đây.

1. Bạn có thể viết một hàm để so sánh và lặp qua tất cả dữ liệu. Phải mất một thời gian dài và không phải lúc nào bạn cũng muốn viết mã để sử dụng một lần.

2. Một giải pháp khác là tạo một truy vấn chọn nhóm dữ liệu để chỉ trả về các hàng duy nhất:

CHỌN country_id, city_name
TỪ bảng của tôi
NHÓM THEO country_id, city_name

Chúng tôi nhận được mẫu sau:

Sau đó chúng ta ghi tập dữ liệu kết quả vào một bảng khác.

3. B các quyết định trênáp dụng bổ sung Mã chương trình hoặc các bảng bổ sung. Tuy nhiên, sẽ thuận tiện hơn nếu làm mọi thứ chỉ bằng cách sử dụng truy vấn SQL không có bảng bổ sung. Và đây là một ví dụ về giải pháp như vậy:

XÓA a.* TỪ mytable a,
(LỰA CHỌN

TỪ bảng b của tôi

)c
Ở ĐÂU
a.country_id = c.country_id
VÀ a.city_name = c.city_name
VÀ a.id > c.mid

Sau khi thực hiện truy vấn như vậy, chỉ các bản ghi duy nhất sẽ còn lại trong bảng:

Bây giờ chúng ta hãy xem xét kỹ hơn cách thức hoạt động của tất cả. Khi yêu cầu xóa, bạn phải đặt điều kiện cho biết dữ liệu nào sẽ bị xóa và dữ liệu nào nên để lại. Chúng ta cần loại bỏ tất cả các mục không duy nhất. Những thứ kia. nếu có một số bản ghi giống hệt nhau (chúng giống nhau nếu chúng có cùng giá trị country_id và city_name), thì bạn cần lấy một trong các dòng, ghi nhớ mã của nó và xóa tất cả các bản ghi có cùng giá trị country_id và city_name nhưng khác mã (nhận dạng).

Chuỗi truy vấn SQL:

XÓA a.* TỪ mytable a,

chỉ ra rằng việc xóa sẽ được thực hiện từ bảng mytable.

Sau đó, truy vấn chọn sẽ tạo một bảng phụ trong đó chúng tôi nhóm các bản ghi sao cho tất cả các bản ghi là duy nhất:

(LỰA CHỌN
b.country_id, b.city_name, MIN(b.id) ở giữa
TỪ bảng b của tôi
NHÓM THEO b.country_id, b.city_name
)c

MIN(b.id) mid – tạo thành cột mid (viết tắt id tối thiểu), chứa giá trị id tối thiểu trong mỗi nhóm con.

Kết quả là một bảng chứa các bản ghi duy nhất và id hàng đầu tiên cho mỗi nhóm bản ghi trùng lặp.

Bây giờ chúng ta có hai bảng. Một cái chung chứa tất cả các bản ghi. Các dòng bổ sung sẽ bị xóa khỏi nó. Dòng thứ hai chứa thông tin về các hàng cần lưu.

Tất cả những gì còn lại là tạo một điều kiện cho biết: bạn cần xóa tất cả các dòng có trường country_id và city_name khớp nhau, nhưng id sẽ không khớp. TRONG trong trường hợp này giá trị id tối thiểu được chọn, do đó tất cả các bản ghi có id lớn hơn giá trị được chọn trong bảng tạm thời sẽ bị xóa.

Cũng cần lưu ý rằng thao tác được mô tả có thể được thực hiện nếu có trường khóa trong bảng. Nếu đột nhiên bạn gặp một bàn không có định danh duy nhất, sau đó chỉ cần thêm nó:

ALTER TABLE ` mytable` ADD `id` INT(11) NOT NULL AUTO_INCREMENT , THÊM KHÓA CHÍNH (`id`)

Sau khi thực hiện truy vấn như vậy, chúng ta nhận được một cột bổ sung chứa đầy các Giá trị kiểu số cho mỗi hàng của bảng.

Chúng tôi thực hiện tất cả các hành động cần thiết. Sau khi hoàn tất thao tác xóa bảng các bản ghi trùng lặp, trường này cũng có thể bị xóa.