vyhladavanie s/bez diakritiky v SQL

[martinnr]

Ako riešite v SQL databáze vyhľadávanie slov s diakritikou? Typicky je to napríklad vyhľadávanie v tabuľke firiem (názov).
našiel som nejaké deklarácie funkcií pre odstránenie diakritiky, ale to je možno dosť pomalé.
pomohlo by vytvoriť si nejakú podružnú tabuľku výskytu slov, alebo pridať pole s duplicitným názvom ale odstránenou diakritikou? (plný názov "Konštrukčné prvky, s. r. o." a k tomu podružné pole upraveným s textom "konstrukcne prvky sro" a potom so užívateľom zadaného reťazca odstrániť diakritiku a hladať cez LIKE v tom druhom poli? užívateľ môže zadať napr. "prvky kon s.r.o." a malo by to nájisť ten záznam).
záleží na počte záznamov v tabuľke? riešime to aj pre tabuľku s pár tisíc záznamami, ale aj onú tabuľku s viac ako 100k záznamami.

máte s týmto niekto praktické skúsenosti?

[vandrovnik]

Pro vyhledávání přidávám pomocné pole, kde je údaj bez diakritiky a velkými písmeny. V mém případě je to pole i indexované, protože u nás se mnohem častěji hledá podle počátku (STARTING WITH) než obsah kdekoli uvnitř (CONTAINING).

Snažím se nepoužívat LIKE, protože do hledaného textu pak musím přidat další znaky (%), jenže to dělá neplechu v případě, že pole je třeba varchar(10), v dotazu by bylo něco jako Pole LIKE :Pole a do parametru bych pak zadal %123456789% - parametr má rázem 11 znaků, přičemž přípustných je 10. Když použiju CONTAINING, stačí do parametru zapsat 123456789.

[Jan Fiala]

Pro určité číselníky taky přidáváme další pole, kde jsou agregované údaje z dalších polí bez diakritiky. např. pro adresář pole obsahuje komplet adresu (jméno + ulice + místo). Pro hledání používáme like, kde pak hledáš jakoukoliv část.
Bez existence "%" se dělá like automaticky, jakmile hledaný řetězec obsahuje "%", necháváme to na uživateli - kombinace jako: %novak%brno%

[František]

takze ako potom riesite vyhladavanie vyskytu v celom  varchar(4000), ci to rozsekat na jednotlive slova?

[pf1957]

takze ako potom riesite vyhladavanie vyskytu v celom  varchar(4000), ci to rozsekat na jednotlive slova?

Jestli chces full text search, tak to se resi pres inverted index, ale posledni free implementace, kterou jsem pred lety videl, byla v Jave.

[Jan Fiala]

takze ako potom riesite vyhladavanie vyskytu v celom  varchar(4000), ci to rozsekat na jednotlive slova?

Zase až tak dlouhé to není, většinou si vystačíš s několika stovkami znaků pro to agregované pole. Kromě toho univerzálního hledání má uživatel možnost hledat i podle jednotlivých polí. Ale když máš 2 pole pro název, tak nevíš, kam uživatel napíše jméno a kam třeba jméno firmy. To univerzální je víc používané. Výsledek je omezený na 200 řádků, takže i když dáš hledat "a", nedostaneš komplet seznam desítek tisíc záznamů.

Příklad 1:
Máš pole pro zadání kódu zákazníka. Když zadáš kód a najdeš jej, dostaneš zpět svůj záznam. Když zadáš delší číslo, provede se hledání dle IČ, pokud najde, dostaneš ho. Když tam je něco jiného než čísla, pustí se univerzální hledání a dostaneš nabídku záznamů.
Můžeš si také vyvolat dialog, kde hledáš podle jednotlivých údajů číselníku.
Co použiješ je na tobě.

Příklad 2:
Máš pole pro zadání položky. Zadáš kód, pokud jej znáš. Když ho nenajde, hledá podle kódu zákazníka. Umí hledat dle EAN, když zadáš text, hledá dle jména. Umí hledat i podle části kódu - nabídne ti všechny položky, které začínají na zadaný kód.  S tím, že si můžeš vyvolat dialog pro podrobné hledání podle jednotlivých polí.

Tím, že se vše zpracuje na straně serveru pošleš pouze jeden dotaz a o vše se postará uložená procedura. A protože je to uložená procedura, není nutné při požadavku na úpravu měnit klienta.

[František]

takze ako potom riesite vyhladavanie vyskytu v celom  varchar(4000), ci to rozsekat na jednotlive slova?

