[SQL] where exists in zelfde tabel

[PdeBie]

Hoi allen,

Even een MS SQL vraagje. Ik heb een tabel met daarin zogeheten plaats verwijzingen. Deze tabel heeft twee kolommen, te weten search en target.

Doel van deze tabel is als volgt:
Orders worden ingelegd voor plaats A (search) en worden uitgevoerd door onze leden in plaats B (target). Dus bv. orders voor Zoelen, worden uitgevoerd door Tiel. Dit noemen wij de verwijstabel met verwijsplaatsen.

Tabel:

Search	Target
Zoelen	Tiel
Ooij	Beek
Oosterhout (NBR)	Breda

Deze tabel is al heel oud en de data erin is zwaar vervuild. Dit willen we opschonen, echter compleet legen en opnieuw vullen is geen optie i.v.m. de hoeveelheid werk die hierin zit.

Waar bestaat deze vervuiling uit?
In Nederland bestaan er vele plaatsen die in meerdere provincies hetzelfde heten, bijvoorbeeld Oosterhout. Deze plaats ligt in zowel Noord-Brabant (bij Breda) als in Gelderland (boven Nijmegen). Om dit onderscheid te maken hebben ze de tabel ooit gevuld met bijvoorbeeld target Oosterhout (GLD) en Oosterhout (NB).

Nu willen we deze tabel gaan koppelen aan een officiële postcode database en de plaatsen Oosterhout (GLD) en Oosterhout (NB) zijn geen geldige plaatsnamen. Dit is namelijk gewoon Oosterhout. Echter, om nu met de hand al deze records op te gaan zoeken is geen optie. Dus hier heb ik een query voor geschreven, om deze op te zoeken.

SQL:

-- selecteer alle records van place, waarbij de target niet bestaat in de postcode dataset.
select * from place
where not exists (select plaats from pcstraat where place.[target] = pcstraat.plaats) 

En uiteraard ook een soortgelijke query voor de search kolom. Ik moet echter kijken of er al vervangende verwijsplaatsen zijn opgegeven. Dus dat er in plaats van het record:

Search	Target
Oosterhout (NBR)	Breda

al een record

Search	Target
Oosterhout	Breda

...bestaat.

En daar kom ik dus niet uit, want ik heb natuurlijk geen koppeling tussen 'Oosterhout (NBR)' en 'Oosterhout'. Is wat ik wil überhaupt mogelijk? Of wordt dit toch echt handwerk?

Ik zat er anders aan te denken om de records, waarbij de target niet bestaat, stuk voor stuk na te lopen en de bijbehorende waarde van search te query'en en zo te kijken of er al een vervangend record is. Maar dit is dus echt handwerk en heel tijdrovend.

Wie o wie heeft er een idee?

[ Voor 0% gewijzigd door PdeBie op 02-12-2014 10:05 . Reden: query aangepast ]

[Verwijderd]

In hoeveel gevallen staat er wat achter (tussen haakjes)? Kan je deze er niet af trimmen in je query? Of iets een REGEXP like? Een gewone like of INSTR lijkt me riskant .

De distinct in je subquery is onzin trouwens. Je wilt weten of hij niet bestaat, een distinct verandert daar niks aan. Mocht hij twee keer bestaan, bestaat hij; dat is het enige wat je wilt weten.

[ Voor 10% gewijzigd door Verwijderd op 02-12-2014 10:04 ]

[PdeBie]

Het probleem is alleen dat het niet alleen foute namen door bijvoorbeeld haakjes zijn. Zo is de officiele naam van Baarle-Nassau bijvoorbeeld met streepje en in deze tabel staat hij zonder, dus Baarle Nassau. Ook dit soort foutjes moeten eruit gehaald worden.

Die distinct is in deze query is inderdaad niet correct. Had hem wel nodig in een van mijn andere, maar deze had ik even snel geknipt/geplakt van die query Even aangepast in de eerste post.

[ Voor 3% gewijzigd door PdeBie op 02-12-2014 10:05 ]

[Rushleader]

Met MySQL maak je het wel een stuk lastiger dan nodig. Zou je dit niet kunnen doen met PHP oid?
Is wat meer werk maar maakt het wel makkelijker denk ik

[PdeBie]

Ik zou niet weten hoe dat makkelijker zou kunnen zijn, want je zal toch eerst iets van een lijst met records moeten hebben en die maak je in SQL

ps. het is MSSQL en geen MySQL

[Verwijderd]

Ik zou met regexp of een translate een vergelijking maken aan beide kanten met alleen de letters uit de plaatsnaam, zonder spaties of andere tekens. Beide in hoofdletters. Alles tussen haakjes negeer je.

dat je zoiets krijgt:

WHERE 'BAARLENASSAU' = 'BAARLENASSAU'

qua performance niet fijn, maar voor een conversie moet het kunnen

