與德語變音符號的三元組相似度 (pg_trgm)

我試圖弄清楚如何使用德語變音符號 (äöü) 改進 Postgres 10.6 pg_trgm 查詢。在德語中，“ö”可以寫成“oe”。但要注意：不是每個 ‘oe’ 都可以寫成 ‘ö’。

CREATE TABLE public.names
 (name text COLLATE pg_catalog."default");

CREATE INDEX names_idx
   ON public.names USING gin (name COLLATE pg_catalog."default" gin_trgm_ops);

SHOW LC_COLLATE; -- de_DE.UTF-8

當我使用該similarity() 函式查詢***‘Schoenstraße’***的相似性時。

SELECT
   name,
   similarity (name, 'Schoenstraße') AS similarity,
   show_trgm (name)
FROM
   names
WHERE
   name % 'Schoenstraße'
ORDER BY
   similarity DESC;

我得到以下結果：

Name            similarity  show_trgm

Schyrenstraße   0.588235    {0x9a07c3,0xde3801,""  s"","" sc"",chy,ens,hyr,nst,ren,sch,str,tra,0x76a40e,yre}
Schönstraße     0.5625      {0x9a07c3,0xde3801,0xf00320,0x095f29,""  s"","" sc"",0x6deea5,nst,sch,str,tra,0x76a40e}

db<>在這裡擺弄

我可以做些什麼來改進它，還是我需要替換數據庫中的所有變音符號？

首先：德國詩人仍然是詩人。‘詩人’ <> ‘詩人’; ‘oe’ <> ‘ö’。用 ‘oe’ 代替 ‘ö’ 的約定如今已基本不再使用。看：

• https://de.wikipedia.org/wiki/Umlaut#Darstellung_von_Umlauten

我曾多次遇到相關問題。附加模組**unaccent**有助於涵蓋雙方的拼寫變體和拼寫錯誤。每個數據庫安裝一次：

CREATE EXTENSION unaccent;

看：

• PostgreSQL 是否支持“不區分重音”排序規則？

棘手的部分是獲得索引支持。對於初學者來說，細節可能很難。

三元相似度

然後按照此處的說明創建包裝函式（再次使用相同的連結）：

• PostgreSQL 是否支持“不區分重音”排序規則？

用它創建一個新索引：

CREATE INDEX names_trgm_idx ON names USING gin (f_unaccent(name) gin_trgm_ops);

查詢展示：

SELECT name
    , similarity(f_unaccent(name), f_unaccent('Schoenstraße')) AS sim_unaccent
    , similarity(name, 'Schoenstraße') AS sim
FROM   names
WHERE  f_unaccent(name) % f_unaccent('Schoenstraße')
ORDER  BY f_unaccent(name) <-> f_unaccent('Schoenstraße')
       , name <-> 'Schoenstraße';  -- best match first

排序依據name <-> 'Schoenstraße' 還可以作為決勝局首先獲得最佳匹配（在使用索引進行廉價過濾之後）。

對於“最近鄰”類型的查詢，考慮使用 GiST 索引而不是 GIN LIMIT。看：

• 相似度函式的最佳索引

全文搜尋（FTS 或僅 TS）

考慮全文搜尋以匹配完整的單詞（在詞乾之後），或使用前綴匹配/片語搜尋……請參閱：

• 在 PostgreSQL 中使用 LIKE、SIMILAR TO 或正則表達式進行模式匹配

該unaccent模組提供了一個用於集成的字典TEXT SEARCH CONFIGURATION：

CREATE TEXT SEARCH CONFIGURATION de (COPY = german);
ALTER TEXT SEARCH CONFIGURATION de
ALTER MAPPING FOR hword, hword_part, word WITH unaccent, german_stem;

基於它的索引：

CREATE INDEX names_fts_idx ON names USING GIN (to_tsvector('de', name));

或者，由於您正在處理names，您可能會基於simple文本搜尋配置而不是german. 沒有停用詞，沒有詞幹。名稱不一定遵循特定於語言的規則。

查詢展示：

SELECT name
    , ts_lexize('unaccent', name)
    , to_tsvector('de', name)
    , to_tsquery('de', 'Schoenstraße')
    , to_tsvector('de', name) @@ to_tsquery('de', 'Schoenstraße') AS match
    , similarity(name, 'Schoenstraße')
FROM   names
WHERE  to_tsvector('de', name) @@ to_tsquery('de', 'Schoenstraße')
ORDER  BY name <-> 'Schoenstraße';  -- exact matches first

同樣，排序依據name <-> 'Schoenstraße'作為決勝局，可能是其他排序標準的補充。

但是請注意，使用 FTS 查找“Schoenstraße”不會找到“Schönstraße”，因為unaccent()不會更改“oe”，並將“ö”映射到“o”。(‘oe’ <> ‘ö’)。

有關的：

• 在欄位上創建不區分大小寫和重音/變音符號的搜尋
• 在列中查找最常用的非停用詞

db<>fiddle here - 尋找“Schoenstraße”

db<>fiddle here - 尋找“Schönstraße”

引用自：https://dba.stackexchange.com/questions/271412