Temat: [SQL Server] Usuwanie duplikujacych się danych klienta

Witam,

Mam tabelę w której występują duplikaty klientów, deduplikację chcę wykonać po PESEL, REGON i NIP lub po dowolnej kombinacji, ponieważ nie zawsze wszystkie pola są uzupełnione.
Chcę zrobić oceny dla rekordów, po 1 punkcie za każde niepuste pole.

,case when len(isnull(pesel,''))>10 then 1 else 0 end+

case when len(isnull(regon,''))>8 then 1 else 0 end+ 

case when len(isnull(nip,''))>9 then 1 else 0 end suma

Informacje o id rekordów które zostały usunięte chce przechowywać w osobnej tabeli.

Pesel | REGON | NIP
121 |___ | ___
121 | 25 | ___
____ | 25 | 12

W efekcie chciałbym otrzymać jeden rekord z danymi:
Pesel | REGON | NIP
121 | 25 | 12

Przy okazji czy istnieje prosty sposób na nietraktowanie Peseli
1111111111111
2222222222222 jako peseli
na teraz przychodzi mi pominięcie peseli gdzie pierwszy_znak=drugi
drug!=trzeci i tak do 11-tego i pomięcie takich przypadków.

Czy macie coś co naprowadziło by mnie na najskuteczniejsze rozwiązanie problemu.Ten post został edytowany przez Autora dnia 26.07.16 o godzinie 15:40

Temat: [SQL Server] Usuwanie duplikujacych się danych klienta

Usunięcie rekordów z duplikatami PESEL z tabeli TabName:

WITH tabCTE AS (

     SELECT *, ROW_NUMBER() OVER 

                  (PARTITION BY PESEL ORDER BY PESEL, REGON DESC, NIP DESC)  AS RowNumber 

FROM TabName)

DELETE FROM tabCTE WHERE RowNumber > 1

Analogicznie powtórzyć dla REGON i NIP. Porządkowanie DESC aby zachować na pierwszych pozycjach rekordy zawierające dane.

Powyższe usunie duplikaty ale jeżeli są rekordy które nie zawierają par PESEL, REGON, NIP to zamierzonego celu się nie osiągnie. Niestety ale przykładowe 3 rekordy dotyczące osoby o tym samym imieniu i nazwisku ale zawierające tylko po jednej danej PESEL, REGON, NIP nadal pozostaną 3-ma rekordami i aby się dowiedzieć czy to rzeczywiście jedna i ta sama osoba wypada posłużyć się dodatkowymi danymi (adres, imiona i nazwiska rodziców, .., itd. itp.).Ten post został edytowany przez Autora dnia 27.07.16 o godzinie 23:23

Temat: [SQL Server] Usuwanie duplikujacych się danych klienta

Michał K.:

...

Informacje o id rekordów które zostały usunięte chce przechowywać w osobnej tabeli.

Prościej i bezpieczniej będzie skopiować całą tabelę klientów zanim zaczniesz cokolwiek usuwać.
...

Przy okazji czy istnieje prosty sposób na nietraktowanie Peseli
1111111111111
2222222222222 jako peseli
na teraz przychodzi mi pominięcie peseli gdzie pierwszy_znak=drugi
drug!=trzeci i tak do 11-tego i pomięcie takich przypadków.

Nie wiem, czy dobrze zrozumiałem, ale jeśli chcesz wykluczyć pesel, w którym dwa kolejne znaki są identyczne, to jest to złe założenie.
Jeśli chcesz porównać całe 11 znaków to
wykluczenie : replicate ( left(pesel(1)),11) = pesel

Czy macie coś co naprowadziło by mnie na najskuteczniejsze rozwiązanie problemu.

Pierwsze 6 cyfr pesela musi sparsować się do daty YYMMDD

Temat: [SQL Server] Usuwanie duplikujacych się danych klienta

Generalnie deduplikacja klientów jest tematem, którego nie da się ogarnąć 20 liniami kodu.
Proponuję:
skopiuj tabelę na bok (select * into ...) i pracuj na kopii
wyczyść niepoprawne numery PESEL
dodaj kolumny:
- "złoty rekord" wskazujący ID klienta do którego chcesz przepisać brakujące dane z duplikatów
- [CntP] ilość identycznych peseli
- [CntR] ilość identycznych regonów
- [CntN] ilość identycznych nipów
Uzupełnij pola Cnt... (1 jeśli null)
Jeśli para PESEL-REGON (oba niepuste) występuje raz to przyjmij, że jest poprawna
to samo dla par PESEL-NIP, REGON-NIP
jeśli trzy pary są poprawne to zapisz najniższy ID klienta jako złoty rekord we wszystkich w których
PESEL lub REGON lub NIP są zgodne
przeanalizuj dane, dla których "złoty rekord" jest pusty i [CntP]+[CntR]+[CntN]>3

