Normalizarea unui tabel cu slabă integritate

Am primit o masă cu aproximativ 18000 de rânduri. Fiecare înregistrare descrie locația unui singur client. Problema este că, atunci când persoana a creat masa, nu au adăugat un câmp pentru "Nume Companie", numai "Numele Locației" și o singură companie poate avea mai multe locații.

De exemplu, aici sunt câteva înregistrări care descriu același client:

Tabelul de locație

 ID  Location_Name     
 1   TownShop#1        
 2   Town Shop - Loc 2 
 3   The Town Shop     
 4   TTS - Someplace   
 5   Town Shop,the 3   
 6   Toen Shop4        

Obiectivul meu este de a arata ca:

Tabelul de locație

 ID  Company_ID   Location_Name     
 1   1            Town Shop#1       
 2   1            Town Shop - Loc 2 
 3   1            The Town Shop     
 4   1            TTS - Someplace   
 5   1            Town Shop,the 3   
 6   1            Toen Shop4        

Tabelul Companiei

 Company_ID  Company_Name  
 1           The Town Shop 

Nu există tabel "Companie", va trebui să generăm lista de nume de companie din cel mai descriptiv sau cel mai bun nume de locație care reprezintă locațiile multiple.

În prezent mă gândesc că trebuie să generez o listă cu numele de locație care sunt similare și apoi să treci prin listă manual.

Orice sugestii despre modul în care pot aborda acest lucru sunt apreciate.

@Neal, Vă mulțumim pentru afirmația dvs., dar, din nefericire, fiecare nume de locație este diferit, nu există nume de locație duplicat, ci doar similare. Deci, în rezultatele din afirmația dvs., "repcount" este 1 în fiecare rând.

@yukondude, Pasul 4 este inima întrebării mele.

0
fr hi bn

5 răspunsuri

Actualizați întrebarea, aveți la dispoziție o listă cu CompanyNames? Vă întreb pentru că ați putea să utilizați Levenshtein algo pentru a găsi o relație între lista dvs. de CompanyNames și LocationNames.


Actualizare

Nu există o listă cu nume de companii, va trebui să generez numele companiei din cel mai descriptiv sau cel mai bun nume de locație care reprezintă locațiile multiple.

Bine ... încercați acest lucru:

  1. Build a list of candidate CompanyNames by finding LocationNames made up of mostly or all alphabetic characters. You can use regular expressions for this. Store this list in a separate table.
  2. Sort that list alphabetically and (manually) determine which entries should be CompanyNames.
  3. Compare each CompanyName to each LocationName and come up with a match score (use Levenshtein or some other string matching algo). Store the result in a separate table.
  4. Set a threshold score such that any MatchScore < Threshold will not be considered a match for a given CompanyName.
  5. Manually vet through the LocationNames by CompanyName | LocationName | MatchScore, and figure out which ones actually match. Ordering by MatchScore should make the process less painful.

Scopul acțiunilor de mai sus este automatizarea pieselor și limitarea domeniului de aplicare a problemei. Este departe de a fi perfect, dar vă va salva, fără îndoială, dificultatea de a trece prin înregistrările de 18K cu mâna.

0
adăugat

În mod ideal, probabil că doriți o tabelă separată numită Companie și apoi o coloană de firmă în acest tabel "Locație", care este o cheie străină pentru cheia primară a tabelului Companiei, numită probabil id. Acest lucru ar evita un dublu echivalent de duplicare a textului în acest tabel (peste 18.000 de rânduri, o cheie străină întregă ar economisi destul spațiu într-o coloană varchar).

