Jak często zdarzało Ci się, w wynikach zapytań SQL-owego dostać zduplikowane wiersze? 

Domyślam się, że skoro jesteś tutaj ze mną to pewnie więcej niż 1 raz. Zwykle zduplikowane wiersze nie są czymś pożądanym, ale czy tak jest zawsze? Jak sprawdzić czy w danych wynikowych znajdują się powielone wpisy i jak je ewentualnie usunąć? Odpowiedzi na te pytania znajdziesz w tym wpisie.

TL;DR

Duplikaty w danych nie są niczym nadzwyczajnym. W pierwszej kolejności odpowiedz sobie na pytanie czy się ich “spodziewasz”, czy może wynikają z oczywistego błędu w danych. Do ich usunięcia najczęściej pomoże Ci DISTINCT lub kombinacja ROW_NUMBER() OVER() + WHERE.

Czym jest duplikat?

Definicja być może nie książkowa, ale spróbujmy: “identyczny pod względem wartości w kolumnach wiersz w tabeli źródłowej lub w wynikowym zbiorze danych względem innego wiersza w tym samym zbiorze lub tabeli”.

Przykład:

W powyższym kontekście Product 1 jest duplikatem w zbiorze wynikowym – wszystkie kolumny mają te same wartości.

A co w takim przypadku:

Powyżej, widzimy sytuację, gdzie wiersze dla Product 1 różnią się jedynie identyfikatorem (autoincrement-em), czy to też jest duplikat? 

Według definicji powyżej nie, jednak śmiało moglibyśmy założyć, że został popełniony błąd przy modelu bazy danych. 

Tabel nie posiada żadnych ograniczeń (constraints), nie ma też innego pola (kolumny), które definiowałoby przyczynę wstawienia tego samego wiersza. 

Wbrew pozorom takie sytuacje nie są rzadkością, a my jako osoby pracujące z danymi musimy sobie z takimi sytuacjami radzić. 

Przykład w SQLFiddle: http://sqlfiddle.com/#!17/c4b89/2

Przyczyny powstawania duplikatów?

Jakie mogą być przyczyny powstawania duplikatów:

• Błędny model bazy danych – brak ograniczeń (constraints) co do wartości które możemy dodać (np. Primary Keys / Unique Keys)
• Brak propagacji zmian do obiektów zależnych – jeżeli tabela w systemie źródłowym została rozszerzona o np. flagę aktwyne_od (do śledzenia historii), a inne systemy korzystające z danej tabeli nie zostały zaktualizowane o tą zmianę, bardzo prawdopodobne, że pojawią się duplikaty;
• Niewłaściwe zbudowanie warunków łączenia tabel;
• Błędy ludzkie – np. ręczne wstawianie rekordów (szczególnie do tabeli bez ograniczeń – patrz błędny model bazy danych)
• Brak zrozumienia źródła danych – np. pobieranie z tabeli źródłowej tylko części kolumn, w których nie będzie jasno widzieć różnicy pomiędzy rekordami (np. pominięcie timestamp-u, flagi active etc.)

Jak znaleźć duplikaty?

Żeby odpowiedzieć, na to pytanie najpierw dobrze jest wiedzieć, czy w zbiorze wynikowym lub w tabeli, możesz określić kolumnę lub grupę kolumn, które mają być unikalne? 

Tak, mam taką kolumnę (grupę kolumn). Załóżmy, że kolumna nazywa się product_name.

SELECT count(f.product_name) 
  FROM products f;

Powyższe zwróci całkowitą ilość rekordów z duplikatami.

SELECT count(distinct f.product_name)
  FROM products f;

Powyższe zwróci całkowitą ilość rekordów bez duplikatów.

Nie, wszystkie kolumny tworzą unikat. W takiej sytuacji, możemy albo stworzyć unikatowy ciąg znaków dla wszystkich kolumn (hash) lub sprawdzać po wszystkich kolumnach.

Bez Hash-a

SELECT count(f.*)
  FROM products f;

Powyższe zwróci całkowitą ilość rekordów z duplikatami.

SELECT count(distinct f.*)
  FROM products f;

Powyższe zwróci całkowitą ilość rekordów bez duplikatów.

Z Hash-em

     SELECT  md5(f.*::text), count(*)
       FROM products f
   GROUP BY md5(f.*::text);

md5 – to funkcja generująca ciąg znaków na bazie kolumn lub kolumn wejściowych

W powyższym przypadku dostaniemy, unikatowe ciągi znaków, z całkowitą ilością wystąpień.

“No fajne, ale oprócz tego, że są chcę wiedzieć, które to wiersze”

COUNT + GROUP BY + HAVING (dla product_name)

 SELECT product_name, count(*)
   FROM products
GROUP BY product_name
  HAVING count(*) >  1;

group by – stworzy grupy wierszy dzieląc po product_name