Zase až tak dlouhé to není, většinou si vystačíš s několika stovkami znaků pro to agregované pole.

no ale mam tovar s nazvom 300 znakov a popis 4000 znakov a potrebujem vyhladavat vyskyty slova ... ako to urychlit lebo like %% je faaaaakt pomale (a este pomalsie pre UNICODE)

[pf1957]

no ale mam tovar s nazvom 300 znakov a popis 4000 znakov a potrebujem vyhladavat vyskyty slova ... ako to urychlit lebo like %% je faaaaakt pomale (a este pomalsie pre UNICODE)

Psal jsem ti, ze se to dela invertovanym indexem: text rozbijes na token, preskaces nevyznamny balast a zbytek token ulozis do indexu s odkazem, kde se to vyskytuje. A hledas obracene: z hledany text rozbijes na token, vyhazis balast a hledas v indexu. Kdysi jsme to meli implementovane v SQLite a chodilo to svizne.

[vandrovnik]

no ale mam tovar s nazvom 300 znakov a popis 4000 znakov a potrebujem vyhladavat vyskyty slova ... ako to urychlit lebo like %% je faaaaakt pomale (a este pomalsie pre UNICODE)

Pro zajímavost, kolik je tam záznamů?

[František]

Pro zajímavost, kolik je tam záznamů?

do 600.000

[František]

Psal jsem ti, ze se to dela invertovanym indexem: text rozbijes na token, preskaces nevyznamny balast a zbytek token ulozis do indexu s odkazem, kde se to vyskytuje. A hledas obracene: z hledany text rozbijes na token, vyhazis balast a hledas v indexu. Kdysi jsme to meli implementovane v SQLite a chodilo to svizne.

co konkretne znamena toto

text rozbijes na token

lebo nneviem ako to aplikovat na toto:

Kód: [Vybrat]

nazov:
"32 D-LED Philips 32BDL3550Q- FHD,VA,350cd,AN,18/7"

popis:
"Informujte a zaujměte své zákazníky s tímto profesionálním Full HD displejem Philips řady Q. Toto spolehlivé řešení můžete zprovoznit tak rychle, jak potřebujete. * Provoz, sledování a údržba pomocí systému CMND
aktualny cas: 11s

este jedna zasadna vec: denne (alebo aj castejsie) sa to aktualizuje, takze cas importu je tiez dolezity

[František]

Psal jsem ti, ze se to dela invertovanym indexem ...

https://forum.delphi.cz/index.php/topic,15317.msg93075.html#msg93075
ale nevedel som ako na to, a ked sa to zase tu spomenulo tak je to uz zase na stole

[pf1957]

co konkretne znamena toto

text rozbijes na token

no ze ten text rozlozis na nejake prvky, se kterymi budes operovat tj. budes je chtit hledat. V nejjednoduzsim pripade napr. pouzit TStringList.DelimitedText a u nej vhodne nastavit Delimiter,  StrictDelimiter a po nejakem predzpracovani udelat z textu seznam. Z toho seznamu vyhazet stop words a zbytek pridat do slovniku + odkaz, kde se to najde.

lebo nneviem ako to aplikovat na toto:

Kód: [Vybrat]

nazov:
"32 D-LED Philips 32BDL3550Q- FHD,VA,350cd,AN,18/7"


No to muze byt orisek, je otazka, co to vsechno ma znamenat a co bude vyznamne pro hledani a co zahodit.

Kód: [Vybrat]

"Informujte a zaujměte své zákazníky s tímto profesionálním Full HD displejem Philips řady Q. Toto spolehlivé řešení můžete zprovoznit tak rychle, jak potřebujete. * Provoz, sledování a údržba pomocí systému CMND

Tak to je standardni text a jako s takovym bych s nim zachazel, pokud by mel byt prohledavan.

este jedna zasadna vec: denne (alebo aj castejsie) sa to aktualizuje, takze cas importu je tiez dolezity

No to zalezi, jak intelignentne je udelany ten import: jestli na prasaka tj. DELETE ALL + INSERT ALL, nebo neco inkrementalniho

[František]

no ze ten text rozlozis na nejake prvky, se kterymi budes operovat tj. budes je chtit hledat. V nejjednoduzsim pripade napr. pouzit TStringList.DelimitedText a u nej vhodne nastavit Delimiter,  StrictDelimiter a po nejakem predzpracovani udelat z textu seznam. Z toho seznamu vyhazet stop words a zbytek pridat do slovniku   odkaz, kde se to najde.