Dar totuși vă confruntați cu o metodă de încărcare a tabelului Companiei și apoi asocierea corectă cu rândurile din Locație. Nu există o soluție generală, dar ați putea face ceva în acest sens:

  1. Creați tabelul Companiei, cu o coloană id care crește automat (depinde de RDBMS).
  2. Găsiți toate denumirile unice ale companiilor și le inserați în companie.
  3. Adăugați o coloană, company_id, la locația care acceptă NULL-uri (deocamdată) și aceasta este o cheie străină a coloanei Company.id.
  4. Pentru fiecare rând din Locație, determinați compania corespunzătoare și UPDATE coloana companiei_id a acelui rând cu codul companiei respective. Este probabil cel mai dificil pas. Dacă datele dvs. sunt asemănătoare cu cele afișate în exemplul respectiv, probabil că va trebui să faceți mai multe runde la aceasta cu diferite abordări de potrivire a șirurilor.
  5. Odată ce toate rândurile din locație au o valoare de companie, atunci puteți să ALTERĂ tabelul Companiei pentru a adăuga o constrângere NOT NULL la coloana company_id (presupunând că fiecare locație trebuie să aibă o companie, ).

Dacă puteți face o copie a tabelului Locație, puteți construi treptat o serie de instrucțiuni SQL pentru a popula cheia externă a companiei_id. Dacă faceți o greșeală, puteți să începeți și să reluați scriptul până la punctul de eșec.

0
adăugat

Da, pasul 4 din postarea mea anterioară este o doozy.

Indiferent de ce, probabil că va trebui să faceți o parte din acest lucru manual, dar este posibil să puteți automatiza majoritatea acestuia. Pentru locațiile de exemplu pe care le-ați dat, o interogare precum cea următoare ar stabili valoarea corespunzătoare a companiei_id:

UPDATE  Location
SET     Company_ID = 1
WHERE   (LOWER(Location_Name) LIKE '%to_n shop%'
OR      LOWER(Location_Name) LIKE '%tts%')
AND     Company_ID IS NULL;

Cred că s-ar potrivi exemplele dvs. (am adăugat partea IS NULL pentru a nu suprascrie valorile definite anterior ale companiei), dar bineînțeles în 18.000 de rânduri va trebui să fii destul de inventiv pentru a face față diferitelor combinații.

Alt lucru care ar putea fi de ajutor ar fi să folosiți numele din Companie pentru a genera interogări precum cele de mai sus. Ai putea să faci ceva asemănător (în MySQL):

SELECT  CONCAT('UPDATE Location SET Company_ID = ',
        Company_ID, ' WHERE LOWER(Location_Name) LIKE ',
        LOWER(REPLACE(Company_Name), ' ', '%'), ' AND Company_ID IS NULL;')
FROM    Company;

Apoi rulați doar afirmațiile pe care le produce. Asta ar putea face o mulțime de muncă grunge pentru tine.

0
adăugat

Voi recomanda un algoritm complicat de potrivire a jetoanelor, dar este foarte dificil sa ai dreptate si daca esti datele nu au o multime de corelatii (tacturi, etc), atunci nu va da rezultate foarte bune.

Vă recomandăm să trimiteți un job la Amazon Mechanical Turk și să lăsați un om să-l sorteze.

0
adăugat

Trebuia să fac asta înainte. Singura modalitate reală de a face acest lucru este potrivirea manuală a diferitelor locații. Utilizați interfața consolei bazei de date și grupați declarațiile selectate. Mai întâi, adăugați câmpul "Numele Companiei". Apoi:

SELECT count(*) AS repcount, "Location Name" FROM mytable
 WHERE "Company Name" IS NULL
 GROUP BY "Location Name"
 ORDER BY repcount DESC
 LIMIT 5;

Aflați în ce companie aparține locația din partea de sus a listei și apoi actualizați câmpul cu numele companiei cu un UPDATE ... WHERE "Locație Nume" = "Locația" declarație.

P.S. - Ar trebui să distrugeți cu adevărat numele companiei și numele locației în tabele separate și să le referiți la cheile lor primare.

Actualizare: - Wow - nu sunt duplicate? Câte înregistrări aveți?

0
adăugat