Temat: [SQL Server] Usuwanie duplikujacych się danych klienta

Dziękuję za wskazówki:)

Temat: [SQL Server] Usuwanie duplikujacych się danych klienta

Paweł B.:
przeanalizuj dane, dla których "złoty rekord" jest pusty i [CntP]+[CntR]+[CntN]>3

Jak? A co gdy [CntP]+[CntR]+[CntN]>2? Nie wydaje Ci się Pawle że to trochę przekombinowane?

1. Bez dwóch zdań na początek usunięcie bzdur.
2. Później update brakujących PESEL, REGON, NIP na podstawie wyszukania par i uzupełniania trzeciego pola wartością z innego rekordu.
3. Usunięcie duplikatów tak jak zaproponowałem wcześniej.

I to raczej wszystko co można zaproponować jako działanie "z automatu". Poza robieniem kopii, ale to elementarz elementarza tak samo jak i walidacja przed zapisem do bazy. Skoro dopuszcza się zapis bzdur to albo aplikacja do naprawy albo do naprawy procesy biznesowe. To pierwsze łatwiejsze. ;-) To drugie z pewnymi ludźmi niemożliwe. ;-(

Temat: [SQL Server] Usuwanie duplikujacych się danych klienta

Marek K.:

Paweł B.:
przeanalizuj dane, dla których "złoty rekord" jest pusty i [CntP]+[CntR]+[CntN]>3

Jak? A co gdy [CntP]+[CntR]+[CntN]>2? Nie wydaje Ci się Pawle że to trochę przekombinowane?

Suma tych pól nie może być mniejsza od 3 (każde z nich przybiera wartość z przedziału 1.. ilość klientów.
Jeśli jest równe 3 to klient nie ma duplikatów.
Jesli >3 to klient ma duplikaty przy czym:
jeśli złoty rekord jest niepusty to duplikatami są wszystkie z tym złotym ID
jesli jest pusty to znaczy, że któraś para jest niepoprawna np
Pesel | REGON | NIP
121 |___ | ___
121 | 25 | ___
____ | 25 | 12
____ | 25 | 13
nie rozwiązuje jednoznacznie NIPu
i to należy sprawdzić "białkowo" np na podstawie innych pól i poprawić (albo olać, to zależy od wymagań biznesowych)
Możesz tu uzupełnić pierwszy rekord o REGON 25, ale nie wiesz, czy w rekordach 3 i 4 błędny jest REGON czy NIP

1. Bez dwóch zdań na początek usunięcie bzdur.
2. Później update brakujących PESEL, REGON, NIP na podstawie wyszukania par i uzupełniania trzeciego pola wartością z innego rekordu.
3. Usunięcie duplikatów tak jak zaproponowałem wcześniej.

I to raczej wszystko co można zaproponować jako działanie "z automatu". Poza robieniem kopii, ale to elementarz elementarza tak samo jak i walidacja przed zapisem do bazy. Skoro dopuszcza się zapis bzdur to albo aplikacja do naprawy albo do naprawy procesy biznesowe. To pierwsze łatwiejsze. ;-) To drugie z pewnymi ludźmi niemożliwe. ;-(

Temat: [SQL Server] Usuwanie duplikujacych się danych klienta

Paweł B.:
np
Pesel | REGON | NIP
121 |___ | ___
121 | 25 | ___
____ | 25 | 12
____ | 25 | 13

OK, ale zauważ że zamienianie NULL na 1 oszukuje nas co do ilości identycznie wypełnionych pól. Nie uważasz że trochę dużo tutaj niepotrzebnej kombinacji?

Możesz tu uzupełnić pierwszy rekord o REGON 25, ale nie wiesz,
czy w rekordach 3 i 4 błędny jest REGON czy NIP

A muszę wiedzieć czy błędny jest REGON czy NIP? Przecież tego z automatu nie rozstrzygnie się . Proponuję te rekordy wykluczyć z analizy przez program i przenieść je do tabeli z danymi, które muszą zostać przeanalizowane "na piechotę" przez człowieka.

Natomiast nie widzę przeszkód aby automat zaktualizował brakującą wartość jeżeli inne rekordy zawierają tę samą wartość. W przykładzie uzupełnić REGON o brakujące 25.

Temat: [SQL Server] Usuwanie duplikujacych się danych klienta

Marek K.:

Paweł B.:
np
Pesel | REGON | NIP
121 |___ | ___
121 | 25 | ___
____ | 25 | 12
____ | 25 | 13

OK, ale zauważ że zamienianie NULL na 1 oszukuje nas co do ilości identycznie wypełnionych pól. Nie uważasz że trochę dużo tutaj niepotrzebnej kombinacji?

Przy założeniu, że null <> null każde wartość null jest unikalna, zatem 1 jest tu na miejscu.
ale "count (0) over (partition by [pole z nulem] )" pokaże nam ilość wierszy z polem null a chcemy 1.

Możesz tu uzupełnić pierwszy rekord o REGON 25, ale nie wiesz,
czy w rekordach 3 i 4 błędny jest REGON czy NIP

A muszę wiedzieć czy błędny jest REGON czy NIP? Przecież tego z automatu nie rozstrzygnie się . Proponuję te rekordy wykluczyć z analizy przez program i przenieść je do tabeli z danymi, które muszą zostać przeanalizowane "na piechotę" przez człowieka.

Z automatu raczej się nie da i traktujemy to jako błąd danych do naprawy ręcznej.
> Natomiast nie widzę przeszkód aby automat zaktualizował

brakującą wartość jeżeli inne rekordy zawierają tę samą wartość. W przykładzie uzupełnić REGON o brakujące 25.

Można, ale klient o regonie 25 nadal będzie miał duplikaty, o 1 mniej jeśli usuniemy wiersz 1 lub 2, ale będzie miał.

Temat: [SQL Server] Usuwanie duplikujacych się danych klienta

Paweł B.:

Marek K.:

Paweł B.:

OK, ale zauważ że zamienianie NULL na 1 oszukuje nas co do ilości identycznie wypełnionych pól. Nie uważasz że trochę dużo tutaj niepotrzebnej kombinacji?

Przy założeniu, że null <> null każde wartość null jest unikalna, zatem 1 jest tu na miejscu.
ale "count (0) over (partition by [pole z nulem] )" pokaże nam ilość wierszy z polem null a chcemy 1.

Ja jednak wolę ilość wierszy. ;-)

> Natomiast nie widzę przeszkód aby automat zaktualizował

brakującą wartość jeżeli inne rekordy zawierają tę samą wartość. W przykładzie uzupełnić REGON o brakujące 25.

Można, ale klient o regonie 25 nadal będzie miał duplikaty, o 1 mniej jeśli usuniemy wiersz 1 lub 2, ale będzie miał.

Ale ja proponuję przenieść

Pesel | REGON | NIP
____ | 25 | 12
____ | 25 | 13

do innej tabeli, bo tam jest informacja której prawdziwość musi rozstrzygnąć człowiek. Pozostanie nam:

Pesel | REGON | NIP
121 |___ | ___
121 | 25 | ___

Co proponuję sortować malejąco wg drugiej kolumny i usuwać wszystkie rekordy poza pierwszym. Pozostaje nam:

121 | 25 | ___

czyli osiągamy to co chcemy bez zliczania ilości rekordów.

Temat: [SQL Server] Usuwanie duplikujacych się danych klienta

Paweł B.:

Marek K.:

> Natomiast nie widzę przeszkód aby automat zaktualizował

brakującą wartość jeżeli inne rekordy zawierają tę samą wartość. W przykładzie uzupełnić REGON o brakujące 25.

Można, ale klient o regonie 25 nadal będzie miał duplikaty, o 1 mniej jeśli usuniemy wiersz 1 lub 2, ale będzie miał.

OK. Więc zmodyfikujmy użyty przykład tak aby wyłapać po co ta aktualizacja:

Pesel | REGON | NIP

121 | ___ | ___

____  | 25 | 12

 ___  | 25 | 13

____ |25 | 33

121|__|33

Załóżmy teraz, że jako pierwsze wyłapujemy rekordy z tym samym NIP:

____ | 25 | 33

121 |__ | 33

niezgodności nie ma, tak jak i powtórzeń więc można przejść do aktualizacji brakujących wartości:

121 | 25 | 33

121 | 25 | 33

Mamy:

Pesel | REGON | NIP

121 | ___ | ___

____ | 25 | 12

 ___ | 25 | 13

121 | 25 | 33

121 | 25 | 33

Teraz wyłapujemy te z tym samym REGON

Pesel | REGON | NIP

____ | 25 | 12

 ___ | 25 | 13

121| 25 | 33

121| 25 | 33

i wypada skierować do "ręcznej" analizy te z 12 i 13 jako NIP. Pozostają do dalszej analizy rekordy:

Pesel | REGON | NIP

121 |___ | ___

121 | 25 | 33

121 | 25 | 33

Wyłuskujemy teraz te z tym samym PESEL. Niezgodności brak więc można przejść do usuwania duplikatów. Czyli sortujemy malejąco po REGON i NIP i usuwamy wszystkie rekordy poza pierwszym. Pozostaje:

Pesel | REGON | NIP

121 | 25 | 33

;-)Ten post został edytowany przez Autora dnia 31.07.16 o godzinie 18:06