ok to znamena ze kazde slovo samostatny zaznam v DB plus stlpec s FK ID ku comu patri?
nebude to pomale v ramci deletovania a insetovania takeho mnozstva? ale to si skusim - a nebude efektivnejsie parsovat to cez PChar ako to nechat na TStringList.DelimitedText ?

alebo aj nieco taketo

Kód: [Vybrat]

"I(32C/64T Model 7573X (2.8/3.6GHz Max Boost, 768MB, 280W, SP3) Tray Technical Specification Specifications CPU: AMD EPYC 7573X CPU Core Name: Milan-X CPU Socket Standard: SP3 CPU Clock Rate: 2.80 GHz Turbo Clock Rate: 3.60 GHz CPU Core Quantity: 32 Threads Quantity: 64 L3 Cache Size: 768 MB Production Technology: 7 nm TDP: 280 W CPU Features: AMD Infinity Architecture AMD Infinity Guard Memory Types: DDR4 Memory Channels Supports: 8 Supported Memory Speed: 3200MHz(PC4-25600) Graphic Card: No Multiprocessing: 2 Warranty and Logistic Warranty Warranty Products Returnable: Yes Warranty Term (month): 36 month(s) Warranty Validation Criteria: Serial Number Logistic Pack Weight Brutto (kg): 0.23 kg Pack Weight Netto (kg): 0.22 kg Pieces in pack: 1 Box Weight Brutto (kg): 1.98 kg Retail Packaging Net Weight Carton: 0.01 kg Retail Packaging Net Weight Plastic: 0 kg Packs in Box: 4 Package Type: Tray!

Tak to je standardni text a jako s takovym bych s nim zachazel, pokud by mel byt prohledavan.

No to zalezi, jak intelignentne je udelany ten import: jestli na prasaka tj. DELETE ALL   INSERT ALL, nebo neco inkrementalniho

no tie data sa mozu menit (napr opravy chyb), takze potrebujem aktualnu verziu, ked to rozsekam na slova tak kolko bude ma 600.000 takychto zaznamov pripojenych slov? uvedny priklad ma cca 200 a to su aj vyrazne dhsie texty .. tak ako to rozumne naimportovat a mat to aktualne
ps. urobit ten FK napiamo a tak budemm at v tabulke slov zhodne slova s roznym FK, alebo to prepojit cez spojovaciu TABLE?

[pf1957]

ok to znamena ze kazde slovo samostatny zaznam v DB plus stlpec s FK ID ku comu patri?

Ne: co slovo to zaznam, ale pak k nemu vazebni tabulka m:n, protoze kazde slovo se muze vyskytovat v ruznych textech

nebude to pomale v ramci deletovania a insetovania takeho mnozstva?

To zalezi, kolik toho bude a jak to naprogramujes :-D

nebude efektivnejsie parsovat to cez PChar ako to nechat na TStringList.DelimitedText ?

Zalezi, jak dobry programator na low-level jsi :-D Ale jinak je to premature optimization, ten string list ti umozni to jednoduse realizovat, protoze se ti jinak muze stat, ze sice vymakas matlani "bitiku" pres PChar, ale zjistis, ze uzke hrdlo je jinde... Predelat to PChar muzes vzdycky, to je relativne dobre ohranicena funkcionalita.

alebo aj nieco taketo

Z toho nic neni videt, protoze to neni rozlamano do radku. Ale samozrejme je otazka nejake analyzy, co vsechno vsechno se ma hledat tj. kolilk tokenu bude rozhodujicich pro nalezeni pozadovanych zaznamu. A to si musis nejak urcit sam - to je vlastne na urovni tech stop words, ktere se daji ignorovat, aniz by to nejak podstatne omezilo vysledek hledani.

no tie data sa mozu menit (napr opravy chyb), takze potrebujem aktualnu verziu, ked to rozsekam na slova tak kolko bude ma 600.000 takychto zaznamov pripojenych slov? uvedny priklad ma cca 200 a to su aj vyrazne dhsie texty ..

Zalezi, jestli nejak dokazes sparovat zaznam v DB s importovanym. Pak jde jen o aktualizaci toho invertovaneho indexu: tokeny, pokud na ne odkazuje alespon jeden zaznam, ve slovniku zustavaji/pridavaji nove a odebiraji/pridavaji se odkazy. Kolik unikatnich tokenu v tech zaznamech budes mit? Myslim, ze to budou spis tisice nez desetitisice.