Autor Zpráva
bukaJ
Profil
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 *
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
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
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 *
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
Ahoj, když having vyhodím, tak to stoupá po jednom.
Kajman_
Profil *
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
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
@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 *
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 *
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
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
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
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 *
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
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 *
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
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.

Vaše odpověď

Mohlo by se hodit

Odkud se sem odkazuje


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: