| Autor | Zpráva | ||
|---|---|---|---|
| bukaJ Profil |
#1 · Zasláno: 23. 4. 2009, 21:10:14 · Upravil/a: bukaJ
Ahoj,
před nedávnem jsem řešil tady na fóru takový problém s jednou rozsáhlou tabulkou (viz link). Tehdy jsem měl tabulku shops, která měla přes spojovací tabulku připojeny určité vlastnosti z číselníku - tedy vztah N:M a potřeboval jsem najít ty shopsy, které mají nastaveny všechny učené flagy. Dnes potřebuji tuto tabulku trochu rozšířit. Produkty jsou opět ve vztahu N:M zatříděny tentokrát podle typů a tak udělám to, že pro ukázku vypíšu tu tabulku. SELECT s.id, name, s.vote, t.name
FROM shops s
JOIN (select shop_id
from flags
where flag_id IN (2,12,65,67,82,83,84,150,151,170,190)
GROUP BY shop_id
HAVING count(*) = 11
) f
ON f.shop_id = s.id
join shop_types st on s.id=st.shop_id and st.active
join `types` t on st.type_id=t.id
group by t.id,s.id
order by t.id,s.vote desc;
Tabulka je například: id name vote name ------ ----------------------------------------------- -------- ----------------------------- 199 Mauris arcu integer semper est nibh felis 0.73436 Bar 117 Commodo aenean magna hendrerit nullam odio just 0.68739 Bar 126 Tincidunt velit in morbi faucibus curabitur sap 0.24317 Bar 180 Ullamcorper Mauris ipsum proin tortor vestibulu (NULL) Bar 175 Placerat sem et diam proin maecenas libero done 0.5057 Pizzerie 166 Turpis felis non hendrerit eros 0.361232 Pizzerie 144 Tempus nulla non nunc parturient ac vitae (NULL) Pizzerie 132 Ultricies velit sem velit massa accumsan consec (NULL) Pizzerie 199 Mauris arcu integer semper est nibh felis 0.73436 Hospoda 175 Placerat sem et diam proin maecenas libero done 0.5057 Hospoda 148 Fusce dictum malesuada iaculis natoque 0.310918 Hospoda 162 Ut velit nostra ac nisl et leo (NULL) Hospoda 180 Ullamcorper Mauris ipsum proin tortor vestibulu (NULL) Rozvoz 162 Ut velit nostra ac nisl et leo (NULL) Rozvoz 117 Commodo aenean magna hendrerit nullam odio just 0.68739 Kavárna 175 Placerat sem et diam proin maecenas libero done 0.5057 Kavárna 126 Tincidunt velit in morbi faucibus curabitur sap 0.24317 Kavárna 180 Ullamcorper Mauris ipsum proin tortor vestibulu (NULL) Kavárna 162 Ut velit nostra ac nisl et leo (NULL) Kavárna 130 Sed enim dapibus tincidunt urna faucibus nec an (NULL) Kavárna 132 Ultricies velit sem velit massa accumsan consec (NULL) Kavárna 166 Turpis felis non hendrerit eros 0.361232 Restaurace (všimněte si, že řádek s id 199 je zobrazen v Barech, tak v Hospodách - to je správně, protože spadá pod obě kategorie) Nyní ale potřebuji tento dotaz upravit tak, aby se v každé kategorii zobrazovaly pouze první tři položky (řazeno sestupně dle hodnocení). Tedy: id name vote name ------ ----------------------------------------------- -------- ----------------------------- 199 Mauris arcu integer semper est nibh felis 0.73436 Bar 117 Commodo aenean magna hendrerit nullam odio just 0.68739 Bar 126 Tincidunt velit in morbi faucibus curabitur sap 0.24317 Bar 175 Placerat sem et diam proin maecenas libero done 0.5057 Pizzerie 166 Turpis felis non hendrerit eros 0.361232 Pizzerie 144 Tempus nulla non nunc parturient ac vitae (NULL) Pizzerie 199 Mauris arcu integer semper est nibh felis 0.73436 Hospoda 175 Placerat sem et diam proin maecenas libero done 0.5057 Hospoda 148 Fusce dictum malesuada iaculis natoque 0.310918 Hospoda 180 Ullamcorper Mauris ipsum proin tortor vestibulu (NULL) Rozvoz 162 Ut velit nostra ac nisl et leo (NULL) Rozvoz 117 Commodo aenean magna hendrerit nullam odio just 0.68739 Kavárna 175 Placerat sem et diam proin maecenas libero done 0.5057 Kavárna 126 Tincidunt velit in morbi faucibus curabitur sap 0.24317 Kavárna 166 Turpis felis non hendrerit eros 0.361232 Restaurace Není přijatelné tento dotaz volat znovu pro každou skupinu, protože základ dotazu je dosti náročný. Tuším, že řešení bude jednoduché, skoro ho mám "na jazyku", ale prostě potřebuji nakopnout. Stejně tak není přijatelné řešit toto na aplikační úrovni. Koneckonců by mi nevadilo ani řešení, kdy by se místo názvů dotaz vracel ID shopů a ID typů v daném pořadí, nebo dokonce ID prvních produktů dané kategorie jako seznam oddělený čárkami, když by bylo nejhůř. name ids ------------ -------------- Bar 199, 117, 126 Pizzerie 175, 166, 144 Hospoda 199, 175, 148 Rozvoz 180, 162 Kavárna 117, 175, 126 Restaurace 166 |
||
| Kajman_ Profil * |
#2 · Zasláno: 23. 4. 2009, 21:23:44
Možná bude nejrychlejší využít uživatelské proměnné...
select k.*, @n:=@n*(@last_name=`tname`)+1 n, @last_name:=`tname` u from (SELECT s.id, name, s.vote, t.name tname FROM shops s JOIN (select shop_id from flags where flag_id IN (2,12,65,67,82,83,84,150,151,170,190) GROUP BY shop_id HAVING count(*) = 11 ) f ON f.shop_id = s.id join shop_types st on s.id=st.shop_id and st.active join `types` t on st.type_id=t.id group by t.id,s.id) k, (select @n:=0, @last_name:='') t having n <=3 order by `tname`, infull(`vote`,0) desc |
||
| bukaJ Profil |
#3 · Zasláno: 23. 4. 2009, 21:31:58
Kajman_e, ty zvíře... :-D Nechápu, jak by to mělo fungovat (rspt: nechápu co to dělá). Jdu to zkoumat.
(ps: díky za doplnění ifnull()). |
||
| bukaJ Profil |
#4 · Zasláno: 23. 4. 2009, 22:02:53
No, vyčerpal jsem svoje dnešní zásoby mozkové kapacity na tenhle oříšek a byla to docela zábava. Řešení nečekané, ale vlastně docela pěkné.
Štve mě ale, že zcela nechápu význam řádku č. 13 - rspt. nechápu jakto, že se nespustí na každém řádku a nevynuluje @n a kdy se teda vlastně provede??? Tuším, že plánovač jej vyhodnotí jako statický (nebo z jiného důvodu, který ovšem netuším) a spustí jej na samém začátku provádění dotazu a již nikdy dále, je tak? A za druhé je tam problém s tím, že @n je inkrementováno po dvou nikoliv po jedné a to už vůbec netuším proč. Takže výsledkem je, že se zobrazují dva řádky místo tří.
id name vote name n
------ ----------------------------------------------- -------- ---------- --
199 Mauris arcu integer semper est nibh felis 0.73436 Bar 1
126 Tincidunt velit in morbi faucibus curabitur sap 0.24317 Bar 3
175 Placerat sem et diam proin maecenas libero done 0.5057 Pizzerie 1
144 Tempus nulla non nunc parturient ac vitae (NULL) Pizzerie 3
199 Mauris arcu integer semper est nibh felis 0.73436 Hospoda 1
148 Fusce dictum malesuada iaculis natoque 0.310918 Hospoda 3
180 Ullamcorper Mauris ipsum proin tortor vestibulu (NULL) Rozvoz 1
117 Commodo aenean magna hendrerit nullam odio just 0.68739 Kavárna 1
126 Tincidunt velit in morbi faucibus curabitur sap 0.24317 Kavárna 3
166 Turpis felis non hendrerit eros 0.361232 Restaurace 1
|
||
| Kajman_ Profil * |
#5 · Zasláno: 23. 4. 2009, 22:11:07
Na 13. řádku je jen inicializace proměnných. Může se to dělat v přechozím dotaze díky set, ale takhle se to může vyřídit v jednom dotaze.
Nulování se uskutečňuje násobením výrazem (@last_name=`tname`) který vrátí 0 nebo 1. Proč se to zvyšuje po dvou, netuším :-) zkuste zakomentovat having, jestli to čísluje po 2 i dál. |
||
| bukaJ Profil |
#6 · Zasláno: 23. 4. 2009, 22:21:59
Ahoj, když having vyhodím, tak to stoupá po jednom.
|
||
| Kajman_ Profil * |
#7 · Zasláno: 23. 4. 2009, 22:28:23
To je divné... zkusil bych jiné sestavení mysql. A třeba
having n in (1,2,3) Nebo něco, kde to nezlobí :-) |
||
| bukaJ Profil |
#8 · Zasláno: 23. 4. 2009, 22:36:46
Myslíš Postgresql? Nemám šanci :-/ Ale dělá to jak na linuxovém tak Windowsím mysql (verze z minulého týdne).
Už jen krůček zbývá... Našlo by se ještě nějaké jiné řešení bez použití uživ. proměnných? Něco s kreativním použitím LIMIT v poddotazu, nebo tak. |
||
| TomášK Profil |
#9 · Zasláno: 23. 4. 2009, 22:51:24 · Upravil/a: TomášK
@n:=@n*(@last_name=`tname`)+1 n, @last_name:=`tname` Myslím si, že MySQL nezaručuje pořadí vyhodnocení těch parametrů - záleží to na plánovači jak se rozhodne. Kdysi jsem s tím měl stejný problém, při jednoduchém dotazu to fungovalo perfektně, ale když jsem ho zesložiťoval, najednou to přestalo fungovat. Jiné vysvětlění mě nenapadlo. Nejlepších n záznamů jde řešit přes korelované poddotazy ( http://www.root.cz/clanky/korelovane-vnorene-dotazy-nepouzivat-a-nahradit/ - na konci článku) nebo přes self join, což se dá najít v diskuzi pod článkem - včetně porovnání rychlosti. Edit: teď koukám na tu diskuzi, self join používají na kumulativní součty, nikoliv na nejlepších n záznamů Edit2: Nešlo by vynutit pořadí pomocí ohavné konstrukce: IF(@n:=@n*(@last_name=`tname`)+1, IF(@last_name:=`tname`, @n, NULL) , NULL) Ale kdybych tohle našel u někoho v kódu, tak si o něm nepomyslím nic pěkného... |
||
| Kajman_ Profil * |
#10 · Zasláno: 23. 4. 2009, 23:33:09
Zkusil bych to na starší stabilnější verzi než na verzi z minulého týdne.
Vyhodnocování proměnných je myslím v tomhle případě v pořádku. Někde na to byly příklady v manuálu. Problém bude v chybném having - proto chci, aby to zkusil na jiném sestavení... nějaké starší stabilnější větvi. |
||
| Kajman_ Profil * |
#11 · Zasláno: 23. 4. 2009, 23:38:43
třeba vyhodit podmínku z having a dát ji až do obaleného where
select id, name, vote, tname
from (select k.*,
@n := @n * (@last_name = tname) + 1 n,
@last_name := tname u
from (SELECT s.id, name, s.vote, t.name tname
FROM shops s
JOIN (select shop_id
from flags
where flag_id IN (2, 12, 65, 67, 82, 83, 84, 150, 151, 170, 190)
GROUP BY shop_id
HAVING count(*) = 11) f ON f.shop_id = s.id
join shop_types st on s.id = st.shop_id
and st.active
join types t on st.type_id = t.id
group by t.id, s.id) k,
(select @n := 0, @last_name := '') t
order by tname, ifnull(vote, 0) desc) t
where n <= 3 |
||
| TomášK Profil |
#12 · Zasláno: 23. 4. 2009, 23:49:48 · Upravil/a: TomášK
Hledal jsem a pořadí zaručené není, viz:
The order of evaluation for user variables is undefined and may change based on the elements contained within a given query. In SELECT @a, @a := @a+1 ..., you might think that MySQL will evaluate @a first and then do an assignment second, but changing the query (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may change the order of evaluation. http://dev.mysql.com/doc/refman/5.0/en/user-variables.html Edit: Zkusil bych to přes ten korelovaný poddotaz, pokud těch záznamů není moc. První nástřel: SELECT
base_s.id, name, base_s.vote, base_t.name
FROM
shops base_s
join shop_types base_st on base_s.id=base_st.shop_id and base_st.active
join `types` base_t on base_st.type_id=base_t.id
group by base_t.id,base_s.id
HAVING (base_t.id, base_s.id) IN
( SELECT s.id, st.type_id
FROM shops s
JOIN (select shop_id
from flags
where flag_id IN (2,12,65,67,82,83,84,150,151,170,190)
GROUP BY shop_id
HAVING count(*) = 11
) f
ON f.shop_id = s.id
join shop_types st on s.id=st.shop_id and st.active
where st.type_id=base_t.id
group by s.id
order by s.vote desc
LIMIT 3) ; |
||
| bukaJ Profil |
#13 · Zasláno: 24. 4. 2009, 00:11:47
„Zkusil bych to na starší stabilnější verzi než na verzi z minulého týdne.“
Tím myslím, že se jedná o verzi, která byla v minulém týdnu dostupná na mysql.com. Tím ale neříkám, že byla vydaná minulý týden, ale je to verze MySQL 5.1.34 (02 April 2009). |
||
| bukaJ Profil |
#14 · Zasláno: 24. 4. 2009, 00:15:42
„Zkusil bych to přes ten korelovaný poddotaz, pokud těch záznamů není moc.“
Záznamů se očekává mezi 5-10tisíc, flagů cca 150000. Zítra se na to podívám a rozhodnu. Zatím díky všem zúčastněným! |
||
| Kajman_ Profil * |
#15 · Zasláno: 24. 4. 2009, 09:10:53
Ještě by to šlo s tím jedním sloupečkem ids - group_concat a ořezat přes substring_index, ale to moc univerzální není. A ten korelovaný poddotaz bude asi pěkně pomalý. I v tom článku je doporučení se jim vyhnout. Ty se většinou vyplatí jen když z hodně řádků jich vybírám jen pár a pro ně potřebuji něco spočítat nebo tak něco.
SELECT t.name, substring_index(group_concat(s.id order by ifnull(s.vote) desc),',',3) ids
FROM shops s
JOIN (select shop_id
from flags
where flag_id IN (2,12,65,67,82,83,84,150,151,170,190)
GROUP BY shop_id
HAVING count(*) = 11
) f
ON f.shop_id = s.id
join shop_types st on s.id=st.shop_id and st.active
join `types` t on st.type_id=t.id
group by t.id, t.name
order by t.id |
||
| bukaJ Profil |
#16 · Zasláno: 24. 4. 2009, 09:16:02
„Ještě by to šlo s tím jedním sloupečkem ids - group_concat a ořezat přes substring_index, ale to moc univerzální není. “
To bude to, co jsem hledal. Není to univerzální, zrovna elegantní taky ne, ale hlavní je, že nejnáročnější dotaz proběhne jen jednou a druhým dotazem si podle těch indexů vytáhnu informace o nich. Elegance je sice stále mizerná, ale praktičnost v tom vidím velikou. Díky! |
||
| Kajman_ Profil * |
#17 · Zasláno: 24. 4. 2009, 09:39:41
Když s tím takhle můžete operovat, tak proč si rovnou nevyfiltrujete ty 4. a další z kategorie až v aplikaci?
|
||
| TomášK Profil |
#18 · Zasláno: 24. 4. 2009, 11:28:40
Ten korelovaný poddotaz určitě pomalý je, u uvedeného množství záznamů bych tipnul, že bude mít problémy. Nicméně mi to přišlo bezpečnější než pracovat s proměnnými, pokud by se ukázalo, že těch záznamů nění tolik. Když jsem kdysi řešil totéž, prošel jsem přes proměnné, korelovaný poddotaz a skončil jsem u procedur a triggerů.
O triku s group_concat jsem se dozvěděl až později od tebe, "čisté" to moc není, ale užitečné hodně. V mezičase jsem na něj pozapomněl, ale je to asi lepší řešení než poddotaz. |
||
|
Časová prodleva: 15 let
|
|||
0