Sådan bruges fuzzy string matching med Postgresql

Det er en kendsgerning - folk laver stavefejl eller bruger simpelthen alternative stavemåder hyppigt.

Uanset årsagen, fra et praktisk synspunkt, kan forskellige varianter af lignende strenge udgøre udfordringer for softwareudviklere. Din applikation skal være i stand til at håndtere disse uundgåelige edge-cases.

Tag for eksempel navne. Jeg går forbi Peter nogle steder, Pete andre steder. Blandt andre varianter kan mit navn repræsenteres af:

  • "Pete Gleeson"
  • "Peter J Gleeson"
  • "Hr. P Gleeson"
  • "Gleeson, Peter"

Og det er ikke at nævne alternative stavemåder for mit efternavn, såsom "Gleason". Alle disse forskellige variationer for kun en streng - at matche dem mod hinanden programmatisk virker måske ikke indlysende.

Heldigvis er der løsninger derude.

Det generiske navn for disse løsninger er 'fuzzy string matching'. Den 'fuzzy' refererer til det faktum, at løsningen ikke ser efter et perfekt match efter position, når man sammenligner to strenge. I stedet tillader de en vis grad af uoverensstemmelse (eller 'uklarhed').

Der findes løsninger på mange forskellige programmeringssprog. I dag undersøger vi nogle muligheder, der er tilgængelige i Postgresql (eller 'Postgres') - en udbredt open source SQL-dialekt med nogle meget nyttige tilføjelsesfunktioner.

Sætte op

Først skal du sørge for at have Postgres installeret på din maskine.

Opret derefter en ny database i sin egen mappe (du kan kalde det hvad du vil, her kaldte jeg det 'fuzz-demo'). Fra kommandolinjen:

$ mkdir fuzz-demo && cd fuzz-demo $ initdb . $ pg_ctl -D . start $ createdb fuzz-demo

Til denne demo brugte jeg et bord med detaljer om kunstnere på Museum of Modern Art. Du kan downloade filen artists.csv fra Kaggle.

Dernæst kan du starte psql (en terminalbaseret frontend til Postgresql):

$ psql fuzz-demo

Opret nu en tabel kaldet artists:

CREATE TABLE artists ( artist_id INT, name VARCHAR, nationality VARCHAR, gender VARCHAR, birth_year INT, death_year INT);

Endelig kan du bruge Postgresqls COPY-funktion til at kopiere indholdet af artists.csv til tabellen:

COPY artists FROM '~/Downloads/artists.csv' DELIMTER ',' CSV HEADER;

Hvis alt har fungeret hidtil, skal du være i stand til at starte forespørgsel på kunstnerens tabel.

SELECT * FROM artists LIMIT 10;

Jokertegn filtre

Sig, at du husker fornavnet på en kunstner, der hedder Barbara, men ikke helt kan huske hendes andet navn. Det begyndte med 'Hep ...', men du er ikke sikker på, hvordan det sluttede.

Her kan du bruge et filter og SQL's wildcard-operatør %. Dette symbol står for et hvilket som helst antal uspecificerede tegn.

SELECT * FROM artists WHERE name LIKE 'Barbara%' AND name LIKE '%Hep%';

Den første del af filteret finder kunstnere, hvis navn begynder med 'Barbara' og slutter i enhver kombination af tegn.

Den anden del af filteret finder kunstnere, hvis navn kan begynde og slutte med en hvilken som helst kombination af tegn, men skal indeholde bogstaverne 'Hep' i den rækkefølge.

Men hvad nu hvis du er usikker på stavningen af ​​begge navne? Filtre og jokertegn får dig kun så langt.

Brug af trigrammer

Heldigvis har Postgres en nyttig udvidelse med det fængende navn pg_trgm. Du kan aktivere det fra psql ved hjælp af kommandoen nedenfor:

CREATE EXTENSION pg_trgm;

Denne udvidelse bringer med sig nogle nyttige funktioner til fuzzy string matching. Det underliggende princip er brugen af ​​trigrammer (som lyder som noget ud af Harry Potter).

Trigrammer dannes ved at bryde en streng i grupper på tre på hinanden følgende bogstaver. For eksempel vil strengen "hej" blive repræsenteret af følgende sæt trigrammer:

  • "h", "han", "hel", "ell", "llo", "lo"

Ved at sammenligne, hvor ens sæt af trigrammer er mellem to strenge, er det muligt at estimere, hvor ens de er på en skala mellem 0 og 1. Dette muliggør fuzzy matching ved at indstille en lighedstærskel, over hvilken strenge anses for at matche.

SELECT * FROM artists WHERE SIMILARITY(name,'Claud Monay') > 0.4 ;

Måske vil du se de fem bedste kampe?

SELECT * FROM artists ORDER BY SIMILARITY(name,'Lee Casner') DESC LIMIT 5;

Standard tærsklen er 0,3. Du kan bruge %operatøren i dette tilfælde som stenografi for fuzzy matchende navne mod et potentielt match:

SELECT * FROM artists WHERE name % 'Andrey Deran';

Måske har du kun en idé om en del af navnet. Den %Operatøren kan du sammenligne mod elementerne i et array, så kan du matche mod enhver del af navnet. Den næste forespørgsel bruger Postgres ' STRING_TO_ARRAYfunktion til at opdele kunstnernes fulde navne i arrays med separate navne.

SELECT * FROM artists WHERE 'Cadinsky' % ANY(STRING_TO_ARRAY(name,' '));

Fonetiske algoritmer

En anden tilgang til fuzzy string matching kommer fra en gruppe algoritmer kaldet fonetiske algoritmer.

Dette er algoritmer, der bruger sæt regler til at repræsentere en streng ved hjælp af en kort kode. Koden indeholder nøgleoplysningerne om, hvordan strengen skal lyde, hvis den læses højt. Ved at sammenligne disse forkortede koder er det muligt at fuzzy matchstrenge, der er stavet forskelligt, men som lyder ens.

Postgres leveres med en udvidelse, der lader dig gøre brug af nogle af disse algoritmer. Du kan aktivere det med følgende kommando:

CREATE EXTENSION fuzzystrmatch;

One example is an algorithm called Soundex. Its origins go back over 100 years - it was first patented in 1918 and was used in the 20th century for analysing US census data.

Soundex works by converting strings into four letter codes which describe how they sound. For example, the Soundex representations of 'flower' and 'flour' are both F460.

The query below finds the record which sounds like the name 'Damian Hurst'.

SELECT * FROM artists WHERE nationality IN ('American', 'British') AND SOUNDEX(name) = SOUNDEX('Damian Hurst');

Another algorithm is one called metaphone. This works on a similar basis to Soundex, in that it converts strings into a code representation using a set of rules.

The metaphone algorithm will return codes of different lengths (unlike Soundex, which always returns four characters). You can pass an argument to the METAPHONE function indicating the maximum length code you want it to return.

SELECT artist_id, name, METAPHONE(name,10) FROM artists WHERE nationality = 'American' LIMIT 5;

Because both metaphone and Soundex return strings as outputs, you can use them in other fuzzy string matching functions. This combined approach can yield powerful results. The example below finds the five closest matches for the name Si Tomlee.

SELECT * FROM artists WHERE nationality = 'American' ORDER BY SIMILARITY( METAPHONE(name,10), METAPHONE('Si Tomlee',10) ) DESC LIMIT 5;

Here, a trigram-only approach would not have helped much, as there is little overlap between 'Cy Twombly' and 'Si Tomlee'. In fact, these only have a SIMILARITY score of 0.05, even though they sound similar when read aloud.

Due to their historical origins, neither of these algorithms works well with names or words of non-English language origin. However, there are more internationally-focused versions.

One example is the double metaphone algorithm. This uses a more sophisticated set of rules for producing metaphones. It can provide alternative encodings for English and non-English origin strings.

As an example, see the query below. It compares the double metaphone outputs for different spellings of Spanish artist Joan Miró:

SELECT 'Joan Miró' AS name, DMETAPHONE('Joan Miró'), DMETAPHONE_ALT('Joan Miró') UNION SELECT 'Juan Mero' AS name, DMETAPHONE('Juan Mero'), DMETAPHONE_ALT('Juan Mero');

Going the distance

Finally, another approach to fuzzy string matching in Postgres is to calculate the 'distance' between strings. There are several ways to do this. Postgres provides functionality to calculate the Levenshtein distance.

At a high level, the Levenshtein distance between two strings is the minimum number of edits required to transform one string into the other. Edits are considered at the character level, and can include:

  • substitutions,
  • deletions, and
  • insertions

For example, the Levenshtein distance between the words 'bigger' and 'better' is 3, because you can transform 'bigger' into 'better' by substituting 'igg' for 'ett'.

Meanwhile, the Levenshtein distance between 'biggest' and 'best' is also 3, because you can transform 'biggest' into 'best' by deleting the letters 'igg'.

See below for a query which finds the artists with the smallest Levenshtein distances from the name 'Freda Kallo'.

SELECT *, LEVENSHTEIN(name, 'Freda Kallo') FROM artists ORDER BY LEVENSHTEIN(name, 'Freda Kallo') ASC LIMIT 5

Thanks for reading!

Hopefully this overview of fuzzy string matching in Postgresql has given you some new insights and ideas for your next project.

There are of course other methods for fuzzy string matching not covered here, and in other programming languages.

Hvis du f.eks. Bruger Python, skal du kigge på fuzzywuzzy-pakken. Eller hvis du foretrækker R, kan du bruge den indbyggede agrep()funktion eller prøve stringdist-pakken.