having count(*) > 1 – odfiltruje z wyniku te produkty, które występują tylko raz

W wyniku dostaniesz listę duplikatów.

SELECT product_name
 FROM (
   SELECT product_name, count(*) over(partition by product_name) rc
     FROM products
 ) sq
WHERE rc > 1;

count(*) over(partition by product_name) – dla każdego wiersza zlicz ilość wystąpień wierszy grupując je po product_name

where rc > 1 – pokaż te wiersze gdzie ilość wystąpień jest większa od 1 (duplikaty)

ROW_NUMBER + WHERE (subselect) (dla product_name)

SELECT product_name
 FROM (
   SELECT product_name, row_number() over(partition by product_name) rc
     FROM products
 ) sq
WHERE rc=2;

row_number() over(partition by product_name) – ponumeruj każdy wiersz względem grupy stworzonej na podstawie kolumny product_name

WHERE rc=2 – wyświetl tylko te wiersze, gdzie numer wiersza jest równy 2 (wskazuje to duplikaty, nawet jeżeli duplikatów jest więcej niż 2 dla 1 product_name, chcemy widzieć tylko 1 wiersz w celu późniejszego usunięcia)

Jak usunąć duplikaty?

To zależy od tego czy chcemy usunąć je całkowicie z tabeli źródłowej czy może jedynie ze zbioru wynikowego.

W przypadku usunięcia ze zbioru wynikowego, możesz użyć jednego z przykładów powyżej, np. row_number.

SELECT product_name
 FROM (
   SELECT product_name, row_number() over(partition by product_name) rc
     FROM products
 ) sq
WHERE rc=1;

lub skorzystać z opcją którą SQL daje prosto z pudełka – DISTINCT.

DISTINCT usuwa duplikaty ze zbioru wynikowego sprawdzając wszystkie kolumny wiersza.

SELECT DISTINCT product_category,product_name 
  FROM products;

W przypadku całkowitego usunięcia wykorzystaj składnie DELETE + WHERE, gdzie w where dodasz jedno z  podzapytań jak wyżej.

Usunięte zostaną wszystkie wpisy których kluczem są zduplikowane. 

A co jeżeli chcemy usunąć same duplikaty (zostawić 1 rekord). Wtedy dobrze by było dodać kolumnę, np. row_number (jak w przykładzie powyżej) i usunąć na jej podstawie te rekordy gdzie row_number jest > 1. 

A gdyby tak nie usuwać?

Czasem duplikaty są czymś ”spodziewanym” (w mniejszym lub większym stopniu) i być może zamiast wyrzucać je z danych wynikowych, będziemy chcieli je pokazać, np. dla osób decyzyjnych, żeby wskazać problem z danymi. 

W takiej sytuacji warto wrócić do samego modelu danych i spróbować ustalić przyczynę powstawania duplikatów. 

Przykład:

W naszym przypadku może to być kolumna product_description. Czyli różny opis dla różnych produktów, dodany do źródłowej tabeli produktów, ale nie uwzględniony w zmianach aplikacyjnych. JOIN przed dodaniem tej kolumny, mógł zwracać dane poprawnie, po jej dodaniu dostaniemy duplikaty. 

W takiej sytuacji warto rozważyć grupowanie wierszy z różnym product_description do 1 wiersza, w którym kolejne opisy będą elementami listy / tekstu, oddzielonego separatorem.

     SELECT product_name,
            string_agg(COALESCE(product_description,''), ' / ')
       FROM products
   GROUP BY product_name;

string_agg – tworzy jednolity tekst z elementów, oddzielonych w naszym przypadku “/” 

coalesce – pozwala na zmianę wartości NULL w kolumnie na ciąg znaków zadeklarowany przez użytkownika (u nas pusty tekst); Stosowany tutaj aby w zbiorze wynikowym uniknąć wartości NULL przy łączeniu tekstu, gdzie jeden z elementów jest NULL-em właśnie.

Wszystkie przykłady dostępne w SQL Fiddle: http://sqlfiddle.com/#!17/d66ec6/22

Podsumowanie

Dobre zrozumienie systemów na których pracujemy jest kluczem do budowania, poprawnych zapytań. Mając wiedzę o modelu danych, sposobie przechowywania danych i tego jakie ograniczenia towarzyszą tabelom, możemy w prosty sposób wyeliminować duplikaty ze zbioru wynikowego.

Czasami jednak, musimy skorzystać z pomocy i warto mieć świadomość, że w Twój arsenał do walki z duplikaty nie musi kończyć się na DISTINCT-cie. 

— 

Ten artykuł w formie skróconej opublikowałem wcześniej w ramach newslettera, który prowadzę (ZrozumSQL, link: https://zrozumsql.pl). Jeżeli interesuje Cię tematyka danych, w każdą środę dostaniesz ode mnie coś ciekawego prosto na Twoją skrzynkę.