Dotaz: MySQL - součty z jedné tabulky do více sloupců při různých podmínkách

[otula]

Potřeboval bych poradit se SELECTem v MySQL.

Mám jednoduchou tabulku, nazvěme ji tabulka, v níž jsou pro mne podstatná pole photoid, tedy identifikátor fotografie a pole rating, které může nabývat hodnoty z množiny {-1;0;1}

Do tabulky se ukládají hlasy z ankety - co hlas, to další záznam v dané tabulce.

No a já bych rád dostal na výstupu tabulku, kde by (v jednodušším případě) byly sloupce

photoid | kolikrát-fotka-dostala-nulu | celkový-počet-hlasů-pro-danou-fotku

S výstupem, kde dostanu jen nulové hlasy (či celkový počet hlasů pro každou fotku) nemám problém:

SELECT COUNT(*) AS pocetnul, photoid FROM tabulka WHERE rating=0 GROUP BY photoid

Jak do toho ale zamíchat seskupený součet při další podmínce, to nemám páru.

No a pokud by to bylo možné, ještě více bych ocenil i další sloupce v rámci toho jednoho výstupu, a to počet záporných hlasů a počet kladných hlasů

---

---

Odpovědi

[otula]

Netestoval jsem to, ale mohlo by to vypadat nějak takto:

SELECT photoid,
    sum(if(rating=-1,1,0)) AS minus,
    sum(if(rating=0,1,0)) AS zero,
    sum(if(rating=1,1,0)) AS plus,
    sum(rating) AS celkem
    FROM tabulka GROUP BY photoid;

[otula]

Mockrát díky, bezva nápad, jak to řešit, funguje perfektně.

[Kit]

Rádo se stalo. Už jsem kdysi něco podobného řešil v SQLite, ve kterém to sice tak elegantně nevypadá, ale běhá to svižně.

[blondak]

ještě lze využít toho, že true == 1 a false == 0

SELECT 
  photoid,
  sum(rating=-1) AS minus,
  sum(rating=0) AS zero,
  sum(rating=1) AS plus,
  count(rating) AS celkem
FROM 
  tabulka 
GROUP BY
  photoid;

u celkem musí být count a ne sum, protože pro 3 hodnocení -1,0,1 by sum vrátil 0, count vrátí 3.

[Kit]

count(rating) vs. sum(rating)

Samozřejmě záleží na tom, zda potřebujeme součet počtu hodnocení nebo součet bodů. Pokud budu chtít sestavit žebříček podle úspěšnosti fotek, tak použiji sum(rating). Pokud podle počtu hodnocení (logické mi to nepřipadá, ale budiž), použiji count(rating). Záleží na zadání.

[otula]

Jak už jsem reagoval níže, podstatný byl pro mne ten geniálně jednoduchý princip, který řešil můj problém. Jinak jsem skutečně (zdánlivě nelogicky^*) požadoval počet hlasů, ale tvé řešení jsem slepě neopsal, ale upravil si ho pro svou potřebu, takže jsem s ním naprosto spokojen.

*) Zkoušel jsem tvořit nějaké (pokud to tak lze nazvat) statistiky - například u kterých fotek jsou lidé nejvíce nerozhodní, takže mají hodně nul a málo +/-, nebo které jsou hodně kontroverzní s velkým počtem protikladných hlasů oproti malému zbytku ostatních hlasů… případně pro jiné potřeby.

[otula]

Samozřejmě, bylo to jinak, než jsem žádal, ale to byl pro mne nepodstatný detail, kterého jsem si hned všimnul a upravil. Potřeboval jsem poradit jak se to dělá, nikoliv postup step-by-step. Pro mne byla nosná myšlenka toho sčítání možností při daných podmínkách, která je ve své jednoduchosti naprosto geniální - takže to pro mne bylo řešením problému.

[kuka]

Neznam mysql, nicmene analogie tam jiste bude

select sum(decode(0, 1, 0)) nuly, sum(decode (1, 1, 0)) jednicky, sum(decode (-1, 1, 0), count(*) all ...

[otula]

Myšlenka stejná jako u předchozího příspěvku, decode ale v MySQL nefunguje. Každopádně děkuji za odpověď.

[otula]

Ahoj

Asi neco jako

select TC.photoid, TC.pocetcelkem, T_1.pocetminusjedna, T0.pocetnula, T1.pocetjedna
from
   (  select photoid, count(*) as pocetcelkem
         from tabulka
         group by photoid
   ) TC
left outer join
   (  select photoid, count(*) as pocetminusjedna
         from tabulka
         where rating=-1
         group by photoid
   ) T_1 on T_1.photoid = TC.photoid
left outer join
   (  select photoid, count(*) as pocetnula
         from tabulka
         where rating=0
         group by photoid
   ) T0 on T0.photoid = TC.photoid
left outer join
   (  select photoid, count(*) as pocetjedna
         from tabulka
         where rating=1
         group by photoid
   ) T1 on T1.photoid = TC.photoid

Akorat jsem to mozna spojil "opacne" (a mely by tam byt right outer join)

Dejv

[kuka]

Tak tohle je ucebnicovy priklad, jak neco co je vecne spravne je technicky naprosto desive. Teoreticky mozna existuje optimalizator, ktery pozna, ze tam zadny join nema byt a projde tabulku jen jednou, ale v databazich, co znam, to tak nebude. Self join kvuli interpretaci absolutni hodnoty by mel podle mne sokovat kazdeho a v neposledni rade tento zapis naprosto zamlzuje o co se vlastne jedna.

[otula]

Možná technicky děsivé, ale také funkční řešení. Sice využiju elegantní řešení z prvního příspěvku, ale tato konstrukce je pro mne zajímavá na prostudování, jak vlastně funguje (ať už je, či není optimální) - holt osud člověka, který se potřebuje rýpat v databázích jednou za pár let :)

[kuka]

Tak (relacni) databaze neni cerna skrinka. Pokud s ni clovek pracuje, mel by chpat, co je tabulka, index, join apod., je to podobne chapani slozitosti operaci pri "klasickem" programovani. Jinak vznikne paskvil. Kvadraticke razeni je jiste take funkcni a z hlediska zapisu muze byt dokonce svym zpusobem elegantni, v nekterych jazycich i jednoradkove, ale pro vetsi mnozinu dat jednoduse nedobehne. Na SQL je zradne, ze je deklarativni, takze vznika dojem, ze je uplne jedno, jak se co napise - realne tomu tak ale neni.

[otula]

To je mi jasné, že databáze není černá skříňka. Jen je pro mne problém, že sql dotazy potřebuji tvořit opravdu jen výjimečně, když něco vytvářím - při tom se vždy něco naučím, to, co jsem na začátku zvládal krkolomně, pak dělám levou zadní… Udělám co potřebuji, a po čase, až zase na databázi narazím, jsem opět úplně na začátku :)