Autor Zpráva
T-fon
Profil
Ahoj, mám tabulku, kde uživatelé hodnotí tvůrce a to můžou udělat několikrát.

 user_id |  tvurce_id  |  hodnoceni
------------------------------------------  
 1       |     1       |  3
 1       |     1       |  4
 1       |     1       |  3
 1       |     2       |  1
 1       |     2       |  4
 2       |     1       |  5
 2       |     1       |  3

Chtěl bych vytvořit tabulku, kde by se načetlo pořadí tvůrců dle průměru. Průměr by měl být vypočtený z průměrného hodnocení každého uživatele. Čili např. tvůrce s ID 1 nebude mít průměr (3+4+3+5+3)/5, ale ((3+4+3)/3+(5+3)/2)/2.
Zkoušel jsem něco takového:
SELECT AVG(prumer) AS hodnoceni FROM (SELECT tvurce_id, AVG(hodnoceni) AS prumer FROM rating GROUP BY tvurce_id) a
Dostanu jen jeden řádek. Prosím o radu, co je v dotazu špatně. Děkuji.
juriad
Profil
Ve vnitřním dotazu si vypočítáš průměrné hodnocení pro každého tvůrce a uživatele. Ve vnějším si z těchto hodnoceních vypočítáš průměr pro každého tvůrce. A pak to seřadíš sestupně podle vypočítaného průměru.

SELECT tvurce_id, AVG(hodnoceni) AS hodnoceni
FROM (
  SELECT user_id, tvurce_id, AVG(hodnoceni) AS hodnoceni
  FROM rating
  GROUP BY user_id, tvurce_id
) unikatni
GROUP BY tvurce_id
ORDER BY 2 DESC
T-fon
Profil
Díky moc, to je přesně ono.
T-fon
Profil
Mám ještě jeden dotaz: přidání Count(hodnoceni) AS pocet mi spočte už spojené položky. Jak bych mohl spočítat kompletně všechny řádky v tomto dotazu?
juriad
Profil
Musíš venku sečíst kolikrát každý uživatel hlasoval:

SELECT tvurce_id, AVG(hodnoceni) AS hodnoceni, SUM(pocet) AS pocet
FROM (
  SELECT user_id, tvurce_id, AVG(hodnoceni) AS hodnoceni, COUNT(*) AS pocet
  FROM rating
  GROUP BY user_id, tvurce_id
) unikatni
GROUP BY tvurce_id
ORDER BY 2 DESC
T-fon
Profil
Aha, jasně. Díky.
T-fon
Profil
Ahoj, mám ještě něco k tomuto tématu, resp. se jedná o složitější SQL dotaz k tomu, co mi napsal juriad.
Jde teď o to, že tento průměr z průměru ke konkrétnímu tvůrci by měl otestovat, zda user je level 1 nebo 2. V případě, že je level 2, má jeho hodnocení vyšší váhu, což se řeší tím, že se jeho hodnocení započítá 2x.
Vyřešil jsem to takto:
SELECT SUM(hodnoceni1) AS hodnoceni1, COUNT(pocet1) AS pocet1
FROM (
  SELECT user_id, tvurce_id, level,  AVG(hodnoceni) AS hodnoceni1, COUNT(*) AS pocet1
  FROM rating
  JOIN users ON user_id = users.id 
  GROUP BY user_id, tvurce_id
) unikatni1
WHERE tvurce_id = 50 AND level = 1 
GROUP BY tvurce_id
UNION
SELECT SUM(hodnoceni2) AS hodnoceni2, COUNT(pocet2) AS pocet2
FROM (
  SELECT user_id, tvurce_id, level,  AVG(hodnoceni) AS hodnoceni2, COUNT(*) AS pocet2
  FROM rating
  JOIN users ON user_id = users.id 
  GROUP BY user_id, tvurce_id
) unikatni2
WHERE tvurce_id = 50 AND level = 2 
GROUP BY tvurce_id

A pak ten celkový průměr spočítám zvlášť: $prumer = (hodnoceni1*1 + hodnoceni2*2)/(pocet1*1 + pocet2*2)
To *1 jsem tam dal jen pro názornost, že hodnocení userů z levelu 1 se započítává normálně, z levelu 2 se násobí dvěma.

Zaprvý nevím, jestli má smysl používat UNION nebo udělat 2 samostatné dotazy a zadruhý si myslim, že by se výsledek mohl získat přímo z dotazu, což je mimo mé schopnosti. Předem děkuji za jakoukoliv radu.
TomášK.
Profil *
Prolétl jsem to jen zběžně, myslím, že chceš toto:
SELECT SUM(level * hodnoceni1) AS hodnoceni1, COUNT(pocet1) AS pocet1
FROM (
  SELECT user_id, tvurce_id, level,  AVG(hodnoceni) AS hodnoceni1, COUNT(*) AS pocet1
  FROM rating
  JOIN users ON user_id = users.id 
  GROUP BY user_id, tvurce_id
) unikatni1
WHERE tvurce_id = 50
GROUP BY tvurce_id
T-fon
Profil
Díky, když bych udělal podobně ten pocet1, tak by to mělo být přesně ono. Pak by $prumer = hodnoceni1/pocet1.
A byla by možnost toto vyřešit i v případě, že levelů bude víc a jejich čísla nebudou odpovídat koeficientu? (Tzn. např. user na levelu 3 se bude násobit 5x).
TomášK
Profil
A byla by možnost toto vyřešit i v případě, že levelů bude víc a jejich čísla nebudou odpovídat koeficientu? (Tzn. např. user na levelu 3 se bude násobit 5x).

Ano, nejlépe si vytvořit další tabulku se sloupci (level, coeficient), najoinovat ji přes level a pak místo level násobit coeficientem. Aneb natvrdo SUM((CASE level WHEN 1 THEN 1 WHEN 2 THEN 5 ELSE 10 END)*hodnoceni)

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

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