Dotaz: slouceni dvou dotazu

[Leoš Literák]

Ahoj, mam dalsi dotaz pro vyvoj abicka, holt sql neni moje parketa. Mam dva dotazy, ktere samostatne funguji. Chtel bych to zoptimalizovat, abych je nemusel spoustet a zpracovavat samostatne, ale mel jen jeden dotaz se dvema hodnotovymi sloupecky. Lisi se jen jednou podminkou rovnosti S.pridal,RS.kdo a obsahuji count a group by. Databaze mysql.

select K.autor,count(*) from komentar K, reseni RS, relace R, spolecne S where 
RS.zaznam=R.potomek and R.typ_potomka='Z' and R.predek=S.cislo and S.typ='P' and 
(S.pridal<>RS.kdo or S.pridal is null) and K.cislo=RS.komentar and RS.kdo<>K.autor group by K.autor

| autor | count(*) |
+-------+----------+
|     1 |        1 |
|    11 |        1 |
|    20 |        2 |
|   151 |        4 |
|   595 |        2 |

select K.autor,count(*) from komentar K, reseni RS, relace R, spolecne S where 
RS.zaznam=R.potomek and R.typ_potomka='Z' and R.predek=S.cislo and S.typ='P' and 
S.pridal=RS.kdo and K.cislo=RS.komentar and RS.kdo<>K.autor group by K.autor

| autor | count(*) |
+-------+----------+
|     1 |        3 |
|   151 |        2 |
|   421 |        1 |
|   595 |        2 |
|   868 |        1 |

Chtel bych dostat takovyto vysledek:

| autor | count(*) | count(*) |
+-------+----------+----------+
|     1 |        3 |        1 |
|   151 |        2 |        4 |
|   421 |        1 |        0 |
|   595 |        2 |        2 |
|   868 |        1 |        3 |

Diky

---

---

Odpovědi

[kulik]

Myslim ze by melo jit ty lisici se podminky vytahnout do agregace. Nevim jak se to udela v mysql, ale v Oracle by to mohlo vypadat takto (lepsi nez decode je case, ale pravdepodobnost vyskytu jeho analogie v mysql vidim jako mensi):

select K.autor,        
       sum(decode(S.pridal, null, 1, RS.kdo, 0, 1)) cnt1,
       sum(decode(S.pridal, null, 0, RS.kdo, 1, 0)) cnt2       
from komentar K, reseni RS, relace R, spolecne S 
where 
RS.zaznam=R.potomek and R.typ_potomka='Z' and R.predek=S.cislo and S.typ='P' and K.cislo=RS.komentar and RS.kdo<>K.autor group by K.autor

[happy barney]

+1 aj s CASE +10 za verziu s triggrami.

[Leoš Literák]

decode mysql nema, case ano. Tento prikaz skonci chybou. Jak by to vypadalo s case? Dik

[posejdon]

myslim, ze mysql 5 a vyssi by si melo poradit s timto, nicmene reseni navrzene vyse by melo byt optimalnejsi (jedinny pruchod tabulky), mysql pouziti CASE umoznuje a v tomto pripade by mohlo zvysit prehlednost kodu (resp umysl bude bit do oci na 1. pohled).

select t1.autor, t1.c1, t2.c2

FROM

(select K.autor,count(*) as c1 from komentar K, reseni RS, relace R, spolecne S where

RS.zaznam=R.potomek and R.typ_potomka='Z' and R.predek=S.cislo and S.typ='P' and

(S.pridal<>RS.kdo or S.pridal is null) and K.cislo=RS.komentar and RS.kdo<>K.autor group by K.autor) t1

JOIN

(select K.autor,count(*) as c2 from komentar K, reseni RS, relace R, spolecne S where

RS.zaznam=R.potomek and R.typ_potomka='Z' and R.predek=S.cislo and S.typ='P' and

S.pridal=RS.kdo and K.cislo=RS.komentar and RS.kdo<>K.autor group by K.autor) t2

ON t2.autor = t1.autor

[Leoš Literák]

Dik, tohle funguje, explain ukazuje deset tabulek. Zatim je tam malo dat, takze rychlost neni prekazkou. Jsem perfekcionista, zajimala by me ta varianta s case.

[posejdon]

No myslel jsem, ze nakopnuti od kolegy a dokumentace k MySQL postaci, ale zkusim nazorny priklad, vzdyt je to pro dobrou vec (a zvladnou to i prehistoricke vydani MySQL)

select K.autor,
       sum( CASE WHEN S.pridal=RS.kdo and K.cislo=RS.komentar THEN 1 ELSE 0 END ) cnt1,
       sum( CASE WHEN S.pridal<>RS.kdo or S.pridal is null THEN 1 ELSE 0 END ) cnt2
from komentar K, reseni RS, relace R, spolecne S 
where RS.zaznam=R.potomek 
 and R.typ_potomka='Z' 
 and R.predek=S.cislo 
 and S.typ='P' 
 and K.cislo=RS.komentar 
 and RS.kdo<>K.autor 
group by K.autor

[Leoš Literák]

Jeste bych se chtel primluvit za to, abys pouzival ke spojovani tabulek JOIN namisto kartezskeho soucinu omezeneho v klauzuli where, nebot se celkem spatne hleda, co je spojeno s cim (ale na rychlost dotazu to nema vliv a je to spise o zvyku, ja takto rovnou vidim relaci a nemusim to hledat v podminkach)

SELECT K.autor,
       SUM( CASE WHEN S.pridal = RS.kdo AND K.cislo = RS.komentar THEN 1 ELSE 0 END ) cnt1,
       SUM( CASE WHEN S.pridal <> RS.kdo OR S.pridal IS NULL THEN 1 ELSE 0 END ) cnt2
FROM komentar K
JOIN reseni RS ON RS.komentar = K.cislo
JOIN relace R ON R.potomek = RS.zaznam 
              AND R.typ_potomka='Z'
JOIN spolecne S ON s.cislo = r.predek
                AND S.typ='P' 
WHERE RS.kdo <> K.autor 
GROUP BY K.autor

[Leoš Literák]

Ahoj, mozna by stacila, ale nemel jsem klid na praci (dve deti) a hlavne bych musel zjistit, k cemu ten oracle decode vlastne slouzi. Pak bych mel sanci. Kazdopadne dekuju, funguje to, druhy priklad uz v explain ma jen ctyri tabulky pouzivajici klice.

Pokud jde o ty joiny, souhlasim, ze jejich pouziti cini dotazy citelnejsi. Maji jeste nejake dalsi vyhody oproti ekvivalentnim dotazum s podminkami ve where? Treba lepsi vykon? Pak by melo smysl prepsat vsechny SQL dotazy.

[kulik]

Vykon by mel byt stejny, jde jen o ruznou formu zapisu tehoz. Ale samozrejme nelze nikdy uplne vyloucit, ze konkretni stroj se k tomu postavi ruzne, treba kvuli nejakemu bugu apod.

[posejdon]

Jde o to, jestli se v tom hrabes sam, nebo to ma nekdy nekdo po tobe louskat. A taky jde o ten zvyk. Znam nektere matadory, kteri join nepouzivaji, ale na druhou stranu si pomahaji treba tim, ze ty relacni podminky davaji na prvni misto a jeste je odlisuji od ostatnich tim, ze je pisi cele kapitalkama. Takovy dotaz se uz cte lip, nicmene ten JOIN navic hlida kodera, aby nezapomnel tu vazbu uvest, coz se treba pri spojeni 10 tabulek stane raz dva.