[ Voor 5% gewijzigd door Verwijderd op 02-12-2014 10:15 ]

[The Eagle]

Je probleem zit hem in de datavervuiling en niet zozeer in je logica
Wat ik zou doen is in iedere geval eerst een scan maken van om hoeveel vervuiling het gaat. Dat kun je met twee UNION statements boven water halen.
Dan kijken wat de impact is op je bestaande data qua stamgegevens, en vervolgens besluiten of je je stamgegevens wilt updaten of dat je het anders aan wilt gaan vliegen.

Tuurlijk, je kunt het in de logica oplossen, maar dan ga je telkens tegen het probleem aan lopen als er een nieuwe klant is. Das niet handig

[HuHu]

Ik zou een student of uitzendkracht inhuren die dit handmatig gaat controleren.

Een technische, niet-praktische, oplossing kan een full-text search zijn waarbij je gaat zoeken naar matches die "ongeveer" lijken op wat je zoekt. Bijvoorbeeld met een full-text index en CONTAINS: MSDN: CONTAINS (Transact-SQL)

[Croga]

Als je doel is te koppelen met een postcode database dan is het wellicht toch handiger om gewoon helemaal van kaal af te beginnen.

De hamvraag is in dit geval: Hoeveel "Target" locaties heb je? Als dat aantal te overzien is, kun je deze conversie aangrijpen om je search/target tabel meteen structureel juist op te zetten. Je kunt per target locatie bepalen welke postcodes van toepassing zijn.

Met een dergelijke actie ben je meteen van álle vervuiling af (ook die vervuiling die je nu níet kunt definieren). Daarnaast voorkom je toekomstige vervuiling én weet je zeker dat er een keer serieus nagedacht is over de indeling.

[PdeBie]

The Eagle schreef op dinsdag 02 december 2014 @ 10:17:
Je probleem zit hem in de datavervuiling en niet zozeer in je logica
Wat ik zou doen is in iedere geval eerst een scan maken van om hoeveel vervuiling het gaat. Dat kun je met twee UNION statements boven water halen.
Dan kijken wat de impact is op je bestaande data qua stamgegevens, en vervolgens besluiten of je je stamgegevens wilt updaten of dat je het anders aan wilt gaan vliegen.

Ik heb al overzichten van de vervuiling, dus deze laat ik sowieso zien aan de manager. Dan mag hij beslissen hoe we het gaan aanvliegen. Ik wilde echter alvast kijken welk deel van de vervuiling al opgelost is door een correct record in deze tabel. Stel dat 70% al is opgelost, dan geeft dat een heel ander beeld dan wanneer je alle records nog handmatig na moet lopen. Alleen die controle wilde ik dus met een query doen, maar is lastiger dan ik in eerste instantie dacht.

Croga schreef op dinsdag 02 december 2014 @ 10:22:
Als je doel is te koppelen met een postcode database dan is het wellicht toch handiger om gewoon helemaal van kaal af te beginnen.

De hamvraag is in dit geval: Hoeveel "Target" locaties heb je? Als dat aantal te overzien is, kun je deze conversie aangrijpen om je search/target tabel meteen structureel juist op te zetten. Je kunt per target locatie bepalen welke postcodes van toepassing zijn.

Met een dergelijke actie ben je meteen van álle vervuiling af (ook die vervuiling die je nu níet kunt definieren). Daarnaast voorkom je toekomstige vervuiling én weet je zeker dat er een keer serieus nagedacht is over de indeling.

Dit heb ik ook voorgesteld, maar is voor nu geen oplossing, omdat we hier momenteel niet de capaciteit voor hebben. We willen eerst die target tabel van vervuiling ontdoen. Helemaal opnieuw vullen is geen optie, want dan praat je over duizenden records die je moet vullen. Deze collectie is in de loop der jaren gevuld, maar nooit goed bijgehouden/opgeschoond.

Uiteindelijk komt er een postcode in deze verwijstabel bij, zodat je kan verwijzen op postcode i.p.v. op plaats. Plaatsnamen veranderen nog wel eens namelijk (gemeentes die samenvoegen bijvoorbeeld), postcodes niet.

[ Voor 4% gewijzigd door PdeBie op 02-12-2014 10:33 ]

[Russel88]

Ik zou een tabel aanmaken en daarin de records bijhouden die niet kloppen.

zoiets bv:
Create table incorrectplace (search, target, occurance, missingSearch, missingtarget)

En dan je tabel vullen:

SQL:

Insert into incorrectplace(search, target, occurance, missingSearch)
    Select Search, Target,Count(*) , 1
    from place
    Left outer Join postcodeTabel where plaats = Search
    Where postcodeTabel.id is null
    Group by Search, Target

Insert into incorrectplace(search, target, occurance, missingtarget)
    Select Search, Target,Count(*) , 1
    from place
    Left outer Join postcodeTabel where plaats = target
    Where postcodeTabel.id is null
    Group by Search, Target

En van daaruit kun je vrij makkelijk de fouten corrigeren.
Je moet wel elk record afzonderlijk benaderen, maar daar zal ook wel een patroon in zitten die je met een script automatisch kan corrigeren.

[PdeBie]

Ik zal eens kijken wat ik daarmee kan. Heb nu een aantal lijstjes in Excel staan, maar echt heel overzichtelijk wordt het er niet van. Het liefst in 1 overzicht en dat zou met deze tabel goed kunnen.

[Janoz]

Volgens mij moet je je gewoon niet blind gaan staren op een volledig geautomatiseerde oplossing. Je ontkomt er niet aan dat je handmatige acties uit moet gaan halen. Maar dan nog denk ik dat dit niet meer dan een mandag gaat kosten.

Maak gewoon een nieuwe tabel met search en searchPostcode en target en targetPostcode. Vul deze met je vervuilde tabel en ga vervolgens scriptjes schrijven die de Postcode varianten vult. De eerste vulling is redelijk triviaal. Vervolgens hou je steeds minder, maar wel lastigere gevallen over totdat elk record van de oude tabel een bijbehorende postcode heeft. Vervolgens zul je nog even moeten controleren of er geen dubbelen in zitten (en als er dubbelen inzitten, of de search wel naar dezelfde target wijst) en je bent klaar..

[The Eagle]

Mja, alleen een tabel met postcodes en plaatsname update is niet zo lastig. De vraag is: in hoeveel van de gevallen zit er een (volgens de postcodetabel als nieuwe basis) verkeerd gespelde plaatsnaam in de stamgegevens van het betreffende systeem?

[PdeBie]

@Janoz: ik denk inderdaad dat ik het in die richting op ga pakken. Heb het ook al aangekaart dat ik hier voor wil gaan zitten. Even mezelf opsluiten, zodat ik me erop kan concentreren en gaan met die banaan.

@The Eagle: jammergenoeg veel te veel. Er is in de loop der jaren niet gekeken naar de officiele plaatsnaam. Als de order er maar naar toe kon, dat was het doel. Als de database is opgeschoond komt er sowieso een verwijzing (foreign key) naar de postcode data, zodat je geen foute namen meer in kan vullen.

[The Eagle]

Als jij de postcodetabel toch hebt, dan moet je met een paar statements wel kunnen zien waar de fouten zitten, en ze ook op kunnen lossen. Statements door je DTAP straat heen gooien en gaan. Komt vast goed

En vraag even aan je baas of je oude klanten ook mee moet nemen of niet. Kan een slok op een borrel schelen

Zat trouwens ook nog omgekeerd te denken: wat als je nou een verzorgingsgebied adhv een postcode definieert ipv op plaats? Dan kun je gewoon met ranges werken ipv losse waarden. Zoekt een stuk sneller

[PdeBie]

Dat laatste wordt ook het geval, maar is voor de snelle oplossing geen optie, omdat dat ook een flinke aanpassing in onze applicatiesoftware met zich mee brengt. En we willen eerst de tabel opschonen, zodat we eerst kunnen identificeren waar o.a. probleemgebieden liggen, maar dit allemaal even terzijde.

Maandag sta ik ingepland om de data flink op de schop te gooien, dus ik ga alle genoemde opties even onder de loep nemen. Bedankt voor het meedenken allemaal!

[Soulfix]

Misschien kan je iets doen met de minimum edit distance (zie http://en.wikipedia.org/wiki/Levenshtein_distance)? Als je een lijst hebt van correcte plaatsnamen kun je voor elk element uit je database kijken wat de minimum edit distance is voor elke correcte plaatsnaam, de correcte plaatsnaam met de laagste edit distance is (misschien?) de juiste. Bijvoorbeeld: Utregt vs Utrecht heeft minimum edit distance 2, aangezien er 2 operaties nodig zijn (een replace en insert) om het ene woord in het andere te veranderen.

[MSalters]

Het moet handmatig, omdat je niet kunt automatiseren of twee verwijzigen duplicaten zijn. Is Geldrop-Mierlo een duplicaat van Geldrop? Is Hoge Vuursche een duplicaat van Lage Vuursche?

[The Eagle]

Als het Q&D moet zou je in die verwijstabel natuurlijk ook gewoon een extra kolom op kunnen nemen met plaatsnaam volgens PC tabel. Kan denk ik een hoop issues in 1x oplossen. Kun je nu iig verder, en bij een volgende iteratie pak je de rest aan

[HollowGamer]

Kan je niet het oude systemen beter houden en beginnen met een nieuwe?
Schrijf/export.. een log-bestand van wat allemaal gebruikt/gedaan (van acties) wordt bij huidig systeem > deze (handmatig) importeren in nieuw (of niet..).