Autor | Zpráva | ||
---|---|---|---|
JardaB Profil |
#1 · Zasláno: 21. 5. 2013, 11:58:45
Zdravím potřebuji nějak formulovat následující dotaz do mysql, pokud to jde..
v tabulce 'table' jsou sloupce 'key' a 'text'. Potřebuji dle $string najít 5 adekvátních výsledků v porovnání s 'key' a těchto 5 výsledků dále prohledat zda obsahují nebo neobsahují shodu $string v sloupci 'text'. Potřebuji vrátit ne jen 5 výsledků seřazených dle nelepší shody 'key', ale také u každého hodnotu, zda se podařilo řetězec nebo shodu najít v text. Snad jsem to napsal srozumitelně.. díky za reakce, prvně prosím než začnete cokoliv psát, prosím o zamyšlení nad dotazem... |
||
juriad Profil |
#2 · Zasláno: 21. 5. 2013, 12:02:49
co je "adekvátní výsledek"?
co znamená "seřazených dle nelepší shody"? Pokud to dokážeš dostatečně přesně definovat a bude ta definice aplikovatelná v jazyce SQL, tak to snad půjde. Zkus se víc rozepsat a uvést nějaké příklady. |
||
JardaB Profil |
#3 · Zasláno: 21. 5. 2013, 12:34:18
Jednoduše řečeno 5 výsledů, které nejlépe vyhovují hledanému řetězci (podobnost řetězce)... tedy hledám např. slovo televize a to musí být obsaženo v sloupci key (může být obsaženo ve více než sto řádích) a může nebo nemusí být obsaženo v sloupci text. Kdy těchto 5 výsledků potřebuji vrátit od nejvyhovujícího dle sloupce key a s hodnotou, zda je slovo televize obsaženo ve sloupci text. Rád bych k tomu použil fulltextové vyhledávání.
Jde o to, že slovo televize může být obsaženo v tisíce řádcích sloupce text.. chci tedy laicky řečeno vymezit výběr na pět hodnot dle sloupce key a teprve ty porovnat na klíčové slovo dle jejich textové části sloupce text. A vrátit 5 hodnot, kde bude i hodnota zda je hledaný výraz i ve sloupci text. Chci si takhle vytvořit jednoduchou kategorizaci zboží, kdy mám v sloupci key, klíčová slova jednotlivých kategorií a v sloupci text bližší charakteristiku dané kategorie obsahující další klíčová slova a přívlastky. Výsledkem dotazu by mělo být 5 možných výsledků kategorií produktu. Nečekám 100% úspěšnost. |
||
peta Profil |
#4 · Zasláno: 21. 5. 2013, 13:25:23
vaha 1:
`key` = 'hledany text'
vaha 2: `text` LIKE '%hledany text%'
SELECT * FROM tabulka WHERE `key` = 'hledany text' LIMIT 5 UNION SELECT * FROM tabulka WHERE `text`LIKE '%hledany text%' LIMIT 5 |
||
JardaB Profil |
#5 · Zasláno: 21. 5. 2013, 13:35:20
peta:
Pokud to dobře chápu, takt první řádek zajistí těch 5 hodntot dle key a druhý řádek těchto 5 hodnot ještě prohledá dle text? V druhém řádků by LIMIT už být nemusel, ne? Protože nemůže vrátit více jak těch 5 hodntot, které jsou limitované prvním řádkem. |
||
juriad Profil |
#6 · Zasláno: 21. 5. 2013, 13:51:51
JardaB:
Peta zase blábolí. Jeho select vrátí 5 řádků, jejichž key se přesně shoduje a dalších 5 řádků, jejichž text obsahuje podřetězec; select vrátí množinu řádků, tedy jich může být 5 až 10. Použití fulltextového vyhledávání má některé, často nepěkné důsledky na schéma databáze (tabulka musí byt MyISAM). SELECT *, (text LIKE '%hledany text%') AS obsahuje FROM tabulka WHERE key = 'hledany text' ORDER BY obsahuje DESC LIMIT 5 Nějak mi není jasné, jak by vypadala taková tabulka (jaká obsahuje data). Obávám se, že naše dotazy dělají něco úplně jiného a moc si nerozumíme. Zkus napsat nějakou demonstraci, například pomocí termínů: televize, lcd, plasma, projektor, monitor. |
||
peta Profil |
2 selecty, kazdy vyhleda prvnich 5 radku podle kazdeho kriteria. cili dohromady max 10, min 0. Nekdo sikovnejsi ti to bude jiste umet napsat jako 1 dotaz, ktery vrati max 5 a min 0.
juriad: To ty ses uplne mimo :) televize muze, ale nemusi byt v `key` Ale to mi pripomina, ze ten druhy dotaz je chybne SELECT * FROM tabulka WHERE `key` <> 'hledany text' AND `text`LIKE '%hledany text%' LIMIT 5 |
||
JardaB Profil |
#8 · Zasláno: 21. 5. 2013, 14:58:09
juriad:
Ono je to krapánek složitější, něž jsem zde uvedl, ale to není podstatné. Ty hledané řetězce jsou ve skutečnosti dva. První z nich prohledá key, vrátí skupinu shod a druhý řetězec z vrácených pěti výsledků prohledá samotný text těch výsledků. Př.: key - obsahuje slova (Bytové doplňky) text - obsahuje slova (byt,bytový,doplňky,vybavení bytu,bytové...) key chci porovat se $string1, který často odpovídá dané kategorizaci, tedy (Bytové doplňky), chci dostat 5 řádků které tomu odpovídají. Může se totiž stát, že bude kategorie (Pracovní doplňky, Oděvní doplňky). těcho 5 získaných řádků chci dále prohledat dle $sting2 v sloupci text, kde tento řetězec již obsahuje jednotlivá klíčová slova, která by měla zaručit správnou volbu kategorie produktu z pěti, které se prohledaly dle key. těžko bude např. v bytových doplňcích slovo pracovní, ochranné atd. Jednoduše řečeno, první fází dostanu několik hrubých odhadů, které zpřesním dalším prohledáním výsledku. Vylučujte to tedy použití klasického LIKE, ale asi MATCH a AGAINST |
||
JardaB Profil |
#9 · Zasláno: 22. 5. 2013, 07:48:22
Tak koukám že už nikdo žádný nápad? V zásadě mi jde jen strukturu dotazu dle popisu, jeho syntaxi si udělám sám. Tedy potřebuji najít z celé tabulky 5 záznamů dle sloupce key a teprve tyhle záznamy prohledat dle sloupce txt.
...WHERE key LIKE $string AND txt LIKE $string1 LIMIT 5 .. je logicky nesmysl, najde sice 5 výsledků, ale v txt výraz musí být, což nepotřebuji v mém případě tam být nemusí ...WHERE key LIKE $string OR txt LIKE $string1 LIMIT 5 .. taky kravina, páč to prohledá naprosto celou tabulku na sloupec txt, já potřebuji hledat jen v těch 5 záznamech ...WHERE key LIKE $string AND (key LIKE $string OR txt LIKE $string1) LIMIT 5 .. Fungovalo by tohle dle toho zadání?? |
||
ts_istudio Profil |
#10 · Zasláno: 22. 5. 2013, 09:29:03
JardaB:
„a těchto 5 výsledků dále prohledat zda obsahují“ Nehledáš něco jako toto? SELECT * FROM (SELECT FROM * WHERE ...) WHERE ... |
||
juriad Profil |
JardaB:
Mě přijde, že na to jdeš špatně: Ty hledáš "bytový doplněk"; ze stovky klíčů, které obsahují slovo "doplněk", vybereš 5 více-méně náhodných a doufáš, že v popisu bude i to slovo "bytový". Nebylo by lepší hledat fulltextově jak v klíčích, tak v textu, příčemž shoda v klíči by měla mnohem větší váhu (třeba 3x)? http://stackoverflow.com/questions/4767145/give-some-fields-more-relevance-and-sort-by-relevance-in-mysql-full-text-search SELECT *, MATCH (key) AGAINST ('hledany text') AS rel_key, MATCH (text) AGAINST ('hledany text') AS rel_text, (rel_key*3)+(rel_text) AS rel_total FROM tabulka WHERE MATCH (key,text) AGAINST ('hledany text') ORDER BY rel_total Limit bych do SELECTu nedával, můžeš klidně načíst v PHP jen pár řádků a zbytek query zahodit až ti přijde, že relevance klesla příliš. Pokud trváš na tom, že se napřed vybere 5 podle klíče a ty se budou třídit podle textu, lze ten dotaz tak přepsat. Přítomnost "hledaného textu" ve sloupečku text poznáš podle nenulovosti rel_text. |
||
JardaB Profil |
#12 · Zasláno: 22. 5. 2013, 10:37:38 · Upravil/a: JardaB
juriad:
Sloupec key obsahuje klíčová slova stromové struktury kategorií, tedy je předpoklad, že výsledek bude mít max 5 řádků, čímž zúžím danou množinu, kterou dále prohledám v textové dle části dle text a určím výsledek. Dle mě je to zcela logický postup. data klientů mají v klíčovém řetězci svou logiku a moje klíčová slova jsou na tom logicky postavena... Ale logicky na základě jen key to přesně nikdy neurčím. To co jsi napsal bude vracet i položky, které nevyhovují key, byť s menší váhou? Jestli ano, tak to je přesně to co nechci, to si umím napsat i sám. Limit bych rád použil pokud to zrychlí dotazy, protože se zpracovává třeba 20 tis produktů. ts_istudio: Ánoo tohle vypadá logicky jako to co hledám... pokud je takto možné dotaz zapsat? JardaB: „a těchto 5 výsledků dále prohledat zda obsahují“ Nehledáš něco jako toto? SELECT * FROM (SELECT FROM * WHERE ...) WHERE ... |
||
juriad Profil |
#13 · Zasláno: 22. 5. 2013, 12:16:05
JardaB:
SELECT *, MATCH (text) AGAINST ('hledany text') AS rel_text FROM (SELECT *, FROM tabulka WHERE MATCH (key) AGAINST ('hledany text') LIMIT 5) ORDER BY rel_text DESC Vnitřní SELECT vybere jen prvních 5 podle key a výsledek se seřadí podle rel_text (nulovost rel_text značí, že "hledany text" nebyl ve sloupci text nalezen) OK, pokud tam máš logiku, tak předchozí select by měl fungovat. Jen mi to přijde jako dost nešikovné řešení, pokud váhu zvýšíš 10x, tak se bude rozhodovat téměř čistě podle key a jen pokud v key nenalezne shodu, tak půjde podle textu - větší šance, že alespoň nějak produkt zařadíš. FULLTEXT musí (nemusí, ale pak je rychlejší i moje babička) mít INDEX, a pokud jsou data v indexu, moc nezáleží na tom, kolik jich je. |
||
JardaB Profil |
#14 · Zasláno: 22. 5. 2013, 12:57:52
juriad:
Mě to nepřijde nešikovné... není podmínkou abych dostal výsledek vždy. Tedy ani na key nemusím dostat výsledek. Což bude znamení, že nemám takovou kategorii nebo data klienta postrádají doporučenou logiku. V takovém případě produkt nekategorizuji a jen dávám k dispozici ve fulltextu. Přesně o to jde... pokud najdu shodu v key, tak teprve budu hledat v textu... protože kdybych nenašel shodu v key a dále se snažil hledat nějaký výsledek v textu, byly by výsledky už šíleně nepřesné, to bych mohl rovnou prohledávat už jen v tom textu. |
||
peta Profil |
Ja to chapu tak, ze ma treba
kat: televize prod: philips kat: kabelaz prod: JACK/CINCH kabely k televizi Ted je samozrejme otazka presneho ucelu. Jestli tam treba dat random nebo nechat nalezt prvnich 5. U tech mych dotazu spojenych UNION proste vemu 0-5 a 0-5 z obou. Bude to serazene tak, ze prvnich 0-5 bude vzdy vaha1 a dalsich -50 bude vaha2. To najde treba 7. a pomoci php bych prvnich 5 vypsal. juriadovo reseni #11 s nasobeni tremi je zajimave, ale to by potlacilo vyznam kategorie, kdyz by na konec napsal LIMIT 5. To by pak naslo treba 3 z kategorie, 2 v textu a pritom v kategorii by bylo jeste dalsich 30 vyrobku. Spravne by se tedy melo zobrazit 5 televizi a zadna kabelaz. |
||
JardaB Profil |
#16 · Zasláno: 22. 5. 2013, 15:17:53
ano takhle nějak to je...
zkusím otestovat vše co tu bylo nějak naznačeno a uvidí se.... Jen pořád nevím, proč všem nějak nesedí má úvaha o zpřesňování výsledku určením hrubé množiny a její následující prohledání. Ve finále potřebuji stejně jeden jediný výsledek který musí být produktu přiřazen. Ta váha je ale zajímavá... protože když najdu shodu jen na key a již ne v textu, tak bych hodnotu váhy zapsal k produktu... tedy by se tyto produkty v dané kategorii zobrazovaly na konci.. kde už by případně mylná kategorizace nebyla tak tristní. |
||
JardaB Profil |
#17 · Zasláno: 23. 5. 2013, 10:08:05
Tak moje řešení funguje a hledání je cca na 90% úspěšnosti...
Chtěl jsem otestovat ještě následující, ale nějak ten dotaz namůžu dostat do validní formy, vidí tam někdo zjevně problém v zápisu? Nebo je vůbec možné dotaz takhle napsat? SELECT *, MATCH (key) AGAINST ('hledany text') AS rel_key, MATCH (text) AGAINST ('hledany text') AS rel_text, (rel_key*3)+(rel_text) AS rel_total FROM tabulka WHERE MATCH (key,text) AGAINST ('hledany text') Také jsem chtěl otestovat tohle, ale hlásí to problém s aliasem. Dá se to nějak ošetřit? SELECT * FROM (SELECT FROM * WHERE ...) WHERE ... ORDER BY rel_total |
||
juriad Profil |
JardaB:
Omlouvám se za zmatek, funkční SQL je: SELECT *, MATCH (`key`) AGAINST ('hledany text') AS rel_key, MATCH (text) AGAINST ('hledany text') AS rel_text, (MATCH (`key`) AGAINST ('hledany text'))*3+(MATCH (text) AGAINST ('hledany text')) AS rel_total FROM tabulka WHERE MATCH (`key`,text) AGAINST ('hledany text') ORDER BY rel_total Měl jsem tam dvě chyby: 1) sloupec se nesmí jmenovat key, to je rezervované slovo, proto musí být escapovaný; obdobně je to s text, ale to ještě MySQL z důvodu zpětné kompatibility zkousne. 2) na alias se můžu odvolávat jen z GROUP BY, HAVING a ORDER BY, takže je třeba celou definici zopakovat |
||
JardaB Profil |
#19 · Zasláno: 23. 5. 2013, 10:36:36 · Upravil/a: JardaB
juriad:
.. :D no jasně.. někdy kravina a člověka to nenapadne.. Díky, vyzkouším a dám vědět, které řešení mělo větší úspěch... ještě mi ten tvůj zápis hlásí #1191 - Can't find FULLTEXT index matching the column list |
||
Kajman Profil |
#20 · Zasláno: 23. 5. 2013, 11:17:10
Musíte si oba indexy vytvořit. Klikněte si na against a tam to je popsáno.
|
||
JardaB Profil |
#21 · Zasláno: 23. 5. 2013, 12:08:38
Jasně díky, nestačí mít indexy jen na (key) , (text) jak jsem měl, ale i (key,text), teď to funguje...
|
||
juriad Profil |
#22 · Zasláno: 23. 5. 2013, 12:10:53
JardaB:
Pak ty dotazy porovnej a pověz, který dává lepší výsledky a jestli dotaz není příliš pomalý. Samotného mě to zajímá. |
||
JardaB Profil |
#23 · Zasláno: 23. 5. 2013, 12:30:07
To tvoje řešení vypadá hodně zajímavě... zkusím to nasadit do kódu a uvidíme výsledky u cca 100 tis produktů. Jinak dotaz cca 0.0012 sec., kde tabulka s kategorizačními klíči má má zatím cca 900 řádků, kdy ve finále bude mít kolem 2 tis, to se potom uvidí...
|
||
Časová prodleva: 11 let
|
0